img by Coba

How is anyone supposed to know how to use trace flag 4621?

I'm helping a client work through some TokenAndPermUserStore performance issues and we're investigating using the new trace flag 4621 that was introduced in Service Pack 3 for SQL Server 2005. Oh wow is this not easy stuff to wade through...

Here's a little background: in SQL Server 2005, there is a known bottleneck in how SQL Server checks permissions particularly for ad-hoc queries. For more reading, here you go:

So we have the problem on multiple sites and we're just now getting around to investigating the new feature in SQL Server 2005 SP3 that allows us to create custom quotas for the token cache. In SP2 we had trace flag 4618 which allowed us to limit the token cache to 1024 entries or 8192 (when combined with trace flag 4610). From http://support.microsoft.com/kb/959823:

When you use trace flag 4618, the number of entries in the cache store is limited to 1,024. When you use trace flag 4618 together with trace flag 4610, the number of entries in the cache store is limited to 8,192. When the limit is reached, SQL Server 2005 removes some entries from the TokenAndPermUserStore cache store.
However, for some workloads, limiting the cache store to 1,024 or to 8,192 entries may not be an optimal solution. This is because trace flag 4618 forces SQL Server to prematurely clean up the TokenAndPermUserStore cache store even if you are not experiencing any performance issues
.

We run DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') on a daily basis at some sites due to the memory pressure. We just checked and, 45 minutes after running a DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') job, we had more than 40,000 entries in the TokenAndPermUserStore! You might be wondering why - well, this particular application uses primarily ad-hoc SQL for all data access therefore there are a lot more security checks that must be undergone for each query/statement.

Enter SP3 and Trace Flag 4621

So 8192 entries was not optimal - we needed more than 8192 entries. SQL Server 2005's SP3 introduces trace flag 4621 (link: http://support.microsoft.com/kb/959823) which allows us to customize the number of entries in the token cache. No more 1024 or 8192; now we can choose much higher values. That brings us to the documentation for this trace flag...

To implement trace flag 4621 requires two things:

  1. Enabling the trace flag
  2. Modifying the registry

Seems easy enough, right? They even have a handy little formula to help you "guesstimate" the custom value you need:

You can determine the custom value for the quota by using the following formula:

Quota = 1,024 * 2 * ( <Number of Distinct Logins> + <Total Number of Users in each database>)

For example, an instance of SQL Server 2005 has 3,000 distinct logins. All these logins connect to a single database. In this case, the quota value is 1,024 * 2 * (3,000 + 3,000) = 12,288,000. When you determine the number of logins and users, count only active logins and users.

Wait - okay... So my client's database has a single user account with about 100 concurrent users. I've seen as many as 25 different connections per user depending on what a user was doing. How do we use this formula? We decided to just plug in the values: 1024 * 2 * (1 + 100) = 2,832,848.

But wait - there's more. Below that, it says,

If you enable trace flag 4610, you substitute 8,192 for 1,024 in the formula. Therefore, the formula is the following: Quota = 8,192 * 2 * (<Number of Distinct Logins> + <Total Number of Users in each database>)

In other words, you've now increased the available size by a factor of eight.

Which Formula to Use?

Okay - but *should I use trace flag 4610*? It's quite a big difference in our formulas:

  • Quota = 1024 * 2 * (1 + 100) = 206,848
  • Quota = 8192 * 2 * (1 + 100) = 1,654,784

So which should we use? Unfortunately the article makes no suggestions or gives any "Use this value when your X is above Y but use trace flag 4610 when your entries are above N." Google is no help as there aren't many articles featuring the term "trace flag 4610". Since we know that we're having intense pressure, we assumed that we should go for the larger value (but in actuality it is having a "larger than normal" token cache that got us into this problem in the first place). We set up some tracking table to store our token cache history:

CREATE TABLE dbo.TokenAndPermCacheHistory (
    LineId INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , AddDate DATETIME NOT NULL DEFAULT (GETDATE())
    , SiteId VARCHAR(128) NOT NULL -- WC for West Clinic?
    , TokenCacheSizeInMB_BeforeFlush DECIMAL(19,2) NOT NULL
    , TokenCacheSizeInMB_AfterFlush DECIMAL(19,2) NOT NULL
    , NumberOfEntriesForSecurityCache_BeforeFlush DECIMAL(19,2) NOT NULL
)

Since we were already using a DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') job daily, we modified it to allow us to keep track of the values:

DECLARE @TokenCacheSizeInMB_BeforeFlush DECIMAL(10,2)
    , @TokenCacheSizeInMB_AfterFlush DECIMAL(10,2)
    , @NumberOfEntriesForSecurityCache_BeforeFlush INT
    , @SiteId VARCHAR(128)
 
SET @SiteId = 'WC'
 
SELECT @TokenCacheSizeInMB_BeforeFlush = SUM(single_pages_kb + multi_pages_kb)/1024.0
FROM sys.dm_os_memory_clerks
WHERE [name] = 'TokenAndPermUserStore'
 
-- Retrieve the contents of the security Tokens
SELECT @NumberOfEntriesForSecurityCache_BeforeFlush = COUNT(*) 
FROM sys.dm_os_memory_cache_entries
WHERE cast(entry_data as xml).value('(//@class)[1]', 'bigint') = 65535;
 
-- Flush the cache
DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
 
-- Get the updated values
SELECT @TokenCacheSizeInMB_AfterFlush = SUM(single_pages_kb + multi_pages_kb)/1024.0
FROM sys.dm_os_memory_clerks
WHERE name = 'TokenAndPermUserStore'
    
INSERT dbo.TokenAndPermCacheHistory
    (AddDate, SiteId, TokenCacheSizeInMB_BeforeFlush, TokenCacheSizeInMB_AfterFlush, NumberOfEntriesForSecurityCache_BeforeFlush)
VALUES 
    (GETDATE(), @SiteId, @TokenCacheSizeInMB_BeforeFlush, @TokenCacheSizeInMB_AfterFlush, @NumberOfEntriesForSecurityCache_BeforeFlush)

Lastly, once we have the historical data in place, we can query against it to help us figure out the "optimal" size:

SELECT * FROM TokenAndPermCacheHistory
WHERE SiteId = 'WC'
ORDER BY AddDate DESC

Now that we've put that in place, we have some data and can make more educated decisions. We needed to know what the number of entries was at peak, after one hour, etc - and now we can find that out.

CAUTION: The above code should not be run on production systems without severe testing!

Now that we know the number of entries we want, now what?

So we settled on using trace flag 4610 along with trace flag 4621 and we wanted to use around 24,576 entries as our starting point. We didn't know what was "right" but, after reviewing our data, we felt 24,576 was a good starting point and that we could watch it closely and modify in the coming days. Back to the documentation and we see, "Under the MSSQLServer registry subkey, add a registry entry..." Folks - that's just a "Key" when you are in the registry editor. Yes, we all probably know that but not everyone is familiar - many will likely create subkeys and entries underneath them. Not for the faint of heart, I suppose...

The question then becomes, "What value do I need to put in the registry to make it say 24,576?" How about this gem from the documentation:

To set the quota of 4,096 for the TokenAndPermUserStore cache store, set the value of the TokenPermQuota registry entry to 00001000.

Gee, thanks! Just so you know, since this is a DWORD (32-bit BTW), you'll need to use Hexadecimal notation. We want to set our value to 24,576 so we have to figure out how to write it in hex notation (it's 00006000). Geez - was that hard or what? How were you supposed to know that was in Hex notation? At least the article could've mentioned it for those noobs having trouble.

SQL Server 2008 Is Cleaner and Clearer

In SQL Server 2008 there are new advanced configuration options in sp_configure named "access check cache bucket count" and "access check cache quota" which handle these options much better. You can read more about them here: http://support.microsoft.com/kb/955644 

Wrapup

Okay - this post was really spawned after my frustration at trying to figure out an article on a very important topic. I hope I've made someone's life a little better with this explanation. If I haven't, then I'm sorry!

authors
scott whigham
grant moyle
chad weaver
recent comments
  • Search Engine Optimization: Thank you for a great post read more
  • Matthew Cadiz: Great post I must say.. Simple but yet interesting and read more
  • Hello Kitty Coloring Pages: I enjoyed this. Needed more pictures though. read more
  • Watch Free Movies Online: Could you please clarify the other comment? read more
  • chicago dentists: Thanks for such a good blog. It was what I read more
  • Kitty White: Great post, thanks. Do you have a RSS feed? read more
  • Kitty Pages: Great post, thanks. Like your blog design too. read more
  • Molly Wedell: Can I quote you in my report for school? read more
  • Skate Boarding Games: Wonderful to read! read more
  • Love Calculator: Which golf clubs will be the best for beginner ? read more