Процедура sp_setapprole и sp_unsetapprole или EXECUTE AS & REVERT приводит к увеличению TokenAndPermUserStore до 2 ГБ и снижению производительности

Question

Sp _setapprole и sp_unsetapprole или EXECUTE AS & REVERT приводят к увеличению TokenAndPermUserStore до 2 ГБ и снижению производительности при запуске в цикле (имитация пула соединений, который выполняет это при получении или возврате соединения)

Параметры, которые я пытался настроить SQL, чтобы избежать этого замедления:

• 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: * Версия: SQL Server 2017 - (RTM-GDR) - Разработчик * Ram: 10 ГБ -> 20 ГБ

Пробовал разные версии SQL Server: * 2016 (SP2-GDR) * 2017 (RTM-GDR)

Ниже приведен пример выполнения процедуры sp_setapprole, время, затраченное на выполнение последних 1000 операций SET / UNSET, записывается вместе с размером кеша. По мере выполнения сценария кеш увеличивается, и вместе с ним увеличивается время выполнения операций SET / UNSET.

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

Точно такой же результат можно получить, используя EXECUTE AS USER и 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

Результат одного из этих запусков приведен ниже, как вы можете видеть, как размер кеша, так и затраченное время увеличиваются и увеличиваются...

Размер превышает 3 ГБ.

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

См. Также: https://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/19e17e27-ee75-4e45-b8af-b53b5ceca93b