Benchmarking SQL Server Execution Plans For Automated Testing?

I'm using SQL Server 2008. I noticed that when I run a sproc and include an execution plan, SQL Server will include an alert if I'm "missing" an index and will recommend one for me to add in order to increase the performance of the sproc.

To me at least, I think it would make sense for my automated tests to run some of my more performance intensive sprocs and then include an execution plan in XML. Then, parse the XML plan for any warnings or alerts spitted by SQL Server. If any are encountered, then this is considered a test failure. I could take it a step further and throw a failure if some query costs have significantly increased since the last time I ran it, but for now I'm just keeping it simple.

This sounds like something that someone would have done already, but I don't see anything from Googling. Is this a reasonable approach for automated performance testing/benchmarking, and, if so, are there any existing tools or frameworks that would make this easier?




I would advise against this. The missing indexes that are identified by the optimizer are very myopic. That is to say that they do not take the whole system into account and suggest indexes that might benefit this query but might tank the performance of DML. While your heart is in the right place, index analysis is more art than science (though the latter can and should inform the former).

Need Your Help

Automatically adding .Net code comments

visual-studio plugins commenting

Where can I find a Visual Studio plug-in that automatically generates documentation header for methods and properties?

Array list algorithm - Interview

java algorithm collections

I was asked this question in an interview today. I have tried a solution but would like to know if there's a better way to solve this: