none
sp_setapprole & sp_unsetapprole or EXECUTE AS & REVERT cause TokenAndPermUserStore to grow to 2GB and performance to degrade when run in a loop RRS feed

  • Question

  • sp_setapprole & sp_unsetapprole or EXECUTE AS & REVERT cause TokenAndPermUserStore to grow to 2GB and performance to degrade when run in a loop (simulating a connection pool that performs these when getting or returning a connection)

    Options that I have tried to configure SQL to avoid this slowdown:

    • DBCC TRACEON (4610, -1)
    • DBCC TRACEON (4618, -1)
    • sp_configure @configname = 'access check cache bucket count', @configvalue = '512'
    • sp_configure @configname = 'access check cache quota', @configvalue = '2048'

    SQL Server Properties: * Version: SQL Server 2017 - (RTM-GDR) - Developer * Ram: 10GB -> 20GB

    Tried different SQL Server versions: * 2016 (SP2-GDR) * 2017 (RTM-GDR)

    Below is an example of sp_setapprole, running, the time taken to perform the last 1000 SET / UNSET operations is recorded along with cache size. As the script progresses the cache grows and so does the time to perform the SET / UNSET operations.

    SELECT pages_kb AS KB, pages_kb / 1024 AS [MB], pages_kb / (1024 * 1024) AS [GB] FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore'
    DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
    GO
    
    DECLARE @cookie VARBINARY(8000);
    DECLARE @msg NVARCHAR(MAX);
    DECLARE @run INTEGER = 0;
    DECLARE @d1 DATETIME
    DECLARE @rc INTEGER = 0
    
    SELECT @msg = N'KB=' + CONVERT(NVARCHAR(20), pages_kb) + N', MB=' + CONVERT(NVARCHAR(20), pages_kb / 1024) + N', GB=' + CONVERT(NVARCHAR(20), pages_kb / (1024 * 1024))
    FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
    
    SELECT @d1 = GETDATE()
    
    WHILE (@run < 10000) BEGIN
        EXECUTE @rc = sys.sp_setapprole @rolename = 'my_app_role', @password = 'my_app_role_password', @fCreateCookie = true, @cookie = @cookie OUTPUT
        IF @rc = 1 BREAK
    
        IF ((@run + 1) % 1000) = 0 BEGIN
            SELECT @msg = CONVERT(VARCHAR(12), GETDATE() - @d1, 114);
            RAISERROR(@msg, 0, 0) WITH NOWAIT;
    
            SELECT @d1 = GETDATE()
        END
    
        EXECUTE sys.sp_unsetapprole @cookie;
    
        IF ((@run + 1) % 1000) = 0 BEGIN
            SELECT @msg = N'KB=' + CONVERT(NVARCHAR(20), pages_kb) + N', MB=' + CONVERT(NVARCHAR(20), pages_kb / 1024) + N', GB=' + CONVERT(NVARCHAR(20), pages_kb / (1024 * 1024))
            FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore';
            RAISERROR(@msg, 0, 0) WITH NOWAIT;
        END
    
        SELECT @run = @run + 1;
    END
    GO

    The exact same result can be obtained using EXECUTE AS USER and REVERT

    SELECT pages_kb AS KB, pages_kb / 1024 AS [MB], pages_kb / (1024 * 1024) AS [GB] FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore'
    DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
    GO
    
    DECLARE @cookie VARBINARY(8000);
    DECLARE @msg NVARCHAR(MAX);
    DECLARE @run INTEGER = 0;
    DECLARE @d1 DATETIME
    
    SELECT @msg = N'KB=' + CONVERT(NVARCHAR(20), pages_kb) + N', MB=' + CONVERT(NVARCHAR(20), pages_kb / 1024) + N', GB=' + CONVERT(NVARCHAR(20), pages_kb / (1024 * 1024))
    FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
    
    SELECT @d1 = GETDATE()
    
    WHILE (@run < 10000) BEGIN
        EXECUTE AS USER = 'proxy_user_test' WITH COOKIE INTO @cookie;
    
        IF ((@run + 1) % 1000) = 0 BEGIN
            SELECT @msg = CONVERT(VARCHAR(12), GETDATE() - @d1, 114);
            RAISERROR(@msg, 0, 0) WITH NOWAIT;
    
            SELECT @d1 = GETDATE()
        END
    
        REVERT WITH COOKIE = @cookie;
    
        IF ((@run + 1) % 1000) = 0 BEGIN
            SELECT @msg = N'KB=' + CONVERT(NVARCHAR(20), pages_kb) + N', MB=' + CONVERT(NVARCHAR(20), pages_kb / 1024) + N', GB=' + CONVERT(NVARCHAR(20), pages_kb / (1024 * 1024))
            FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore';
            RAISERROR(@msg, 0, 0) WITH NOWAIT;
        END
    
        SELECT @run = @run + 1;
    END
    GO


    The output from one of these runs is listed below, as you can see, both the cache size and time taken are going up and up...

    The size tops out just under 3GB

    KB=768, MB=0, GB=0
    00:00:01:820
    KB=124216, MB=121, GB=0
    00:00:01:950
    KB=247616, MB=241, GB=0
    00:00:02:130
    KB=371024, MB=362, GB=0
    00:00:02:300
    KB=494424, MB=482, GB=0
    00:00:02:450
    KB=617824, MB=603, GB=0
    00:00:02:560
    KB=741224, MB=723, GB=0
    00:00:02:703
    KB=864624, MB=844, GB=0
    00:00:02:877
    KB=988024, MB=964, GB=0
    00:00:03:070
    KB=1111424, MB=1085, GB=1
    00:00:03:273
    KB=1234824, MB=1205, GB=1






    Friday, September 13, 2019 7:02 AM

All replies

  • Have you tried the effect of installing the latest Cumulative Update for SQL 2017?

    I'm not saying that it has been fixed, but it might have.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 13, 2019 10:15 AM
  • I have seen few people on forums have reported similar issue I have hunch that this is bug. Can you please let me know if below clears the memory utilized

     DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') 
    Also strongly advise you to check again after applying latest CU.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, September 13, 2019 10:47 AM
    Moderator
  • I was able to repro this on

    Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64)   Jun 15 2019 00:45:05   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: )

    This is not the very latest CU, only the second-latest, but I'm on a train, and then download of the CU was too slow for my patience.

    I only tested the script with EXECUTE AS USER. Shanky, the script has DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') on top, and that resets the situation back to the initial faster state.

    This is from a single connection, but I guess you have observed this when you have lot of connections using EXECUTE AS or sp_setapprole when they connect? (I ask, because that would be the normal use case for this pattern, but I understand that you have it in a single script to simplify the demonstration.)



    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 13, 2019 3:19 PM
  • Erland,

    Yes, the script above is meant to simplify reproduction of the problem. The actual application uses dozens of connections and performs SET / UNSET when retrieving from the connection pool.

    Full SQL Server Version Strings that this was reproduced on:

    • Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)   Jul 12 2019 17:43:08   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) 
    • Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)   Jun 15 2019 00:26:19   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) 
    • Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)   Jun 15 2019 23:15:58   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

    Now tested on the latest CU.


    Sunday, September 15, 2019 11:51 PM
  • Hi Lawrence Sim,

     

    What is the result of your test? I  recommend you to submit it to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 16, 2019 6:53 AM
  • Hello Desmond,

    The results of the test on the updated version of SQL Server were the same.

    I have already raised https://feedback.azure.com/forums/908035-sql-server/suggestions/38605258-tokenandpermuserstore-grows-for-each-sp-setapprole

    Monday, September 16, 2019 7:57 AM
  • I have already raised https://feedback.azure.com/forums/908035-sql-server/suggestions/38605258-tokenandpermuserstore-grows-for-each-sp-setapprole

    Good!

    Just to set your expectations right, a bug report here means that Microsoft may fix it at some point, but there is no guarantee that they will.

    If you find this a blocking issue, and you need a fix, you need to open a support case. I would suspect, though, that in this case, you will be told that running DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') is a suitable workaround. If you disagree, you may need to argue and particularly point out how this affects your business.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, September 16, 2019 9:04 PM
  • Just to add a bit more on what Erland has mentioned there was similar bug in SQL Server 2008 r2 related to this user and token perm and I used DBCC FREESYSTEMCACHE and it worked just fine. Even in support article from MS this was mentioned to you can use it.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, September 17, 2019 10:23 AM
    Moderator
  • Just to be clear here.

    DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') is NOT a suitable workaround.

    When your system makes many calls per second, the cache fills up so fast that freeing it does not do much...


    Vague performance results:

    • set / unset approle: 10
    • set / unset approle (free cache every 15 seconds): 3
    • Just using logins and users: 1

    Wednesday, September 18, 2019 3:59 AM
  • I agree but unless this is fixed you might have to use this small command frequently

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 18, 2019 6:37 AM
    Moderator
  • Just to be clear here.

    DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') is NOT a suitable workaround.

    When your system makes many calls per second, the cache fills up so fast that freeing it does not do much...

    This is good ammunition if you open a case with Microsoft. Freeing the cache every 15 seconds? That's a little crazy.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, September 18, 2019 9:14 PM