2

If i run the following query on our production server:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time / 100000 AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) / 100000 AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
,s.plan_handle, s.plan_generation_num
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time / 100000 DESC

My top queries are "create procedure ......." does this mean the sql server recompiles this stored procedure more then once?

Improve this question

1 Answer 1

Reset to default
5

No, regarding your query that means, that this procedure takes much time than others

And

Periodically sql server recompiles procedures or its inner statements, it is an intended behavior depending from server activity, memory pressure, user commands, RECOMPILE option, amount of underlying data changes and many more.

Update:

The queries is started from CREATE PROCEDURE because sql server presents the SP's body in such a way.

You may even drill down into procedure's inner statements by this query:

Select 
    s3.name as [Obj Name], 
    s3.type as [Obj Type], 
    (select top 1 substring(text,(s1.statement_start_offset+2)/2,
    (CASE when s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2
    else s1.statement_end_offset end - s1.statement_start_offset) /2 ) FROM sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement], 
    execution_count, 
    plan_generation_num, 
    last_execution_time, 
    ((total_worker_time+0.0)/execution_count)/1000 as [avg_worker_time], 
    total_worker_time/1000.0 total_worker_time, 
    last_worker_time/1000.0 last_worker_time, 
    min_worker_time/1000.0 min_worker_time, 
    max_worker_time/1000.0 max_worker_time, 
    ((total_logical_reads+0.0)/execution_count) as [avg_logical_reads], 
    total_logical_reads+0.0 total_logical_reads,
    last_logical_reads+0.0 last_logical_reads, 
    min_logical_reads+0.0 min_logical_reads, 
    max_logical_reads+0.0 max_logical_reads, 
    ((total_logical_writes+0.0)/execution_count) as [avg_logical_writes], 
    total_logical_writes+0.0 total_logical_writes, 
    last_logical_writes+0.0 last_logical_writes, 
    min_logical_writes+0.0 min_logical_writes, 
    max_logical_writes+0.0 max_logical_writes, 
    ((total_logical_writes+0.0)/execution_count + (total_logical_reads+0.0)/execution_count) as [avg_logical_IO], 
    total_logical_writes + total_logical_reads+0.0 total_logical_IO, 
    last_logical_writes +last_logical_reads+0.0 last_logical_IO, 
    min_logical_writes +min_logical_reads+0.0 min_logical_IO, 
    max_logical_writes + max_logical_reads+0.0 max_logical_IO
from sys.dm_exec_query_stats s1
cross apply sys.dm_exec_sql_text(sql_handle) as s2
join sys.objects s3 on ( s2.objectid = s3.object_id )
left join sys.schemas sch on(s3.schema_id = sch.schema_id)
where s2.dbid = db_id()
order by s3.name, s1.sql_handle
Improve this answer
6
  • Thanks for the fast reply, but why is the t.TEXT QueryName "create procedure ..." and not "exec ..."? thats gives me the headache..
    – Peter
    Commented Jan 24, 2012 at 10:27
  • See updated answer. And addition - sql server keeps the compiled query text - say me, what it should compile in EXEC someproc? 8-)
    – Oleg Dok
    Commented Jan 24, 2012 at 10:30
  • Im not sure what this query does but i get no results from it, i can only run it in my master database else i get a "Incorrect syntax" even if i remove the "where s2.dbid = db_id()" i still get no results, but any way thanks you answered my question!
    – Peter
    Commented Jan 24, 2012 at 10:42
  • The code definitely works - I use it every almost day
    – Oleg Dok
    Commented Jan 24, 2012 at 10:55
  • @Petoj If you can only run it in the master database then maybe the compatibility level of your user databases is lower. The join on sys.objects will end up excluding queries that belong to other databases.
    – Martin Smith
    Commented Jan 24, 2012 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.