Compiled Parameter Values in SQL Server: Read Them From the Cache

Compiled parameter values are the values SQL Server used when it built a plan, and the plan cache keeps them. Reading them is the first step in any parameter sniffing investigation.

Why the Compiled Value Matters

A stored procedure gets its plan on the first call. SQL Server looks at the parameter values of that call and builds a plan that suits them. Later calls reuse the plan, whatever their values are. SQL Server does this on purpose, because compiling costs CPU and reusing a plan saves it. The side effect is parameter sniffing.

A plan built for a rare value can be a poor fit for a common one. The same procedure then runs fast for one caller and slow for another. A slow call in the application that runs fast in Management Studio is the classic sign. To confirm it, you need the compiled parameter values of the cached plan. They tell you which caller shaped the plan for everyone else.

A Demo With Skewed Data

The first script creates a database named CompiledValuesDemo. The Orders table has 5,000 rows for Portland and only 3 for Boise. The procedure counts the orders of one city and adds up the revenue. Run it on a test server.

IF DB_ID(N'CompiledValuesDemo') IS NULL CREATE DATABASE CompiledValuesDemo;
GO
USE CompiledValuesDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (OrderID int IDENTITY(1,1) PRIMARY KEY, City varchar(30) NOT NULL, Total decimal(8,2) NOT NULL);
CREATE INDEX IX_Orders_City ON dbo.Orders (City);
INSERT INTO dbo.Orders (City, Total)
SELECT CASE WHEN n <= 5000 THEN 'Portland' ELSE 'Boise' END, 10 + n % 50
FROM (SELECT TOP (5003) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS t;
GO
CREATE OR ALTER PROCEDURE dbo.OrdersForCity @City varchar(30)
AS
SELECT COUNT(*) AS OrderCount, SUM(Total) AS Revenue FROM dbo.Orders WHERE City = @City;

Now the rare value goes first. Boise builds the plan. Portland reuses it. The third call asks for a fresh plan with WITH RECOMPILE. SET STATISTICS IO prints the pages each call read.

EXEC dbo.OrdersForCity @City = 'Boise';

SET STATISTICS IO ON;

EXEC dbo.OrdersForCity @City = 'Portland';

EXEC dbo.OrdersForCity @City = 'Portland' WITH RECOMPILE;

SET STATISTICS IO OFF;

Both calls return 5000 orders and revenue of 172500.00. The reused plan reads about 480 times more pages. The fresh plan reads the whole table once, 21 pages. The reused plan reads two pages for each Portland row. That points to a lookup per row, a fair choice for three Boise rows.

Read the Compiled Parameter Values

The next script finds the plan and reads its parameter list. It works in two steps. The first step collects the plan handles for the procedure into a temporary table. The second step reads the plan XML for those handles only. On a server with a large cache, that order matters. Reading the XML of every cached plan is slow. Reading these views needs VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

DROP TABLE IF EXISTS #Plans;
SELECT cp.plan_handle, cp.usecounts, st.objectid
INTO #Plans
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.dbid = DB_ID() AND st.objectid = OBJECT_ID(N'dbo.OrdersForCity');

SELECT OBJECT_NAME(pl.objectid, DB_ID()) AS ProcedureName,
       p.Param.value('@Column', 'nvarchar(128)') AS ParameterName,
       p.Param.value('@ParameterDataType', 'nvarchar(128)') AS DataType,
       p.Param.value('@ParameterCompiledValue', 'nvarchar(128)') AS CompiledValue,
       pl.usecounts AS UseCount
FROM #Plans AS pl
CROSS APPLY sys.dm_exec_query_plan(pl.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes(N'declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //p:ParameterList/p:ColumnReference') AS p (Param)
ORDER BY ParameterName;

There it is. The plan was compiled for Boise, and it served two calls. The compiled value keeps its quotes for text, and numbers appear in parentheses. In your own database, change the filter to the procedure you suspect, or filter on part of the query text.

The query uses no TRY_CONVERT, so it also ran in the demo database at compatibility level 100. Parameter names come from the caller. A query sent by an application shows names such as @p1, because that is what the application called them.

What the Cache Does Not Show

A cached plan holds compiled values only. It does not hold the values of the latest call. Those appear in an actual plan. For the last known actual plan, read Last Known Actual Plan in SQL Server: Query Plan Stats.

The cache also forgets. A restart, a recompile or a memory clean-up removes the plan, and the evidence goes with it. Read the values as soon as someone reports the slowdown.

Break the Link Between the First Caller and the Plan

Once the compiled value is the problem, you have a few options. WITH RECOMPILE on the call, as the demo showed, builds a fresh plan for that call. It costs compile time on every call that uses it. The same option on the procedure itself applies to every call. OPTION (RECOMPILE) on one statement does the same for that statement only. OPTIMIZE FOR UNKNOWN builds a plan for an average value. Query Store hints can add such a hint without touching the code on SQL Server 2022 and later.

Newer versions can also keep several plans for one query when the data is skewed. In the demo database at compatibility level 170, the Boise plan was still reused for Portland. Do not count on the feature. Check the plan cache.

You could argue that the compiled value is only a symptom. The real fix is the index or the query. That is true. The compiled value shows whether sniffing is the cause. Then you fix the right thing.

What to Remember

Read compiled parameter values when a procedure is fast for some callers and slow for others. Collect the plan handles first and read the XML second. Compare the compiled value with the value of the slow call. If they differ, you have found the cause.

When you finish with the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'CompiledValuesDemo') IS NOT NULL
BEGIN
    ALTER DATABASE CompiledValuesDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CompiledValuesDemo;
END;

A compiled value is not a bug, it is the first caller leaving a fingerprint on the plan.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.