January 15, 2013 at 2:23 pm
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.
January 15, 2013 at 7:31 pm
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?
January 16, 2013 at 1:15 am
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
January 16, 2013 at 8:48 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply