SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimize ad hoc workloads


Optimize ad hoc workloads

Author
Message
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3993 Visits: 4025
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
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33618 Visits: 18560
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91103 Visits: 45285
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33618 Visits: 18560
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91103 Visits: 45285
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6022 Visits: 11771
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.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33618 Visits: 18560
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

sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3993 Visits: 4025
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.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33618 Visits: 18560
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91103 Visits: 45285
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search