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