Selectively disable “Include actual execution plan”

The “include actual execution plan” feature in SQL Server Management Studio is an invaluable tool for performance tuning. It returns the actual execution plan used for each statement, including actual row counts, tempdb spills and a lot of other information you need to do performance tuning.

But sometimes you want to run a series of statements or procedures where you only want the execution plan for some of the statements. Here’s how:

The actual execution plan is enabled by turning on SET STATISTICS XML., not unlike enabling STATISTICS IO or TIME. And just like SET NOCOUNT, the SET statements apply to the current context, which could be a stored procedure, a session, etc. When this context ends, the setting reverts to that of the parent context.

Say you have a sequence of commands in a stored procedure,

EXECUTE dbo.LogEntry @txt='Starting calculation';
EXECUTE dbo.CalculateStuff;
EXECUTE dbo.LogEntry @txt='Calculation finished';

You might want to know the actual execution plan of the “CalculateStuff” procedure, but turning on “Include actual execution plan” would also return all the execution plans of the logging procedure calls, which degrades performance and clutters up your Management Studio output.

The solution is to specifically disable the execution plan in the log procedures;

CREATE PROCEDURE dbo.LogEntry
    @txt     varchar(max)
AS

SET STATISTICS XML OFF;    --- Just in case it's ON in the
                           --- parent (calling) context.

INSERT INTO dbo.LogTable ...

SQL Server will disable the STATISTICS XML setting while inside the LogEntry procedure, but when that procedure finishes, control returns to the parent procedure or script, where STATISTICS XML is still enabled. The effect is that you won’t see the actual execution plan for the logging procedure but for everything else.