This Post Needs More Stored Procedures

New in SQL Server 2008 is a server setting called “Optimize for adhoc workloads”. I was happy to see this. You should be too especially if you have ever had arm wrestle an app that causes a bloated proc cache on an x86 box. Ugghh… Adam blogged on it here, Bob blogged on it here and here is the documentation.

I was wondering how this setting would play with the forced parameterization database setting. It looks like forced parameterization trumps the new server wide setting. If my simple tests below are right, it could present an interesting problem. What if your app generates a lot of totally unique adhoc queries that have no benefit from parameterization and you have queries that would benefit from parameterization? Well, the answer is still the same. Write stored procedures. :)

In the real world, sometimes you get a pig and all you can do is put lipstick on it. If you have to choose, “Optimize for Adhoc” would most benefit memory\IO bound servers while “forced parameterization” could help both the CPU and memory usage. However, that is a very general statement. Other things should be considered so it will depend.

--So what happens when you turn on "Optimize for adhoc" and forced parameterization 
exec sp_configure  'show advanced options', 1
reconfigure with override
go
exec sp_configure 'optimize', 1
reconfigure with override 
alter database master set parameterization forced
dbcc freeproccache 

--Let's run a query will not generate a trivial plan.
select name, object_id, create_date
from sys.all_objects
where object_id = 3 and create_date = '2008-07-09 16:19:59.943'
go

--The CacheObjType is a compiled plan and 57344 bytes
select p.cacheobjtype, p.size_in_bytes ,  s.*
from sys.dm_exec_query_stats s
join sys.dm_exec_cached_plans p
on s.plan_handle = p.plan_handle
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select name , object_id , create_date from%'

--Now, let’s turn off forced parameterization

alter database master set parameterization simple
dbcc freeproccache

select name, object_id, create_date
from sys.all_objects
where object_id = 3 and create_date = '2008-07-09 16:19:59.943'
go 

--The CacheObjType is a Compiled Plan Stub and 320 bytes
select p.cacheobjtype, p.size_in_bytes ,  s.*
from sys.dm_exec_query_stats s
join sys.dm_exec_cached_plans p
on s.plan_handle = p.plan_handle
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select name , object_id , create_date from%'

email it! |   |   |   |  | 

Permalink     5 Comments  

Rate this Post: 12345
COMMENTS:

Are you saying that if both "optimize for ad hoc" and forced parameterization are enabled, the adhoc versions of the parameterized plans will still be cached?

posted @ Wednesday, September 24, 2008 10:01 AM by Adam Machanic

---

Yes, a full parameterized compiled plan will be stored. A good thing if it is reused quite a bit. Not so good, if you still have 1000's of single use queries.

posted @ Wednesday, September 24, 2008 10:06 AM by JasonMassie

---

Well in that case it sounds like it's working perfectly. I would rather have a thousand single use prepared plans than tens of thousands of adhoc nonparameterized plans. How many apps actually submit numbers in the multiple thousands of adhoc queries that can't be parameterized down to the same form? The worst I've ever seen was on the order of a few thousand seen over the course of a week of monitoring (pretty much constant plan cache churn in that environment).

A database I'm working on right now generally has over 60,000 cached adhoc plans, which render down to a few hundred parameterized forms. I would take the 300 prepared forms, over 60,000 adhoc any day. Or in the case of the other environment, 3,000 prepared forms, which would easily fit in and remain in memory, over hundreds of thousands of adhoc plans constantly churning in and out of memory.

posted @ Wednesday, September 24, 2008 10:50 AM by Adam Machanic

---

F7iJRdjvtgqvboxxwv, maxcvdlwscir, [link=http://bioqeruqtqgu.com/]bioqeruqtqgu[/link], http://qjwttbeosnzv.com/

posted @ Tuesday, October 28, 2008 4:27 PM by wmfsux

---

Comments from the following blog entry: http://www.pythian.com/news/1264/log-buffer-116-a-carnival-of-the-vanities-for-dbas/

posted @ Monday, November 16, 2009 12:54 PM

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]