4

Assuming i have a table X with 12 columns . in my queries i am filtering on these conditions

select * from x where a=@a and b=@b

select *  from x where a=@b and b=@b and c=@c

select * from x  where a=@b and b=@b and d=@d

select * from x where a=@b and b=@b and e=@e

This table is very active table and i need to avoid blocking. If i need to create indexes on this table should i create 4 indexes.

 (a,b) (include columns) 

 (a,b,c) (include columns) 

 (a,b,d) (include columns) 

 (a,b,e) (include columns) 

Or

(a,b) (include columns)

(c)

(d)

(e) 

Or create 1 index (a,b,c,d,e) (include columns)

these are the counts of distinct values for each column. total rows 1446631 , a = 366279 , b= 96 , c = 6 , e = 2 , d= 11098

Thank you

  • 1
    What is your Clustered Index on this table? Could it be on A, B? – Arthur D May 27 '16 at 16:02
  • @ArthurD No they are not the primary key. – sebeid May 27 '16 at 16:06
  • 7
    Be careful here: clustered index and primary key are completely different concepts. It happens to be that in SQL Server, when you create a primary key, it's clustered by default. But it doesn't have to be. – Aaron Bertrand May 27 '16 at 16:21
9

If all of your query patterns include filters on both a and b, and the table gets written to a lot, I would argue for testing a single index that looks like this:

(a,b) include (c,d,e,other include columns)

The reason is it's a single index to maintain rather than three or four, and the selectivity of the third column in the filter probably won't add much additional benefit to individual queries since the first two columns already filter out most of the table (well, really, the first column, since the second column has very few distinct values). You can monitor the queries and if see if any patterns or particular parameter values lead to horribly bad estimates, long runtimes, or missing index warnings. I suspect they won't because, again, based on the density you've quoted, this should lead to very small ranges as long as your queries use filters on a and b or, at the very least, a.

I will stress that where you end up is not going to be a simple "oh, this is clearly what you should do" answer. You will need to test with your hardware, your data, and your query patterns (both read and write) to determine the best index(es) for your workload.

2

As with all things it depends. Specifically on the cardinality of the columns. If the combination of columns a and b is unique then you really only need the one index (a,b). If it's even close (say < 100 rows each) you'll probably be ok. On the other hand let's say that c has a high cardinality (large number of possible values) and d has a low cardinality (low number of possible values). You might create just create (a,b,c). This will cover the specific query using a,b and c. It will also just as easily cover a query using only a and b. And for a, b and d it will come close. SQL will get close using a and b and then can scan through the remaining rows given that d isn't going to be a whole lot of help anyway.

All things being equal and assuming a reasonable cardinality of each I would create the following.

(a,b,c) (include columns) 

(a,b,d) (include columns) 

(a,b,e) (include columns) 

The query using only a and b will be covered by the others. Assuming that all of the other columns are covered in the include columns of course.

  • thanks .. these are the counts of distinct values for each column. total rows 1446631 , a = 366279 , b= 96 , c = 6 , e = 2 , d= 11098 – sebeid May 27 '16 at 16:15
  • 1
    Yea, at that point one index (a, b, d) include (c,e) – Kenneth Fisher May 27 '16 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.