It’s been quite a while since I narrated a story to the audience. Today, I will narrate a scenario when Catalog Views helped a fellow colleague of mine – exactly the way it happened. Let’s call him Mike.

Mike: How can I check when a particular index was last rebuilt or defragmented? Are there any DMVs or system tables that can help me get this information?

Nakul: There is no direct way to get this information from SQL Server. There is one indirect way I know of, if you are interested.

Mike: Sure, I would like to know about it.

Nakul: Whenever indexes are rebuilt or reorganized, they update the statistics associated with them (vice-versa is not true). Hence, by looking at the date on which these statistics were last updated, you can determine when the index was last rebuilt/defragmented.

Mike: This is really interesting, and I will try it out. Can you share a query that would help me get this information?

Nakul: Sure, you can use a combination of the STATS_DATE() function and the sys.indexes Catalog View to achieve this. Below is a query that you may find useful:

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

At that moment, a friend and colleague, Hardik Doshi (Blog), joined us.

Hardik: What’s up, guys?

Nakul: I was just working with Mike on a query he has. Would you like to share your thoughts on how to check when a particular index was rebuilt or reorganized?

Hardik: SQL Server does not track index rebuilt or defragmentation operation details automatically. You would need to setup trace or create DDL triggers to track these types of operations. However, I agree with your approach to look at the statistics update information to begin with.

Mike: Hmm… I will make it a point to explore DDL triggers today afternoon.

Hardik: I can help you with more detailed information. Would you be interested? The only issue is that the approach uses catalog views, which are marked for deprecation in one of the future SQL Server releases.

Us: Sure! Let’s go through your approach and see how it works out.

Hardik: Okay, here you go – this query will not only give you the last date/time when the index statistics were updated, but also gives the number & percentage of rows changed during the last operation and the associated index name.

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

Mike: Hardik & Nakul: Thank-you very, very much for helping me out – I think I now have enough material with me to work upon these queries.

Nakul: Hardik, as you rightly mentioned, it would not be a good idea to use catalog views – after all, they are marked for deprecation.

Hardik: I completely agree. One of these days, let’s figure out a way to replace the catalog view – sys.sysindexes used in my query.

Nakul: Deal!

References:

• STATS_DATE System function: http://msdn.microsoft.com/en-us/library/ms190330.aspx
  
• Sys.indexes Catalog View: http://msdn.microsoft.com/en-us/library/ms173760.aspx
  
• Sys.sysindexes Compatibility View: http://msdn.microsoft.com/en-us/library/ms190283.aspx

Until we meet next time,

Be courteous. Drive responsibly.