LessThanDot

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Showing cached Execution Plans
In this post I’m going to use information from the following Dynamic Management Views and Functions to show some information about the cached Execution Plans of the queries used in this post:

The result set can contain thousands of rows, depending on the uptime of the server, the number of queries and their corresponding execution plans, the available buffer memory…

IF using 2 code blocks
So let’s create a simple Stored Procedure that executes against the AdventureWorks2008R2 database:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE TwoPlans
@IfParameter int
AS
SET NOCOUNT ON;
IF @IfParameter = 1
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress bea
INNER JOIN Person.Address a
ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City
ELSE
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);
GO

The results of the query aren’t important what is important is to see what happens in the Procedure Cache. To be able to see what’s happening we are going to empty the procedure cache:

WARNING: executing the following code deletes all cached plans from the Procedure Cache. All Execution Plans need to be recompiled. This can result in a slow or unresponsive server.
DON’T EXECUTE THE FOLLOWING CODE ON A PRODUCTION SERVER!!!

An easier trick to see the Graphical Execution Plans is to open SQL Sentry Plan Explorer and copy the XML into the Plan XML tab:

After doing this SQL Sentry Plan Explorer will give you all the details about the Execution Plan and you’ll see in the Plan Diagram that SQL Server created an Execution Plan for both the queries altough only the first one was used:

IF calling 2 Stored Procedures
Now let’s create 2 Stored Procedures that each will execute 1 part of the code from the previous query:

CREATE PROCEDURE EmpCntCity
AS
SET NOCOUNT ON;
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress bea
INNER JOIN Person.Address a
ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;
GO
CREATE PROCEDURE OrderAmountYear
AS
SET NOCOUNT ON;
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);
GO

Next we create a Stored Procedure that will contain the IF…ELSE logic and based on the input parameter will execute one of the above Stored Procedures:

Now we see that executing the second Stored Procedure also created 2 Executions Plans: 1 for the Stored Procedure “TwoProcs” and one for the Stored Procedure “EmpCntCity” that was effectively executed. But we see that there was no Execution Plan created for the Stored Procedure “OrderAmountYear”. And this Execution Plan was more complex than the Execution Plan of our “TwoProcs” Stored Procedure. We can also see that the sum of the sizes of the 2 Execution Plans (65536 + 16384 = 81920) is still smaller than the Execution Plan of the “TwoPlans” Stored Procedure (98304).

Conclusion
Avoid Stored Procedures that contain complete code blocks encapsulated in IF…ELSE or CASE blocks. It will result in SQL Server creating Execution Plans for all possibilities, consuming more Buffer Cache (memory) and in the end slow down the execution of the code.
As a bonus, troubleshooting the individual Stored Procedures will be much easier and there is a bigger chance that you can reuse the Stored Procedures.

About the Author

After 12 years as a MS SQL Server consultant and trainer, Axel is now using his data knowledge to implement "Product Information Management" solutions. These implementations are usually but not always linked to E-commerce projects.