Need report of time spent statement-by-statement inside stored procedure

I've tried "Display Estimated Execution Plan," which provides a very large graph with percentages that is perhaps kind of helpful, but I'm hoping someone can get my started by pointing me in the right direction here:

I have a very complex and long stored procedure that in turn calls other stored procedures.

The stored proc is taking too long to run and I'm guessing I have an inefficient query or am missing an index on a table somewhere.

(I tried a low-tech approach of adding select 'here' statements to see where it's getting hung up but I've determined that I can't trust the output as it seems to be buffered or slowing down in statements that can't be taking a long time to complete.)

Ideally, it would be nice if Microsoft provides a tool that shows each SQL statement from beginning to end of running the stored proc with the number of milliseconds spent executing those statements, in a two column table (not a graph), like this:

select blah blah         100 ms
update blah blah      100 ms
...

Then I could affect the offending statement(s).

Does such a tool exist?

Pete
LVL 2
ZuZuPetalsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mdouganConnect With a Mentor Commented:
Somewhere in my files, I have a statement that you can execute before your SQL statement to give you some of that output... the closest I can come through google at the moment is the option below.  Before you run it, assuming you're running it in something like Query Analyzer, you should go to the Options menu, click on the Output tab and select the Text output option.  When you get the output, you can copy it and paste it into Excel, and then it gives you nicely tabbed columns.

set statistics profile on
go

-- run your query
select * from mytable
go

set statistics profile off
go

0
 
mfsamuelConnect With a Mentor Commented:
Query Analyzer in SQL Server Enterprise Manager.  You can look at execution plans to determine problems.
0
 
mdouganCommented:
Here are some other options that you can run (C&P)

SET SHOWPLAN_TEXT ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run.
SET SHOWPLAN_ALL ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run, plus additional information, such as the estimated number of rows, I/O, CPU, and the average size of a the query.
SET STATISTICS IO ON: Shows the number of scans, logical reads, and physical reads performed during the query. Returns actual data based on a query that has run.
SET STATISTICS TIME ON: Shows the amount of time (in milliseconds) needed to parse, compile, and execute a query. Returns actual data based on a query that has run.
SET STATISTICS PROFILE ON: Shows a recordset that represents a profile of the query execution. Returns actual data based on a query that has run.
You will not want to run the first two commands listed above at the same time as the others because the first two commands are based on estimated data, while the last three are based on real data.

If you are using SQL Server 2000, using these commands are less needed as you can get all of the same type of data other ways from within Query Analyzer.

0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
mdouganCommented:
I think it was the showplay_all ON that I was using.  I also see that I was calling some DBCC functions to clear out the cache, so, that I would be sure to be analyzing the latest query plan for the query:

         set showplan_all on
         DBCC DROPCLEANBUFFERS
         DBCC FREEPROCCACHE
0
 
fesnyngConnect With a Mentor Commented:
If you are using SQL Server 2005, you can use the SQL Server Profiler (SSMS Tools menu).  Most of the templates will give you duration, but the TSQL_Duration template is focused. The Tuning template is another good choice.  Save to a file (not a table) to keep a record of the results.  
0
 
TreadHeadConnect With a Mentor Commented:
I agree with fesnyng.  When you use profiler, you'll want to capture the SP:StmtCompleted event.  That will allow you to see the number of milliseconds required for each statement within the stored procedure.
0
 
mfsamuelCommented:
The reason I like the Query Analyzer and execution plans is you can see exactlly what is being done.

Types of scans:
Table Scans (worst performance, no index used/ this is what your queries did origianlly)
Bookmark Scan (next worst, used an index but needed to do a scan of the table too)
Index Scan (uses index, but not optimally)
Index Seek (this is what you want to see)

This lets you see the problems and properly create you indexes to maximize query performance.  Not just time, but types of scans and indexes used.  You can also create the correct indexes right in the interface.
0
All Courses

From novice to tech pro — start learning today.