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:
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
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.aspxPlan Cache Related Performance Problems.doc (68.5 KB)