An update on the TokenAndPermUserStore “problem”
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/07/19/more-tokenandpermuserstore.aspx
As the original incident had been raised on the back of an application service pack upgrade I made use of one of my msdn incidents.
I have patch 3179 applied and I can see that the cache size fluctuates over the day, however I am actually clearing it out twice a day and the entire procedure cache once a day.
I expect a number of eyebrows will be raised at carrying out this type of command on a production server, however, it’s a long story and we were clearing the cache every day as part of the process of applying plan guides to resolve performance problems.
The application doesn’t seem to work as well on SQL 2005 as it did on SQL 2000, the application users ( via their forums and blogs ) say that there’s a bug in SQL 2005 which messes up the application. My view is that the application does not have effective indexing which makes diagnosis of unappropriate use of cached plans tricky. The application doesn’t use stored procedures, makes very heavy use of client side cursors and appears to give users open season on query writing.
Amongst the solutions mooted under our incident was the use of plan guides to force recompiles to make sure there wasn’t a mismatch of cached plans to queries.
The application also generates vast amount of ad-hoc queries which look as if they should be parameterised but in actual fact are not – I’m advised that turning on forced parameterisation is probably a bad idea.
Well it seems that recompiles and non parameterised ad-hoc queries are a cause of the TokenAndPermUserStore problem, as well as multi-page allocations ( mem to leave if I remember correctly for sql 2000 ) This is something I’ve seen before with similar applications.
So it seems the application is to blame and the “optimisation” suggested by Microsoft has actually made all this worse, somewhat ironic as it’s actually a Microsoft application.