The Importance of SQL Server Statistics

It’s impossible to overstate just how important statistics are to SQL Server. Without them, SQL Server’s impressive cost-based optimizer simply wouldn’t even be an option.

The Need for Updated Statistics

As a rough and tumble analogy for gaining a sense of how important statistics are, image a team of individual treasure hunters combing a beach with metal-detectors.Then assume they’re working an area where they’re actually finding LOTS of treasure. Some of this treasure is tiny in the form of jewelry, trinkets, or single coins. Some is larger and takes the form of mast-heads, cannons, or even entire ‘treasure chests’. And, in this analogy, there’s also the possibility of running into entire shipwrecks – complete with literal mounds of treasure.

Then, imagine that for every found treasure, this team needs to decide HOW they’ll get each ‘find’ back up to their base of operations. At their disposal they’ve got additional team members – back at the base – waiting to be called down and fetch/retrieve treasure with the following tools:

Now, obviously, each of these ‘containers’ is ideally suited for different sizes and ‘types’ of treasure. And, in many ways, if you think about queries against your system/databases as being similar to these kinds of ‘treasure seeking’ activities, you’ll realize that queries come in all sorts of different sizes and shapes. Some (hopefully most) are bucket to wheel-barrow sized affairs where a treasure-hunter can quickly find ‘treasure’ (data), call in a bucket or a wheel-barrow to quickly zip in and grab the data, while other operations might require a few trips from a pickup or earth-mover. Only, each and every time you have to fire up an earth-mover (or pickup) and operate it on sand, you’re going to expect things to be a bit slower and more cumbersome to deal with.

And, in this regard, updated statistics are NOT quite the treasure-hunter NOR are they the retrieval mechanisms. Instead, statistics are MORE like the ‘assessment’ and ‘reporting’ process that a treasure-hunter would go through when they find a treasure and radio back to base for something to come in and retrieve the treasure. Because while it would be overkill to spin-up a pickup truck to come and grab a single earring, an even worse scenario would be finding a sunken galley full of treasure and assessing that a bucker or wheel-barrow would suffice – because, once committed, what you’d end up with would be a bucket/wheel-barrow making gobs and gobs and gobs and gobs of trips back and forth to fetch your data (or gold) and, obviously, you’d watch performance suffer.

Consequently, without accurate and up-to-date statistics, SQL Server can and WILL make the wrong assessment about what kinds of specific retrieval (or modification) mechanisms it should use when processing queries.

Additional Learning Resources

To learn more about the core importance of statistics, I highly recommend the following resources: