In my latest enhancement to SQL Server Metrics Pack , I added a new feature to the index metrics scripts that automatically generates a CREATE INDEX script and stores it in the database along with the metrics for each index. (read more about that here) I’m really excited about this particular feature, because it has already proven to be very helpful to my team, in a big way!
I recently had a case at work where a database was restored, and several important indexes were accidentally removed. But because we are using SQL Server Metrics Pack to track the indexes on that server, we were easily able to recover all of the deleted indexes.
In order to spotlight the feature a little, I wanted to answer a few questions and provide some queries to help explain how to use this new feature.
How Can You Recover A Deleted Index?
If you are running the latest release of SQL Server Metrics Pack, you can use the following query to get the CREATE INDEX scripts. This will give you a historical look of the versions of the index that have been recorded with the Index Metrics scripts.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT [DatabaseName] ,[SchemaName] ,[TableName] ,[IndexName] ,[IndexType] ,[DateInitiallyChecked] ,[DateLastChecked] ,[CreateStatement] FROM [dbo].[IndexMetrics] WHERE DatabaseName = N'YourDBName' AND SchemaName = N'YourSchemaName' AND TableName = N'YourTableName' AND IndexName = N'YourIndexName' ORDER BY DateLastChecked DESC |
What if the index definition changes over time?
If your index changes, the Index Metrics scripts will create a new entry for it, generating a new CREATE INDEX script, and begin tracking metrics on it. You can get a historical look of the versions of the index that have been recorded with the Index Metrics scripts by using the previously mentioned query.
Can I view a history of changes to my index definition?
Yep! From the moment you start tracking your indexes with SQL Server Metrics Pack it will keep up with any changes that are made to your index definitions, and keep a log of the CREATE INDEX script for each version of the index. You can get a historical look of the versions of the index that have been recorded with the Index Metrics scripts by using the previously mentioned query.
Will SQL Server Metrics Pack script out every index in my database?
Yep! All you have to do is install SQL Server Metrics Pack, and run the loadIndexMetrics stored procedure for the database you want to script the indexes on. (or use the loadAllIndexMetrics procedure to script every database on the server) Once the procedure is finished, you can query the vwIndexMetrics_GetScripts view to get the CREATE INDEX scripts. (and DROP INDEX scripts, if you need them)
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [DatabaseName] ,[SchemaName] ,[TableName] ,[IndexName] ,[IndexType] ,[CreateStatement] ,[DropStatement] FROM [dbo].[vwIndexMetrics_GetScripts] WHERE DatabaseName = N'YourDBName' |
Does SQL Server Metrics Pack cost anything?
Nope, it’s completely free and open source. Check it out on GitHub.
What if it doesn’t work for me, or I find a bug?
Let’s work together to fix it! The reason I put this on GitHub was so that I could collaborate with others to help make it better. I have no doubt that there are issues with my code and there will be problems and situations that I never thought of, but you may encounter regularly. I am looking forward to working with (and learning from) the SQL community on my open source projects! Please let me know if you are interested in contributing.