question

SSGC avatar image
SSGC asked

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
dmvcache
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.