Our migration to SQL Server 2005 x64 was catastrophic :-( We probably ran into an obscure bug that will be fixed in Service Pack 2. A temporary workaround is to add the user(s) that connect to SQL Server to the sysadmin role.
The issue is that the USERSTORE_TOKENPERM cache grows too large. Every statement that gets executed is matched to this store to check whether the executing user has sufficient permissions. While checking this SQL Server uses spinlocks to prevent multiple users from accessing the store, when the cache grows too large the queries might suffer from slowdowns. Under great load this can become a big issues because locks might be taken too long causing many problems.
Our problem was enlarged by the fact that we use UpdateBatch for our inserts and updates. UpdateBatch sends the statements as prepared statements with the parameters already defined. Unfortunately when doing this it sizes varchars by the length of the text to insert and not the field length. This makes SQL Server consider it as a new query whenever the size is different and as a result our procedure cache gets flooded with query plans. SQL Server stores only one entry for parameterized queries but since it considers practically all our inserts as unique queries the userstore grows larger too.
Lucky for us SQL Server 2005 offers a lot of dmv's to check all this.
To check the size of the cache items use the following query:
SELECT * FROM sys.dm_os_memory_clerks
ORDER BY (single_pages_kb + multi_pages_kb) DESC
The single_pages_kb are stolen from the buffer pool whereas multi_pages_kb are pages taken outside the buffer pool.
Subscribe to:
Post Comments (Atom)
2 comments:
There's a hotfix now but at the time all you needed to do is run
DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
You didnt need to add everyone to SA, at least that's my understanding.
Thanks for the information.
If you read my more recent posts about this issue you will notice that there are 3 solutions, one of them is indeed the freesystemcache.
Post a Comment