SQL SERVER – Execution Plan Ignores Tabs, Spaces and Comments

July 14, 2018

Just another day I was delivering my training SQL Server Performance Tuning Practical Workshop, I noticed that organizations had many stored procedures but none of the SP had any comments. When I asked them why their Stored Procedures does not have comments, their point was that they believed because of the comments, the size of the execution plan increases. Not True, when SQL Server builds an execution plan, it ignores tabs, spaces, and all the comments.

Let us see a quick demonstration for the same.

Here we will create three Stored Procedures.

Stored Procedure 1: Create a procedure with spaces

CREATE OR ALTER PROCEDURE TestSP_Space
AS
BEGIN
SELECT 1




SELECT 2
END
GO

Stored Procedure 2: Create a procedure without spaces

CREATE OR ALTER PROCEDURE TestSP
AS
BEGIN
SELECT 1
SELECT 2
END
GO

Stored Procedure 3: Create a procedure with commments

CREATE OR ALTER PROCEDURE TestSP_Comments
AS
BEGIN
SELECT 1
/*
Lorem Ipsum is simply dummy text of the printing and typesetting industry. 
Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, 
when an unknown printer took a galley of type and scrambled it to make a type specimen book. 
It has survived not only five centuries, but also the leap into electronic typesetting, 
remaining essentially unchanged. 
It was popularised in the 1960s with the release of Letraset 
sheets containing Lorem Ipsum passages, 
and more recently with desktop publishing software 
like Aldus PageMaker including versions of Lorem Ipsum.
*/
SELECT 2
END
GO

Once the three SPs are created. Run the following script to execute them multiple times so they are properly cached.

EXEC TestSP_Space
GO 5
EXEC TestSP
GO 5
EXEC TestSP_Comments
GO 5

Now run the following script which will show you the size of the execution plan for the stored procedure. You will notice that the size of the execution plan for all the three SP will be the same.

SELECT usecounts, cacheobjtype, objtype, text, size_in_bytes,
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE [Text] LIKE '%TestSP%'
ORDER BY usecounts DESC;
GO