Phantom results from a DM or Missing results from Profiler Trace?

by Patrick Trongo   Last Updated October 09, 2017 18:06 PM

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]


Related Questions


Capture and Replay workload

Updated May 13, 2015 16:02 PM

Database performance stats

Updated October 04, 2017 04:06 AM


SQL Profiler plus Include Actual Execution Plan

Updated May 24, 2018 19:06 PM