Statistics in SQL Server are used to identify the type of plan that needs to be executed for a query. SQL Server is pretty good in estimating the statistics. But, there are some circumstances in which it does not, in that case, a better understanding of statistics is required. Moreover, how SQL Server finds outdated statistics in SQL Server is also important to resolve the performance problem. If a user is not having good command on statistics, then it is difficult for a user to handle queries without having proper indexes. Thus, in this post, what exactly is SQL Server Statistics and how to find the outdated statistics in SQL Server is discussed to improve the SQL Server performance.
What is SQL Server Statistics?
In simple words, statistics in the SQL Server are the base on which the indexes are built. If there are no accurate statistics, then the indexes simply do not work and moreover, the good execution plans is also not created. It is generally used by the query optimizer to determine an accurate execution plan for a particular column.
The statistics, which are collected by the SQL Server are the number of different values that occur for a given column or a set of columns. In addition, after sampling the data in the table, SQL Server identify the statistics. But, it all depends on the size of the table whether it has to sample large or small percentage of the rows.
For example, assume that a user is having a column in a table in which all responses of MCQ question are stored. In it, one-fourth of the values are A, half are B, and the another one-fourth are C. however, it can also be possible that SQL Server will have a look on only one percent of the rows and comes up with three different values. It is because the server has correctly estimated the number of different values and give correct results. So, for a large amount of data low sampling rate is always a good idea.
Method to Check SQL Server's Statistics
As everyone is making SQL Server at different rates and perform the comparison of those samples, then it becomes possible to determine how the computed density changes with the sampling rate. In particular, one can easily compare the default sampling rate of the SQL Server with the actual statistics. After comparison, the result will determine whether the SQL Server handle the updating of statistics or if one needs to create his/ her own statistic update scripts. Now, to perform comparison process, follow the steps given below:
- First, you need to identify all the statistics for a particular database by executing sp_autostats command on each table
- After that, execute the UPDATE STATISTICS on all tables with the help of default sampling rate
- Now, to record the estimated density values, run the DBCC SHOW_STATISTICS for every statistic
- Then, execute the UPDATE STATISTICS command along with the FULLSCAN option on each table
- Now, to record the actual density values, use DBCC SHOW_STATISTICS
- Now, compare both the estimated values & the actual values. If the density varies by a large amount, then one cannot use the SQL Server default statistics routines
From the above procedure, it is pretty much clear that, if there is a non-avoidable difference in both actual & estimated density value then, SQL Server statistics are outdated and needs to be updated quickly.
The Bottom Line
Statistics in SQL Server are very important from the performance point of view. It has a direct impact on the performance of the SQL Server. If the statistics are outdated then, it will degrade the performance really fast such as bad execution plan. Therefore, a manual method to find outdated statistics in SQL Server is covered above. It is simple to execute and does not take much time. One simply use it and identify whether they are working on outdated statistics or not.
