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


Could enabling "Optimize for Ad hoc Workloads" help performance some?


Could enabling "Optimize for Ad hoc Workloads" help performance some?

Author
Message
jasona.work
jasona.work
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9888 Visits: 12526
I have a specific question on this, and have done some digging. The application our Devs are working on seems to use a goodly number of AdHoc queries (according to sys.dm_exec_cached_plans when cross applied to sys.dm_exec_sql_text, as described in this MSDN article)

Now, I may be off in thinking it's a lot of adhoc, based on getting counts of the query types:
AdHoc = 432
Prepared = 2168
Triggers = 32

So I may be barking up the wrong tree here. I'm trying to find out if the SQL Server needs tuning (it's one particular client out of several complaining,) the program needs optimizing, or the DB needs tuning (which I think it does but I'm losing the battle fighting the Devs {long story})

I'm planning to fire up a SQL Profiler session against the SQL Server, to see what that might find as well.

Personally, I'm leaning towards the application being the problem (LOTS of "SELECT *..." stuff), the SQL Server CPU (when I'm watching) is barely ticking over, the RAM doesn't seem to be low, and the DB isn't very big (143MB DB / 70MB TLog)

Any suggestions or pointers would be appreciated...

Jason A.
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1872 Visits: 1266
Are users reporting performance problems and can you reproduce them, as user sees them?
Start with that and WAIT analysis (overall cumulative waits of the system and waits of the currently executing sessions).
You could also set perfmon counters on the OS (guest and host OS if it is a virtual machine) to pin-point the root cause.
Is it a virtual machine?
Is there any other software except your sql instance, or any other sql instance?

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Andrew G
Andrew G
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: 3967 Visits: 2279
Enabling "optimize for ad hoc workloads" is a no brainer, read about it, then turn it on. It will reduce plan cache bloat for single use plans. If a plan is used more than once, the entire plan will remain in cache, otherwise a placeholder is used which is much smaller than the entire plan.

It may free up some RAM but probably not going to solve your performance issue (especially for a 100MB db).
Check out the wait stats and performance dmvs as Vedran suggested
jasona.work
jasona.work
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9888 Visits: 12526
OK thanks to both of you. I've made a couple small changes to the OS and the backup plan on the server (I noticed and cleaned up a bunch of old backup files, the server when I started on this had very little free drive space, which could also be part of the problem {and may have been})

So I'm starting "clean" as of today, I cleared out the wait stats counters, and will monitor them. While I'd love to set up alerts, the bosses went cheap and used SQL Express, so no DBMail, no Agent jobs... Angry

Jason
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