January 31, 2013 at 9:25 am
Dear All,
Hi! I have a Store Procedure which has 10 diff-diff SELECT statements. I need to know that out of these 10 SELECT statements which one is causing blocking and / or taking too much time in execution.
January 31, 2013 at 10:44 am
Run them seperately in SSMS and then run sp_who2 to see if it is blocking.
MCSE SQL Server 2012\2014\2016
January 31, 2013 at 2:10 pm
if you want to see the stats the database has collected over time for this stored proc, instead of just testing one time execution, you could use the DMVs:
SELECT
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.total_logical_reads, qs.total_logical_writes,
qs.total_logical_reads / qs.execution_count AS average_logical_reads,
qs.total_logical_writes / qs.execution_count AS average_logical_writes,
qs.execution_count,
o.name AS object_name,
SUBSTRING (qt.text, qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) AS SQLStatement,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE qt.dbid = DB_ID()
AND o.name = 'your stored proc name here'
ORDER BY total_seconds DESC;
February 1, 2013 at 2:34 am
another alternative would be use of profiler with event like sp:stmtstart and sp_stmtcompted this will give you cpu, IO , memory usage
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply