{"id":822,"date":"2017-03-23T06:00:48","date_gmt":"2017-03-23T13:00:48","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=822"},"modified":"2017-03-20T12:02:32","modified_gmt":"2017-03-20T19:02:32","slug":"sqlskills-sql101-updating-sql-server-statistics-part-i-automatic-updates","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-sql101-updating-sql-server-statistics-part-i-automatic-updates\/","title":{"rendered":"SQLskills SQL101: Updating SQL Server Statistics Part I \u2013 Automatic Updates"},"content":{"rendered":"

One of my favorite topics in SQL Server is statistics, and in my next two posts I want to cover how they are updated: either by SQL Server or by you.<\/p>\n

We\u2019ll start with updates by SQL Server, and these happen automatically. In order for automatic updates of statistics to occur, the AUTO UPDATE STATISTICS database option must be enabled for the database:<\/p>\n

\"Auto<\/a>
Auto Update Statistics option via SSMS<\/figcaption><\/figure>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

This option is enabled by default for every new database you create in SQL Server 2005 and higher, and it is recommended to leave this option enabled. If you\u2019re not sure if this option is enabled, you can check in the UI or you can use the following T-SQL:<\/p>\n

\r\nSELECT\r\n\t[name] [DatabaseName],\r\n\tCASE\r\n\t\tWHEN [is_auto_update_stats_on] = 1 THEN 'Enabled'\r\n\t\tELSE 'Disabled'\r\n\tEND [AutoUpdateStats]\r\nFROM [sys].[databases]\r\nORDER BY [name];\r\nGO\r\n<\/pre>\n
If you want to enable the option, you can run:<\/p>\n
\r\nUSE [master];\r\nGO\r\nALTER DATABASE [<database_name_here] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT;\r\nGO\r\n<\/pre>\n
With the option enabled, SQL Server marks statistics as out of date based on internal thresholds.<\/p>\n
For SQL Server 2014 and earlier, the threshold was 500 rows plus 20% of the total rows in a table. For example, if I have a table with 10,000 rows in it, when 2500 rows have changed, then SQL Server marks the statistic as out of date. There are exceptions to this (e.g. when a table has less than 500 rows, or if the table is temporary), but in general this threshold is what you need to remember.<\/p>\n
A new trace flag, 2371, was introduced in SQL Server 2008R2 SP1 to lower this threshold. This change was designed to target large tables. Imagine a table with 10 million rows; over 2 million rows would need to change before statistics would be marked as out of date. With trace flag 2371, the threshold is lower.<\/p>\n
In SQL Server 2016, the threshold introduced by trace flag 2371 is used if you have the compatibility mode for a database set to 130. This means that in SQL Server 2016, you only need to use trace flag 2371 to get that lower threshold if you have the database compatibility mode set to 120 or lower.
\nIf statistics have been marked as out of date, then they will be updated by SQL Server automatically the next time they are used in a query. Understand that they are not updated the moment they are out of date\u2026they are not updated until they are needed. Imagine the following scenarios using the original threshold:<\/p>\n
Example 1 – PhysicianData<\/strong><\/p>\n\n\n\n\n\n\n\n
Date\/Time<\/strong><\/td>\nAction<\/strong><\/td>\n<\/tr>\n
Sunday, March 19, 2017 2:00 AM<\/td>\nStatistics updated for table PhysicianData, which has 500,000 rows in it<\/td>\n<\/tr>\n
Monday, March 21, 6:00 AM<\/td>\nProcessing job runs, and 50,000 new rows are added to the PhysicianData table<\/td>\n<\/tr>\n
Tuesday, March 21, 6:00 AM<\/td>\nProcessing job runs, and 50,500 new rows are added to the PhysicianData table; statistics for PhysicianData are marked as out of date<\/td>\n<\/tr>\n
Tuesday, March 21, 7:35 AM<\/td>\nA user queries PhysicianData for the first time since processing ran at 6:00 AM; statistics for PhysicianData are updated<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
Example 2 – PatientData<\/strong><\/p>\n\n\n\n\n\n\n\n\n
Date\/Time<\/strong><\/td>\nAction<\/strong><\/td>\n<\/tr>\n
Sunday, March 19, 2017 2:00 AM<\/td>\nStatistics updated for table PatientData, which has 2,000,000 rows in it<\/td>\n<\/tr>\n
Monday, March 20, all day<\/td>\nDifferent processes and user activities access PatientData, adding new rows, changing existing rows.\u00a0 By the end of day 100,000 rows have changed or been added.<\/td>\n<\/tr>\n
Tuesday, March 21, all day<\/td>\nDifferent processes and user activities access PatientData, adding new rows, changing existing rows.\u00a0 By the end of day 250,000 rows have changed or been added.<\/td>\n<\/tr>\n
Wednesday, March 22, all day<\/td>\nDifferent processes and user activities access PatientData, adding new rows, changing existing rows.\u00a0 At 8:15PM, 400,500 rows have changed or been added.<\/td>\n<\/tr>\n
Wednesday, March 22, 8:16 PM<\/td>\nA user queries PatientData; statistics for PatientData are updated<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
I\u2019ve given two very contrived example to help you understand that statistics are not always updated the exact moment they are marked as out of date.\u00a0 They might be \u2013 if the table has a lot of activity, but they might not be.<\/p>\n
As I stated originally, it is recommended to leave this option enabled for a database.\u00a0 However, we do not want to rely on SQL Server for our statistics updates.\u00a0 In fact, think of this option as a safety net for statistics.\u00a0 We<\/em> want to control when statistics are updated, not SQL Server.\u00a0 Consider of the first scenario I described, where statistics updated at 7:35AM.\u00a0 If that\u2019s a busy time of day and this is a large table, it could affect performance in the system.\u00a0 It\u2019s preferable to have statistics updated when the system has less activity, so that resource use doesn\u2019t contend with user activity, but we always want to leave Auto Update Statistics enabled for a database…just in case.<\/p>\n
Additional Resources:<\/p>\n