asked
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 ?
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.
@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.
3 People are following this question.
