Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Monday, February 11, 2008

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)
Monday, February 11, 2008 10:16:01 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
Default Trace - Heavy load, turn it...
SQL 2008 R2 License / Cost = Open S...
Door Installation
Recent Posts
Archive
September, 2019 (1)
August, 2019 (1)
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll