I like using Ola Hallengren’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 database statistics are used:
“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.”
The sample rate is set to full s or 100 percent of the rows will be scanned. A full scan will create overhead. Need to always look at the impact to your environment. You could have old statistics and not used or duplicate statistics. In some cases, the higher sample rate can actually cause the plan be less optimal.
Rule of thumb, your queries will perform better with fresh statistics.
I have two scripts to I use to find the health of statistics and update statistics based upon certain criteria.
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 INDEX_COL() 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.
DISCLAIMER — 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 here.
/*
/*
Statistics Information(DMV)
Simple query to evaluate health of database statistics.
JCD 1/14/2016
https://jackdonnell.com
*/
SET NOCOUNT ON
GO
DECLARE @rows_less_than BIGINT -- < = Rows in Table
,@rows_greater_than BIGINT -- <= Rows in Table
,@modification_rows BIGINT -- >= Modication count
,@modification_percent DECIMAL(16,5) -- >= % modification (modification count / rows)
,@sample_percent DECIMAL(16,5) -- < = % sample ( rows samples/ rows)
,@lastupdated_less_than DATE -- last updated < this date
,@lastupdated_greater_than DATE -- last updated > this date
,@update_state_command NVARCHAR(1200) -- sql executed
,@count INT -- number of statistics to update
SET @rows_less_than =3550000
SET @rows_greater_than = 1500
SET @modification_rows = 1
SET @modification_percent = 0.505
SET @sample_percent = NULL
SET @lastupdated_less_than = GETDATE() -3
SET @lastupdated_greater_than = NULL
-------------------------------------------------------------------------
----- No need to edit below this line. -----
-------------------------------------------------------------------------
SELECT DISTINCT
SERVERPROPERTY('ServerName') [ServerName]
,DB_NAME() [database_name]
,QUOTENAME(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+QUOTENAME(Object_name(stat.object_id)) [table_name]
,sp.stats_id
,stat.name [stats_name]
,sp.last_updated
,DATEDIFF(DAY,sp.last_updated,GETDATE())[days_since_last_updated]
,CAST(((sp.rows_sampled*1.000)/sp.rows *100.000) as DECIMAL(12,4)) [percent_sampled]
,sp.rows
,sp.rows_sampled
,sp.modification_counter
,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]
,index_col(RTRIM(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+RTRIM(Object_name(stat.object_id)), stat.stats_id,1) [stat_column_1]
,ISNULL(index_col(RTRIM(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+RTRIM(Object_name(stat.object_id)), stat.stats_id,2),'') [stat_column_2]
,ISNULL(index_col(RTRIM(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+RTRIM(Object_name(stat.object_id)), stat.stats_id,3),'') [stat_column_3]
,ISNULL(index_col(RTRIM(OBJECT_SCHEMA_NAME(stat.[object_id])) +'.'+RTRIM(Object_name(stat.object_id)), stat.stats_id,4),'') [stat_column_4]
,'UPDATE STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(stat.[object_id]))+'.'+QUOTENAME(OBJECT_NAME(stat.[object_id]))+' '+QUOTENAME(stat.[name]) + ' WITH FULLSCAN; '[update_statementFULLSCAN]
,'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;'
,'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;'
FROM sys.stats AS stat with(NOLOCK)
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
LEFT JOIN sys.indexes as ix WITH (NOLOCK)
on stat.[object_id] = ix.[object_id]
WHERE 1=1
AND objectproperty(stat.[object_id], 'ISMSShipped')= 0
AND sp.last_updated < = ISNULL(@lastupdated_less_than,'2078-01-01')
AND sp.last_updated >= ISNULL(@lastupdated_greater_than,'1900-01-01')
AND CAST(((sp.rows_sampled*1.00)/sp.rows *100.00) as DECIMAL(6,2)) < ISNULL(@sample_percent,101)
AND sp.rows <= ISNULL(@rows_less_than, 999999999999)
AND sp.rows >= ISNULL(@rows_greater_than, 0)
AND 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)
AND sp.modification_counter >= ISNULL(@modification_rows,0)
ORDER BY 3, sp.stats_id
OPTION (MAXDOP 1) -- Restrict MAXDOP for query
GO
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 “useful” pages. This example uses a full scan.
/*
/*
Statistics Information(DMV)
Simple query to evaluate health of database statistics.
JCD 1/14/2016
https://jackdonnell.com
*/
SET NOCOUNT ON
GO
DECLARE @rows_less_than BIGINT -- < = Rows in Table
,@rows_greater_than BIGINT -- <= Rows in Table
,@modification_rows BIGINT -- >= Modication count
,@modification_percent DECIMAL(16,5) -- >= % modification (modification count / rows)
,@sample_percent DECIMAL(16,5) -- < = % sample ( rows samples/ rows)
,@lastupdated_less_than DATE -- last updated < this date
,@lastupdated_greater_than DATE -- last updated > this date
,@update_state_command NVARCHAR(1200) -- sql executed
,@count INT -- number of statistics to update
SET @rows_less_than = 19950000
SET @rows_greater_than = 1500
SET @modification_rows = 1
SET @modification_percent = 0.505
SET @sample_percent = NULL
SET @lastupdated_less_than = '2016-01-30'
SET @lastupdated_greater_than = NULL
-------------------------------------------------------------------------
----- No need to edit below this line. -----
-------------------------------------------------------------------------
WHILE(
SELECT COUNT(DISTINCT stat.name)
FROM sys.stats AS stat with(NOLOCK)
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
LEFT JOIN sys.indexes as ix WITH (NOLOCK)
on stat.[object_id] = ix.[object_id]
WHERE 1=1
AND objectproperty(stat.[object_id], 'ISMSShipped')= 0
AND sp.last_updated < = ISNULL(@lastupdated_less_than,'2078-01-01')
AND sp.last_updated >= ISNULL(@lastupdated_greater_than,'1900-01-01')
AND CAST(((sp.rows_sampled*1.00)/sp.rows *100.00) as DECIMAL(6,2)) < ISNULL(@sample_percent,101)
AND sp.rows <= ISNULL(@rows_less_than, 999999999999)
AND sp.rows >= ISNULL(@rows_greater_than, 0)
AND 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)
AND sp.modification_counter >= ISNULL(@modification_rows,0)
) > 0
BEGIN
SELECT DISTINCT TOP 1
@update_state_command = 'UPDATE STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(stat.[object_id]))+'.'+QUOTENAME(OBJECT_NAME(stat.[object_id]))+' '+QUOTENAME(stat.[name]) + ' WITH FULLSCAN; '
FROM sys.stats AS stat with(NOLOCK)
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
LEFT JOIN sys.indexes as ix WITH (NOLOCK)
on stat.[object_id] = ix.[object_id]
WHERE 1=1
AND objectproperty(stat.[object_id], 'ISMSShipped')= 0
AND sp.last_updated < = ISNULL(@lastupdated_less_than,'2078-01-01')
AND sp.last_updated >= ISNULL(@lastupdated_greater_than,'1900-01-01')
AND CAST(((sp.rows_sampled*1.00)/sp.rows *100.00) as DECIMAL(6,2)) < ISNULL(@sample_percent,101)
AND sp.rows <= ISNULL(@rows_less_than, 999999999999)
AND sp.rows >= ISNULL(@rows_greater_than, 0)
AND 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)
AND sp.modification_counter >= ISNULL(@modification_rows,0)
ORDER BY 'UPDATE STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(stat.[object_id]))+'.'+QUOTENAME(OBJECT_NAME(stat.[object_id]))+' '+QUOTENAME(stat.[name]) + ' WITH FULLSCAN; '
-- ORDER BY OBJECT_SCHEMA_NAME(stat.[object_id]),OBJECT_NAME(stat.object_id), sp.stats_id,stat.name
OPTION (MAXDOP 1)
PRINT @update_state_command
exec sp_executesql @update_state_command
SET @count = @count -1
END
GO
Tiny URL for this post:
Comments are closed.