Thursday, March 3, 2016

Statistics Function

--http://www.sqlskills.com/blogs/erin/new-statistics-dmf-in-sql-server-2008r2-sp2/
SELECT sch.name+'.'+so.name AS [Table]
     , ss.name AS [Statistic]
     , CASE
           WHEN ss.auto_Created = 0
                AND ss.user_created = 0
           THEN 'Index Statistic'
           WHEN ss.auto_created = 0
                AND ss.user_created = 1
           THEN 'User Created'
           WHEN ss.auto_created = 1
                AND ss.user_created = 0
           THEN 'Auto Created'
           WHEN ss.AUTO_created = 1
                AND ss.user_created = 1
           THEN 'Not Possible?'
       END AS [Statistic Type]
     , CASE
           WHEN ss.has_filter = 1
           THEN 'Filtered Index'
           WHEN ss.has_filter = 0
           THEN 'No Filter'
       END AS [Filtered?]
     , CASE
           WHEN ss.filter_definition IS NULL
           THEN ''
           WHEN ss.filter_definition IS NOT NULL
           THEN ss.filter_definition
       END AS [Filter
Definition]
     , sp.last_updated AS [Stats Last Updated]
     , sp.rows AS [Rows]
     , sp.rows_sampled AS [Rows Sampled]
     , sp.unfiltered_rows AS [Unfiltered Rows]
     , sp.modification_counter AS [Row Modifications]
     , sp.steps AS [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 < GETDATE() - 10
      --AND sp.modification_counter > 10000
      --AND sp.last_updated IS NULL
ORDER BY sp.last_updated DESC;

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home