<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Optimize ad hoc workloads
12 posts, Page 1 of 2
1
2
»»
Optimize ad hoc workloads
Rate Topic
Display Mode
Topic Options
Author
Message
sqlfriends
sqlfriends
Posted Tuesday, February 05, 2013 12:29 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 1,610,
Visits: 2,794
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
Post #1416032
SQLRNNR
SQLRNNR
Posted Tuesday, February 05, 2013 12:44 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754,
Visits: 12,337
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1416040
GilaMonster
GilaMonster
Posted Tuesday, February 05, 2013 12:45 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741,
Visits: 30,019
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1416041
SQLRNNR
SQLRNNR
Posted Tuesday, February 05, 2013 12:49 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754,
Visits: 12,337
GilaMonster (2/5/2013)
Is google down again?
Yes
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1416045
GilaMonster
GilaMonster
Posted Tuesday, February 05, 2013 12:54 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741,
Visits: 30,019
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1416048
Michael Valentine Jones
Michael Valentine Jones
Posted Tuesday, February 05, 2013 12:59 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 2,945,
Visits: 10,517
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.
Post #1416049
SQLRNNR
SQLRNNR
Posted Tuesday, February 05, 2013 1:02 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754,
Visits: 12,337
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1416050
sqlfriends
sqlfriends
Posted Tuesday, February 05, 2013 1:03 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 1,610,
Visits: 2,794
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.
Post #1416051
SQLRNNR
SQLRNNR
Posted Tuesday, February 05, 2013 1:06 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754,
Visits: 12,337
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1416055
GilaMonster
GilaMonster
Posted Tuesday, February 05, 2013 1:50 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741,
Visits: 30,019
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1416075
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
