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: