How (not) to abuse the plan cache with parameters

This is the 5th in a series of posts about query plans.  The first post covered what a query plan is and how to read it.  The second post described the effort required to make a query plan.  The third entry explained how the plans are stored in a cache to be reused.  The 4th post showed how the optimizer finds appropriate plans in cache.  This post will cover how poor coding practices can lead to excessive plans being stored in the cache.

Plans are found in cache to be reused only when an exact match of the SQL query is found.  This works great until constant values are introduced — often as a value being searched.  If user input is being considered when running a query, perhaps by accepting a parameter from a web form, it must be handled properly for there to be good plan reuse.

Let’s see it in action.  To follow along open a SSMS window and point at a WideWorldImporters database.  These examples were done with SSMS 17 and SQL Server 2017 RTM.

This snippet will run the same query 3 times for each of 3 different search parameters and then take a look at the plan cache.

GO

SELECT TOP 10
a.CustomerName
, a.CreditLimit
, b.InvoiceDate
, b.DeliveryInstructions
FROM
Sales.Customers a
INNER JOIN
Sales.Invoices b ON a.CustomerID = b.BillToCustomerID
WHERE
CustomerName = 'Wingtip Toys (Head Office)';
GO 3

SELECT TOP 10
a.CustomerName
, a.CreditLimit
, b.InvoiceDate
, b.DeliveryInstructions
FROM
Sales.Customers a
INNER JOIN
Sales.Invoices b ON a.CustomerID = b.BillToCustomerID
WHERE
CustomerName = 'Alinne Matos';
GO 3

SELECT TOP 10
a.CustomerName
, a.CreditLimit
, b.InvoiceDate
, b.DeliveryInstructions
FROM
Sales.Customers a
INNER JOIN
Sales.Invoices b ON a.CustomerID = b.BillToCustomerID
WHERE
CustomerName = 'Aakriti Byrraju';

GO 3

SELECT planXML.query_plan, sqlText.text, creation_time, last_execution_time, execution_count
FROM sys.dm_exec_query_stats queryStats
CROSS APPLY sys.dm_exec_query_plan(queryStats.plan_handle) planXML
CROSS APPLY sys.dm_exec_sql_text(queryStats.sql_handle) sqlText
WHERE planXML.dbid = DB_ID()
AND queryStats.execution_count BETWEEN 2 and 3
AND last_execution_time > DATEADD(n, -50, GETDATE())

Even though these are these queries are basically the same and should share a single query plan they will not.  This is because the SQL being run is not identical.  Each one has a different hard -coded customer name.  What a waste of CPU and memory resources!

The results of the final query.  3 plans each with 3 executions each.  We would much rather have 1 plan with 9 executions.

One solution often brought about is to use a procedure instead of calling the statement directly.   That is a good first step, but must also be done correctly.  Create this procedure then use the subsequent script to execute it 3 times for each of 3 names.

CREATE PROCEDURE usp_GetSales (@CustomerName NVARCHAR(200)) AS
DECLARE @SQL NVARCHAR(4000);
SET @SQL = N'
SELECT TOP 10
a.CustomerName
, a.CreditLimit
, b.InvoiceDate
, b.DeliveryInstructions
FROM
Sales.Customers a
INNER JOIN
Sales.Invoices b ON a.CustomerID = b.BillToCustomerID
WHERE
CustomerName = ''' + @CustomerName + '''';

EXEC sp_executeSQL @SQL;

And then this group of batches to execute the procedure.

EXEC usp_GetSales N'Wingtip Toys (Head Office)';
GO 3

EXEC usp_GetSales N'Alinne Matos';
GO 3

EXEC usp_GetSales N'Aakriti Byrraju'
GO 3

The output of the query cache query is identical!!  This is because the concatenated string is what is actually running and that string still matches the first example exactly.

Modify the procedure to this new definition below.  Notice that the query call now includes a parameter name and not the value within the parameter.  When this procedure is executed the SQL statement will be 100% identical no matter what the parameter value might be.  After updating the procedure run it using the same 3 exec batches from the prior test.   Also modify the query plan query to look for plans used up to 9 time instead of 3.

ALTER PROCEDURE usp_GetSales (@CustomerName NVARCHAR(200)) AS
SELECT TOP 10
a.CustomerName
, a.CreditLimit
, b.InvoiceDate
, b.DeliveryInstructions
FROM
Sales.Customers a
INNER JOIN
Sales.Invoices b ON a.CustomerID = b.BillToCustomerID
WHERE
CustomerName = @CustomerName;
GO

Finally!  Now we see 1 plan used 9 times.  This is great plan reuse which will save CPU cycles used to create the plan and memory to store the plan!

Summary

Bad coding practices can cause the plan cache to bloat by storing many similar or duplicate query plans.  Make sure that the coding methods in your environment aren’t leading to a waste of precious server resources.

This example was able to be resolved by creating a procedure and avoiding dynamic SQL.  But what if dynamic SQL is necessary?  The next post in this series will show how to achieve this same kind of plan reuse while still using dynamic SQL.