Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 19 down vote accepted

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).

share|improve this answer
1  
+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 Jan 24 '11 at 0:11
add comment

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

share|improve this answer
1  
Not always. There are times when creating a statistic manually can help query performance. –  mrdenny Jan 23 '11 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 Jan 24 '11 at 5:45
1  
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 Jan 24 '11 at 23:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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