Are Stored Procedure Parameters always cached?

By Greg No comments

When SQL Server runs a stored procedure for the first time, it goes and compiles a query plan for it based on the parameters that you passed in. SQL Server then keeps that plan hanging around for you so that if you call it again it will remember how to best get your data. Plan Caching is usually a great performance benefit, but can sometimes lead to parameter sniffing.

When SQL Server caches your plan, it caches it with the parameter values that you pass through the first time, and it assumes that the same query plan will be the best one for any parameter you pass in next time.

But does SQL Server always cache your parameters? Does it always keep track of what you pass in?

I would have said yes, until I was sitting in Brent Ozar’s Mastering Query Tuning class recently. When he started talking about Dynamic SQL, I found that my assumptions were all wrong.

It seems that SQL Server only caches Parameters when it does something non-trivial with them. I’ve created this script to find out.

Let’s start with when it’s not cached:

--1. Send in parameter, don't use it at all
CREATE OR ALTER PROC dbo.usp_ParamTest
	@DisplayName NVARCHAR(40)
AS
BEGIN
	SELECT	*
	FROM	TestTable
END;

--2. Send in parameter, select it straight back out
CREATE OR ALTER PROC dbo.usp_ParamTest
	@DisplayName NVARCHAR(40)
AS
BEGIN
	SELECT	@DisplayName
END;
GO

--5. Send in parameter, set it to a local variable
CREATE OR ALTER PROC dbo.usp_ParamTest
	@DisplayName NVARCHAR(40)
AS
BEGIN
	DECLARE @DisplayName_Internal NVARCHAR(40)

	SET @DisplayName_Internal = @DisplayName
END;
GO

Starting with the simple, if you pass in a parameter but don’t use it, or if you use it on a statement that doesn’t need to be compiled (because it’s trivial) then SQL doesn’t cache it

Makes sense. So when does it get cached?

--3. Send in parameter, select it straight back out with a table
CREATE OR ALTER PROC dbo.usp_ParamTest
	@DisplayName NVARCHAR(40)
AS
BEGIN
	SELECT	@DisplayName
			,*
	FROM	TestTable
END;
GO

--4. Send in parameter, use it in the where clause
CREATE OR ALTER PROC dbo.usp_ParamTest
	@DisplayName NVARCHAR(40)
AS
BEGIN
	SELECT	*
	FROM	TestTable
	WHERE	TestTable.Value = @DisplayName
END;
GO

Example 4 was expected, this is the usual use case for parameterized Stored Procs – you pass in the value and it’s added in the where clause. No surprises that it gets cached. Example 3 is also not surprising, the parameter is used as part of a compiled plan, so it’s cached.

We have now seen that SQL caches the parameters for a stored proc when the parameter is used in a compiled plan inside the stored proc, otherwise they are not cached.

There are 3 other examples in the script I provided at the top, the first is the use of local variables inside a stored procedure.


--6. Send in parameter, set it to a local variable and use the variable in the select list
CREATE OR ALTER PROC dbo.usp_ParamTest
	@DisplayName NVARCHAR(40)
AS
BEGIN
	DECLARE @DisplayName_Internal NVARCHAR(40)
	SET @DisplayName_Internal = @DisplayName

	SELECT	@DisplayName_Internal
			,*
	FROM	TestTable
END;
GO

--7. Send in parameter, set it to a local variable and use the variable in the where clause
CREATE OR ALTER PROC dbo.usp_ParamTest
	@DisplayName NVARCHAR(40)
AS
BEGIN
	DECLARE @DisplayName_Internal NVARCHAR(40)
	SET @DisplayName_Internal = @DisplayName

	SELECT	*
	FROM	TestTable
	WHERE	TestTable.Value = @DisplayName_Internal
END;
GO

In both these examples, we are taking in a parameter and setting it to a local variable, which is then used in the stored procedure. Neither of these cache the parameter being passed in. This can cause it’s own performance problems, but that’s another post.

The final example is actually the first question I had in Brent’s class – what happens to dynamic SQL?

--8. Send in parameter, pass it to dynamic SQL
CREATE OR ALTER PROC dbo.usp_ParamTest
	@DisplayName NVARCHAR(40)
AS
BEGIN

	DECLARE @SQLString NVARCHAR(4000) = 'SELECT	*
	FROM	TestTable
	WHERE	TestTable.Value = @DisplayName';

	EXEC sys.sp_executesql @SQLString, N'@DisplayName NVARCHAR(40)', @DisplayName
END;
GO

As you might guess if you’ve read this far, the parameter is not being used in any non-trivial command in the stored procedure, so it is not cached. However, by using sp_executesql the parameter value is cached for the query. That is, whenever the select statement is run, it is optimized for the value cached in @DisplayName, it’s just not cached for this stored proc.

Leave a Reply