Microsoft SQL Servers collect statistical information on indexes and column data stored in each database. These statistics are used by the query optimizer to choose the most efficient way to execute your queries. Good statistics hep the optimizer to asses the cost of different query plans, then choose the “best” method to execute your query.

The term cost in this case is the price you pay in terms of time, cpu cycles, file IO, page scans, etc. All of which add up as a cost. The lower the price, the more efficient the query is. The reason I put best in quotes, is some times the statistics aren’t as up to date as they could be, and as a result, your queries will return slower than they really could, and as a result, the optimizer’s idea of best, may not really be the best you could get.

By default, SQL Server 2000 and 2005 have automatic create and update statistics turned on. As it should be, since Microsoft considers this a best practice. I have to say I agree with them. That way you can rely on the server engine to take care of itself, and you wont have to keep up with all the queries your fellow database developers are creating all the time. Plus, your database administrators can concentrate on managing the exceptions, rather than the rules.

In some situations you may need to manually create, update or drop statistics. I’d like to cover how to do that in this article, since you’ll need to know how in order to manage the exceptions.

CREATE STATISTICS

The following code will allow you to create a statistic on a table or indexed view.

CREATE STATISTICS statisticName
ON tableName | indexedViewName (columnName)

This is the least you’ll have to do to create a statistic. Provide the statistic’s name, the table or view name, and a list of the columns you want to collect statistics on. There are additional variables you can set, but as a 200 level course, I’m not going to dig into that too deeply, check the Books online for more information.

UPDATE STATISTICS

The following will allow you to update a specific statistic for a table or a view.

UPDATE STATISTICS tableName | indexedViewName statisticName |indexName

It’s really that easy, just provide the table name or indexed view name, and then the statistic’s name or an index name. Yes, there are statistics on indexes as well as columns! If you have a poorly performing query, this one can often be the difference between minutes and sub-second response times!

DROP STATISTICS

Occasionally, you’ll find you have absolutely no use for a statistic… in those cases, you’ll want to get rid of it, just so that you don’t have to deal with it anymore. To do that… check this out:

DROP STATISTICS tableName.statisticsName | viewName.statisticsName

Just provide the table or view name then dot, then the statistics name. If you’re unfamiliar with dotted notation, please check my archive for information on that… it makes notation pretty simple.

That’s pretty much all you’ll need to get started with statistics. Keep them in mind when you’re trying to debug long running queries. Like I said before, they can be the difference in very slow queries and speedy queries. As usual, if you have any questions, send them in! I’m here to help!