Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

I'm building a BI system on SQL Server 2012. I have a test set of data with some tens of millions of rows.

Currently many functions and stored procedures are unacceptably slow. I'm looking for a tool I can use to automatically compare query times from previous executions as I run and re-run these queries and make performance improvements.

I want to tinker, measure, compare execution times, repeat.

For example, I want to be able to see a report showing what query times were when run on 7/31/2013. Then after I make some changes, I want to kick off the tool again on say, 8/1/2013. I'd like to see side-by-side the difference in query times between all of the historical executions.

I know I can track query times manually with SET STATISTICS TIME ON. I've also seen stored procedures others have written to manually track the performance of one or two queries. But the process is very manual.

I'm looking for a tool that I can enter the 30 functions and stored procedures I want to test into (or give it a trace), and then it will do the work of kicking of the queries 5 times (or replaying the trace), record the average time each query took to execute, and compare those times to previous executions.

Regarding Similar Questions

I've looked at:

  1. Testing stored procedure scalability - My question is not about stress testing. (Although the RML tools mentioned are VERY close to what I'm looking for. If all else fails, I will use the RML tools.)
  2. How to profile stored procedures - I know how to profile a stored procedure and use tools like SQL Profiler. Profiler won't give me comparisons to previous executions.

The RML tools are very close to what I want, as one can load traces into multiple DBs and view the results side by side.

But is there any tool that automates: replaying sql statements and viewing the performance diff of different iterations side-by-side? (The major limitation with the RML tools is that the trace from each iteration has to be loaded into a different database and the side-by-side comparison is only possible by opening two instances of Reporter and switching back and forth between the two of them. Ten different databases and ten different windows for ten different executions seems unwieldy...)

share|improve this question

marked as duplicate by Mark Storey-Smith, Paul White, dezso, StanleyJohns, Kin Jul 29 '13 at 14:26

This question was marked as an exact duplicate of an existing question.

Apologies, Testing stored procedure scalability is a better fit. – Mark Storey-Smith Jul 29 '13 at 10:14
I don't think this question is a duplicate of either of the two questions indicated. I've addressed this in my latest edit. – user497745 Jul 29 '13 at 17:10
If it's not a duplicate, it's off-topic because it's a shopping-list question. Please see this help page. – Jon Seigel Jul 29 '13 at 17:14
Now, with that all said, have you looked at SQL Sentry Plan Explorer? In the PRO version one of the top features is the ability to have a session - where you can make minor tweaks to the query (or to the underlying structure), run the query (or queries) again, and see the differences - while you can't view them side by side, it is very easy to switch back and forth. And you can save your session from 7/31 and open it up and resume on 8/1. Disclaimer: I work for SQL Sentry. – Aaron Bertrand Jul 31 '13 at 7:17
@Aaron Thank you for explaining the shopping list point. That makes sense. Also, thank you for pointing out the Sentry Plan Explorer. This will definitely be a valuable tool during my development. – user497745 Jul 31 '13 at 20:12