Technical Article

Statistics not updated for the last 30 days

,

We want to keep them up to date as we know that statistics are vital for our Performance. So I will share with you the script below that you can follow and update our statistics.
In the script below you can find the statistics that have not been updated for 30 days and can automate your process accordingly.
Yusuf KAHVECI
yusufkahveci@sqlturkiye.com
www.sqlturkiye.com
Thanks.
/*
SQLTURKIYE.COM
www.sqlturkiye.com
info@sqlturkiye.com
*/
SELECT
sch.name + '.' + so.name AS
'sqltr_Table'
   ,ss.name AS
'sqltr_Statistic'
   ,CASE
WHEN ss.auto_Created = 0 AND
ss.user_created = 0 THEN 'sqltr_Index Statistic'
WHEN ss.auto_created = 0 AND
ss.user_created = 1 THEN 'sqltr_User Created'
WHEN ss.auto_created = 1 AND
ss.user_created = 0 THEN 'sqltr_Auto Created'
WHEN ss.AUTO_created = 1 AND
ss.user_created = 1 THEN 'sqltr_Not Possible???'
END AS
'sqltr_Statistic Type'
   ,CASE
WHEN ss.has_filter = 1 THEN 'Filtered Index'
WHEN ss.has_filter = 0 THEN 'No Filter'
END AS
'sqltr_Filtered'
   ,CASE
WHEN ss.filter_definition
IS NULL THEN ''
WHEN ss.filter_definition
IS NOT NULL THEN ss.filter_definition
END AS 'sqltr_Filter
Definition'
   ,sp.last_updated AS
'sqltr_tats Last Updated'
   ,sp.rows AS 'sqltr_Rows'
   ,sp.rows_sampled AS
'sqltr_Rows Sampled'
   ,sp.unfiltered_rows AS
'sql_trUnfiltered Rows'
   ,sp.modification_counter AS
'sqltr_Row Modifications'
   ,sp.steps AS
'sqltr_Histogram Steps'
FROM sys.stats ss
JOIN sys.objects so
ON ss.object_id = so.object_id
JOIN sys.schemas sch
ON so.schema_id = sch.schema_id
OUTER APPLY sys.dm_db_stats_properties(so.object_id, ss.stats_id) AS sp
WHERE so.TYPE = 'U'
AND sp.last_updated & lt;
getdate() - 30
ORDER BY sp.last_updated
DESC;

Rate

3.33 (3)

Share

Share

Rate

3.33 (3)