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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mdouganCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mfsamuelCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
fesnyngCommented:
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
TreadHeadCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.