We are having a security memory cache bloating issue, known as
TokenAndPermUserStore. At two different instances (SQL Server 2019, SQL Server 2014 – patched to the latest SP & CU) the size of this cache grows to tens of GB through the day, pushing out data in other caches (plan cache especially).
I am aware of known workarounds (
DBCC FREESYSTEMCACHE(TokenAndPermUserStore), trace flags, playing with access check cache bucket count, and access check cache quota options) and we are using them as a short term solution.
As a long-term solution, we want to get to the source of the entries in this security cache and change the appropriate logic in the apps and infrastructure. But we don’t know what the source is – like which statements, queries, sessions, etc.
As I cannot share the real data here, I reproduced the issue with Erik Darling's source code using
IF NOT EXISTS ( SELECT 1/0 FROM sys.database_principals AS dp WHERE dp.type = 'A' AND dp.name = 'your_terrible_app' ) BEGIN CREATE APPLICATION ROLE your_terrible_app WITH DEFAULT_SCHEMA = [dbo], PASSWORD = N'y0ur_t3rr1bl3_4pp'; END DECLARE @counter INT = 0; DECLARE @holder table ( id INT PRIMARY KEY IDENTITY, cache_size DECIMAL(10,2), run_date DATETIME ); WHILE @counter <= 20000 BEGIN SET NOCOUNT ON; DECLARE @cronut VARBINARY(8000); DECLARE @bl0b_eater SQL_VARIANT; EXEC sys.sp_setapprole @rolename = 'your_terrible_app', @password = 'y0ur_t3rr1bl3_4pp', @fCreateCookie = true, @cookie = @cronut OUTPUT; SELECT @bl0b_eater = USER_NAME(); EXEC sys.sp_unsetapprole @cronut; SELECT @bl0b_eater = USER_NAME(); IF @counter % 1000 = 0 BEGIN INSERT @holder(cache_size, run_date) SELECT cache_size = CONVERT ( decimal(38, 2), (domc.pages_kb / 1024. / 1024.) ), run_date = GETDATE() FROM sys.dm_os_memory_clerks AS domc WHERE domc.type = 'USERSTORE_TOKENPERM' AND domc.name = 'TokenAndPermUserStore'; RAISERROR('%i', 0, 1, @counter) WITH NOWAIT; END; SELECT @counter += 1; END;
I know what the source (statement in this case) is and I can observe different DMVs to analyze the memory cache.
Unfortunately, I didn’t find any method to track things down – either with DMVs or tools like Extended Events (no relevant event fires up on inserting to the security cache).
The most promising is the
sys.dm_os_memory_cache_entries DMV, where I can categorize different types of security tokens and for some of them get the SID (user identifier) of the entry through parsing XML content of the
entry_data column. The values of class and subclass properties inside the XML of the token could be interesting too, but I didn’t find any documentation about it.
This is the
entry_data XML content:
<entry name="UserToken" store_address="0x00000246D65AE080" entry_address="0x00000246D65B52E0" key="0x1" class="7" subclass="0" id="2" dbid="1" sid="0x010500000000000515000000CE1C74755713A3F4FC356AE360080000" timestamp="15035312" audit_ts="15035312" idAccessCheck="0" />
Any ideas on how to reach the goal mentioned above and not just workaround thing?