views:

119

answers:

5

I am facing a problem that running a stored procedure is taking too much resources which sometimes causes a time out on the server (especially when the CPU usage is more than 90%).

Can anyone suggest what the best and quickest way is to spot the block which takes much resources, and also suggest a good way to solve it, please?

  • I am using SQL server 2005
+5  A: 

You want to use the Query profiler. Explained here. Which will show you a graphical representation of your queries execution path, as well as which parts of it are taking the most time.

Matthew Vines
A: 

If you are using the SQl Server Management studio, you can turn on the execution plan to display information about how the query will be executed by sql server including what percentage of the entire process will be taken up by each sub-process.

often when doing this, there will be a part of the query that is obviously using most of the resources.

using this informationm you can then make an informed decision about how to tune the database. (like adding an index to the offending table(s))

+3  A: 

If you want to know which block is slowest, use the following

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SET STATISTICS TIME ON

When you run the SP this will display stats for each query.

tekBlues
A: 

You don't need to use SQL Profiler to view an execution plan - just:

SET SHOWPLAN_XML ON
Aaron Alton
A: 

If there are a bunch of statements in the sproc it can be a bit convoluted to turn on the SET STATISTICS options since you have many chunks of output to associate with input.

The graphical representation of a query plan in SSMS is pretty useful since it shows you the % cost of each statement relative to the cost of the entire batch/sproc. But this is a single value, so it can be more helpful at times just to run Profiler and turn on statement level output. Profiler will give you separate IO and CPU cost for each statement if you add event SQL:StmtCompleted and columns CPU and Reads.