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: CREATEs(?<NonClustered>NON)?CLUSTEREDsINDEXs[(?<IndexName>.*?)].*?ONs
2: [(?<Schema>.*?)].[(?<Table>.*?)].*?((?<ColumnList>.*?))s*?(?:INCLUDEs
3: ((?<IncludeList>.*?)))??s*?(?:WITHs*?(.*?)s*?ONs[.*?])
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: CREATEsSTATISTICSs[(?<StatisticsName>.*?)].*?ONs[(?<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…