통계 확인 쿼리( DB 전체 5% 이상)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
  DB_NAME()     AS DBNAME
 , ss.name      AS SchemaName
 , st.name      AS TableName
 , s.name      AS IndexName
 , STATS_DATE(s.id,s.indid) AS [Statistics Last Updated]
 , s.rowcnt     AS [Row Count]
 , s.rowmodctr     AS [Number Of Changes]
 , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS [% Rows Changed]
INTO #Statistics_Change
FROM sys.sysindexes s
  INNER JOIN sys.tables st ON st.[object_id] = s.[id]
  INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100
  AND s.indid > 0
  AND s.rowcnt >= 500
  AND CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) > 5
  AND 1= 2
ORDER BY [% Rows Changed] DESC

EXEC sp_MSForEachDB 'USE [?];

IF DB_NAME() NOT IN ( ''master'', ''msdb'', ''model'', ''tempdb'', ''distribution'' )
BEGIN
 INSERT INTO #Statistics_Change
 SELECT
   DB_NAME() AS DBNAME
  , ss.name AS SchemaName
  , st.name AS TableName
  , s.name AS IndexName
  , STATS_DATE(s.id,s.indid) AS [Statistics Last Updated]
  , s.rowcnt AS [Row Count]
  , s.rowmodctr AS [Number Of Changes]
  , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS [% Rows Changed]
 FROM sys.sysindexes s
   INNER JOIN sys.tables st ON st.[object_id] = s.[id]
   INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
 WHERE s.id > 100
   AND s.indid > 0
   AND s.rowcnt >= 500
   AND CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) > 5
 ORDER BY [% Rows Changed] DESC
END
'

SELECT * FROM #Statistics_Change
ORDER BY [% Rows Changed] DESC

DROP TABLE #Statistics_Change