How to persist SQL Server system table records?

I am running a query that returns the last execution time for a stored procedure:

SELECT 
    o.name, 
    ps.last_execution_time 
FROM   
    sys.dm_exec_procedure_stats ps 
INNER JOIN 
    sys.objects o ON ps.object_id = o.object_id 
ORDER BY 
    ps.last_execution_time DESC 

I am getting the correct results, but if I run the query again in around 30 seconds, I don’t get any results.

Is there a setting or command I need to set or add to persist the results?

My goal is to find out what stored procedures ran in the past 3 days. I’m running this query against SQL Server 2019 Express.

Answer

I would suggest extended events for this. First, the session definition:

CREATE EVENT SESSION [ProcExecutions] ON SERVER
    ADD EVENT sqlserver.module_end
    ADD TARGET package0.event_file(
        SET filename = N'ProcExecutions',
        max_file_size = 10,
        max_rollover_files = 5
    )
    WITH (
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = ON
    )
GO
ALTER EVENT SESSION [ProcExecutions] ON SERVER
    STATE = START;

You may want to modify the session definition to suit your needs. Examples would be:

• Filtering by a particular user (e.g. your application’s login)
• Grabbing just a sample (e.g. “one in a hundred executions”)
• Grab additional data (e.g. “what user called the proc?”, “what was the whole statement (including parameters)?”, etc)

Here’s how to read the data:

IF object_id('tempdb.dbo.#events') IS NOT NULL
    DROP TABLE #events;

select cast(event_data as xml) as [event]
into #events
from sys.fn_xe_file_target_read_file('ProcExecutions*.xel', null, null, null);

WITH XEData AS (
    SELECT
        [event].value('(event/@timestamp)[1]', 'datetime2') AS [timestamp],
        db_name([event].value('(event/data[@name="source_database_id"])[1]', 'int')) AS [database],
        [event].value('(event/data[@name="object_name"])[1]', 'sysname') AS [object_name],
        [event].query('.') AS [event]
    from #events
)
SELECT * 
FROM XEData
ORDER BY [timestamp];

Again, this is very basic (returning only the timestamp, database, and procedure name). When I set about munging a new event session’s data, I’ll use that event column as a reference for what the XML looks like so I can write appropriate xpath expressions to pull the data that I need.