{"id":567,"date":"2016-01-14T15:29:51","date_gmt":"2016-01-14T21:29:51","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=567"},"modified":"2016-02-02T16:15:34","modified_gmt":"2016-02-02T22:15:34","slug":"database-statistics-health-and-update-scripts","status":"publish","type":"post","link":"https:\/\/practical-prints.com\/?p=567","title":{"rendered":"Database Statistics Health and Update Scripts"},"content":{"rendered":"<p>I like using <a href=\"https:\/\/ola.hallengren.com\/sql-server-index-and-statistics-maintenance.html\" target=\"_blank\">Ola Hallengren&#8217;s<\/a> maintenance scripts. Super configuratible, logging, SQl Agent job creation and etc. Sometimes I want to spot check on some of the statistics. Stale statistics can cause cardinality issues with SQL plans.<\/p>\n<p style=\"padding-left: 30px;\">We learn how\u00a0<a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms190397.aspx\" target=\"_blank\">database statistics<\/a>\u00a0are used:<br \/>\n<em>&#8220;The query optimizer uses statistics to create query plans that improve query performance. For most queries, the query optimizer already generates the necessary statistics for a high quality query plan; in a few cases, you need to create additional statistics or modify the query design for best results. This topic discusses statistics concepts and provides guidelines for using query optimization statistics effectively.&#8221;<\/em><\/p>\n<p>The sample rate is set to\u00a0full s or 100 percent of the rows will be scanned. A full scan will create overhead.\u00a0 Need to always look at the impact to your environment.\u00a0You could have old statistics and not used or duplicate statistics. \u00a0In some cases, the higher sample rate can actually cause the plan be less optimal.<\/p>\n<p>Rule of thumb, your queries will perform better with fresh statistics.<\/p>\n<p>I have two scripts to I use to find the health of statistics and <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms187348.aspx\" target=\"_blank\">update statistics<\/a> based upon certain criteria.<br \/>\n<!--more--><\/p>\n<p>The first script is used to look at the statistics with some parameters that can be set to drill down into you environment. It does not update the statistics. Like every script on this site, you need to tweak it to your specific needs. Note the use of <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms178545.aspx\" target=\"_blank\">INDEX_COL()<\/a> to grab the columns in that statistic. The example shows the first four columns, but statistics can have more. You will just add a line and increment the last number. I played around with returning all the stats in one column, but failed.<\/p>\n<p>DISCLAIMER &#8212; These scripts have been adapted from several scripts found online. I am not sure of the foundation code of these scripts. In the very least, some of the syntax is from <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/jj553546.aspx\" target=\"_blank\">here<\/a>.<\/p>\n<pre lang=\"tsql\">\/*\r\n\/*\r\nStatistics Information(DMV)\r\nSimple query to evaluate health of database statistics.\r\nJCD 1\/14\/2016\r\nhttps:\/\/jackdonnell.com\r\n*\/\r\nSET NOCOUNT ON \r\nGO\r\n\r\nDECLARE @rows_less_than BIGINT -- < = Rows in Table\r\n,@rows_greater_than BIGINT -- <= Rows in Table\r\n,@modification_rows BIGINT -- >= Modication count\r\n,@modification_percent DECIMAL(16,5) -- >= % modification (modification count \/ rows)\r\n,@sample_percent DECIMAL(16,5) --  < = % sample ( rows samples\/ rows) \r\n,@lastupdated_less_than DATE -- last updated < this date\r\n,@lastupdated_greater_than DATE -- last updated > this date\r\n,@update_state_command NVARCHAR(1200) -- sql executed \r\n,@count INT -- number of statistics to update\r\n\r\n\r\nSET @rows_less_than =3550000\r\nSET @rows_greater_than = 1500\r\nSET @modification_rows =  1\r\nSET @modification_percent = 0.505\r\nSET @sample_percent = NULL\r\nSET @lastupdated_less_than = GETDATE() -3\r\nSET @lastupdated_greater_than = NULL\r\n-------------------------------------------------------------------------\r\n-----          No need to edit below this line.                     ----- \r\n-------------------------------------------------------------------------\r\n\r\nSELECT DISTINCT\r\nSERVERPROPERTY('ServerName') [ServerName]\r\n,DB_NAME() [database_name]\r\n,QUOTENAME(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+QUOTENAME(Object_name(stat.object_id)) [table_name]\r\n,sp.stats_id\r\n,stat.name [stats_name]\r\n,sp.last_updated\r\n,DATEDIFF(DAY,sp.last_updated,GETDATE())[days_since_last_updated]\r\n,CAST(((sp.rows_sampled*1.000)\/sp.rows *100.000) as DECIMAL(12,4)) [percent_sampled]\r\n,sp.rows\r\n,sp.rows_sampled\r\n,sp.modification_counter \r\n,CASE WHEN sp.modification_counter = 0.00000000   then 0. else CAST(((sp.modification_counter *1.000)\/sp.rows *100.000) as DECIMAL(16,5))  END [modification_percent]\r\n,index_col(RTRIM(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+RTRIM(Object_name(stat.object_id)), stat.stats_id,1)\t[stat_column_1]\r\n,ISNULL(index_col(RTRIM(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+RTRIM(Object_name(stat.object_id)), stat.stats_id,2),'')\t[stat_column_2]\r\n,ISNULL(index_col(RTRIM(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+RTRIM(Object_name(stat.object_id)), stat.stats_id,3),'')  [stat_column_3]\r\n,ISNULL(index_col(RTRIM(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+RTRIM(Object_name(stat.object_id)), stat.stats_id,4),'')  [stat_column_4]\r\n,'UPDATE STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(stat.[object_id]))+'.'+QUOTENAME(OBJECT_NAME(stat.[object_id]))+' '+QUOTENAME(stat.[name])  + ' WITH FULLSCAN; '[update_statementFULLSCAN]\r\n,'DBCC SHOW_STATISTICS (N'''+OBJECT_SCHEMA_NAME(stat.[object_id]) +'.'+Object_name(stat.object_id)+''', '''+stat.name+''') WITH NO_INFOMSGS;' [get_info] ---WITH STATS_STREAM,NO_INFOMSGS;'\r\n,'DBCC SHOW_STATISTICS (N'''+OBJECT_SCHEMA_NAME(stat.[object_id]) +'.'+Object_name(stat.object_id)+''', '''+stat.name+''') WITH HISTOGRAM;' [get_histogram] ---WITH STATS_STREAM,NO_INFOMSGS;'\r\nFROM sys.stats AS stat with(NOLOCK)\r\nCROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp\r\nLEFT JOIN sys.indexes as ix WITH (NOLOCK)\r\non stat.[object_id] = ix.[object_id]\r\nWHERE 1=1\r\nAND objectproperty(stat.[object_id], 'ISMSShipped')= 0\r\nAND sp.last_updated < = ISNULL(@lastupdated_less_than,'2078-01-01')\r\nAND sp.last_updated >= ISNULL(@lastupdated_greater_than,'1900-01-01')\r\nAND CAST(((sp.rows_sampled*1.00)\/sp.rows *100.00) as DECIMAL(6,2)) < ISNULL(@sample_percent,101)\r\nAND sp.rows <=  ISNULL(@rows_less_than, 999999999999)\r\nAND sp.rows >=  ISNULL(@rows_greater_than, 0)\r\nAND CASE WHEN sp.modification_counter = 0.00000000   then 0. else CAST(((sp.modification_counter *1.000)\/sp.rows *100.000) as DECIMAL(16,5))  END >= ISNULL(@modification_percent, 0)\r\nAND sp.modification_counter >= ISNULL(@modification_rows,0)\r\nORDER BY 3, sp.stats_id\r\nOPTION (MAXDOP 1) -- Restrict MAXDOP for query\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p>The following script updates statistics based upon certian criteria. You need to verify you criteria be for launching. For example, you could have a logging or archiving table that is HUGE and rarely used. You may not want to polute up your buffer cache with pages that will not be used. It will tank your page life expectancy and cause the system to go to disk for &#8220;useful&#8221; pages. This example uses a full scan.<\/p>\n<pre lang=\"tsql\">\/*\r\n\/*\r\nStatistics Information(DMV)\r\nSimple query to evaluate health of database statistics.\r\nJCD 1\/14\/2016\r\nhttps:\/\/jackdonnell.com\r\n*\/\r\nSET NOCOUNT ON \r\nGO\r\n\r\nDECLARE @rows_less_than BIGINT -- < = Rows in Table\r\n,@rows_greater_than BIGINT -- <= Rows in Table\r\n,@modification_rows BIGINT -- >= Modication count\r\n,@modification_percent DECIMAL(16,5) -- >= % modification (modification count \/ rows)\r\n,@sample_percent DECIMAL(16,5) --  < = % sample ( rows samples\/ rows) \r\n,@lastupdated_less_than DATE -- last updated < this date\r\n,@lastupdated_greater_than DATE -- last updated > this date\r\n,@update_state_command NVARCHAR(1200) -- sql executed \r\n,@count INT -- number of statistics to update\r\n\r\nSET @rows_less_than = 19950000\r\nSET @rows_greater_than = 1500\r\nSET @modification_rows =  1\r\nSET @modification_percent = 0.505\r\nSET @sample_percent = NULL\r\nSET @lastupdated_less_than = '2016-01-30'\r\nSET @lastupdated_greater_than = NULL\r\n\r\n-------------------------------------------------------------------------\r\n-----          No need to edit below this line.                     ----- \r\n-------------------------------------------------------------------------\r\nWHILE(\r\nSELECT  COUNT(DISTINCT stat.name)\r\nFROM sys.stats AS stat with(NOLOCK)\r\nCROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp\r\nLEFT JOIN sys.indexes as ix WITH (NOLOCK)\r\non stat.[object_id] = ix.[object_id]\r\nWHERE 1=1\r\nAND objectproperty(stat.[object_id], 'ISMSShipped')= 0\r\nAND sp.last_updated < = ISNULL(@lastupdated_less_than,'2078-01-01')\r\nAND sp.last_updated >= ISNULL(@lastupdated_greater_than,'1900-01-01')\r\nAND CAST(((sp.rows_sampled*1.00)\/sp.rows *100.00) as DECIMAL(6,2)) < ISNULL(@sample_percent,101)\r\nAND sp.rows <=  ISNULL(@rows_less_than, 999999999999)\r\nAND sp.rows >=  ISNULL(@rows_greater_than, 0)\r\nAND CASE WHEN sp.modification_counter = 0.00000000   then 0. else CAST(((sp.modification_counter *1.000)\/sp.rows *100.000) as DECIMAL(16,5))  END >= ISNULL(@modification_percent, 0)\r\nAND sp.modification_counter >= ISNULL(@modification_rows,0)\r\n) > 0\r\n\r\nBEGIN\r\n\tSELECT DISTINCT TOP 1 \r\n\t@update_state_command = 'UPDATE STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(stat.[object_id]))+'.'+QUOTENAME(OBJECT_NAME(stat.[object_id]))+' '+QUOTENAME(stat.[name])  + ' WITH FULLSCAN; '\r\nFROM sys.stats AS stat with(NOLOCK)\r\nCROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp\r\nLEFT JOIN sys.indexes as ix WITH (NOLOCK)\r\non stat.[object_id] = ix.[object_id]\r\nWHERE 1=1\r\nAND objectproperty(stat.[object_id], 'ISMSShipped')= 0\r\nAND sp.last_updated < = ISNULL(@lastupdated_less_than,'2078-01-01')\r\nAND sp.last_updated >= ISNULL(@lastupdated_greater_than,'1900-01-01')\r\nAND CAST(((sp.rows_sampled*1.00)\/sp.rows *100.00) as DECIMAL(6,2)) < ISNULL(@sample_percent,101)\r\nAND sp.rows <=  ISNULL(@rows_less_than, 999999999999)\r\nAND sp.rows >=  ISNULL(@rows_greater_than, 0)\r\nAND CASE WHEN sp.modification_counter = 0.00000000   then 0. else CAST(((sp.modification_counter *1.000)\/sp.rows *100.000) as DECIMAL(16,5))  END >= ISNULL(@modification_percent, 0)\r\nAND sp.modification_counter >= ISNULL(@modification_rows,0)\r\n\r\nORDER BY 'UPDATE STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(stat.[object_id]))+'.'+QUOTENAME(OBJECT_NAME(stat.[object_id]))+' '+QUOTENAME(stat.[name])  + ' WITH FULLSCAN; '\r\n--\tORDER BY OBJECT_SCHEMA_NAME(stat.[object_id]),OBJECT_NAME(stat.object_id), sp.stats_id,stat.name\r\n\tOPTION (MAXDOP 1)\r\n\r\n\tPRINT @update_state_command\r\n\texec sp_executesql @update_state_command\r\n\tSET @count = @count -1 \r\nEND \r\nGO \r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I like using Ola Hallengren&#8217;s maintenance scripts. Super configuratible, logging, SQl Agent job creation and etc. Sometimes I want to spot check on some of the statistics. Stale statistics can cause cardinality issues with SQL plans. We learn how\u00a0database statistics\u00a0are &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/practical-prints.com\/?p=567\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_seo_schema_type":"","_wpcom_ai_launchpad_first_post":false,"footnotes":""},"categories":[273,266,22,351],"tags":[390,391,392,315,36,295,52,388,307,387,291,78,389],"class_list":["post-567","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","category-ssms","tag-index_col","tag-modification_counter","tag-objectproperty","tag-query","tag-sql","tag-sql-server","tag-ssms","tag-sys-dm_db_stats_properties","tag-sys-indexes","tag-sys-stats","tag-t-sql","tag-tsql","tag-update-statistics"],"jetpack_shortlink":"https:\/\/wp.me\/phfTTO-99","jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/567","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=567"}],"version-history":[{"count":11,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/567\/revisions"}],"predecessor-version":[{"id":649,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/567\/revisions\/649"}],"wp:attachment":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=567"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=567"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=567"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}