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
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
No comments:
Post a Comment