ConfigMgr by default runs in every 10 minutes captures a snapshot of the running and waiting SQL SPIDs in the DBMON_RunningQueries table. This includes CPU running time, Waiting time , Wait_Type, Proposed Execution Plan, The Number of locks and Memory acquired by the SPID.

So here as a case study we know the info that the Incoming Messages for DRS were not getting processed the last day. Ideally if the issue is not existent now we could not do anything but with ConfigMgr we do capture the snapshot of running queries every 10 mins.

So for the DRS the component is SMS_DATA_REPLICATION_SERVICE. If you are monitoring other component you can change it below like for State Messages it is SMS_STATE_SYSTEM. 

Below is the query that will give us details of the last 72 hours from the time of interest.

 

 

SQL
Edit|Remove
DECLARE @timeOfInterest DATETIME = '2019-03-05 00:00:00.000' 
 
DECLARE @spanOfHours INT = 72   -- update this if you want to increase or reduce the number of hours before/after the time @timeOfInterest 
DECLARE @componentBlocked NVARCHAR(512) = N'SMS_DATA_REPLICATION_SERVICE'  
 
IF (@timeOfInterest IS NULLBEGIN 
    PRINT 'PLEASE specify a valid date time for @timeOfInterest in UTC time' 
    PRINT 'i.e. if you want to check UTC time what was happening around 2019-03-05, SET @timeOfInterest = N''2019-03-05 00:00:00.000''' 
END 
ELSE 
BEGIN 
    DECLARE @timeStart DATETIME  
    DECLARE @timeEnd DATETIME 
 
    SELECT @timeStart = DATEADD(hh, -1*@spanOfHours@timeOfInterest), @timeEnd =  DATEADD(hh@spanOfHours@timeOfInterest) 
 
    -- specifically look for anything  
    ;WITH CompOfInterest AS 
    ( 
        SELECT  
            ID, 
            SnapshotTime, 
            session_id, 
            program_name, 
            Context, 
            host_name, 
            start_time, 
            running_time, 
            cpu_time, 
            wait_time, 
            wait_type, 
            last_wait_type, 
            wait_resource, 
            reads, 
            writes, 
            SUBSTRING(convert(nvarchar(max), text), statement_start_offset/2statement_end_offset/2as currentstatement, 
            statement_start_offset, 
            statement_end_offset, 
            text, 
            blocking_session_id  
        FROM DBMON_RunningQueries WITH (NOLOCKWHERE program_name=@componentBlocked AND SnapshotTime BETWEEN @timeStart AND @timeEnd 
    ) 
    SELECT  
            b.SnapshotTime, 
            b.session_id, 
            b.blocking_session_id, 
            b.program_name, 
            b.Context, 
            b.host_name, 
            b.start_time, 
            b.running_time, 
            b.cpu_time, 
            b.wait_time, 
            b.wait_type, 
            b.last_wait_type, 
            b.wait_resource, 
            b.reads, 
            b.writes, 
            SUBSTRING(convert(nvarchar(max), b.text), b.statement_start_offset/2b.statement_end_offset/2as currentstatement, 
            b.statement_start_offset, 
            b.statement_end_offset, 
            b.text, 
            b.ID 
        FROM DBMON_RunningQueries b WITH (NOLOCK)  
        INNER JOIN CompOfInterest c ON b.session_id = c.blocking_session_id   
        WHERE b.SnapshotTime BETWEEN @timeStart AND @timeEnd 
    UNION ALL 
    SELECT  
        SnapshotTime, 
        session_id, 
        blocking_session_id, 
        program_name, 
        Context, 
        host_name, 
        start_time, 
        running_time, 
        cpu_time, 
        wait_time, 
        wait_type, 
        last_wait_type, 
        wait_resource, 
        reads, 
        writes, 
        SUBSTRING(convert(nvarchar(max), text), statement_start_offset/2statement_end_offset/2as currentstatement, 
        statement_start_offset, 
        statement_end_offset, 
        text, 
        ID 
    FROM CompOfInterest 
    ORDER BY ID DESC 
END