Create function text in DMVs but old creation date for functions

Question

I was just trying to find the top queries with high execution count by using this query

SELECT TOP 20 
    st.text, 
    creation_time AS plan_creation_time,
    last_execution_time,
    execution_count, 
    total_elapsed_time, 
    last_elapsed_time, 
    total_worker_time AS total_cpu_time,
    last_worker_time AS last_cpu_time, 
    total_logical_reads, 
    last_logical_reads,
    total_physical_reads, 
    last_physical_reads
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where last_execution_time>'2012-03-26 8:00'
ORDER BY execution_count desc

and I found that there are few create function statements.I checked the creation date for functions and found that it was 2 years old. Has anyone idea if there is create function statement how is it possible that creation date does not change ? Other thing I notice that if there is alter function statement the creation date does not change but modified date change.But if there is alter statement why create statement is shown in DMV ?

Answer 1

0

The text you are getting back is the t-SQL for the function. The query inside the function is what is being held in the query cache and not the function creation command. This means that the function is being called often, not the create function command.

This can be a little misleading at first, but this will allow you to identify that the function(s) returned from your DMV query are the most called since the query cache was last cleared/SQL server was last restarted.

You should be able to see what plan the cached query has and then decide if this plan is optimal or not. If the query is called a lot, then even a small improvement of the query execution plan can bring some noticeable performance improvements/resource usage reduction.

answered Mar 26 '12 at 08:26 PM

WilliamD
25.3k ● 16 ● 18 ● 41

Thanks a lot William.That is really misleading

Mar 27 '12 at 01:04 PM inder

@inder Misleading it may be, but I beleive it is more helpful. Moreover, the DMV presents the aggregated performance statistics for cached query plans only and if you want the statements then those should be extracted like to get a better picture

SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement

As far as ALTER does not change the created date is concerned, ALTER is used to change an existing object, so it does not make sense to changed the existing date. If you want it to be changed, then DROP/CREATE would do it.

And why a CREATE was still there, ALTER should have removed the function/procedure etc. from the cached query plans and hence from DMV as it would have been marked for a recompile. It will only come into the picture upon calling explicitly again. Furthermore, explicit DDL statements would not be reflected in this DMV, as they would not be cached.

@WilliamD +1 from here.

Mar 27 '12 at 02:29 PM Usman Butt

(comments are locked)

Create function text in DMVs but old creation date for functions

Follow this question

Related Questions