The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Thursday, March 28, 2024 Login
Public

SQL Server 2005 runaway, excessive connections, server unresponsive 2/11/2008 10:16:01 AM

Been chasing an issue for a long time, where suddenly the connections to a sql server increase exponentially and queries become unresponsive.

Really never made much headway, but found two interesting articles, URL's below:

http://sqlblog.com/blogs/sarah_henwood/archive/2007/07/23/connection-failures-sql-2005-appears-unresponsive.aspx 

http://support.microsoft.com/kb/927396 

Supposedly this was fixed in Service Pack 2.  I'm having the issue with SQL Server 2005, SP2, Cumulative Hotfix 5, version 3215.

Monitor the query results of:

SELECT SUM(single_pages_kb + multi_pages_kb) AS
   "CurrentSizeOfTokenCache(kb)"
   FROM sys.dm_os_memory_clerks
   WHERE name = 'TokenAndPermUserStore'

If it is continuously increasing, than try running:

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

We have 81+ servers, 100+ instances of SQL Server, and it is only affecting one, of course it is the one under the greatest load, OLTP 5000 transactions per second.  You can see the result of the TokenAndPermUserStore sum growing to 120mb, we now reset it around 40-50mb and have seen no re-occurences of the issue.

I have a case open with Microsoft and am trying to get confirmation that this is the right solution, but since we began running the DBCC statement we have not yet crashed, and previously we could not make it more than 2-3 days without crash, so that there is proof positive of some correlation.

We'll see what Microsoft responds with.

More on plan cache:

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/21/2-0-diagnosing-plan-cache-related-performance-problems-and-suggested-solutions.aspx

Plan Cache Related Performance Problems.doc (68.5 KB)

Blog Home