February 5, 2013 at 12:29 pm
We have a database server that has databases for research department.
It is used mainly for them to read data using select queries, or combine some data together to write to new table.
Sometimes they wrote complicated queries to join across databases on the server and the query runs minutes.
I see there is Optimize for AD HOC workloads database property, its default is false, to make it true, will that help? What does it mean?
thanks
February 5, 2013 at 12:44 pm
Ad hoc workloads is there to help with plan cache pollution. If you have multiple single use queries, a stub is saved instead of the entire plan. If a query comes along again and thinks the plan stub is appropriate, then the query will cause the full plan associated to that stub to be stored in the plan cache.
If you have a ton of one off types of queries - this can be helpful. The downside is that you may start seeing parameter sniffing and longer running queries.
If you decide to enable it - be prepared to disable it if performance starts degrading. And of course, the best option is to test it in a suitable environment first.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 12:45 pm
Is google down again?
http://msdn.microsoft.com/en-us/library/cc645587.aspx
http://blogs.msdn.com/b/timchapman/archive/2012/09/10/optimizing-ad-hoc-workloads.aspx
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
http://www.bradmcgehee.com/2011/04/do-you-enable-optimize-for-ad-hoc-workloads/
4 of the top 5 results from a google search for "optimize for ad-hoc workloads"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2013 at 12:49 pm
GilaMonster (2/5/2013)
Is google down again?
Yes :crying:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 12:54 pm
SQLRNNR (2/5/2013)
If you have a ton of one off types of queries - this can be helpful. The downside is that you may start seeing parameter sniffing and longer running queries.
Optimise for ad-hoc can't cause parameter sniffing where there was none before. Forced parametrisation can, but that's a whole nother matter.
Optimise for ad-hoc just means that an ad-hoc batch's plan is only cached the second time it's seen, not the first. It can cause higher compilations in cases where identical ad-hoc queries often run 2 or 3 times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2013 at 12:59 pm
sqlfriends (2/5/2013)
We have a database server that has databases for research department.It is used mainly for them to read data using select queries, or combine some data together to write to new table.
Sometimes they wrote complicated queries to join across databases on the server and the query runs minutes.
I see there is Optimize for AD HOC workloads database property, its default is false, to make it true, will that help? What does it mean?
thanks
Optimize for AD HOC workloads will not make those queries run faster.
It might help the overall performance of the server by making the plan cache smaller.
February 5, 2013 at 1:02 pm
GilaMonster (2/5/2013)
SQLRNNR (2/5/2013)
If you have a ton of one off types of queries - this can be helpful. The downside is that you may start seeing parameter sniffing and longer running queries.Optimise for ad-hoc can't cause parameter sniffing where there was none before.
I'll agree on that. Sometimes though the param sniffing isn't made evident until adhoc workload is enabled. Could be a coincidence and I haven't dived deeply into it. I just ran into a case in the last two weeks and then ran into another DBA last week that experienced the same thing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 1:03 pm
Thanks ALL.
I read a tutorial and recommend this option should be enabled for all instances. Read and still a little confusing about when I should enable or disable it.
I will dig more.
February 5, 2013 at 1:06 pm
sqlfriends (2/5/2013)
Thanks ALL.I read a tutorial and recommend this option should be enabled for all instances. Read and still a little confusing about when I should enable or disable it.
I will dig more.
Cite that article please.
I wouldn't agree that it is something that must be done on all instances. Test it first for your environment and then make a decision whether or not to implement it in prod - but certainly not just a blanket yes for all instances.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 1:50 pm
SQLRNNR (2/5/2013)
GilaMonster (2/5/2013)
SQLRNNR (2/5/2013)
If you have a ton of one off types of queries - this can be helpful. The downside is that you may start seeing parameter sniffing and longer running queries.Optimise for ad-hoc can't cause parameter sniffing where there was none before.
I'll agree on that. Sometimes though the param sniffing isn't made evident until adhoc workload is enabled. Could be a coincidence and I haven't dived deeply into it. I just ran into a case in the last two weeks and then ran into another DBA last week that experienced the same thing.
I'd say unrelated coincidence. Optimise for ad-hoc only works on ad-hoc queries, not procedures. Can't recall offhand whether it works on prepared statements (parameterised) or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2013 at 5:41 pm
SQLRNNR (2/5/2013)
sqlfriends (2/5/2013)
Thanks ALL.I read a tutorial and recommend this option should be enabled for all instances. Read and still a little confusing about when I should enable or disable it.
I will dig more.
Cite that article please.
I wouldn't agree that it is something that must be done on all instances. Test it first for your environment and then make a decision whether or not to implement it in prod - but certainly not just a blanket yes for all instances.
Glenn Berry talks about it as a standard config. In general may tend to agree with it although I suppose it could take a little while longer before the plan cache "warms up." I'll typically enable it by default unless I have a captive audience where I know all data access is done through stored procs or prepared statements:
Some Suggested SQL Server 2008 R2 Instance Configuration Settings[/url]
Optimize for ad-hoc workloads
Optimize for ad-hoc workloads is a new instance level setting that was added in SQL Server 2008 which is designed to help control the amount of memory that is used by single-use, ad-hoc query plans in the procedure cache. It allows SQL Server to only store a small stub of an ad-hoc query plan in the procedure cache the first time the ad-hoc plan is executed, which reduces the memory required by the plan in the procedure cache.
With SQL Server 2005, it was very common to see very large amounts of memory being used by single-use, ad-hoc query plans (often in the 6 to 8 GB range). Later builds of SQL Server have changes that reduced this problem somewhat, but it was still a big issue. Interestingly, one of the biggest offenders that generated ad-hoc query plans in SQL Server 2005 was SQL Server Agent! Another big offender was SharePoint.
In my opinion, you should always enable this setting on SQL Server 2008 and above. I really cannot think of a good reason not to do this.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 6, 2013 at 12:02 am
opc.three (2/5/2013)
SQLRNNR (2/5/2013)
sqlfriends (2/5/2013)
Thanks ALL.I read a tutorial and recommend this option should be enabled for all instances. Read and still a little confusing about when I should enable or disable it.
I will dig more.
Cite that article please.
I wouldn't agree that it is something that must be done on all instances. Test it first for your environment and then make a decision whether or not to implement it in prod - but certainly not just a blanket yes for all instances.
Glenn Berry talks about it as a standard config. In general may tend to agree with it although I suppose it could take a little while longer before the plan cache "warms up." I'll typically enable it by default unless I have a captive audience where I know all data access is done through stored procs or prepared statements:
Grant and Jonathan I think as well. It's pretty harmless as settings go.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply