-- Check Stats for specific tables
SELECT object_name(sp.object_id) AS [Table]
,sp.stats_id
,name
,filter_definition
,last_updated
,rows
,rows_sampled
,steps
,unfiltered_rows
,modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id('SPECIFY TABLE HERE');
-- Check stats for all user tables on current database
SELECT SCHEMA_NAME(schema_id) AS [SchemaName]
,obj.name AS [TableName]
,obj.object_id
,stat.name [StatName]
,stat.stats_id
,filter_definition
,rows
,rows_sampled
,steps
,unfiltered_rows
,last_updated
,modification_counter
FROM sys.tables AS obj
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE obj.type <> 'S'
ORDER BY last_updated ASC;
--Check Stats for current database where the leading column has been modified over 1000 times
SELECT SCHEMA_NAME(schema_id) AS [SchemaName]
,obj.name AS [TableName]
,obj.object_id
,stat.name [StatName]
,stat.stats_id
,last_updated
,modification_counter
FROM sys.tables AS obj
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE modification_counter > 1000
AND obj.type <> 'S';
No comments:
Post a Comment
Note: only a member of this blog may post a comment.