Tell me more ×
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.
up vote 5 down vote favorite
2

I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file.

My question is, how exactly do statistics aid in performance?

share|improve this question

2 Answers

up vote 5 down vote accepted

statistics is used when query execution plan created.

Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, and in doing so improve query performance.

See http://msdn.microsoft.com/en-us/library/ms190397.aspx for more details.

share|improve this answer
1  
great link and description. Is it always beneficial to have statistics created on tables that are queried often? – Thomas Stringer Aug 16 '11 at 1:46
yes, we must be sure that statistics for most-readable tables are always adequate. If your table is write-intensive, it is possible that you need to update statistics, for example, every night. – Alex_L Aug 16 '11 at 1:52
2  
@Surfer513 - be careful: creating statistics can have a detrimental effect. The better information you are giving the planner may make it choose a different execution path and because this is a game of probabilities, it is possible that this path will be slower (even if it is likely to be faster in general). In other words, on a production database, test before you implement. – Jack Douglas♦ Aug 16 '11 at 12:03
up vote 3 down vote

If DTA recommended a single column statistic, you must have auto create stats turned off? Auto create will build stats on any column used in a predicate, automatically, so generally its a good idea to leave it switched on. Check the status of auto update for this database, which again should usually be on.

One of the best uses of DTA is for identifying missing multi-column statistics, which auto create won't build. But for single column, auto create should be taking care of them for you.

share|improve this answer

Your Answer

 
discard

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