Why dmv query creation_time(cache_time) keep refresh

I use following DMV query to tracking our production server queries usage, but I see creation_time from sys.dm_exec_query_stats keep refreshing to current time for all queries. we use SQL2016 and most queries call by front end app. it looks it happen after we upgrade from SQL2012 to SQL2016. we didn't change database and server didn't re-start. Could some expert tell me why it happen? Thanks.

SELECT CASE WHEN dbid = 32767 then 'Resource' ELSE DB_NAME(st.dbid)END AS DBName ,OBJECT_SCHEMA_NAME(st.objectid,dbid) AS [SCHEMA_NAME] ,OBJECT_NAME(st.objectid,dbid)AS [OBJECT_NAME] ,MAX(qs.creation_time) AS 'cache_time' ,MAX(qs.last_execution_time) AS 'last_execution_time' ,MAX(cp.usecounts) AS [execution_count] ,SUM(total_worker_time) / MAX(cp.usecounts) AS AVG_CPU ,SUM(total_elapsed_time) / MAX(cp.usecounts) AS AVG_ELAPSED ,SUM(total_logical_reads) / MAX(cp.usecounts) AS AVG_LOGICAL_READS ,SUM(total_logical_writes) / MAX(cp.usecounts) AS AVG_LOGICAL_WRITES ,SUM(total_physical_reads) / MAX(cp.usecounts)AS AVG_PHYSICAL_READS FROM sys.dm_exec_query_stats qs join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st WHERE objtype = 'Proc' AND dbid <> 32767 AND text NOT LIKE '%CREATE FUNC%' GROUP BY cp.plan_handle,DBID,objectid

more ▼

asked Nov 25, 2017 at 02:34 PM in Default

avatar image

196 13 20 26

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

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

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 25, 2017 at 02:34 PM

Seen: 33 times

Last Updated: Dec 04, 2017 at 04:37 PM

Copyright 2018 Redgate Software. Privacy Policy