{"id":38,"date":"2007-10-21T23:06:31","date_gmt":"2007-10-21T21:06:31","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/10\/21\/memory-and-sql-2005-sp2\/"},"modified":"2007-10-21T23:06:31","modified_gmt":"2007-10-21T21:06:31","slug":"memory-and-sql-2005-sp2","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/10\/21\/memory-and-sql-2005-sp2\/","title":{"rendered":"Memory and SQL 2005 SP2"},"content":{"rendered":"

Or “Why are all my processes waiting on memory. There’s tonnes of memory<\/em>”<\/p>\n

It’s probably not new news that there was a fairly nasty memory-related bug in SQL 2005 RTM and SP1 that was related to the relaxing of limits on cache size. Specifically the TokenAndPermUserStore cache.<\/p>\n

On systems with large amounts of memory (20GB+) and frequent ad-hoc queries or significant usage of dynamic SQL, the cache can grow quite large, and by quite large I’m talking upwards of 2GB. I think I saw the cache at close on 8GB at one time on one of my servers.<\/p>\n

The problem with this is that is takes quite a bit of time to search through several GB of cache to find the required tokens. Making matters worse, access to that cache is synchronised, so only a single thread may have access at a time.<\/p>\n

The main symptom of that problem is lots of CMEMTHREAD waits without an apparent wait resource and a higher than normal CPU usage.<\/p>\n

But that problem was fixed in SP2 with a change to the caching behaviour<\/a>. Right?<\/p>\n

Well, not really.<\/p>\n

While investigating memory-related waits on one of my servers, I ran into a few blog entries that seemed to suggest that the caching bug was not fixed in SP2, not completely anyway.
\nTroubleshooting Performance issues with TokenAndPermUserStore in SQL2005 x64<\/a>
\nMore TokenAndPermUserStore
\n<\/a>TokenAndPermUserStore continued<\/a>
\nWesley’s blog point to an entry in the list of fixed included in the 4th cumulative update that related to the token cache. Interesting that there was no KB article for it.<\/p>\n

To sum things up. If you’ve got a SQL 2005 server with lots of memory, you’re running a build before 3179 and you’re seeing lots of CMEMTHREAD waits, you may be running into this bug. If flushing the cache (DBCC<\/font> FREESYSTEMCACHE <\/font>(<\/font>‘TokenAndPermUserStore’<\/font>)<\/font>) makes the problems go away, then it’s almost certain. Get hold of the latest cumulative rollup and see if it helps.<\/p>\n

p.s. There has been at least one report that the problem persists even in the latest build. If I find more on that, I’ll post it.<\/p>\n","protected":false},"excerpt":{"rendered":"

Or “Why are all my processes waiting on memory. There’s tonnes of memory” It’s probably not new news that there was a fairly nasty memory-related bug in SQL 2005 RTM and SP1 that was related to the relaxing of limits… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[20,15],"tags":[],"class_list":["post-38","post","type-post","status-publish","format-standard","hentry","category-admin","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-C","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/38","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=38"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/38\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=38"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=38"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=38"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}