This site is maintained by Jason Massie. He has 10 years experience as a DBA and has specialized in performance tuning for the last five. He was recognized by Microsoft as a SQL Server MVP. Jason has spoken at the Professional Association of SQL Server Conference, the North Texas SQL Server Users Group, SQL Connections and TechED. He has worked at Terremark (formerly Data Return) for a decade.
You can contact him at jason@statisticsio.com , MSN IM jason_massie@hotmail.com or 469.569.5965
Jason has the following certifications:
Abstracts addition Affinity Aggregation allocation Always Analysis Announced another API Appending article Authentication backup be Behavior between Bootstrapper Breaking Build Cache Caching Check checksums Codeplex collection Connecting contest Controller Creating CTEs CTP CUBE cursors Data Database DATALENGTH Debugging Design Diagnosing Diagnostic Differences Documentation DTS Emergency enhancement Entity ETW Exchange execution Express Extensions Fall February Filestream Filtered group GROUPING have Hosting Idle impact Improvement Increase Index Indexes Inserts Instances Interoperability Introduction IO large Late LOB local Localized Magazine Maintaining Maintenance Management maps March Microsoft minutes missing Mix Never November Offline OLE Online operations operators optimizations Optimized Overlapping Package Page Paging Panacea parallel part Partial Partition partitioned Partitioning PASS Performance PFS plan Plans Practices problem Problems Procedure Program programmatically Programming Protection Queries query read recent Recursive Related released Reports Restore return ROLLUP ROWCOUNT Runtime Security Select Sequence sequential Server Services set SETS Shooting shorts sizes Solutions Sortable SPARSE Spool SQL SQLIOSim SSIS Stalled Star Statement Statements stats Stored strategy Stuck Studio Submission Subreports Suggested Summarizing system Table Tables Tampa Task Than there through Timeouts Total Traces Transaction transfer Tricks Trouble TSQL turning understand Understanding undocumented Unique unused upgrade Upgrading Useful Value variables VDI Vista Will Windows Wireless
SQL Server News & Information tsql, performance tuning, industry trends, & bad jokes
tsql, performance tuning, industry trends, & bad jokes
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', 1reconfigure with overridegoexec sp_configure 'optimize', 1reconfigure with override alter database master set parameterization forceddbcc freeproccache --Let's run a query will not generate a trivial plan.select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go --The CacheObjType is a compiled plan and 57344 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%' --Now, let’s turn off forced parameterizationalter database master set parameterization simpledbcc freeproccache select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go --The CacheObjType is a Compiled Plan Stub and 320 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%'
--So what happens when you turn on "Optimize for adhoc" and forced parameterization exec sp_configure 'show advanced options', 1reconfigure with overridegoexec sp_configure 'optimize', 1reconfigure with override alter database master set parameterization forceddbcc freeproccache
--Let's run a query will not generate a trivial plan.select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go
--The CacheObjType is a compiled plan and 57344 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross 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 simpledbcc freeproccache
select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go
--The CacheObjType is a Compiled Plan Stub and 320 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%'
posted @ Wednesday, September 24, 2008 10:01 AM by Adam Machanic
posted @ Wednesday, September 24, 2008 10:06 AM by JasonMassie
posted @ Wednesday, September 24, 2008 10:50 AM by Adam Machanic
posted @ Tuesday, October 28, 2008 4:27 PM by wmfsux
posted @ Monday, November 16, 2009 12:54 PM
Name (required)
Email (required)
Website
Notify me of followup comments via e-mail