![\"Update](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/06\/update-stats.jpg\")
<\/a>I don\u2019t like this option because it means that statistics that have had little or no change will be updated.\u00a0 I could have a 10 million row table where only 1000 rows change, and yet the statistics for that table will update.\u00a0 This is a waste of resources.\u00a0 For a small database, or system that\u2019s not 24×7, that isn\u2019t such a big deal.\u00a0 But in a database with multiple 10 million row tables, it is<\/em> a big deal.<\/p>\n The sp_updatestats command isn\u2019t a favorite of mine either.\u00a0 I\u2019ve written about that here<\/a>, so I won\u2019t re-hash it.<\/p>\n If you have used Ola Hallengren\u2019s scripts<\/a> for maintenance, you hopefully know that it will also update statistics using the @UpdateStatistics parameter.\u00a0 The default value for this is NULL, which means do not update statistics.\u00a0 To be clear, if you drop in Ola\u2019s scripts and have it create the jobs for you, and then you start running the \u201cIndexOptimize \u2013 USER_DATABASES\u201d job, by default you\u2019re not updating statistics.\u00a0 The code the IndexOptimize \u2013 USER_DATABASES job has, by default, is:<\/p>\n If you want to have the job also update statistics, you need:<\/p>\n With this variation, we are updating index and column statistics, which is great.\u00a0 But\u2026we are updating them regardless of whether it\u2019s needed.\u00a0 Statistic with no rows modified? Update it.\u00a0 Statistic with 10 rows modified? Update it.<\/p>\n There has always been an option to only update statistics that have changed, this is the @OnlyModifiedStatistics option, and this gets us behavior just like sp_updatestats.<\/p>\n With this option, if no rows have changed, the statistic will not be updated.\u00a0 If one or more rows have changed, the statistic will be updated.<\/p>\n Since the release of SP1 for 2012, this has been my only challenge with Ola\u2019s scripts.\u00a0 In SQL Server 2008R2 SP2 and SQL Server 2012 SP1 they introduced the sys.dm_db_stats_properties<\/a> DMV, which tracks modifications for each statistic<\/a>.\u00a0 I have written custom scripts to use this information to determine if stats should be updated, which I\u2019ve talked about here<\/a>.\u00a0 Jonathan has also modified Ola\u2019s script for a few of our customers to look at sys.dm_db_stats_properties to determine if enough data had changed to update stats, and a long time ago we had emailed Ola to ask if he could include an option to set a threshold.\u00a0 Good news, that option now exists!<\/em><\/p>\n Using Ola’s script to update statistics based on a threshold of change<\/strong><\/p>\n With the IndexOptimize stored procedure<\/a>\u00a0Ola now includes the option of @StatisticsModificationLevel.\u00a0 You can use this to set a threshold for modifications, so that only statistics with a specific volume of change are updated.\u00a0 For example, if I want statistics updated if 5% of the data has changed, use:<\/p>\n Take note: the option @OnlyModifiedStatistics option is not included here…you cannot use both options, it has to be one or the other.<\/em><\/span><\/p>\n This is great!\u00a0 I can further customize this for\u00a0different tables.\u00a0 Consider a database that has a very volatile table, maybe dbo.OrderStatus, where auto-update may or may not kick in during the day, so I want to make sure stats are updated nightly:<\/p>\n This will address fragmentation and update statistics for all tables in the\u00a0SalesDB database except<\/em>\u00a0dbo.OrderStatus, and it will update statistics if 10% or more of the rows have changed.<\/p>\n I would then have a second job to address fragmentation and stats for OrderStatus:<\/p>\n For the dbo.OrderStatus table, statistics would be updated when only 1% of the data had changed.<\/p>\n I love the flexibility this provides!<\/p>\n You might be wondering why I chose 1%…take a close look at this important note which is included in Ola\u2019s documentation:<\/p>\n Statistics will also be updated when the number of modified rows has reached a decreasing, dynamic threshold, SQRT(number of rows * 1000)<\/span><\/p><\/blockquote>\n This is critical to understand because if the threshold I have set for @StatisticsModificationLevel ends up having a number of rows HIGHER than the formula above, statistics will update sooner than I expect.<\/p>\n For example, if I have 1 million rows in a table and I have @StatisticsModificationLevel = 10, then 10% of the rows, or 100,000, have to change in order to update statistics.\u00a0 HOWEVER, if you plug 1 million into SQRT(1,000,000 * 1000), you get 31,623, which means Ola\u2019s script will update statistics after 31,623 rows have changed\u2026well before 100,000.<\/p>\n This may be important for some of you to understand in terms of these thresholds, so I dropped the information into a table to make it easier to comprehend (at least, it\u2019s easier for me!).<\/p>\n Using my original example, if\u00a0dbo.OrderStatus\u00a0has about one million rows, then with 1% as the threshold, only 10,000 rows need to change before stats are updated.\u00a0 If the SQRT algorithm were used, over 30,000 rows would need to change before stats were updated, and depending on the data skew, that might be too high.<\/p>\n Understand that as tables get larger, statistics will likely be updated before<\/em> the set percentage value is reached because the SQRT algorithm has a lower threshold.\u00a0 (Yes, I’m driving this point home.)\u00a0 Consider a table with 10\u00a0million rows.\u00a0 If I set the threshold to 5%, I would\u00a0expect\u00a0statistics to update after 500,000 modifications, but in fact they will update after 100,000.<\/p>\n If you\u2019re wondering where the SQRT algorithm comes from, please review Microsoft\u2019s Statistics documentation<\/a>.\u00a0 This threshold was originally introduced with trace flag 2371<\/a> to lower the threshold for automatic updates.\u00a0 It is applied by default started in SQL Server 2016 when using compatibility level 130.\u00a0 My assumption is that Ola determined this was a good threshold to use as a fail-safe\/catch-all for his script, and I think it was smart move on his part.\u00a0 In general, I\u2019d rather have statistics update too often, rather than not often enough.\u00a0 However, using the new @StatisticsModificationLevel option gives us better control than we\u2019ve had previously, unless we write a custom script (which is still an option\u2026do what works best for you!).<\/p>\n","protected":false},"excerpt":{"rendered":" I am a HUGE fan of updating statistics as part of regular maintenance.\u00a0 In fact, if you don\u2019t know if you have a step or job that updates out of statistics on a regular basis, go check now!\u00a0 This post will still be here when you get back 😊 At any rate, for a long […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"yoast_head":"\n\r\nEXECUTE [dbo].[IndexOptimize]\r\n@Databases = 'USER_DATABASES',\r\n@LogToTable = 'Y'\r\n<\/pre>\n
\r\nEXECUTE [dbo].[IndexOptimize]\r\n@Databases = 'USER_DATABASES',\r\n@UpdateStatistics = 'ALL',\r\n@LogToTable = 'Y'\r\n<\/pre>\n
\r\nEXECUTE [dbo].[IndexOptimize]\r\n@Databases = 'USER_DATABASES',\r\n@UpdateStatistics = 'ALL',\r\n@OnlyModifiedStatistics = 'Y',\r\n@LogToTable = 'Y'\r\n<\/pre>\n
\r\nEXECUTE [dbo].[IndexOptimize]\r\n@Databases = 'USER_DATABASES',\r\n@UpdateStatistics = 'ALL',\r\n@StatisticsModificationLevel= '5',\r\n@LogToTable = 'Y'\r\n<\/pre>\n
\r\nEXECUTE [dbo].[IndexOptimize]\r\n@Databases = 'USER_DATABASES',\r\n@Indexes = 'ALL_INDEXES, -SalesDB.dbo.OrderStatus',\r\n@UpdateStatistics = 'ALL',\r\n@StatisticsModificationLevel= '10',\r\n@LogToTable = 'Y'\r\n<\/pre>\n
\r\nEXECUTE [dbo].[IndexOptimize]\r\n@Databases = 'USER_DATABASES',\r\n@Indexes = 'SalesDB.dbo.OrderStatus',\r\n@UpdateStatistics = 'ALL',\r\n@StatisticsModificationLevel= '1',\r\n@LogToTable = 'Y'\r\n<\/pre>\n
<\/a>