x

Create function text in DMVs but old creation date for functions

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

asked Mar 26, 2012 at 06:43 PM in Default

inder gravatar image

inder
211 17 19 22

Thanks Magnus.I have learnt a lesson from it.
Mar 27, 2012 at 01:41 PM inder
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Mar 26, 2012 at 08:26 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

Thanks a lot William.That is really misleading
Mar 27, 2012 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, 2012 at 02:29 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x57
x22
x3
x2

asked: Mar 26, 2012 at 06:43 PM

Seen: 838 times

Last Updated: Mar 27, 2012 at 02:29 PM