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.