access check cache 크기에 따른 성능 문제

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

데이터베이스 개체를 SQL Server에서 액세스 할 때 액세스 검사는 access check result cache라는 내부 구조에 캐시 된다. 캐시의 크기가 너무 크거나 너무 작은 경우 성능에 문제가 발생 할 수 있다.

 

예를 들어 너무 많은 메모리를 사용하는 경우 access check result cache 크기를 줄이는 것이 좋다. 또한 사용 권한을 다시 계산하여 높은 CPU 사용량이 발생할 경우 access check result cache 크기를 늘려야 한다.

 

sp_configure의 access check cache quota 및 access check cache bucket count 옵션은 access check result cache에 사용되는 해시 버킷의 수와 항목을 제어 한다.

 

 

SQL Server 2005의 버전에서 알려진 문제는 x86, x64 시스템과 메모리 2GB 이상의 환경에서 쿼리 성능 저하가 발생할 수 있다. sysadmin 고정 서버 역할 구성원이 아닌 로그인의 컨텍스트에서 쿼리를 실행할 때 높은 CPU 사용량, 작업자 스레드, SQL 사용자 연결의 급격한 증가 등으로 Security Token cache에서 성능 저하가 발생 한다.

 

2GB 메모리 미만의 시스템에서는 일반 메모리 요구 사항이 너무 커지지 않기 때문에 이러한 문제가 발생하지 않는다.

 

이러한 증상이 있을 경우 다음과 같은 방법으로 해결 할 수 있다.

  • SQL Server 2005 환경에서는 SP3와 CU1을 적용
  • DBCC FREESYSTEMCACHE('TokenAndPermUserStore')를 사용하여 TOKENPERM 캐시를 정리
  • 플래그 –T4618, -T4610 사용하여 TokenAndPremUserSotre 캐시 유지관리 항목 수 제한

 

 

[현재 캐시에 사용된 토큰 종류 확인]

항목의 수에 따라 시간이 오래 걸릴 수 있으므로 피크 타임에는 가급적 명령을 실행하지 않도록 한다.

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)

) as a

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

 

 

 

[토큰 캐시에 사용된 메모리 양 확인]

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

FROM sys.dm_os_memory_clerks

WHERE name = 'TokenAndPermUserStore'

 

 

문제 발생의 특정한 임계값은 없지만 모니터링을 통해 캐시가 증가하는 속도를 확인해야 한다. 캐시 문제가 발생 할 경우 캐시 증가로 확인 할 수 있다.

 

[캐시를 액세스하는 동안 경합 확인]

아래 스크립트를 실행 하여 MUTEX 증가 값을 확인한다. SQL Server 프로세스 내부에 여러 스레드가 뮤텍스라는 스핀락 경쟁하는 것을 확인 할 수 있다. 스핀락은 SQL Server 엔진에서 사용하는 매우 경량의 동기화 매커니즘이다. 특정 스핀락이 보호하는 데이터 구조에 따라 SQL 엔진 내에서 고유한 이름이 지정된다.

SET NOCOUNT ON

CREATE TABLE #spins([Spinlock Name] varchar(50),Collisions numeric,Spins numeric,[Spins/Collision] float,[Sleep Time (ms)] numeric,Backoffs numeric)

INSERT INTO #spins EXECUTE ('DBCC SQLPERF (''SPINLOCKSTATS'')')

SELECT TOP 20 * FROM #spins ORDER BY Collisions DESC

DROP TABLE #spins

 

 

 

위의 상황과 연관되어 아래와 같은 증상이 나타난다.

  • 쿼리 시간이 오래 걸림
  • CPU 사용량이 상대적으로 증가
  • 응용프로그램에서 연결(특히 커넥션 풀 환경에서)이 계속 증가
  • 연결 또는 쿼리 시간 초과 발생

 

 

[참고자료]

  • Access check cache 옵션 :

http://msdn.microsoft.com/ko-kr/library/cc645588(v=sql.105).aspx

http://blogs.msdn.com/b/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice [IT재직자] 개강임박!! 올해 마지막 무료 및 국비지원 교육 (JAVA, 리눅스 기초/고급 , CCNA, CCIE 등) 코난(김대우) 2022.12.02 125
Notice 2022년 11월 SQLER의 강좌 업데이트 리스트 코난(김대우) 2022.12.01 25
Notice 2022 공개SW 페스티벌 - Open Up 코난(김대우) 2022.12.01 15
1803 SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1152
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1087
1801 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2491
» access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1081
1799 Hot Add CPU jevida(강성욱) 2016.09.29 873
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1042
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1764
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1466
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1245
1794 메모리 관리 아키텍처 – NUMA 지원 방법 jevida(강성욱) 2016.09.29 1572
1793 메모리 관리 아키텍처 – NUMA(Non-Uniform Memory Access)이해 jevida(강성욱) 2016.09.29 1445
1792 메모리 관리 아키텍처 – Hot Add 메모리 jevida(강성욱) 2016.09.28 940
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1079
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1276
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1841
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2589
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1379
1786 메모리 관리 아키텍처 – 프로세스 주소 공간 jevida(강성욱) 2016.09.28 1382
1785 메모리 관리 아키텍처 – 메모리 아키텍처 jevida(강성욱) 2016.09.28 1904
1784 데이터 압축 상태에 대한 개체 크기 예상 jevida(강성욱) 2016.09.28 1391





XE Login

테스트 팝업
Close