I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?

2 Answers 2


Statistics simply are a form of dynamic metadata that assists the query optimizer in making better decisions. For example, if there are only a dozen rows in a table, then there's no point going to an index to do a lookup; you will always be better off doing a full table scan. But if that same table grows to a million rows, then you will probably be better off using the index. But if you query that table on a column that only has very few unique values (e.g. it might be a "sex" column containing only "M" or "F"), well actually a FTS might be better that that case because you would need to retrieve the block anyway to build the result set. Now say your table is 99% "M" and only 1% "F", we should FTS in one case or use an index in the other. Same table, same query, potentially four different query plans depending on the contents of the table. These kinds of things are the "statistics" and they are individual to each database - even two databases with identical table and index structure will have different statistics.

In summary, in a modern database engine there are two kinds of query optimization: rewriting the SQL (rule-based optimization, like a compiler rewrites your C to make it more efficient) and choosing the right path the the data (cost-based optimization, like a JIT compiler identifying hotspots at runtime). You only need to worry about this if you spot the query optimizer doing something obviously wrong (e.g. choosing FTS when you know an index would be better).

  • 3
    +1 I couldn't have explained it any better. It is important to remember that statistics are only helpful if they are kept up to date. This can be done with automatic updating statistics or a regularly scheduled (nightly/weekly) update statistics command. Also, index rebuilds will automatically perform a statistics update.
    – Matt M
    Commented Jan 24, 2011 at 0:11

They are used by the query optimiser (whitepaper on MSDN) to track distribution of values in indexes and/or columns.

Your only concern should be to update regularly: just leave the DB engine to do its stuff

  • 2
    Not always. There are times when creating a statistic manually can help query performance.
    – mrdenny
    Commented Jan 23, 2011 at 21:18
  • @mrdenny: How often have you done this in real life? Very rarely. I haven't IIRC. If it needs statistics one could argue it needs an index, which has statistics anyway. You can only create column statistics as per CREATE STATISTICS. Given OP had to ask about statistics I'd say this is a fair thing to do.
    – gbn
    Commented Jan 24, 2011 at 5:45
  • 2
    I've done it a couple of times, but not often. I was going against your "Your only concern should be to update regularly" statement, as updating stats isn't the only thing.
    – mrdenny
    Commented Jan 24, 2011 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.