Along with the ability to create indexes (which you most definitely should be doing), SQL Server gives you the ability to create statistics. This helps SQL Server guess how many rows will come back for your searches, which can help it make better decisions on seeks vs scans, which tables to process first, and how much memory a query will need.
And I never do that.
I mean sure, I do it in training classes to show as a demo – and then I turn right around and show why it doesn’t usually get you across the query tuning finish line.
I used to think I was missing some kind of Super Secret Query Tuning Technique®, like I was just somehow doing it wrong, but then I stumbled across this note in Books Online’s how-to-create-statistics page and suddenly everything made sense:
Let me rephrase: before you even start playing around with statistics, make sure you haven’t taken away SQL Server’s ability to do this for you.
I like to make fun of a lot of SQL Server’s built-in “auto-tuning” capabilities that do a pretty terrible job. Cost Threshold for Parallelism of 5? MAXDOP 0? Missing index hints that include every column in the table? Oooookeydokey.
But there are some things that SQL Server has been taking care of for years, and automatically creating statistics is one of ’em. If you frequently query a column, you’re gonna get a statistic on it, end of story. You might have edge case scenarios where those statistics aren’t enough, but when you do, the fix isn’t usually to create a statistic.
The fix is usually to create the right indexes, which also happen to give you a free statistic anyway – but the index means that the data access itself will be faster.
Creating stats just tells SQL Server how bad the query is going to suck. Your users aren’t satisfied with that – they want the query to actually suck less. That’s where creating indexes comes in, and you should start your query tuning efforts there first.