The auto-update statistics configuration updates statistics when the amount of row changes in the table achieves 20% of total rows.<\/p>\n
However, for tables with too many rows, this threshold is too high. For example, a table with 1 million rows will only update statistics after 200 thousands changes. That’s too much and can affect query plans and query performance.<\/p>\n
We can enable trace flag 2371 to change this behavior. Using this trace flag, threshold to trigger the auto-update statistics become dynamic. After 25k rows in the table, the amount of updates starts to decrease. A table with 1M rows needs around 3.2% of updates to trigger the auto-update statistics.<\/p>\n
Even with this trace flag enabled, an important administrative task is identifying if the auto-update statistics is running often enough and, if not, schedule the update statistics using a SQL Agent job.<\/p>\n
We can find when the last update statistics happened and how many row modifications happened since the last update statistics using sys.dm_db_stats_properties DMV with a few more joins.<\/p>\n
SELECT\nSCHEMA_NAME([o].[schema_id]) AS [schema], [o].[name] as [TABLE],\n[s].[name] AS Statistic,\n[sp].[last_updated],\n[sp].[rows],\n[sp].[rows_sampled],\n[sp].[modification_counter] AS [Modifications]\nFROM \n [sys].[stats] AS [s] inner join sys.stats_columns sc\n on s.stats_id=sc.stats_id and s.object_id=sc.object_id\n inner join sys.columns c\n on c.object_id=sc.object_id and c.column_id=sc.column_id\n inner join sys.objects o\n on s.object_id=o.object_id\n inner join sys.schemas sch\n on o.schema_id=sch.schema_id\n OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]\nwhere sch.name<>'sys'\nand (sp.modification_counter > 1000 or (sp.modification_counter * 100) > sp.rows) -- removes rows with too few modifications\norder by [Percent] desc,last_updated desc -- the result in order of priority<\/pre>\n<\/p>\nLet’s create a user defined function to make all the work easier. The function StatisticsModifications will retrieve information about all statistics in the database:<\/p>\n
CREATE FUNCTION StatisticsModifications ()\nRETURNS TABLE \nAS\nRETURN \n(\nSELECT\nSCHEMA_NAME([o].[schema_id]) AS [schema], [o].[name] as [TABLE],\n[s].[name] AS Statistic,\n[sp].[last_updated],\n[sp].[rows],\n[sp].[rows_sampled],\n[sp].[modification_counter] AS [Modifications]\nFROM \n [sys].[stats] AS [s] inner join sys.stats_columns sc\n on s.stats_id=sc.stats_id and s.object_id=sc.object_id\n inner join sys.columns c\n on c.object_id=sc.object_id and c.column_id=sc.column_id\n inner join sys.objects o\n on s.object_id=o.object_id\n inner join sys.schemas sch\n on o.schema_id=sch.schema_id\n OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]\nwhere sch.name<>'sys'\n\n)<\/pre>\nNow we can use this function with some filters to retrieve the most out dated statistics.<\/p>\n
select [schema], [Table],\n [Statistic],\n [last_updated], [rows],\n [rows_sampled], [Modifications], \n(100 * modifications)\/[rows] [Percent] \nfrom dbo.statisticsmodifications()\n\/* removes rows with too few modifications *\/\nwhere (modifications > 1000 or (modifications * 10) > [rows]) \norder by [Percent] desc,last_updated desc<\/pre>\nUsing the result of the above query you can identify if the update statistics are running in regular intervals or if you need to schedule the update statistics.<\/p>\n","protected":false},"excerpt":{"rendered":"
The auto-update statistics configuration updates statistics when the amount of row changes in the table achieves 20% of total rows. However, for tables with too many rows, this threshold is too high. For example, a table with 1 million rows will only update statistics after 200 thousands changes. That’s too much and can affect query……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-8898","post","type-post","status-publish","format-standard","hentry","category-other"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8898","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=8898"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8898\/revisions"}],"predecessor-version":[{"id":42545,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8898\/revisions\/42545"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8898"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8898"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8898"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8898"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}