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?
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.
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))
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.
You don't need to use SQL Profiler to view an execution plan - just:
SET SHOWPLAN_XML ON
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.