October 19, 2007 at 3:00 am
Hi All.
I'm having an odd occurence on oone of my prod servers. sometimes, with no pattern, the server slows down massively for 5 min or so. A check of running processes reveals lots and lots of CMEMTHREAD waits, usually around 10-30 ms. No blocking is evident and no other wait types show.
At the moment I suspect it may be due to recompiles. Does anyone know it it is the recompile itself that can cause waits, or is it the cache insert/remove that must wait?
Anything else that may cause MEMThread waits?
Any suggestions on tracking down the cause for this? Profiler events, perfmon counters, ...?
I have a profiler trace running for sp:recompile, sp:cacheinsert and sp:cacheremove.
Server specs:
12 proc Itanium, 48 GB memory (42 allocated to SQL)
Server 2003 data center, SQL 2005 enterprise SP2
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
October 19, 2007 at 3:13 am
Cathan Kirkwood's "How to Diagnose and Fix Wait Locks" article describes this in some detail. See http://www.sqlservercentral.com/articles/Administering/wait_types/856/
Regards,
Andras
October 19, 2007 at 3:47 am
Thanks. Looking into the output of the DBCC.
Interesting thing checking perfmon. The private bytes for the SQL process is climbing constantly, as is the working set. However, target and total server memory are constant.
Huh? I'm wondering it I've got another memory leak in the MEM_TO_LEAVE area.
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
October 19, 2007 at 5:42 am
Right, lots of querying, searching and reading later...
The Practical Troubleshooting Guide (edited by Ken Henderson) pointed me at the cache counters and cache entries DMVs. The top 3 cache stores, in terms of size, were the sql plan cache at 1.7 GB, the object plan cache at 1.8GB and the Token and Perm User Store at 2.6 GB with 1.3 million entries.
A quick google query turned up a couple of blog entries that noted that the size problem with that cache that was supposedly fixed in SP2, still occured well after, up to build 3186 at least.
Troubleshooting Performance issues with TokenAndPermUserStore in SQL2005 x64
TokenAndPermUserStore continued
Seems there's another fix for that cache in a recent hotfix that's included in the Cumulative update 4. Interesting that there was no kb article published for it. There's just a note in one of the articles listing post SP2 hotfixes
Build 09.00.3179
This build contains the following SQL Server 2005 post-SP2 hotfixes.
SQL Hotfix Knowledge Base Description
bug number article number
50001482 The TokenAndPermUserStore cache store may continue to grow steadily
and decrease performance
Fun and games. Time to go flush a system cache and see if it helps...
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply