CODEWITHSUNDEEP
sql-serverindexingsql-server-2008-r2database-performancepartitioning
Pedram
Pedram

Reputation: 828

Indexing and Partitioning: Improving SqlServer 2008 R2 query performance

I have a database in 'SQL Server 2008 R2' that its size is about 5 TB and it grows in size continuously. I have some problem with running a simple query on tbl1 with hundreds of million rows:

select x1,x2,x3
from tbl1
where date > '2017-04-03 00:00:00.000' and date < '2017-04-04 00:00:00.000'
      and mid = 300

this query takes about 20 seconds. I have two non-clustered indexes on date and mid columns and this query takes advantage of them.

What is the best idea for improving performance of select and insert in this table? (such as automatic partitioning)

I'm using Entity Framework, so I don't want to change the name of the table or partitioning it into some different names.

I appreciate any help.

Upvotes: 1

Views: 88

Answers (1)

Mike Nakis
Mike Nakis

Reputation: 61993

The way your question is stated it leads me to believe that you are under the impression that partitioning is something that you have to do manually, i.e. splitting a table into multiple tables each having a different name.

That's not the case.

With ms-sql-server, all you need to do in order to partition your tables and indexes is to issue the CREATE PARTITION commands. So, go ahead and look them up:

CREATE PARTITION FUNCTION

CREATE PARTITION SCHEME

So, in your case I would presume that you would partition on the date column, probably putting each year on a different partition, or possibly even each month on a different partition.

However, be aware that your question might be a case of an X-Y problem. The difficulty you are having seems to be performance related. You appear to have arrived at the conclusion that what you need to do in order to solve your problem is partitioning, so you are posting a question about partitioning. I am answering your question, but it may well be the case that your problem is not partitioning. It could be a large number of other things, for example locking: if your table is so huge and it is continuously growing, then what is probably happening is that rows are being continuously added to it, so it could be that your SELECTs are fighting against your INSERTs for access to the table.

Upvotes: 2

Related Questions