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: 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.


This entry was posted in Uncategorized. Bookmark the permalink.

Comments are closed.