SQL Database Tuning Advisor Output Renamer
I’ve uploaded the ‘Database Tuning Advisor Output Renamer’ at http://DtaOutputRenamer.codeplex.com/.
OK… so Friday marked the first day I’ve ever gotten sunburned while coding. I had a little bit of free time while at an outdoor event, and whipped up a a little utility to help apply standards to to DTA recommendations.
I use the SQL Database Tuning Advisor (DTA) a lot to generate basic recommendations for indexes and statistics based on a workload. In my team, we store all index and statistics creation scripts in .SQL files, which are then run as part of our deployments. We use a standard naming convention for each of the objects to enhance the maintainability.
Last week I ran the DTA against a workload I generated based on running reports on some new schema… not surprisingly, quite a few recommendations were generated. It occurred to me my time could be better spent doing something besides renaming 50 database objects based on their definitions. I decided to write a small application to help change the default names (such as ‘_dta_index_SsasProcessingRunArchive_c_7_1677965054__K10’) to something a little more user friendly (like ‘IX_dbo_SsasProcessingRunArchive_ObjectType_EventClass_SessionID_I_StartTime’). You’ll want to modify the application to match your local coding standards, but it should be pretty straight forward to do.
This application only handles a few cases, but does cover Clustered/Non-Clustered Indexes (with and without INCLUDE columns) and Statistics. It should be easy to extend it if you need to. This app is just something I whipped up in an hour or two, so it isn’t the most robust thing ever created.
I created the following regex (remove the line breaks… I just used those for presentation) to capture the index name, table name, and column/include lists for the indexes:
1: CREATE\s(?<NonClustered>NON)?CLUSTERED\sINDEX\s\[(?<IndexName>.*?)\].*?ON\s\
2: [(?<Schema>.*?)\]\.\[(?<Table>.*?)\].*?\((?<ColumnList>.*?)\)\s*?(?:INCLUDE\s
3: \((?<IncludeList>.*?)\))??\s*?(?:WITH\s*?\(.*?\)\s*?ON\s\[.*?\])
I created the following regex (remove the line breaks… I just used those for presentation) to capture the statistics name, table name, and column list for the statistics:
1: CREATE\sSTATISTICS\s\[(?<StatisticsName>.*?)\].*?ON\s\[(?<Schema>.*?)\]
2: \.\[(?<Table>.*?)\].*?\((?<ColumnList>.*?)\
I then just update the object names with a new name created based column lists and such. I also through in the functionality to strip ‘go’ statements from input.
Enjoy…