데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

SQL Server Spinlock 소개

jevida(강성욱) 2017.01.11 08:28 Views : 1587

SQL Server Spinlock 소개

 

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

 

Spinlock은 Latch처럼 공유 데이터 구조에 대한 접근 스레드가 동기화 스토리지 엔진에 의해 사용되는 경량의 동기화 객체이다. Latch가 발생할 경우 연관된 오버헤드가 발생하는데 바쁠 때는 래치의 데이터 구조를 보호하는 것은 의미가 없다. 이 때문에 SQL Server는 Spinlock를 구현한다.

 

 

 

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

No. Subject Author Date Views
» SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1587
1992 Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2574
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 3033
1990 클러스터된 SQL 서버 인스턴스에 대한 호스트 이름 확인 jevida(강성욱) 2017.01.11 1368
1989 sys.dm_tran_locks 를 이용한 잠금 정보 확인 jevida(강성욱) 2017.01.11 1533
1988 외래키 제약 조건 삭제 후 재작성 스크립트 생성하기 jevida(강성욱) 2017.01.11 1781
1987 페이지 ID로 테이블 이름 찾기 jevida(강성욱) 2017.01.11 1436
1986 DBCC CHECKPRIMARYFILE 사용법 jevida(강성욱) 2017.01.11 1331
1985 컬럼스토어 인덱스 대용량 데이터 로드 jevida(강성욱) 2017.01.11 1745
1984 컬럼스토어 인덱스 INSERT 작업과 동시성 jevida(강성욱) 2017.01.11 1493
1983 컬럼스토어 인덱스 동시성 jevida(강성욱) 2017.01.11 1783
1982 컬럼스토어 인덱스 ROW와 ROWGROUP 영향 jevida(강성욱) 2016.11.23 2953
1981 테이블 변수와 TF 2453 jevida(강성욱) 2016.11.23 3186
1980 Sp_trace_create MaxfileSize 오류 jevida(강성욱) 2016.11.23 2400
1979 RANDBETWEEN 함수 만들기 jevida(강성욱) 2016.11.23 4640
1978 Optimize for hint 쿼리 최적화 jevida(강성욱) 2016.11.23 3512
1977 TempDB 파일 사이즈 증가 시 경고 받기 jevida(강성욱) 2016.11.23 3047
1976 블록킹 세션을 찾아 우선순위 낮은 세션 종료하기 jevida(강성욱) 2016.11.23 3250
1975 다양한 포맷의 이름 파싱 하기 jevida(강성욱) 2016.11.23 2666
1974 비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리 jevida(강성욱) 2016.11.23 3151





XE Login