SQL Profiler running on server side causes high CPU usage
-
Hello
I was trying to run SQL profiler trace server side (non-GUI) during off peak hours and filtering it on a single databaseID with query/SP duration of over 5 seconds.
I would do this procedure regularly 6 months ago and not have any issues locating the poorly performing queries and fine tuning them. Ofcourse the application has changed in these 6 months and new features (read Stored procedures) have been added/modified. Now, when I run the exact same trace I see numerous WAIT_TYPES of CMEMThread and the CPU touches 100% usage. Thankfully, the application hasn't crashed so far but just starts to drag until I kill the trace.
My question is how do I find these offending procedures without running profiler and any more details on CMEMThread wait type which could help me solve the problems
Thanks in advance
Anish
-
Forgot to add:
CMEMThread wait type only seems to show up in Activity monitor when I have the profiler trace running. As soon as the trace is stopped....things go back to normal?
We have 40GB main memory....and I never had an issue before running traces.
-
You don't mention what type of application this is, how big the database is nor the details of the OS. I haven't seen your specific issues myself, but from the symptioms you've described I'd guess your system is a heavily used transactional system running on a 32-bit machine. I'm guessing that the procedure cache is churning, hence the continual memory waits and high CPU usage associated with AWE paging. My first inclination would be to look into migrating to x64 as that might make a huge difference, and looking into forcing plan re-use might also make a big difference.
However, do you need to run traces to find what you're looking for? My first point of call now are the DMVs. From your post it looks like sys.dm_exec_query_stats will deliver most of what you're after: long-running queries, multiple query executions and aggregated execution times, reads and writes, etc.
Sure Profiler and scripted traces can be useful to hone in on specific problems, but the cache-querying DMVs provide a very good high to medium level view. The most common time I run traces now is when looking for conflicting processes running under different threads, but in terms of general "sniffing around" the DMVs are now my first option.
-
Thank you Glenn for your response.
Infact, we are on x64 and you are correct in your assumption that it is a heavily used transactional system.
The OS is windows 2003 x64 edition
SQL 2005 Enterprise 64bit 9.00.3050.00
We have 4 databases on the same instance with sizes ranging from 25 to 80GB
The only reason I was looking at profiler was out of sheer habit. Infact our DB wasn't even on compat 90 until last year...so I couldn't use DMV's
As you have suggested I will be looking at 'sys.dm_exec_query_stats' closely to figure out whats happening. Just that the CMEMThread pops up excessively only when I am running a trace. As soon as I close the trace it disappears.
Now, I know there won't be a quick fix to this issue...but, any further help you can provide will be greatly appreciated. Thanks again
-
Check out this blog post[/url] by Gail Shaw (GilaMonster here on SSC).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Thank you Jack!
That seems like it. We are on SP2 build 3050
-
IIRC there was also a bug where having a profiler filter on textdata that was longer than forty-some characters in length would cause a CPU race as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (8/6/2009)
IIRC there was also a bug where having a profiler filter on textdata that was longer than forty-some characters in length would cause a CPU race as well.Do you have a link for that? I do presentations on Profiler and would like to add it to my resources.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Mmm! I never had a filter on textData...only enabled it on Duration and DBID.
Actually, we haven't upgraded our build yet.....but, I did try the trace again after running DBCC Freesystemcache. Unfortunately, it still didn't work
got the same wait bottlenecks.The application works fine otherwise its only the trace that causes it.
However, within the 2 minutes that I had the trace running, luckily I caught a couple of SP's which were hogging most of the resources. I guess if I can fix those first and worry about the trace problem later.
Cheers!
Anish
-
TheSQLGuru (8/6/2009)
IIRC there was also a bug where having a profiler filter on textdata that was longer than forty-some characters in length would cause a CPU race as well.I can't for the life of me find a link to it doing numerous web searches. Sorry!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Thanks for the effort. I'll do a search as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Was something definitely to do with the filtering. I just ran a trace for over 10 minutes without any filters in place. Even though the system was busy, the CPU never clocked more than 10% usage.
Just thought you'll would like to know...
-
Edit: Nevermind. Must read posts more carefully in future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
anish_ns (8/6/2009)
Was something definitely to do with the filtering. I just ran a trace for over 10 minutes without any filters in place. Even though the system was busy, the CPU never clocked more than 10% usage.Just thought you'll would like to know...
Were you originally tracing events like SP:StmtCompleted in the Stored Procedures category? Have you now changed to tracing events from the TSQL section like SQL:BatchCompleted?
I ask since you didn't provide those details, and I have recently seen this combination of high CPU (across sixteen cores) and lots of CMEMTHREAD waits on a system where heavy use is made of scalar user-defined function calls.
The problem occurs when tracing higher-level obejcts like procedures which contain many calls to these scalar functions (particularly if the function is called in a loop or per-row in a set-based operation). The scalar function call is traced by SP:StmtCompleted but not by SQL:BatchCompleted.
Second thing: you really should consider bringing your server up to at least SP3, since fixes like this one (which offers trace flag 8048 for CMEMTHREAD waits) are included.
The latest build (CU4 for SP3) has a version of 09.00.4226 - whereas you are running build 3050 (a build which is not immediately familiar to me, but seems to be very slightly post-SP2). See Builds Released After SP3 for a list of available builds.
Third: you said earlier that you were unable to use DMVs like sys.dm_exec_query_stats since the database compatibility was not set to 90. Many features of 2005 (including the DMVs and DMFs) are invariant to the database compatibility setting. DMVs are always available to you on 2005 and later, regardless of this setting.
To be clear, I would primarily encourage you to patch your server with the latest fixes before putting more work into this - the problem you have may have already been addressed.
Paul
-
>>To be clear, I would primarily encourage you to patch your server with the latest fixes before putting more work into this - the problem you have may have already been addressed.
And if you take the problem to Microsoft, that is the first thing they will check on - are you on the latest service pack.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply