11

It seems like there should be better tools out there for profiling sql than SQL Server Profiler. It takes forever to set up a profile session. I'm looking for a tool which is more like VS profiler. Just choose a stored procedure, and run it with a set of parameters, and what I should get is a profiling graph showing how long each query, SP call and function call takes, and allow me to drill down to individual statements with the parameters which were passed in.

I've seen some for Oracle, but I haven't seen any good tools for SQL server.

bpeikes
  • 3,495
  • 9
  • 42
  • 80

3 Answers3

5

There is nothing wrong with SQL Profiler. Admittedly, it has its idiosyncrasies, but is nonetheless a good tool. The point being, there is often less value in tuning a single stored procedure than there is tuning a query workload.

I do not share your experience that "It takes forever to set up a profile session".

In SQL Server 2008 onwards, there are also extended events, BUT, though powerful, they do not have a simple GUI as yet.

UPDATE: If you have already identified the Stored Proc in question, run in SSMS with the 'Actual Execution Plan' turned on: that will indicate why it takes time to run. You can also include "SET STATISTICS IO ON" to give you a breakdown of the read/writes performed.

For examining execution plans, try using the free SQL Sentry Plan Explorer

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • What is your typical development environment cycle look like when you are developing SQL code for SqlServer? I currently use SQL Server Management Studio for developing my stored procedures, but then I have to go to VS to do any step through debugging, which has it's own set of problems like no access to view content of temp tables or table variables. – bpeikes Oct 05 '11 at 20:44
  • If you want to profile a SP to see what's taking so long, you've got to open up the profiler, add filters so that you filter out what others might be doing, then run the SP from management studio, then dump the output to a table for analysis. Compared to what I can do with code written in C++, C#, C, and even Java, the development environment is primitive. – bpeikes Oct 05 '11 at 20:46
  • 1
    If you already know the SP in question, run in SSMS with the 'Actual Execution Plan' turned on: that will indicate why it takes time to run. – Mitch Wheat Oct 05 '11 at 23:46
  • Although there's no inbuilt GUI for Extended Events; but there is a SSMS Addin for Extended Events by Jonathan Kehayias . You can get it from: http://extendedeventmanager.codeplex.com/releases/view/30480 – Dharmendar Kumar 'DK' Oct 07 '11 at 20:06
  • I've worked with "Actual Execution Plan", and it helps a bit, except working with the data is not as easy as other profiling tools. The main problem I have is that it is not easy to filter straight to the part of the plan which is taking the most time. – bpeikes Oct 13 '11 at 17:48
1

SQL Server Profiler is a great tool. The first few times you use it it can take a bit, but you start to learn the events and the desired output pretty quickly.

An alternative would be to use the system stored procs and functions. Write up a script utilizing them to give you the output you want and then save it and just alter the specific parameters each time. But again this is very similar to saving templates within Profiler.

0

You may want to try SQL Sentry Plan Explorer. This is the much more usable and comprehensive tool than SSMS for the profiling your queries via analysis of the execution plan. For additional information, see this answer.

It's worth mentioning that the tool is available in the free and Pro editions, though, I have found no limitations of the free edition for my needs.

Community
  • 1
  • 1
Alexander Abakumov
  • 13,617
  • 16
  • 88
  • 129