I was using a table that logs the results of querying
dm_exec_procedure_stats every minute to identify activity of stored procedures on one of my servers. basically it compares the database, object and cache time and either updates the current record or inserts a new one. I know accuracy will not be great, but in my case it shouldn't be too off. I wanted to get a feeling on what’s getting hit. Our PLE is not too bad on the server.
Anyway, I expected to be missing activity, but what I've ended up with is activity I can't verify. I have one procedure which seems to be having high activity, so I wanted to get a sample call. I started a profiler trace, and it's coming back with no, zero, nada, zilch activity. I thought I goofed my definition, and changed the target to another proc I was interested in, and its almost right on the money with the activity? Anyone have an explanation?almost right on the money with the activity? Anyone have an explanation?
Sample of my Job code to Log data....
--Code to log results from dm_exec_procedure_stats Use [traceDB] DECLARE @t1 table ( [Database] [nvarchar](128) NULL, [Object] [nvarchar](128) NULL, [cached_time] [datetime] NULL, [last_execution_time] [datetime] NULL, [execution_count] [bigint] NOT NULL, [total_physical_reads] [bigint] NOT NULL, [total_logical_writes] [bigint] NOT NULL ) DECLARE @U AS TABLE( [Object] [nvarchar](128) NULL ) INSERT INTO @t1 ( [Database] , [Object] , [cached_time] , [last_execution_time] , [execution_count] , [total_physical_reads] , [total_logical_writes] ) SELECT DB_NAME(DB_ID()) AS [Database] ,OBJECT_NAME(object_id) AS [Object] ,cached_time ,last_execution_time ,execution_count ,total_physical_reads ,total_logical_writes FROM sys.dm_exec_procedure_stats s WHERE OBJECT_NAME(object_id) IS NOT NULL UPDATE prclog SET prclog.[execution_count] = t.[execution_count] , prclog.[total_physical_reads] = t.[total_physical_reads] , prclog.[total_logical_writes] = t.[total_logical_writes] , prclog.[last_execution_time] = t.[last_execution_time] OUTPUT [Inserted].[Object] INTO @U FROM dbo.exec_procedure_stats_log prclog INNER JOIN @t1 t ON [prclog].[Database] = t.[Database] AND [prclog].[cached_time] = t.[cached_time] DELETE t FROM @t1 t INNER JOIN @U ON [@U].[Object] = [t].[Object] INSERT INTO dbo.exec_procedure_stats_log SELECT t.[Database] , t.[Object] , t.[cached_time] , t.[last_execution_time] , t.[execution_count] , t.[total_physical_reads] , t.[total_logical_writes] FROM @t1 t
Query the results of the above job
--Code to view results of last hours entries SELECT [Database] , [Object] , SUM([execution_count]) AS [Executions] , MIN([cached_time]) AS [since] , MAX([last_execution_time]) AS [last_execution_time] FROM [dbo].[exec_procedure_stats_log] WHERE [last_execution_time] > DATEADD(MINUTE, -60, GETDATE()) GROUP BY [Database] , [Object]