If i run the following query on our production server:

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
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?


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


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.


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:

    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], 
    ((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
  • Thanks for the fast reply, but why is the t.TEXT QueryName "create procedure ..." and not "exec ..."? thats gives me the headache.. – Peter Jan 24 '12 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 Jan 24 '12 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 Jan 24 '12 at 10:42
  • The code definitely works - I use it every almost day – Oleg Dok Jan 24 '12 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 Jan 24 '12 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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