What tools are out there for profiling stored procedures in SQL server other than the MS profiler?

Question

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.

Answer 1

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