This morning I updated the Index Metrics piece of SQL Server Metrics Pack. This update mainly introduced some new functionality, with a few minor bug fixes. The two big enhancements are the capability to generate and store CREATE INDEX scripts and support for Columnstore indexes.
Creating index scripts seems pretty straight forward, until you start factoring in the additional settings to go along with the scripts, such as compression, fill factor, etc. And then you have the differences in the statements between Clustered/Nonclustered indexes and Columnstore indexes, so it took me a little while to get it all sorted out.
Here are the list of updates:
- Added the capability to generate CREATE INDEX scripts
- Changed hash algorithm to account for last DB restore date
- Added “loadAllIndexMetrics” procedure that will take index snapshots of all databases on a server
- Added checks and scripting capabilities for Columnstore indexes
- Updated views to include generated CREATE and DROP SQL scripts
- Added “vwIndexMetrics_GetScripts” view specifically for pulling the generated SQL scripts