Trying out a set of recommendations from database engine tuning advisor on a test CRM system, I saw one particular query go from 1 second to 25 seconds.  Obviously I need to remove the recommendations and see if it’s a fluke and see if any other queries changed.  But there is no ‘unapply’ command. I can’t find anything better than rewriting the sql file or restoring from a backup.  Rewriting the sql file is easier since there are only two types of statements in it – create index and create statistics.  Regular expressions to the rescue…

First off, in tuning advisor, always always always pick ‘Save Recommendations’ before (or instead of) apply recommendations. This gives you a SQL file with all the intended changes.

Since it’s a CRM system I was only tuning by adding indexes and statistics.  Index creates are very easy to replace – change CREATE to DROP, get rid of all the extra info after the first line.  The hard part is finding a regular expression tool that can handle multi line replace – sql server and visual studio and notepad++ don’t appear to be up to the task.  I ended up using this visual studio add-in which adds a proper regex parser to visual studio, but it has some bugs so I wouldn’t rely on it.  Make one find/replace, save, reload the file, make the second find/replace, save the file, then put the tool away. 

I just want to find:

^CREATE .*?(INDEX.*)$[^$]*?$?(go)

and replace:

DROP $1

go

Statistic drop syntax is slightly different than create syntax.  But not multi-line, so a bit easier, just needs groups.

Find:

CREATE STATISTICS (.*) ON (.*)\(.*\)

Replace With:

DROP STATISTICS $2.$1

And now I have a SQL file that undoes the changes my tuning advisor made.