Saturday, 16 February 2013

When was the Index maintenance last performed

--Option #01:
USE AdventureWorks2008R2
GO
SELECT name AS index_name,
       STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes
WHERE object_id = OBJECT_ID('HumanResources.Employee');
GO


--Option 02:
USE AdventureWorks2008R2
GO
SELECT   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',
         CASE WHEN s.rowmodctr > 0 
              THEN CAST ((CAST (s.rowmodctr AS DECIMAL (28, 8)) / CAST (s.rowcnt AS DECIMAL (28, 2)) * 100.0) AS DECIMAL (28, 2)) 
              ELSE 0
              END AS '% Rows Changed'
FROM     sys.sysindexes AS s
         INNER JOIN
         sys.tables AS st
         ON st.[object_id] = s.[id]
         INNER JOIN
         sys.schemas AS ss
         ON ss.[schema_id] = st.[schema_id]
WHERE    s.id > 100
         AND s.indid > 0
ORDER BY SchemaName, TableName, IndexName

No comments:

Post a Comment