Statistics is the one of the important factor which helps the optimizer to choose the right execution plan.At the same time, many of us (so called DBA) will not give enough attention to statistics while troubleshooting the performance issues.It might be because of lack of knowledge in statistics or lack of mechanism to figure out the stale statistics is the root cause of the performance issue.
The query optimizer use the statistics to determine the rows returned in each step.The estimated rows information in the execution plan is calculated based on the statistics available on the columns.The statistics gives the distribution of data with in the column.With out statistics, the query optimizer can not be determine the efficiency of different plan.By using the information stored in the statistics , the query optimizer can make right choice in accessing the data.
Each statistics in SQL server store information about density vector and Histogram.
Density
Density is the ratio of unique values with in the given column or a set of columns.Read More
Histogram
Histogram
Histogram gives the details of data distribution of leading column of the statistics. Read More
There are two types of the statistics. Index statistics and column statistics. Index statistics are created automatically when we define index on a table. The column statistics are always on single column and it is created automantically when the column is refered in a query. To create the a column statistics automatically ,the auto create statistics should be enabled in the database level.We have discussed in details about how the statistics are getting created, how the autoupdate and auto create statistics works, how stale statistics hits the performance in our post. Read here
SQL server update the statistics automatically after reasonable changes happened in the table. There is a threshold values for triggering auto update statistics.Is that default threshold is good enough for optimal performance? Do we really bothered about updating the statistics manually ? This post will answer for these question
In SQL server 2005 and 2008, it is difficult task to identify the statistics that need a manual updates. This post will give some idea to start with.
If you liked this post, do like my page on FaceBook
