SQL Server 2005 Enterprise High CPU Usage by Resource monitor

Question

SQL Server 2005 Enterprise High CPU Usage by Resource monitor

Seek-Kill

Valued Member

Points: 53
More actions
November 21, 2011 at 10:30 pm

#248316

[RESOLVED]
Cause by large TokenAndPermUserStore. The high CPU was caused by excessive maintenance of the TokenAndPermUserStore Cache.
Hello All,
Our Production Server Specs:
* SQL Server 2005 Enterprise SP3
* 24 Cores (4 Nodes x 6 Core CPUs) MAXDOP = 4, 256 GB RAM (Around 200GB used, so still some free.)
* SAN drives
Application are experiecing alot of blocking from what appears to be CPU contention... (runnable queue on schedulers)
Problem appears to come and go, and correspond with periods of high load. (ie backups, application scheduled jobs)
Server does not appear to be IO bound, waits on SANS are good.
Whenever the contention occurs, I can see 3 threads consuming around 70% CPU each for three Cores only).
I have tracked the corresponding SQL SPID. They correlate to SPID 1,3,4 Resource Monitor. These processes also have a high ratio of 50:50 privilege:user time.
I have done some queries against sys.sysprocesses and the conclusion supports that the CPU comsuming processes are the 'Resource Monitor' system sessions.
I will get the server patched to SP4 asap, however i fear that may not solve the problem...
Any ideas...
Also the OS is reporting only %16 CPU utilization... However there is a runnable queue within SQLOS.
This is a custom view comparing stats from dm_exec_sessions current to 1 hr before:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

I would try SP4 and also review any CUs for SP4 to see if any bugs fixed by them could be resulting in the behavior you are seeing. Outside of that, you are likely going to have to hit up Microsoft CSS to get someone knowledgeable about such a large system on older software.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Seek-Kill Valued Member Points: 53 More actions · Answer 2

Upon further investigation on the server, I noticed the TokenAndPermUserStore was @ 1.9gb. I cleared it 5 - days ago, however its backup to 1.3gb as of now. Since clearing it, I havent had any performance issues. (But the problem does come sporatically, so i cant be sure if its because i cleared the cache...)

http://support.microsoft.com/kb/927396/en-us kb article seems to suggest this problem was resolved by SQL Server 2005 SP3.

Does anyone know if a high TokenAndPermUserStore would reflect this degradation of performance through the system Resource Monitor Thread?

Article seems to indicate the store should be no more than "couple of hundred mbs".. would 1.9gb definately cause a problem?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

That large a T&UPS would likely lead to issues. I do not see where you have upgraded to SP4 yet or checked post SP4 CUs either. I note that IIRC there were numerous issues with that subsystem over the years.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Seek-Kill Valued Member Points: 53 More actions · Answer 4

Confirmed, its caused by large TokenAndPermUserStore. The high CPU was caused by excessive maintenance of the TokenAndPermUserStore Cache. The system was performing very poorly, clearing the cache alevated the issue (it took 1.5 hours to clear and was ~2GB). I will try to manage the cache with trace flag 4621 (http://support.microsoft.com/kb/959823/)

Thanks All.