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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
set showplan_all on
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.