2

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

8

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:

  • Thanks Aaron, Every time I change the variable value I get the same query_hash "0xDFF8AB3AD70AF28F" and qery_plan_hash "0xB158C10DA5A7FB96" Thanks – user1716729 May 20 '16 at 12:57
  • @user1716729 Regardless of what you may see output from sys.dm_exec_cached_plans, the reason is the same (the variable is not a parameter and is interpreted as part of the query text) and the solution is to use properly parameterized queries (e.g. a stored procedure). This is even easier to demonstrate with things like SELECT TOP (1) SalesOrderID FROM Sales.SalesOrderHeader vs. select top (1) salesorderid from sales.salesorderheader - those are two identical queries, but because case isn't identical, you'll get two different plans (even though the query_hash and query_plan_hash are same). – Aaron Bertrand May 20 '16 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.