# Trace execution plans

> Capture actual execution plans with an Extended Events session since SQL Server 2017 CU14, filtered by database, login or duration, and what it costs.
>
> Source : https://www.pachadata.com/en/docs/howtos/troubleshooting/trace-execution-plans/
> Author : Rudi Bruchez · updated 2026-10-18
> Markdown version of the page, for tools and LLMs. The HTML page is the reference.


To follow what an application or a batch process really does, it helps to capture the actual execution plans (the plans after execution) of the queries it runs.

Extended Events can do that.

The `query_post_execution_plan_profile` event captures actual execution plans through the lightweight profiling infrastructure, since SQL Server 2017 CU14 and in every later version. These plans give the row counts and reads of each operator, but not its CPU time or elapsed time, which only standard profiling measures. On SQL Server 2017, starting the session turns on lightweight profiling for the whole instance; if you start and stop it often, Microsoft recommends enabling trace flag 7412 at startup.

This capture has a cost. Measured on SQL Server 2025, a session on this event, with no actions, cut the throughput of a transactional workload (single-row reads by primary key taking a few tenths of a millisecond) by 71%, and did not visibly slow down an 820 ms query. On a busy transactional server, keep the session running only for the duration of the process you want to observe.

## Create the event session

Run the following on your server. It creates the session without starting it; the session costs nothing until it is started.

```sql
CREATE EVENT SESSION [plan_capture] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'plan_capture',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO
```

Once started, this session captures the plan of every execution. The trace is written to binary files in the server log directory, with the `.xel` extension: at most 5 files of 100 MB each. Beyond that, the oldest files are deleted, so a long or busy capture keeps only the end of the process; the benchmark quoted above wrote more than 4 GB in 20 seconds without a filter.

You can filter by database or by login, as shown below.

### Filtering on one database

```sql
CREATE EVENT SESSION [plan_capture] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([database_name]=N'PachaDataFormation'))
ADD TARGET package0.event_file(SET filename=N'plan_capture',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO
```

Replace `PachaDataFormation` with the name of the target database. The filter applies to the query's context database: a query run from `master` that reads `PachaDataFormation.Contact.Contact` is attributed to `master`, and this session does not capture it.

### Filtering on one login

```sql
CREATE EVENT SESSION [plan_capture] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[username]=N'rudi'))
ADD TARGET package0.event_file(SET filename=N'plan_capture',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO
```

Replace `rudi` with your login name, Windows or SQL Server. For a Windows login, use the full name, domain included.

### Filtering on both a database and a login

```sql
CREATE EVENT SESSION [plan_capture] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([database_name]=N'PachaDataFormation'
       AND [sqlserver].[username]=N'rudi'))
ADD TARGET package0.event_file(SET filename=N'plan_capture',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO
```

## Filtering down to the most expensive queries

Capturing every execution plan is usually pointless. You can:

1. filter out the system databases (`master`, `tempdb`, `msdb`), and `SSISDB` if you use Integration Services;
2. filter on duration, expressed in microseconds.

This example uses both filters to keep only the queries running longer than 100 milliseconds.

These filters reduce the size of the files; the session costs the queries almost as much, filtered or not. Measured on SQL Server 2025, a session filtered on a duration that no query reached cost nearly as much as the unfiltered session (70% of throughput lost, against 71%).

```sql
CREATE EVENT SESSION [plan_capture] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text)
    WHERE (
        [sqlserver].[is_system] = 0 -- skip system sessions
        AND database_name <> N'master'
        AND database_name <> N'msdb'
        AND database_name <> N'tempdb'
        AND database_name <> N'SSISDB'
        AND duration > 100000 -- 100 milliseconds
    ))
ADD TARGET package0.event_file(
    SET filename=N'plan_capture',max_file_size=(100))
WITH (STARTUP_STATE=OFF);
```

## Start the session

At the right moment, just before your batch starts, enable the session:

```sql
ALTER EVENT SESSION [plan_capture] ON SERVER
STATE = START;
```

## Stop the session

Once the batch is over, stop the session:

```sql
ALTER EVENT SESSION [plan_capture] ON SERVER
STATE = STOP;
```

## Collect the data

Now find the `.xel` files so you can send them to me.

By default they are in the SQL Server error log directory. You can find its path with this query:

```sql
SELECT SERVERPROPERTY('ErrorLogFileName');
```

Go to that directory and locate the `plan_capture*.xel` files.

Zip them: these files compress extremely well.

Then send me the archive.


---

## About the author

Rudi Bruchez, independent SQL Server consultant since 2006: performance audits, tuning, migrations, high availability and training. For an audit or hands-on help on your server: https://www.pachadata.com/en/contact/ (remote audit: https://www.pachadata.com/en/services/remote-sql-server-audit/).
