TokenAndPermUserStore

SpinLock , 많은 로그인 생성 등으로 쿼리가 늦어 지는 경우 발생

사이즈 조사하여  작업을 해 줄 필요가 있다.

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

GO




SELECT

[type],

[name],

cache_address,

buckets_count,

buckets_in_use_count

FROM sys.dm_os_memory_cache_hash_tables

WHERE [type] = 'TokenAndPermUserStore';

 

-- SQL 2012

SELECT

[name],

sum(pages_kb)/1024 MB

FROM sys.dm_os_memory_clerks

WHERE type = 'USERSTORE_TOKENPERM' and pages_kb > 8

GROUP BY name;

 

-- SQL 2008

SELECT SUM(single_pages_kb + multi_pages_kb) AS "CurrentSizeOfTokenCache(kb)"

FROM sys.dm_os_memory_clerks

WHERE name = 'TokenAndPermUserStore'

 

-- 어떤 내용들이 있는지 

SELECT COUNT(*) as TokenCount, *

FROM

(SELECT

x.value('(//@name)[1]', 'varchar (100)') AS [Token Name],

x.value('(//@class)[1]', 'bigint') AS [Class],

x.value('(//@subclass)[1]', 'int') AS [SubClass]

FROM

(SELECT CAST (entry_data as xml)

FROM sys.dm_os_memory_cache_entries

WHERE type = 'USERSTORE_TOKENPERM')

AS R(x)

) a

GROUP BY [Token Name],[Class],[SubClass]