As shown in the previous part of this series, inaccurate statistics can degrade SQL Server performance.We described how to work with SQL Server statistics using SQL Server Management Studio options and T-SQL.In this article, we will show how to update SQL Server statistics, what are the updating costs, and when updating is recommended.

sql updating a table from another table-81

In this example, we will use the Adventure Works database, the Person. The table contains 19,614 rows and we will insert additional 10,000 records. After updating table statistics, the correct values are shown. The All density value is calculated as 1/total number of distinct rows.

In the first case it’s 1/19,614 = 0.00005098099 = 5.098399102681758e-5 In the second, it’s: 1/29,614 = 0.00003376781 = 3.376781252110488e-5 The values are of the same order of magnitude and the difference can be neglected.

Even if the obsolete statistics are used, there will be almost no performance degradation.

Note that the number of sampled rows is the same as the total number of table rows.

Query Optimizer updates statistics whenever it determines it’s needed.

In some situations, statistics are not automatically updated and optimal performance is not provided.That’s when SQL Server statistics should be manually updated.Although updated SQL Server statistics provide better execution plans, keep in mind that updating requires time and query recompilation also, which can slow down SQL Server performance.Therefore, frequent statistics updates should be avoided.Another method to update statistics is to use the sp_updatestats stored procedure, but this is recommended only for advanced users, as the procedure updates statistics for all tables and indexed views in the database, which can significantly downgrade SQL Server performance. Although the statistics are inaccurate, the estimated query execution plan shows the correct number of records.If there were no data changes on the table, UPDATE STATISTICS will have no effect. On a table with frequent data changes, statistics old an hour can be obsolete and inaccurate. However, this is the case only for the tables with a small number of records, such as this one.