SQL Server Spinlock 소개
- Version : SQL Server 2005, 2008, 2008R2, 2012, 2014
Spinlock은 Latch처럼 공유 데이터 구조에 대한 접근 스레드가 동기화 스토리지 엔진에 의해 사용되는 경량의 동기화 객체이다. Latch가 발생할 경우 연관된 오버헤드가 발생하는데 바쁠 때는 래치의 데이터 구조를 보호하는 것은 의미가 없다. 이 때문에 SQL Server는 Spinlock를 구현한다.
- SQL Server IO and Latch 설명 : http://sqlmvp.kr/140199995476
Latch의 경우는 쿼리가 Latch를 획득하지 못하였을 경우에는 대기 상태로 대기하고 이후에 실행 가능한 상태로 이동한다. 쿼리는 CPU에서 실행된 상태가 아니기 때문에 실행 가능 상태로 남게된다. 그리고 CPU로 다시 이동해서 마지막으로 성공적으로 획득한 Latch로 보호되는 공유 데이터에 접근하여 쿼리를 실행하게 된다. 다음 그림은 SQLOS의 스케줄링을 나타낸다.
Spinlock은 Latch처럼 공유 데이터 구조에 접근하는 스레드 동기화 스토리지 엔진에 의해 사용되는 경량의 동기화 객체이다. Latch와 다른점은 CPU를 떠나지 않고 spinlock를 획득 할 때까지 루프에서 회전하는 것이다. 즉 CPU에서 항상 실행상태에 있다. Spinlock의 가장 큰 장점은 쿼리가 스핀에서 대기해야하는 경우 컨텍스트 스위치가 관여하지 않는것이다. 이는 바쁜 CPU 사이클에서 다른 쿼리가 효과적으로 사용 할 수 있다.
SQL Server 2008 R2에서는 너무 많은 CPU 사이클을 방지하기 위해 스레드가 회전을 잠시 중지하는 exponential backoff mechanism을 시행한다. 간격 후 스레드는 spinlock을 획득 할 수 있는 시간 사이에 슬립모드로 전환한다. 이러한 행동은 CPU 성능 부담을 줄여준다.
Spinlock 문제를 해결하기 위한 주요 DMV는 sys.dm_os_spinlock_stats 이다. 이 DMV에서 반한되는 모든 행은 SQL 서버에서 하나의 특정 스핀을 나타낸다. SQL Server 2014는 262개의 다른 Spinlock을 구현한다.
select * from sys.dm_os_spinlock_stats |
- Name : Spinlock 이름
- Collisions : 스핀락이 보호된 데이터 구조에 액세스하려고 할 때 스레드가 스핀락에 의해 차단된 횟수
- Spins : 스핀락을 구하려고 루프를 시도한 횟수
- Spins_per_collision : 스핀과 충돌 사이의 비율
- Sleep_time : 백오프 때문에 스레드가 슬립한 시간
- Backoffs : 스레드가 백오프된 동안 다른 스레드가 CPU에 계속 할 수 있도록 한 횟수
가장 중요한 열은 backoffs이다. 이 이벤트는 특정 Spinlock 유형에 대한 발생 빈도를 알 수 있다. 매우 높은 백오프는 높은 CPU소비와 Spinlock 경합을 발생시킨다.
스핀락 경합 문제를 해결하려면 XEvent의 splos.spinlock_backoff를 사용할 수 있다. 백오프가 발생하면 이 이벤트는 발생되며 백오프는 항상 SQL서버에서 발생하기 때문에 좋은 조건절을 사용했는지 확인해야 한다.
-- Retrieve the type value for the LOCK_HASH spinlock. -- That value is used by the next XEvent session SELECT * FROM sys.dm_xe_map_values WHERE name = 'spinlock_types' AND map_value = 'LOCK_HASH' GO
-- Tracks the spinlock_backoff event CREATE EVENT SESSION SpinlockContention ON SERVER ADD EVENT sqlos.spinlock_backoff ( ACTION ( package0.callstack ) WHERE ( [type] = 129 -- <<< Value from the previous query ) ) ADD TARGET package0.histogram ( SET source = 'package0.callstack', source_type = 1 ) GO |
SQL Server 내에서 특정 스핀록 유형에 대한 가장 높은 백오프를 생성한 코드 경로를 볼 수 있다. 이때 공용 심볼을 설치하기 위한 trace flag 3656을 활성화 해야한다. 자세한 내용은 Paul Randal 블로그를 참고 한다.
- How to download a sqlservr.pdb symbol file
http://www.sqlskills.com/blogs/paul/how-to-download-a-sqlservr-pdb-symbol-file/
[참고자료]
http://www.sqlpassion.at/archive/2014/06/30/introduction-to-spinlocks-in-sql-server-2/
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
