x

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 at 02:34 PM in Default

avatar image

SSGC
196 13 20 25

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x28
x4

asked: Nov 25 at 02:34 PM

Seen: 19 times

Last Updated: 6 days ago

Copyright 2017 Redgate Software. Privacy Policy