#0167-SQL Server-A tale (and Query) of using Catalog Views to get Index Maintenance (rebuild/reorganize) History


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

STATS_DATE() alongwith sys.indexes Catalog View can be used to find when an index was last rebuilt/reorganized


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

Using sys.sysindexes compatibility view to identify the changes made during an index rebuild/reorganize


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:



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

3 thoughts on “#0167-SQL Server-A tale (and Query) of using Catalog Views to get Index Maintenance (rebuild/reorganize) History

  1. Chintak Chhapia

    Feeling good after reading about you all..Missing all of you..

    The one other way you can get indication is from modify_date column from sys.all_objects. If table schema is unchanged, you can join it to sys.indexes. But this will also not give index wise detail..

    Like

    Reply
  2. Nakul Vachhrajani

    We miss you too, Chintak!

    Thank-you for your tip about the sys.allobjects – I for one, hadn’t thought of it. Thank-you!

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s