Trace execution plans

How to trace the actual execution plans of your queries.

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.

Actual execution plans can be captured with no noticeable performance impact thanks to the lightweight profiling infrastructure, on SQL Server 2017 from CU21 onwards, and on SQL Server 2019 and later.

Create the event session

Run the following on your server. It creates the session without starting it, so there is no impact yet.

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 every execution plan. The trace is written to binary files in the server log directory, in at most 5 files of 100 MB each, with the .xel extension.

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

Filtering on one database

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'your_database_name'))
ADD TARGET package0.event_file(SET filename=N'plan_capture',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO

Replace your_database_name with the name of the target database.

Filtering on one login

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

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'your_database_name'
       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.

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].[session_id] > 50 -- 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:

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

Stop the session

Once the batch is over, stop the session:

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:

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.

SQL Server Expertise Need help with SQL Server? Contact me