I have a table that has been updated hourly with data from sys.dm_exec_procedure_stats for over a year now.
If I don't see a procedure listed in this table, can I be more than 99% sure it is no longer used? I mean, I understand there may be some crazy edge case scenario where someone designed a process to run a proc and then immediately remove it from the cache so its usage never gets recorded by my process. I'm obviously willing to ignore those kinds of silly edge case scenarios.
The sys.dm_exec_procedure_stats DMV reflects current state of the procedure cache. SQL Server removes entries from this DMV when the corresponding cache entry is removed so you might miss stored procedure executions by taking periodic snapshots of this DMV. Also, stored procs with RECOMPILE would not be captured.
A more reliable method to identify all stored proc executions is with a server-side trace that writes to a file target. The trace data can then be summarized and saved to a table.
Below is example DDL for an XE trace of module_end events, supporting SQL objects, and a PowerShell script. The PowerShell script summarizes stored proc executions from the trace file(s) and saves summary data to a permanent table for analysis. The PS script can be scheduled periodically to process rolled over trace files.
USE YourDatabase;
CREATE EVENT SESSION [StoredProcedureExecutions] ON SERVER
ADD EVENT sqlserver.module_end(
WHERE ([package0].[not_equal_uint64]([source_database_id],(32767)) AND [sqlserver].[equal_i_sql_ansi_string]([object_type],'P')))
ADD TARGET package0.event_file(SET filename=N'D:\SqlTraceFiles\StoredProcedureExecutions',max_file_size=(100),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);
ALTER EVENT SESSION [StoredProcedureExecutions] ON SERVER
STATE=START;
CREATE TABLE dbo.ModuleEndSummaryStaging(
source_database_id smallint NOT NULL
, object_id int NOT NULL
, object_name sysname NOT NULL
, execution_count int NOT NULL
, min_timestamp datetimeoffset NOT NULL
, max_timestamp datetimeoffset NOT NULL
CONSTRAINT PK_ModuleEndSummaryStaging PRIMARY KEY(
source_database_id
, object_id
, object_name
)
);
GO
CREATE TABLE dbo.StoredProcedureExecutionHistory(
DatabaseName sysname NOT NULL
, SchemaName sysname NOT NULL
, ObjectName sysname NOT NULL
, source_database_id smallint NOT NULL
, object_id int NOT NULL
, object_name sysname NOT NULL
, ExecutionCount bigint
, FirstExecutionTimestamp datetimeoffset NOT NULL
, LastExecutionTimestamp datetimeoffset NOT NULL
, CONSTRAINT PK_StoredProcedureExecutionHistory PRIMARY KEY (
source_database_id
, object_id
, object_name
, DatabaseName
, SchemaName
, ObjectName)
);
GO
CREATE OR ALTER PROCEDURE dbo.MergeStoredProcedureExecutionHistory
AS
SET NOCOUNT ON;
MERGE dbo.StoredProcedureExecutionHistory AS target
USING (
SELECT
source_database_id
, object_id
, object_name
, execution_count
, min_timestamp
, max_timestamp
, COALESCE(DB_NAME(source_database_id), N'') AS DatabaseName
, COALESCE(OBJECT_SCHEMA_NAME(object_id, source_database_id), N'') AS SchemaName
, COALESCE(OBJECT_NAME(object_id, source_database_id), N'') AS ObjectName
FROM dbo.ModuleEndSummaryStaging
) AS source ON
source.source_database_id = target.source_database_id
AND source.object_id = target.object_id
AND source.object_name = target.object_name
AND source.DatabaseName = target.DatabaseName
AND source.SchemaName = target.SchemaName
AND source.ObjectName = target.ObjectName
WHEN MATCHED THEN
UPDATE SET
ExecutionCount += source.execution_count
, FirstExecutionTimestamp = CASE WHEN source.min_timestamp < target.FirstExecutionTimestamp THEN source.min_timestamp ELSE target.FirstExecutionTimestamp END
, LastExecutionTimestamp = CASE WHEN source.max_timestamp > target.LastExecutionTimestamp THEN source.max_timestamp ELSE target.LastExecutionTimestamp END
WHEN NOT MATCHED BY TARGET THEN
INSERT (
DatabaseName
, SchemaName
, ObjectName
, source_database_id
, object_id
, object_name
, ExecutionCount
, FirstExecutionTimestamp
, LastExecutionTimestamp
)
VALUES (
source.DatabaseName
, source.SchemaName
, source.ObjectName
, source.source_database_id
, source.object_id
, source.object_name
, source.execution_count
, source.min_timestamp
, source.max_timestamp
);
GO
Below is the PS script example. You'll need to modify the referenced assembly paths for your system (I used the one's installed with the latest SSMS version).
Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\Shared\Microsoft.SqlServer.XE.Core.dll"
Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\Shared\Microsoft.SqlServer.XEvent.Linq.dll"
# utility class to summarize proc calls by source_database_id, object_id, and object_name
Add-Type -TypeDefinition `
@"
using System;
using System.Collections.Generic;
using System.Text;
namespace ExtendedEventsUtility
{
public static class ExtendedEventsAggegator
{
public static Dictionary<string, ModuleEndSummary> AggregatedEvents = new Dictionary<string, ModuleEndSummary>();
public static void AggregateTraceFiles(string xeFilePathPattern)
{
AggregatedEvents.Clear();
using (var events = new Microsoft.SqlServer.XEvent.Linq.QueryableXEventData(xeFilePathPattern))
{
foreach (var xe in events)
{
ExtendedEventsAggegator.aggregateEvent(xe);
}
}
}
private static void aggregateEvent(Microsoft.SqlServer.XEvent.Linq.PublishedEvent eventData)
{
ModuleEndSummary aggregatedEvent;
var key = new StringBuilder();
key.Append(eventData.Fields["source_database_id"].Value.ToString());
key.Append("|");
key.Append(eventData.Fields["object_id"].Value.ToString());
key.Append("|");
key.Append(eventData.Fields["object_name"].Value.ToString());
var keyValue = key.ToString();
if (AggregatedEvents.ContainsKey(keyValue))
{
aggregatedEvent = AggregatedEvents[keyValue];
}
else
{
aggregatedEvent = new ModuleEndSummary()
{
source_database_id = (UInt32)eventData.Fields["source_database_id"].Value,
object_id = (Int32)eventData.Fields["object_id"].Value,
object_name = (string)eventData.Fields["object_name"].Value
};
AggregatedEvents.Add(keyValue, aggregatedEvent);
}
aggregatedEvent.executionCount += 1;
if((DateTimeOffset)eventData.Timestamp < aggregatedEvent.minTimestamp)
{
aggregatedEvent.minTimestamp = (DateTimeOffset)eventData.Timestamp;
}
if ((DateTimeOffset)eventData.Timestamp > aggregatedEvent.maxTimestamp)
{
aggregatedEvent.maxTimestamp = (DateTimeOffset)eventData.Timestamp;
}
}
}
public class ModuleEndSummary
{
public UInt32 source_database_id;
public Int32 object_id;
public string object_name;
public Int32 executionCount = 0;
public DateTimeOffset minTimestamp = DateTimeOffset.MaxValue;
public DateTimeOffset maxTimestamp = DateTimeOffset.MinValue;
}
}
"@ -ReferencedAssemblies ("C:\Program Files\Microsoft SQL Server\140\Shared\Microsoft.SqlServer.XE.Core.dll", "C:\Program Files\Microsoft SQL Server\140\Shared\Microsoft.SqlServer.XEvent.Linq.dll")
try {
# move trace files that are not currently in use to import staging subfolder
$sourceTraceFolderPath = "D:\SqlTraceFiles\"
$targetTraceSubFolderPath = "D:\SqlTraceFiles\ImportStaging\"
$traceFilePattern = "StoredProcedureExecutions*.xel"
if(!(Test-Path $targetTraceSubFolderPath)) {
[void](New-Item -Path $targetTraceSubFolderPath -ItemType Directory)
}
Get-Item "$sourceTraceFolderPath\$traceFilePattern" | Move-Item -Destination $targetTraceSubFolderPath -ErrorAction Ignore
# aggegate usage by source_database_id, object_id, and object_name
[ExtendedEventsUtility.ExtendedEventsAggegator]::AggregateTraceFiles("$targetTraceSubFolderPath\$traceFilePattern")
# create data table for SqlBulkCopy
$dt = New-Object System.Data.DataTable
[void]$dt.Columns.Add("source_database_id", [System.Type]::GetType("System.Int16"))
[void]$dt.Columns.Add("object_id", [System.Type]::GetType("System.Int32"))
[void]$dt.Columns.Add("object_name", [System.Type]::GetType("System.String"))
[void]$dt.Columns.Add("execution_count", [System.Type]::GetType("System.Int32"))
[void]$dt.Columns.Add("min_timestamp", [System.Type]::GetType("System.DateTimeOffset"))
[void]$dt.Columns.Add("max_timestamp", [System.Type]::GetType("System.DateTimeOffset"))
# load proc execution summary into data table
foreach ($proc in [ExtendedEventsUtility.ExtendedEventsAggegator]::AggregatedEvents.Values) {
$row = $dt.NewRow()
$dt.Rows.Add($row)
$row["source_database_id"] = $proc.source_database_id
$row["object_id"] = $proc.object_id
$row["object_name"] = $proc.object_name
$row["execution_count"] = $proc.executioncount
$row["min_timestamp"] = $proc.mintimestamp
$row["max_timestamp"] = $proc.maxtimestamp
}
# bulk insert execution summary into staging table
$connectionString = "Data Source=.;Integrated Security=SSPI;Initial Catalog=YourDatabase"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand("TRUNCATE TABLE dbo.ModuleEndSummaryStaging;", $connection)
$connection.Open()
[void]$command.ExecuteNonQuery()
$connection.Close()
$bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
$bcp.DestinationTableName = "dbo.ModuleEndSummaryStaging"
$bcp.WriteToServer($dt);
$bcp.Dispose()
# merge proc execution summary into history table
$connection.Open()
$command.CommandText="dbo.MergeStoredProcedureExecutionHistory"
$command.CommandType = [System.Data.CommandType]::StoredProcedure
[void]$command.ExecuteNonQuery()
[void]$connection.Close()
#delete files after import
Get-ChildItem "$targetTraceSubFolderPath\$traceFilePattern" | Remove-Item
} catch {
throw
}
