Have you ever been zipping right along working on tuning a query or maybe a handful of queries. Then BAM! All of a sudden you lose the script you were working on. The script just so happened to have a few indexes that you were getting ready to create and you would like to not have to start all over again.
Sure, there are tools out there in this day and age that are supposed to help you recover those scripts. Just suppose that maybe the tool(s) don’t have a record of your script either. Are you up a creek without a paddle at this point? Are you truly forced to start all over again?
Let’s make this predicament even more unfortunate for the DBA. The DBA doesn’t recall the object upon which the indexes were to be created or that maybe upon which the indexes were already created (but the DBA needs to get those indexes to three or four more servers).
A bit Far Fetched?
Does this all seem a bit too implausible or do you know somebody that has experienced something like this? I had a DBA pose this eerily similar scenario to me because it had just happened to him. Let’s be real, crap really does happen at the most inopportune time. We just need to be prepared to deal with it and polish it in the most elegant way we know.
Today, I will show you how to potentially retrieve the indexes that were recently created and maybe, just maybe, save yourself a little time and prevent the dreaded rework. Understand, however, that this is not a bullet proof solution. It is merely a plausible method to potentially recapture the indexes recently created. I will explain in more detail shortly the issues with this method. First the script.
SELECT STATS_DATE(so.object_id, si.index_id) AS LastModDate,
si.name AS IndexName,
SCHEMA_NAME(so.schema_id) + N'.' + so.name AS ObjName,
so.object_id,
si.index_id,
si.type_desc AS IndexType,
ISNULL(
STUFF(
(
SELECT ', <' + c.name + '>'
FROM sys.columns c
INNER JOIN sys.index_columns ic
ON c.column_id = ic.column_id
AND c.object_id = ic.object_id
WHERE ic.object_id = si.object_id
AND ic.index_id = si.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH(''), ROOT('IdxColNames'), TYPE
).value('/IdxColNames[1]', 'varchar(max)'),
1,
2,
''
),
''
) AS IndexKeyColumns,
ISNULL(
STUFF(
(
SELECT ', <' + c.name + '>'
FROM sys.columns c
INNER JOIN sys.index_columns ic
ON c.column_id = ic.column_id
AND c.object_id = ic.object_id
WHERE ic.object_id = si.object_id
AND ic.index_id = si.index_id
AND ic.is_included_column = 1
ORDER BY ic.key_ordinal
FOR XML PATH(''), ROOT('IdxColNames'), TYPE
).value('/IdxColNames[1]', 'varchar(max)'),
1,
2,
''
),
''
) AS IndexIncColumns
FROM sys.indexes si
INNER JOIN sys.objects so
ON so.object_id = si.object_id
WHERE so.is_ms_shipped = 0
ORDER BY LastModDate DESC, so.object_id;If I run that query on a database, I might see information such as the following shows.
Interestingly enough, all of these indexes were certainly not created on the dates shown in the LastModDate column. As it turns out, the trick I am using (Stats_Date) will give us relative dates of indexes being created but it also gives us the date of the last time the index was updated (or stat was updated). This means you may end up with a ton of indexes to try and sift through to find that index you just created (and forgot the pertinent information). So, while this method does have a use case and can help you prevent rework, it doesn’t do much good for an audit requirement. Stay tuned because the next couple of articles will discuss how to better audit your indexes and get the precise information you need. Not only will you be able to find that recent index addition, but you will also be able to assuredly know when an index was created, altered, or dropped – and by whom.
Put a bow on it
This article showed a quick script to help determine indexes that were created recently. This script will help you out of rough spot and help reduce the chance of rework. That said, you will definitely want to add a bit of auditing on indexes to your environment because while it is helpful, it will not help you in cases where maybe the index was created a few days in the past or to determine when an index may have been dropped.
Interested in more back to basics articles? Check these out!
Want to learn more about your indexes? Try this index maintenance article or this index size article.
This is the first article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.
