CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Krish
Krish

Reputation: 176

How to track which query in the corresponding stored procedure is executing

Our team has assigned a new task to me to look into the performance of a huge stored procedure. When I observe in the development environment with the proper data I have noticed that the procedure takes a considerable time to execute. In my scenario it took around 45 minutes. There are multiple INSERT/DELETE/UPDATE queries are used in the stored procedure. But I am unable to get which query is causing the issue. The data volume which is used in the queries are also pretty less.

How can I pinpoint to the exact query in the stored procedure which is getting executed?

My Server version is SQL Server 2008 R2.

Upvotes: 3

Views: 3400

Answers (1)

Gopakumar N.Kurup
Gopakumar N.Kurup

Reputation: 936

There are couple of ways to find out the query which is executing as part of a stored procedure. Learn more about DMVs and SQL Profiler. Both will give you an oversight to pinpoint the queries that are being used in a stored procedure.

In SQL Profiler, use the SP:StmtCompleted or SP:StmtStarting to include the statements inside the query. But I would advice not to go with the Profiler as it affects the system memory as well. Also it may give you unwanted additional information as well.

The best way is to use DMV (Dynamic Management Views). If you know the process id (SPID) use the below query to find out the query

The first query will give you the details about the stored procedures and the second will give you the exact query which is currently executing. Replace the SPID in the below query with the corresponding SPID of your process.

SELECT  requests.session_id, 
        requests.status, 
        requests.command, 
        requests.statement_start_offset,
        requests.statement_end_offset,
        requests.total_elapsed_time,
        details.text
FROM    sys.dm_exec_requests requests
CROSS APPLY sys.dm_exec_sql_text (requests.sql_handle) details
WHERE   requests.session_id = SPID
ORDER BY total_elapsed_time DESC

SELECT  SUBSTRING(detail.text, 
                  requests.statement_start_offset / 2, 
                  (requests.statement_end_offset - requests.statement_start_offset) / 2)
FROM    sys.dm_exec_requests requests
CROSS APPLY sys.dm_exec_sql_text (requests.sql_handle) detail
WHERE   requests.session_id = SPID

Once you have done identifying the queries which is causing slowness, you can use Actual execution plan to identify the issues in the query. Try this and please comment whether it is working for you.

Upvotes: 2

Related Questions