SQL Server 논리  물리 프로세서를 결정하는 방법

 

Version : SQL Server

 

SQL Server  하드웨어 레이아웃 정보를 바탕으로 NUMA 노드를 구성한다. SQL Server 2016 부터는 레이아웃 정보를 바탕으로 자동으로 NUMA 노드를 구성을 한다.

·         SQL Server 2016 Automatic Soft NUMA : http://sqlmvp.kr/220806805474

그렇다면 SQL Server Native(bare metal) 또는 VM 환경과 같이 가상화 환경에서 구성된다면 어떻게 노드를구성할까 과정의 핵심은getlogicalprocessorinformation (windows API)  API이며 논리적으로는 bare metal VM 기반이 동일하다.  VM(Virtual Machine) 설치하는경우 지원 기능에 대해서는 벤더의 정보를 참고할  있도록 한다. SQL Server Windows API 사용하여 프로세서와 패키지간의 상호 작용을 결정한다.

·         GetSystemInfo https://msdn.microsoft.com/en-us/library/windows/desktop/ms724381(v=vs.85).aspx

·         GetLogicalProcessorInformation https://msdn.microsoft.com/en-us/library/windows/desktop/ms683194(v=vs.85).aspx

 

GetLogicalProcessorInformation API  RelationProcessorCore  RelationProcessorPackage 정보를 검색   있다반환된 정보를 사용하여 SQL Server 그에 따라 노드와 스케줄러를 할당 한다예를들면  SQL Server 2016에서 자동으로 NUMA 노드가 구성될  같은 패키지의 논리 프로세서가 동일한 소프트NUMA 노드가할당되는 것을 방지 한다.

 

아래 경우 다양한 시스템 정보에서 코어  패키지가 어떻게 표현되는지   있다아래 샘플 결과는 8 processor system 이며 싱글 CPU, 4Core system, HT enabled 상태이다.

Readtrace 또는 ostress 에서 GetLogicalProcessorInformation API 사용하여 프로세서 관계를 출력

Active proc mask(0): 0x000000FF

        Architecture: 9

           Page size: 4096

                CPUs: 8

    Processor groups: 1

        Highest node: 0

  Proximity: 00  Node: 00

—————————————

               Group: 0

—————————————

        Processor(s): 0x00000003 Function units: Shared    <— 이진 값을 변경하면 논리당 2개의프로세서를 나타냄

        Processor(s): 0x0000000C Function units: Shared    <— 프로세서는 RelationProcessorCore  나타냄

        Processor(s): 0x00000030 Function units: Shared

        Processor(s): 0x000000C0 Function units: Shared

        Package mask: 0x000000FF    <— 패키지는 RelationProcessorPackage 나타냄

        Processor(s): 0x000000FF assigned to Numa node: 0

 

 

MSINFO32 결과

Processor    Intel(R) Core(TM) i7-4810MQ CPU @ 2.80GHz, 2801 Mhz, 4 Core(s), 8 Logical Processor(s)

 

SQL Error Log

SQL Server detected 1 sockets with 4 cores per socket and 8 logical processors per socket, 8 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

 

Hyper-V VM에서 동일하게4 프로세서 할당하였을때 결과이다 구성에서 프로세서는 단일 CPU, 4코어 , 코어당 단일 CPU 시스템으로 표시된다.

 

Active proc mask(0): 0x0000000F

        Architecture: 9

           Page size: 4096

                CPUs: 4

    Processor groups: 1

        Highest node: 0

  Proximity: 00  Node: 00

—————————————

               Group: 0

—————————————

        Processor(s): 0x00000001 Function units: Separated

        Processor(s): 0x00000002 Function units: Separated

        Processor(s): 0x00000004 Function units: Separated

        Processor(s): 0x00000008 Function units: Separated

        Package mask: 0x0000000F

        Processor(s): 0x0000000F assigned to Numa node: 0

 

MSINFO32

Processor    Intel(R) Core(TM) i7-4810MQ CPU @ 2.80GHz, 2794 Mhz, 4 Core(s), 4 Logical Processor(s)

 

API 시스템이 단일 CPIU 패키지의 4CPU 시스템임을 SQL Server에게 알린다프로세서를 별도로 보고함으로써 SQL Server 스케줄러와 노드 할당을 결정할  프로세서를 별도로 처리한다.

Bare metal 단일 CPU, 4코어, HT 지원하므로 4개의 CPU 실제로 어떻게 매핑 될까기본적으로 Hyper-V  경우 프로세서는 논리적(floating) 엔티티이다최적화가 진행되는 동안 VM 노출된 프로세서는 필요에 따라이동하여 VM CPU 사이클을 제공할  있다.

Hard Affinity 경우 NUMA 스패닝과 같은 추가 옵션이 VM 솔루션에서 지원되거나 지원되지 않을 수있다. SQL Server스케줄러 레이아웃(Error log or DMV) 추가 옵션을 검토하여 VM  bare metal 구성에서 레이아웃 최적화를 최적화하는데 필요할  있다.  

 


 

 

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2016/06/09/how-it-works-how-sql-server-determines-logical-and-physical-processors/

https://en.wikipedia.org/wiki/Bare_machine

 

 



 

SQL Server, Hyper-V, VM, Virtual Machine, Processor, Bare Metal, GetLogicalProcessorInformation, CPU, Hyperthread, MS SQL,




강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp





profile

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

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.