[SQL Server] Execution Model

Professional SQL Server 2012 Internals and Troubleshooting 내용 정리

지금까지이 장에서는 SQLOS의 개념을 추상화하여 너무 많은 접미어를 사용하지 않고 아키텍처를 통한 구성 요소의 흐름을 보다 쉽게 이해할 수 있도록했다. 그러나 SQLOS는 SQL Server 아키텍처의 핵심이므로 SQL Server가 작동하는 방식에 대한 이해를 위해 SQL Server가 존재하는 이유와 그 이유를 이해해야한다.

즉, SQLOS는 SQL Server와 Windows 사이에있는 thin User-mode 계층이다. 스케줄링, I / O 완성, 메모리 관리 및 자원 관리와 같은 저수준 작업에 사용된다. 이것이 의미하는 바가 무엇이고 왜 필요한지 정확히 알아보기 위해서는 먼저 SQL Server의 Execution Model을 이해해야한다.

- Execution Model

어플리케이션 하나가 SQL Server에 연결이 되면 Session이 생성되고 이 Session은 Session_id라는 것으로 구분이 된다. sys.dm_exec_sessions라는 DMV를 통해 확인할 수 있다. 세션 내에서 실행 요청이 이루어지면 SQL Server는 작업을 하나 이상의 작업으로 나눈 다음 해당 기간 동안 각 작업에 작업자 스레드를 연결한다. 각 스레드는 세 가지 상태 중 하나 일 수 있습니다.

   -> Running: 1개의 프로세서는 1개의 작업만 실행될 수 있다. 이 상태는 현재 프로세서가 해당 작업을 수행하고 있음을 의미한다.

   -> Suspended: SQL Server에는 협업 스케줄러가 있다. 실행 중인 스레드가 프로세서를 생성하고 리소스를 기다리는 동안 일시 중단된다. 이것을 우리는 wait 상태라고 한다. 

   -> Runnable: 쓰레드가 Wait 상태가 끝나면 Runnable 상태가 된다. 이것은 다시 실행 할 준비가 되어 있다를 의미한다. 이것을 signal wait(신호대기) 상태라고 한다.

Worker Threads를 사용할 수없고 최대 Worker Threads에 도달하지 않은 경우 SQL Server는 새 Worker Thread를 할당한다. 최대 Worker Threads 수에 도달하면 Threads가 사용 가능해질 때까지 대기 유형 THREADPOOL로 태스크가 대기한다.

최대 Worker Threads의 기본값은 CPU 아키텍처 및 논리 프로세스의 수에 의해 결정된다.

공식은 아래와 같다.

For a 32-bit OS:

-> Total available logical CPUs <= 4

- Max Worker Threads = 256

->Total available logical CPUs > 4

- Max Worker Threads = 256 + ((logical CPUs - 4)*8)

For a 64-bit OS:

->Total available logical CPUs <= 4

- Max Worker Threads = 512

->Total available logical CPUs > 4

- Max Worker Threads = 512 + ((logical CPUs - 4)*16)

DMV를 통해서도 현재 실행 중인 시스템의 max workers를 확인 할 수 있으며 구문은 아래와 같다.

SELECT max_workers_count FROM sys.dm_os_sys_info

Worker Threads (THREADPOOL 대기 유형)가 부족하면 종종 병렬 실행 계획이 여러 개 발생하는 증상이 나타나거나 (프로세서 당 하나의 스레드가 사용되므로) 서버가 받아들일 수 있는 성능을 넘어서므로 더 많은 프로세서가있는 프로세서를 구입해야한다.

각 Worker Thread가 필요로 하는 Ram의 용량은 64bit OS는 2MB, 32bit OS는 0.5MB를 필요로 하므로 SQL Server는 Thread를 필요에 따라 생성한다. 

SELECT count(*) FROM sys.dm_os_workers

해당 구문으로 현재 SQL Server에 있는 Threads의 수를 확인 할 수 있다.

Professional SQL Server 2012 Internals and Troubleshooting 22-24p