DBA Scripts: Create SP Execution Session

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
    /* STOP only if it is actually running - ALTER ... STATE = STOP on an already
       stopped session raises Msg 25704 and prints a red error over a successful run */
    IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = @SessionName)
    BEGIN
        SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER STATE = STOP;';
        EXEC sp_executesql @sql;
    END;
    SET @sql = N'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,
        'IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = ' + QUOTENAME(@SessionName, '''') + ') 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:

• sql/traces/Create-SpExecutionSession.sql
• powershell/wrappers/traces/Create-SpExecutionSession.ps1

Example Output

One confirmation row again, and the capacity number is the one to read: 700MB, sitting deliberately between the login session and the decommission audit. Procedure calls are busier than connections but lighter than every batch on the instance. database_filter reads NULL here, so this run profiles procedure calls across every database.

Understanding 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 @DatabaseName when 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 EXEC statement, or from an ad-hoc batch, won’t show up here the way a direct RPC call would.

Microsoft’s reference covers sp_executesql and sys.server_event_sessions in full.

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
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

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.