Performance Tuning Quiz #3 - Part 2

We're back with Part 2 of Quiz#3, showing you how to make SELECT COUNT(*) go faster against a simple table. As is often the case it turns out to be a case of needing a (better) index - did you figure it out? Did you get down to under 30 reads?

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Bob Hartmann on 6/1/2010

The index you add should be useful and something you are going to use. In this case the index is not useful for anything but COUNT(*) because its selectivity is very high (50%). If you add an index that is not useful and the table volatility is high, you will loose whatever performance you gain on COUNT(*) to slower updates. If the volatility is low then it is not clear why you would be doing lots of count queries. So, while this is a good teaching example, you should stress that adding indexes requires good justification and careful analysis.

MattP on 6/1/2010

Would it also be better to do SELECT COUNT(fieldname) as opposed to SELECT COUNT(*), or does it not make any difference?

Robert Djabarov on 6/1/2010

very inadequate selection of a bit column for a nonclustered index key.

Rudy on 6/1/2010

great video

Michael Emmons on 6/1/2010

To MattP: Yes, it does make a difference. From what I understand, if you SELECT COUNT(fieldname), your count will not include rows where the fieldname value is null.

Andy Jarman on 6/2/2010

Understand you want the smallest index possible to satisfy your query, but not sure its a 'best practise' to create a index on a bit column?

Charlie Bruno on 6/2/2010

I am curious as to how or why the query selects the non-clustered index to perform the query, rather than the clustered index. Thanks.

Greg Larsen on 6/2/2010

I'm sure only the people a MS could say for sure why the optimizer selected that index. Remember the query optimizer is a closely guarded secret. But if you think about it for a minute it might start to make sense why the optimizer selected this index. First what does this query need to do? It needs to count all the rows in the table. To do that it would need to use an index that contains an index value for every row. Since the NameStyle column does not allow nulls it will have a value for every row (or every clustered index key) in the leaf level pages. By quickly looking at the index space usage statistics SQL Server can quickly determine it will take less I/O to read the non-clustered index leaf pages verses reading the cluster index leaf pages. Greg

John on 6/2/2010

Thanks!

Rick Fonner on 6/2/2010

Very helpful presentation, now I understand why to use non clustered indexes.

kin on 6/3/2010

keep this kind of cool stuff coming!

Pradeep on 6/4/2010

Excellent video. Pls consider to do more videos of performance tuning. Thanks!

Swati Nandakumar on 6/4/2010

More in-depth would be very helpful and welcome

32DE12EB89 on 6/5/2010

This is really great stuff. Thanks for the performance videos as they help understand the underlying structures. It wasn't clear to my simple and confused mind why picjing the bit column helped - it didn't seem to be a part of the query.

daekyong on 6/8/2010

good information

mark brito on 6/12/2010

Kool, seems like the bit column index is faster because its less data to scan. Can you confirm this?

mark brito on 6/14/2010

Makes sense, a bit column, means its reading less data.

Ravi Raj on 6/25/2010

good article

James on 6/26/2010

nice

Ravi Raj on 6/30/2010

good one

samyan on 8/19/2010

bloody brilliant mate! Was literally taken aback that the nonclustered index read so much less pages. Thanks