March 4, 2012 at 11:11 pm
[font="Verdana"]
Hi All,
I have one stored procedure, executes with 'Recompile' due to which I am not able to gather its statistics from below query:
SELECTTOP 10 serverproperty('machinename') as 'Server Name',
isnull(serverproperty('instancename'),
serverproperty('machinename')) as 'Instance Name',
COALESCE(DB_NAME(qt.dbid), DB_NAME(CAST(pa.value as int)),'Resource') AS DBNAME,
case when sch.name is null then '' else '['+sch.name+'].' end + s3.name as ObjectName,
qs.execution_count as [Execution Count],
(qs.total_worker_time/1000) as [Total CPU Time],
(qs.total_worker_time/1000)/qs.execution_count as [Avg CPU Time],
(qs.total_elapsed_time/1000) as [Total Duration],
(qs.total_elapsed_time/1000)/qs.execution_count as [Avg Duration],
qs.total_physical_reads as [Total Physical Reads],
qs.total_physical_reads/qs.execution_count as [Avg Physical Reads],
qs.total_logical_reads as [Total Logiical Reads],
(qs.total_logical_reads/qs.execution_count) as [Avg Logical Reads] ,
last_elapsed_time/1000 as last_elapsed_time,last_logical_reads,last_physical_reads
FROMsys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.objects s3 on ( qt.objectid = s3.object_id )
left outer join sys.schemas sch on(s3.schema_id = sch.schema_id)
outer apply sys.dm_exec_query_plan (qs.plan_handle) qp
outer APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
whereattribute = 'dbid' and s3.name = '<Sproc Name>'
ORDER BY[avg CPU Time] DESC
Can anybody advice on how to gather statistics in such case?
Thanks in advance,
-- Mahesh
[/font]
MH-09-AM-8694
March 5, 2012 at 1:32 am
You can use profiler or server side trace to get the data when it runs.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply