Actually, just to add to this – SQL Server will do a leaf level scan of the smallest nonclustered index for count(*) as well as count(not-nullable column). So, if you’re particularly interested in a count – having a small nc to scan is very important. In fact, a small/interesting trick is to create a nonclustered index on your clustering key. That’s the smallest index that could exist (but, it has VERY limited uses).

Thanks for writing that up 5 years ago Paul…came in helpful today. Was trying to figure out if SELECT COUNT(*) was worse than SELECT COUNT(pk_id) and if not, why? I could’ve sworn I heard you say once that SELECT COUNT(pk_id) was better…but apparently not. This post explains it. Tried Kimberly’s “trick” as well on a wide 24 million row table. Realized that the optimizer was already using a non-clustered index on another INT field. Once I added the non-clustered index on the pk field the optimizer used the new index, but with only a slight performance gain. Now I’m tempted to drop all the non-clustered indexes and see what happens to performance….. I did find that using/changing MAXDOP hint affected performance and that in this case of a 4 proc VM using MAXDOP(2) had better performance over MAXDOP(1) or no hint.