Finding & Fixing Statistics Without Histograms
Men Without Hats were awesome. Statistics without histograms are terrible, but now they're easy to find and fix.
2018-10-12
3,200 reads
/* 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;