April 20, 2015 at 6:31 am
Hi All,
I have below query:
"SELECT TOP 10
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
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 individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
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
WHERE
qt.dbid = DB_ID()
ORDER BY
average_seconds DESC;"
With the help of above query , I am able to find which Procedure is taking more time, but unable to find what paramater user has passed for the displaying query.
Could anyone please help me regarding this ?
One more scenario is that there are so many query statement is there in one procedure , I want to know which statement is taking more time to execute in the particualar Procedure and in which scenario the query is taking more time.
Thanks in Advance.
Arooj
April 20, 2015 at 6:41 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2015 at 6:44 am
Your best bet if you want to capture parameters is to use Extended Events[/url]. You can't capture parameter values querying the cache.
For lost more on query tuning, see the books in my signature below.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply