Plan cache behaviour Can someone please explain me why the following is no getting cached

use AdventureWorks2014
go
DECLARE @id INT=43865
SELECT
sp.[BusinessEntityID]
, so.salesorderid
FROM sales.salesorderheader so JOIN sales.salesperson sp
ON sp.[BusinessEntityID] = so.salespersonid
WHERE so.salesorderid = @id


Every time I execute the above query with different parameter, it create an entry in plan cache

by the way, optimize for ad hoc workloads is set

Thanks
You're using a local variable, not a parameter. So because this is part of your query text:

DECLARE @id INT=43865


It gets included as part of the query hash (which converts the query text, including 43865, to binary), and thus produces a different hash every time you change the text. This is how it will work in a Management Studio query window, but it might be different if you send properly parameterized ad hoc queries from a C# application for example.

You can try changing the database setting Parameterization to forced, but this is kind of like killing a mosquito with a bulldozer. Better yet (IMHO), instead of ad hoc SQL, use stored procedures where you pass the value of your local variable @id via a proper parameter (or skip the local variable altogether and just pass 43865 directly to the stored procedure parameter).

Kendra Little has written a little bit about this too, and she suggests using #temporary procedures to mimic your actual procedure (e.g. for testing changes without changing the base procedure). But that is only relevant if you already have a stored procedure in the first place:


Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)