Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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
share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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