Impact of Comments on your Stored Procedure

programming-transactsql

People will often tell you to comment your code, including the contents of your stored procedures. Some people may tell you that comments embedded in your stored procedures may cause an negative impact on performance.

In this article by Aaron Bertrand, we see the methods he used to test the impact that various lengths of comments might have on the performance of your stored procedures in SQL Server.

First, let’s look at the size of the procedure bodies. No surprises here, just confirming that my construction code above generated the expected size of comments in each procedure:

Procedure Size (bytes)
Small_Separate / Small_Embedded 378
Medium_Separate / Medium_Embedded 4,340
Large_Separate / Large_Separate 40,338
ExtraLarge_Separate / ExtraLarge_Separate 400,348

Next, how large were the plans in the cache?

Procedure Size (bytes)
Small_Separate / Small_Embedded 40,360
Medium_Separate / Medium_Embedded 40,360
Large_Separate / Large_Separate 40,360
ExtraLarge_Separate / ExtraLarge_Separate 40,360

Finally, what was the performance like? Without OPTION (RECOMPILE), here is the average execution time, in milliseconds – pretty consistent across all procedures:

Average duration (milliseconds) - without OPTION (RECOMPILE)
Average duration (milliseconds) – without OPTION (RECOMPILE)

You can read the entire article here.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s