SQL server maintains statistics on distribution of column values across the row. This helps SQL server to decide the right Execution Plan while reading data. We can see the statistics of a table by using SSMS.
If you see the properties of statistics, you can notice it capture the distribution of values in the table.
In the above case, it capture the selectivity of each National ID, This helps SQL server to know upfront how many rows a filter condition will fetch. These statistics are updated periodically if you configured your data base for “Auto Statistic Update”. You can also update the statistic by running TSQL Statement. The more your statistics are up to date, the SQL server can create better execution plan.
When you configured your DB for Auto create statistics. When ever you use a column in Join Clause or Where clause the SQL server will create an statistics on the column automatically.
When you configured your DB for Auto Update Statistics, the statistics are get updated when there is more then 20%+500 row changes happens on the table.
You can see the changes of a particular column (not table change) are tracked in the row counter. Using this SQL Server will automatically trigger the Update Statistics.
Based on your system need you can disable the Auto Create/Update Statistics, which will give some performance gain. But in such case you make sure that you have some manual mechanisms to have proper statistics and update on frequent intervals.
The problem with Auto Create/Update Statistics is the system will no know you Peek user time, system ideal time. Due to this Update Statistics may get triggered at you peek performance load. In such case disable Auto update feature and have a nightly schedule to update the statistics.
If you see the properties of statistics, you can notice it capture the distribution of values in the table.
In the above case, it capture the selectivity of each National ID, This helps SQL server to know upfront how many rows a filter condition will fetch. These statistics are updated periodically if you configured your data base for “Auto Statistic Update”. You can also update the statistic by running TSQL Statement. The more your statistics are up to date, the SQL server can create better execution plan.
UPDATE STATISTICS table_or_indexed_view_name
When you configured your DB for Auto create statistics. When ever you use a column in Join Clause or Where clause the SQL server will create an statistics on the column automatically.
When you configured your DB for Auto Update Statistics, the statistics are get updated when there is more then 20%+500 row changes happens on the table.
SELECT rowmodctr FROM SYSINDEXES WHERE ID = OBJECT_ID('P') AND name = '_WA_Sys_00000001_36D11DD4'
You can see the changes of a particular column (not table change) are tracked in the row counter. Using this SQL Server will automatically trigger the Update Statistics.
Based on your system need you can disable the Auto Create/Update Statistics, which will give some performance gain. But in such case you make sure that you have some manual mechanisms to have proper statistics and update on frequent intervals.
The problem with Auto Create/Update Statistics is the system will no know you Peek user time, system ideal time. Due to this Update Statistics may get triggered at you peek performance load. In such case disable Auto update feature and have a nightly schedule to update the statistics.
No comments:
Post a Comment