Trended Cache Execution Analysis Workflow (POC)

⭐ Why Trended Cache Analysis Is a Best Practice

Logging running memory statistics over a rolling window provides a highly defensive blueprint for system optimization:

• Beats the Volatile Cache Flaw: Standard SQL Server execution stats clear out on reboots or plan evictions. Persistent logging captures long-term runtime history so you never misidentify rare but critical end-of-month routines as dead code.
• Proactive Risk Shield: By cross-referencing lower environments simultaneously, it keeps you from dropping procedures that look dead in Production but are actively running in developer forks or staging test loops.
• Objective Stakeholder Assurance: Showing a flat zero execution count directly alongside weeks or months of verified server uptime numbers completely eliminates user anxiety and accelerates decommission approvals.
• Dual-Action Performance Tuning: It builds a unified repository that satisfies two separate teams—a technical debt loop for purging old clutter, and a performance loop for finding immediate optimization targets.

1 Centralized Repository Table Setup

Configures a persistent logging table inside DBA_Maintenance to record rolling snapshots of execution counts alongside the system uptime metadata needed to trust the numbers.

✔ Setup Script:

USE DBA_Maintenance;
GO

CREATE TABLE dbo.SP_Activity_Rolling_Snapshots (
    SnapshotID INT IDENTITY(1,1) PRIMARY KEY,
    SnapshotDate DATETIME DEFAULT GETDATE(),
    ServerName VARCHAR(128) NOT NULL,
    DatabaseName VARCHAR(128) NOT NULL,
    SchemaName VARCHAR(64) NULL,
    ObjectName VARCHAR(128) NOT NULL,
    TotalExecutionsSinceReboot BIGINT NOT NULL,
    ServerUptimeDaysAtSnapshot INT NULL
);
GO

2 Automation: SQL Server Agent Monthly Baseline Job

To make sure we accurately catch rare routines, end-of-quarter updates, and month-end closing procedures, we deploy a native SQL Server Agent Job on the Central server. This runs completely hands-free on the 1st of every month at midnight to append cache history before reboots or plan evictions wipe memory clean.

✔ SQL Agent T-SQL Deployment Script:

USE msdb;
GO

-- 1. Create the background Agent Job container
EXEC dbo.sp_add_job 
    @job_name = N'DBA_Maintenance_Monthly_Cache_Snapshot', 
    @enabled = 1, 
    @description = N'Captures execution plan counts before month-end reboots wipe volatile memory catalogs.';
GO

-- 2. Target the local SQL Server instance engine
EXEC dbo.sp_add_jobserver 
    @job_name = N'DBA_Maintenance_Monthly_Cache_Snapshot', 
    @server_name = N'(local)';
GO

-- 3. Define Job Step: Fires the local snapshot query collection loop
EXEC dbo.sp_add_jobstep 
    @job_name = N'DBA_Maintenance_Monthly_Cache_Snapshot', 
    @step_name = N'Log Cache Metrics', 
    @subsystem = N'TSQL', 
    @command = N'
        INSERT INTO DBA_Maintenance.dbo.SP_Activity_Rolling_Snapshots (ServerName, DatabaseName, SchemaName, ObjectName, TotalExecutionsSinceReboot, ServerUptimeDaysAtSnapshot)
        SELECT @@SERVERNAME, DB_NAME(st.dbid), OBJECT_SCHEMA_NAME(st.objectid, st.dbid), OBJECT_NAME(st.objectid, st.dbid), ps.execution_count, (SELECT (ms_ticks/1000/60/60/24) FROM sys.dm_os_sys_info)
        FROM sys.dm_exec_procedure_stats ps
        CROSS APPLY sys.dm_exec_sql_text(ps.plan_handle) st
        WHERE st.dbid IS NOT NULL AND DB_NAME(st.dbid) NOT IN (''master'','''model'','''msdb'','''tempdb'');', 
    @retry_attempts = 2, 
    @retry_interval = 5;
GO

-- 4. Create the automated Schedule: Triggers on Day 1 of every month at 12:00 AM
EXEC dbo.sp_add_jobschedule 
    @job_name = N'DBA_Maintenance_Monthly_Cache_Snapshot', 
    @name = N'Monthly_First_Day_Midnight_Schedule', 
    @freq_type = 16,          -- Monthly frequency rule
    @freq_interval = 1,       -- Day 1 of the designated month
    @freq_recurrence_factor = 1, 
    @active_start_time = 000000; -- Midnight military timestamp (HHMMSS)
GO

3 Multi-Server Workstation Cache Collector Script

For cross-server collections, this PowerShell script can be manually run or scheduled via task manager to reach across server isolation boundaries, query remote instances, and securely feed the central repository metrics back home.

✔ PowerShell Cache Collector:

import-module sqlserver -DisableNameChecking

$TargetServers = @("VA-DB-PROD01", "VA-DB-STAGE01", "VA-DB-TEST01", "VA-DB-DEV01")
$CentralServer = "DESKTOP-LOEABP1\TEST"

$CacheQuery = @"
    SELECT @@SERVERNAME AS ServerName, DB_NAME(st.dbid) AS DatabaseName,
           OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS SchemaName, OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
           ps.execution_count AS TotalExecutionsSinceReboot,
           (SELECT (ms_ticks/1000/60/60/24) FROM sys.dm_os_sys_info) AS ServerUptimeDaysAtSnapshot
    FROM sys.dm_exec_procedure_stats ps
    CROSS APPLY sys.dm_exec_sql_text(ps.plan_handle) st
    WHERE st.dbid IS NOT NULL AND DB_NAME(st.dbid) NOT IN ('master','model','msdb','tempdb');
"@

foreach ($Server in $TargetServers) {
    try {
        $Data = Invoke-Sqlcmd -ServerInstance $Server -Query $CacheQuery -ErrorAction Stop
        foreach ($Row in $Data) {
            $InsertQuery = "INSERT INTO DBA_Maintenance.dbo.SP_Activity_Rolling_Snapshots VALUES (GETDATE(), '$($Row.ServerName)', '$($Row.DatabaseName)', '$($Row.SchemaName)', '$($Row.ObjectName)', $($Row.TotalExecutionsSinceReboot), $($Row.ServerUptimeDaysAtSnapshot))"
            Invoke-Sqlcmd -ServerInstance $CentralServer -Query $InsertQuery
        }
    } catch { Write-Warning "Failed to log cache for $Server : $_" }
}

4 The Technical Debt Evaluation Query

This analytics layer filters out any stored procedures that have actively run in Production, then applies a clean 2-Status Risk Model to protect environments under active review.

✔ Clean Risk Assessment Query:

USE DBA_Maintenance;
GO

WITH ObjectExecutionSummary AS (
    SELECT ISNULL(SchemaName, 'dbo') AS SchemaName, ObjectName, COUNT(SnapshotDate) AS TotalGlobalSnapshots,
           MAX(ServerUptimeDaysAtSnapshot) AS MaxObservedUptimeDays,
           MAX(CASE WHEN DatabaseName LIKE 'VA-PROD%' THEN TotalExecutionsSinceReboot ELSE 0 END) AS MaxProdExecs,
           MAX(CASE WHEN DatabaseName LIKE 'VA-STAGE%' THEN TotalExecutionsSinceReboot ELSE 0 END) AS MaxStageExecs,
           MAX(CASE WHEN DatabaseName LIKE 'VA-DEV%' THEN TotalExecutionsSinceReboot ELSE 0 END) AS MaxDevExecs
    FROM dbo.SP_Activity_Rolling_Snapshots
    GROUP BY ISNULL(SchemaName, 'dbo'), ObjectName
)
SELECT SchemaName, ObjectName, MaxObservedUptimeDays AS UptimeDays, MaxProdExecs AS ProdExecs, MaxStageExecs AS StageExecs, MaxDevExecs AS DevExecs,
       CASE WHEN MaxStageExecs = 0 AND MaxDevExecs = 0 THEN 'SAFE TO ARCHIVE' ELSE 'HOLD / UNDER REVIEW' END AS CleanupStatus
FROM ObjectExecutionSummary WHERE MaxProdExecs = 0 ORDER BY CleanupStatus DESC;
GO