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.


Leave a Reply