2

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

Community
  • 1
user497745
  • 121
  • 4
  • 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
  • I just read the help page. This question seems valid... The answer would be simple: xyz tool addresses your requirements or "no tool exists which addresses your requirements." The question is not subjective. e.g. I'm not asking for the "best" tool. It's very black or white. Does a tool exist that fits requirements a, b, and c? – user497745 Jul 29 '13 at 17:28
  • So what is the recourse to deal with questions incorrectly marked as duplicates? This question is not a duplicate, nor is it a shopping list question. The question is not about how to profile. I know how to profile. This question is not about how to stress test. I'm not trying to stress test. – user497745 Jul 31 '13 at 02:50
  • The problem with saying "xyz addresses your requirements" is that xyz may change tomorrow or be taken off the market next week. The goal of sites on the SE network is not just to answer your questions right now but also to build a catalog of questions that will be useful for some time for other users as well. Product recommendations become obsolete quite quickly, and are still going to be subjective because I might not necessarily know how well one product or another meets your specific requirements. – Aaron Bertrand Jul 31 '13 at 07:11
  • To elaborate about the shopping list question, here is the text for the relevant off-topic close reason: Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope. – Aaron Bertrand Jul 31 '13 at 07:12
  • 2
    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 07:17
  • Also this meta question seems timely. Read all of Cody's answer (except the part where you'd have to re-read the help page you've clearly already read). – Aaron Bertrand Jul 31 '13 at 07:37
  • 1
    @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

0 Answers0