How many plans will SQLServer cache for the same query :)

Hey everyone,

Check out this interesting piece in relevance to Adhoc query caching.

Howmany plans you think will sql server cache for below queries.

SELECT * FROM Production.Product where name ='Adjustable Race';
GO
SELECT * FROM Production.Product where Name ='Adjustable Race';
GO
select * from Production.Product where name ='Adjustable Race';
GO
select * from Production.Product where name ='Adjustable Race';
GO
--here we go
select * from Production.Product where name ='Adjustable Race';
GO

Well, you can verify your answer by asking the DMVs as below.

select usecounts,cacheobjtype,objtype,text from
sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(plan_handle)
where text not like '%dm_exec_cached_plans%'
and cacheobjtype='Compiled Plan' and objtype='adhoc'

What is the value you have for below query? and what is the relationship?


select * from sys.configurations
where name ='optimize for ad hoc workloads'

Try it on your own(ofcourse, in your own instance :) ) and dig-in to understand the adhoc query caching behavior in SQL Server.

EXEC sp_configure 'show advanced option', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

What the hell happened to other plans ??

Cheers!!

Source: Microsoft sql server 2012 internals by kalen delaney