Have you ever wondered how SQL Server builds an execution plan for your stored procedures? Many organizations neglect to add comments to their stored procedures, fearing that it will increase the size of the execution plan. However, this is a common misconception.

Let’s debunk this myth with a quick demonstration. We will create three stored procedures to compare their execution plans.

Stored Procedure 1: Procedure with Spaces

CREATE OR ALTER PROCEDURE TestSP_Space
AS
BEGIN
    SELECT 1

    SELECT 2
END

Stored Procedure 2: Procedure without Spaces

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

Stored Procedure 3: Procedure with Comments

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

Once the three stored procedures are created, we can execute them multiple times to ensure they are properly cached.

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

Now, let’s examine the size of the execution plans for these stored procedures. Run the following script:

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;

You will notice that the size of the execution plan for all three stored procedures is the same. This demonstrates that SQL Server ignores tabs, spaces, and comments when building an execution plan.

So, don’t hesitate to add comments to your stored procedures. They are essential for documenting and understanding the logic behind your code.

For more information on execution plans and other SQL Server topics, check out these additional resources:

• SQL SERVER – Puzzle – Why Such a Complicated Execution Plan for a Single Row, Single Table Query?
• What is the Difference Between Physical and Logical Operation in SQL Server Execution Plan? – Interview Question of the Week #122
• How to Send Execution Plan in Email? – Interview Question of the Week #079
• How to Force a Parallel Execution Plan for a Query? – Interview Question of the Week #170