{"id":71,"date":"2008-05-02T16:48:37","date_gmt":"2008-05-02T14:48:37","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/05\/02\/token-and-perm-user-store-growth\/"},"modified":"2008-05-02T16:48:37","modified_gmt":"2008-05-02T14:48:37","slug":"token-and-perm-user-store-growth","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/05\/02\/token-and-perm-user-store-growth\/","title":{"rendered":"Token and perm user store growth"},"content":{"rendered":"

I wrote about TokenandPermUserStore issue<\/a> a while back. I revisited the issue recently on 2 production servers to see if it was still an issue on a fairly recent SQL build.<\/p>\n

The two servers in question run the same application, but with a vast difference in usage.<\/p>\n

Server 1 – Itanium, 12 processors, 48 GB memory, build 3042 (SP2) running on Windows Server 2003 DataCenter SP1
\nServer 2 – x64, 16 processors, 64 GB memory, build 3215 (SP2 CU5) running on Windows Server 2003 DataCenter SP2<\/p>\n

On both servers there’s a job that checks the size of the token cache and flushes it if it’s above 750 MB. The size was set because when I first set up the jobs, I noticed that the CMEMTHREAD waits were getting noticeable if the cache got above 1 GB.<\/p>\n

On both servers, I set up a job that would log the size of the cache to a table. On server 1, that ran every 15 min. On server 2 it ran every 5 min. The results were a little frightening.<\/p>\n

Server 1<\/strong><\/p>\n

\"\"<\/a><\/p>\n

On server 1, during working hours, it takes about 2 and a half hours for the token cache to reach 750 MB. I’ve seen noticeable CMEMTHREAD waits (> 100ms as shown by sys.dm_exec_requests) above 1 GB<\/p>\n

Server 2<\/strong><\/p>\n

\"\"<\/a><\/p>\n

On server 2, the token cache reaches around 750 MB within 45 minutes. I don’t work on that server myself, but apparently the waits become noticeable after around 2.5 GB<\/p>\n

From what I know, there are three requirements to encounter this problem:<\/p>\n

    \n
  1. Lots of memory (16+ GB)<\/li>\n
  2. Multiple different logins\/usernames<\/li>\n
  3. Lots of dynamic\/ad-hoc SQL<\/li>\n<\/ol>\n

    The problem was supposed to be fixed in SP2, then fixed again in a patch included in the 4th cumulative update, but as can be seen, it’s still a problem even on CU5.<\/p>\n

    If anyone knows if there’s a resolution to this in a later CU or in SQL Server 2008, I’d be very happy to hear about it.<\/p>\n","protected":false},"excerpt":{"rendered":"

    I wrote about TokenandPermUserStore issue a while back. I revisited the issue recently on 2 production servers to see if it was still an issue on a fairly recent SQL build. The two servers in question run the same application,… (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-71","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-19","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/71","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=71"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/71\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=71"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=71"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=71"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}