Lawrence Sim

My feedback

  1. 3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Lawrence Sim commented  · 

    Calling DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') is not a solution, unless these SET / REVERT operations are rare. When they are performed multiple per second, even refreshing the cache every 15 seconds does not restore performance.

    Lawrence Sim shared this idea  · 
    An error occurred while saving the comment
    Lawrence Sim commented  · 

    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

    An error occurred while saving the comment
    Lawrence Sim commented  · 

    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

  2. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Lawrence Sim shared this idea  · 

Feedback and Knowledge Base