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

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

SQL Server Spinlock 소개

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

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
2010 재해복구를 위한 SQL Server 역할 가져오기 jevida(강성욱) 2017.01.11 2279
2009 비관리자 계정에 Profiler 실행 권한 부여하기 jevida(강성욱) 2017.01.11 3119
2008 SQL Server Agent 공유 일정 생성하기 jevida(강성욱) 2017.01.11 2147
2007 인덱스 리빌드는 통계를 업데이트 할까? jevida(강성욱) 2017.01.11 2336
2006 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3296
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4066
2004 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2449
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3032
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3739
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1289
2000 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5232
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1609
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1001
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1420
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1361
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 3676
1994 데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기 jevida(강성욱) 2017.01.11 1660
» SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1584
1992 Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2571
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 2999





XE Login