Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Could enabling "Optimize for Ad hoc Workloads" help performance some? Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 2:23 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 980, Visits: 5,108
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.
Post #1407495
Posted Tuesday, January 15, 2013 7:31 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:57 PM
Points: 369, Visits: 1,211
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
Post #1407547
Posted Wednesday, January 16, 2013 1:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:54 PM
Points: 1,193, Visits: 1,666
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
Post #1407656
Posted Wednesday, January 16, 2013 8:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 980, Visits: 5,108
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...

Jason
Post #1407891
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse