Part of the DBA-Tools Project.
Query Store and the plan cache tell you what’s expensive. Neither tells you what’s actually called, by what, how often. Before a migration, you need to know which stored procedures matter in practice, not which ones look interesting in a cached plan that might not even be current anymore. Before a decommission, you need the same answer for a different reason, is anything still calling in.
This script creates an Extended Events session that captures RPC execution, procedure name, duration, login, and calling host, for exactly that purpose. Run it for a representative window and you get a real call profile instead of a guess based on what happens to be sitting in cache today.
Why SP Execution Session Matters
Stored procedure call volume is invisible from most of the usual places a DBA looks:
- The plan cache only shows what’s currently cached, and plans get evicted under memory pressure or a recompile, so “not in cache” doesn’t mean “not called.”
- Query Store captures runtime statistics well, but tying that back to “which application, from which host, is actually calling this” isn’t its job.
- A migration or refactor that misses a rarely-called but business-critical procedure fails quietly until the one day of the month it actually runs.
When to Run This Script
- Before a migration, to build an accurate inventory of which procedures are actually called and by what
- Before decommissioning a server or database, alongside Create Decommission Audit Session as a procedure-level view of activity
- Profiling call frequency and duration for a specific database’s procedures, using the database filter to scope the capture
- Investigating “is this procedure still used” questions during a cleanup or refactor
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Create-SpExecutionSession
Category : traces
Purpose : Creates an Extended Events session capturing stored procedure and RPC execution — procedure name, duration, login, and hostname.
Use to profile what procedures are called most often, by whom, and how long they take — especially useful before a migration or decommission.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-create-sp-execution-session/)
Requires : ALTER ANY EVENT SESSION, VIEW SERVER STATE
*/
-- SAFE:CreatesObjects
-- IMPACT:Low
SET NOCOUNT ON;
/* ── Configuration — edit these before running ───────────────────────────── */
DECLARE @SessionName NVARCHAR(128) = N'SpExecution';
DECLARE @DatabaseName NVARCHAR(128) = N''; /* target database; blank = all databases */
DECLARE @TraceFolder NVARCHAR(260) = N'D:\SQLTrace';
/* Folder must exist; SQL Server service account needs write access. */
DECLARE @MaxFileMB INT = 100; /* max size per .xel file in MB */
DECLARE @MaxFiles INT = 7; /* number of rollover files; oldest is overwritten first */
DECLARE @RetentionDays INT = 7;
/* SQL Server 2025+ (v17+) only: session auto-stops after this many days.
On older versions this has no effect — the session runs until you stop
it manually. */
/* ─────────────────────────────────────────────────────────────────────────── */
DECLARE @ProductMajorVersion INT = CAST(SERVERPROPERTY('ProductMajorVersion') AS INT);
/* Escape any single-quotes in the path so the dynamic SQL string stays valid */
DECLARE @FilePathRaw NVARCHAR(260) = @TraceFolder + N'\' + @SessionName + N'.xel';
DECLARE @FilePathEsc NVARCHAR(522) = REPLACE(@FilePathRaw, N'''', N'''''');
DECLARE @dbFilter NVARCHAR(500) = N'';
IF @DatabaseName <> N''
SET @dbFilter = N'WHERE sqlserver.database_name = N''' + REPLACE(@DatabaseName, N'''', N'''''') + N'''';
DECLARE @sql NVARCHAR(MAX);
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = @SessionName)
BEGIN
SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER STATE = STOP;
DROP EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER;';
EXEC sp_executesql @sql;
PRINT 'Existing session ' + @SessionName + ' stopped and dropped.';
END;
/* Build WITH clause — MAX_DURATION is SQL Server 2025+ (v17+) only */
DECLARE @WithClause NVARCHAR(500) =
N'WITH (
MAX_DISPATCH_LATENCY = 15 SECONDS,
STARTUP_STATE = ON';
IF @ProductMajorVersion >= 17
SET @WithClause = @WithClause
+ N',
MAX_DURATION = ' + CAST(@RetentionDays AS NVARCHAR(5)) + N' DAYS';
SET @WithClause = @WithClause + N'
)';
SET @sql = N'
CREATE EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER
ADD EVENT sqlserver.rpc_completed (
ACTION (
sqlserver.client_hostname,
sqlserver.client_app_name,
sqlserver.username,
sqlserver.nt_username,
sqlserver.server_principal_name,
sqlserver.database_name
)
' + @dbFilter + N'
)
ADD TARGET package0.event_file (
SET filename = N''' + @FilePathEsc + N''',
max_file_size = ' + CAST(@MaxFileMB AS NVARCHAR(10)) + N',
max_rollover_files = ' + CAST(@MaxFiles AS NVARCHAR(10)) + N'
)
' + @WithClause + N';
ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER STATE = START;';
BEGIN TRY
EXEC sp_executesql @sql;
SELECT
@SessionName AS session_name,
@FilePathRaw AS output_file,
NULLIF(@DatabaseName, N'') AS database_filter,
@MaxFileMB AS max_file_mb,
@MaxFiles AS max_files,
@MaxFileMB * @MaxFiles AS total_capacity_mb,
CASE WHEN @ProductMajorVersion >= 17
THEN CAST(@RetentionDays AS VARCHAR(5)) + ' days (MAX_DURATION)'
ELSE 'manual stop (pre-2025)'
END AS session_lifetime,
'RUNNING' AS status,
'ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + ' ON SERVER STATE = STOP; DROP EVENT SESSION ' + QUOTENAME(@SessionName) + ' ON SERVER;'
AS remove_cmd;
PRINT 'Session ' + @SessionName + ' created and started.';
PRINT 'Output file : ' + @FilePathRaw;
PRINT 'View data : SSMS > Management > Extended Events > Sessions > ' + @SessionName + ' > right-click target > View Target Data';
END TRY
BEGIN CATCH
PRINT 'ERROR creating session: ' + ERROR_MESSAGE();
THROW;
END CATCH;
This drops and recreates the session if one already exists under the same name, then builds and starts a server-scoped Extended Events session capturing sqlserver.rpc_completed, optionally filtered to one database, writing the results to a rolling .xel file target.
Before you run this: @TraceFolder defaults to D:\SQLTrace, and that folder has to exist and be writable by the SQL Server service account before the session will start. Create the folder first, or edit @TraceFolder at the top of the script to point at a folder that already exists on your instance. Note also that sqlserver.rpc_completed only fires for procedure calls made over the TDS RPC protocol, calls via EXEC inside an ad-hoc T-SQL batch show up as a batch event instead, not here.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Create (or safely recreate) the SP execution trace:
.\run.ps1 Create-SpExecutionSession
# To run against a remote sql server:
.\run.ps1 Create-SpExecutionSession -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
| session_name | output_file | database_filter | max_file_mb | max_files | total_capacity_mb | session_lifetime | status |
|---|---|---|---|---|---|---|---|
| SpExecution | D:\SQLTrace\SpExecution.xel | (none) | 100 | 7 | 700 | 7 days (MAX_DURATION) | RUNNING |
Real output captured against a local SQL Server 2025 (v17) instance, @DatabaseName left blank so this run captures RPC calls against every database on the instance. Rolling capacity is 700MB across 7 files, matching the script’s default 7-day observation window with one file’s worth of headroom per day.
Understanding the Results
database_filtershowsNULLhere because the default run captures every database. Set@DatabaseNamebefore running to scope this to one database’s procedure calls only.total_capacity_mbat 700MB sits between the Login Activity session (600MB) and the Decommission Audit session (1.4GB), reflecting call volume: procedure execution is busier than logins alone but lighter than full batch-plus-RPC capture across every database.session_lifetimereads7 days (MAX_DURATION)on SQL Server 2025 and later, so this session stops itself. On older versions it runs until you stop it manually.remove_cmdis the exact stop-and-drop DDL for this session, generated for you and ready to paste when you’re done reviewing the results.
Best Practices
- The trace folder must exist and be writable before you run this, the script can’t create it for you.
- Run for a representative window, at least a full business week, so infrequent but real callers show up rather than just the noisiest ones.
- Scope
@DatabaseNamewhen you’re profiling a specific application or database rather than the whole instance, it keeps the capture focused and the output easier to read. - Remember the RPC-only scope: a genuinely unused-looking procedure that’s actually called from within another stored procedure’s
EXECstatement, or from an ad-hoc batch, won’t show up here the way a direct RPC call would.
Related Scripts
You may also find these scripts useful:
- Create Decommission Audit Session
- Create Login Activity Session
- Get Active XE Sessions
- Get XE Session Activity
- Remove XE Session
Summary
Knowing which stored procedures are actually called, and by what, is the kind of fact that’s easy to assume and expensive to get wrong ahead of a migration or a cleanup. This script builds a real call profile from live RPC traffic instead of relying on whatever happens to still be sitting in the plan cache.
Run it for a full week before trusting any conclusion about a procedure’s usage, then read the captured file back with Get XE Session Activity.
Leave a Reply