무거운 질문 🙂 관련된 요소 중 일부를 간략히 설명하겠습니다. 어떤 맥락에서든 이러한 요소들과 다른 요소들은 다양하고 흥미로운 결과를 낳을 수 있습니다.

이걸 더 짧게 만들 수 없어서 미안 해요 …

1. 누적 CPU ms 대 논리적 IO
2. 실제 NUMA 노드와 SQL Server 논리 메모리 노드 정렬
3. 쿼리 작업 영역 메모리 할당의 스핀 록 경합
4. 스케줄러에 작업 할당
5. 버퍼 풀에서 관련 데이터 배치
6. 실제 메모리 배치

1. 누적 CPU ms 대 논리적 IO

   워크로드의 CPU 효율성을 측정하고 스핀 록이 발생하기 쉬운 사례를 찾기 위해 CPU 사용률에 대해 논리적 IO (또는 성능 용어 “버퍼 풀 페이지 조회”) 그래프를 자주 사용합니다.

   그러나 SQL Server는 페이지 조회 및 스핀 록 이외의 많은 활동으로 CPU 시간을 누적합니다.

   • 계획이 컴파일되고 다시 컴파일됩니다.
   • CLR 코드가 실행됩니다.
   • 기능이 수행됩니다.

   다른 많은 활동들은 페이지 조회에 반영되지 않고 상당한 CPU 시간을 씹을 것입니다.

   내가 관찰 한 워크로드에서 이러한 “논리적 IO 집약적이지만 CPU를 사용하는”활동 중 가장 중요한 것은 정렬 / 해싱 활동입니다.

   이유는 다음과 같습니다. 비 클러스터형 인덱스가없는 해시 테이블에 대한 두 쿼리의 구성 예를 고려하십시오. 두 쿼리는 동일한 결과 집합을 갖지만 결과 집합 중 하나는 완전히 정렬되지 않으며 두 번째 결과 집합은 선택한 열 중 둘 이상에 의해 정렬됩니다. 두 번째 쿼리는 버퍼 풀에서 동일한 수의 페이지를 참조하더라도 더 많은 CPU 시간을 소비 할 것으로 예상됩니다.

   다음 포스트에서 작업 공간 메모리 및 사용 된 부여 된 작업 공간 양에 대한 추가 정보 :

   • http://sql-sasquatch.blogspot.com/2015/08/sql-server-grantedreservedstolen_4.html

   • http://sql-sasquatch.blogspot.com/2015/08/sql-server-workspace-memory-with-twist.html

   • http://sql-sasquatch.blogspot.com/2015/03/resource-governor-to-restrict-max-query.html

2. 실제 NUMA 노드와 SQL Server 논리 메모리 노드 정렬

   SQL Server (NUMA 인식 전략을 통합 한 이후)는 기본적으로 서버의 각 NUMA 노드에 대해 SQLOS 메모리 노드를 만듭니다. 메모리 할당이 증가함에 따라 각 할당은 SQLOS 메모리 노드 중 하나에 의해 제어됩니다.

   이상적으로, SQLOS 메모리 노드는 실제 NUMA 노드와 완전히 정렬됩니다. 즉, 각 SQLOS 메모리 노드에는 단일 NUMA 노드의 메모리가 포함되며 다른 SQLOS 메모리 노드에는 동일한 NUMA 노드의 메모리가 포함되지 않습니다.

   그러나 이상적인 상황이 항상 그런 것은 아닙니다.

   다음 CSS SQL Server 엔지니어 블로그 게시물 (Kin의 응답에도 포함)에는 SQLOS 메모리 노드에 대한 NUMA 간 노드 메모리 할당이 지속될 수있는 동작이 자세히 설명되어 있습니다. 이 경우 성능에 치명적인 영향을 줄 수 있습니다.

   • http://blogs.msdn.com/b/psssql/archive/2012/12/13/how-it-works-sql-server-numa-local-foreign-and-away-memory-blocks.aspx

   지속적인 NUMA 노드 참조의 특히 고통스러운 경우에 대한 몇 가지 수정 사항이 있습니다. 아마도이 두 가지 외에도 다른 사람들도 있습니다.

   • FIX : SQL Server 2012 또는 SQL Server 2014에서 외부 페이지를 처리하는 동안 NUMA 환경에서 성능 문제가 발생한다

   • FIX : NUMA 환경에서 SQL Server 성능 문제

3. 작업 공간 메모리 할당 중 스핀 록 경합

   여기가 재미있어지기 시작합니다. 작업 공간 메모리의 정렬 및 해시 작업이 CPU를 소비하지만 bpool 조회 번호에는 반영되지 않는다고 이미 설명했습니다.

   스핀 락 경합은이 특별한 재미의 또 다른 층입니다. 메모리가 버퍼 풀에서 도난 당하고 쿼리 메모리 부여에 사용하도록 할당되면 메모리 액세스는 스핀 록으로 직렬화됩니다. 기본적으로 이것은 NUMA 노드 레벨에서 파티션 된 자원으로 발생합니다. 따라서 작업 공간 메모리를 사용하는 동일한 NUMA 노드의 모든 쿼리는 보조금에 대해 메모리를 훔칠 때 스핀 락 경합이 발생할 수 있습니다. 주의해야 할 점 : 이것은 경합 시점이 실제 허가 시점에 있었던 것처럼 “쿼리 당 한 번”경합 위험이 아닙니다. 그보다는 메모리가 그랜트에 대해 도난 당했을 때-매우 큰 메모리 그랜트를 가진 쿼리는 대부분의 그랜트를 사용하는 경우 스핀 록 경합의 기회가 많습니다.

   추적 플래그 8048은 코어 레벨에서 자원을 추가로 분할하여이 경합을 완화시키는 데 큰 도움이됩니다.

   마이크로 소프트는 “소켓 당 8 개 이상의 코어가 있다면 트레이스 플래그 8048을 고려한다”고 말한다. 그러나 … 실제로 소켓 당 코어 수는 (여러 개가있는 한) 아니라 단일 NUMA 노드에서 수행되는 작업에서 얼마나 많은 경합 기회가 있습니다.

   접착 된 AMD 프로세서 (소켓 당 12 개의 코어, 소켓 당 2 개의 NUMA 노드)에는 NUMA 노드 당 6 개의 코어가 있습니다. 추적 플래그 8048이 활성화 될 때까지 스핀 락 호송 장치에 멈춘 CPU 4 개 (각각 NUMA 노드 8 개, 코어 6 개)가있는 시스템을 보았습니다.

   이 스핀 록 경합이 4 개의 vCPU만큼 작은 VM에서 성능을 저하시키는 것을 보았습니다. 추적 플래그 8048은 해당 시스템에서 사용 가능할 때 수행 된 작업을 수행했습니다.

   적절한 워크로드를 가진 4 코어 주파수 최적화 CPU가 여전히 있다는 것을 고려할 때, 추적 플래그 8048의 이점도 있습니다.

   CMEMTHREAD는 추적 플래그 8048이 완화하는 스핀 록 경합 유형과 함께 대기합니다. 그러나주의 사항 : CMEMTHREAD 대기는이 특정 문제의 근본 원인이 아니라 확증적인 증상입니다. 누적 CMEMTHREAD 대기 시간이 상당히 짧아서 추적 플래그 8048 및 / 또는 9024가 배포에서 지연되는 CMEMTHREAD “대기 시작”이 높은 시스템을 보았습니다. 스핀 록을 사용하면 누적 대기 시간이 일반적 으로 잘못 볼 수 있습니다. 오히려 CPU 시간 낭비를보고 싶을 것입니다. 주로 스핀 자체로 표시되고, 잠재적으로 불필요한 컨텍스트 스위치를 나타내는 관련 대기로 표시됩니다.

   • 작동 방식 : CMemThread 및 디버깅

4. 스케줄러에 작업 할당

   NUMA 시스템에서, 연결은 특정 NUMA 노드와 연관된 연결 엔드 포인트가 없다고 가정 할 때 NUMA 노드 (실제로는 실제로 이와 연관된 SQLOS 스케줄러 그룹)로 분배됩니다. 세션이 병렬 쿼리를 실행하는 경우 단일 NUMA 노드의 작업자를 사용하는 것이 좋습니다. 흠 … 복잡한 쿼리를 4 개의 경로로 나누고 기본값은 MAXDOP 인 4 개의 NUMA 노드 서버를 고려하십시오. 쿼리가 MAXDOP 작업자 스레드 만 사용한 경우에도 NUMA 노드의 각 논리 CPU마다 4 개의 작업자 스레드가 있습니다. 그러나 복잡한 계획에는 4 개의 경로가 있으므로 NUMA 노드의 각 논리 CPU에는 단일 쿼리에 대해 16 명의 작업자가있을 수 있습니다.

   그렇기 때문에 때때로 NUMA 노드 하나가 열심히 작동하는 반면 다른 노드는 로프를 사용하는 것을 보게됩니다.

   작업 할당에는 몇 가지 다른 뉘앙스가 있습니다. 그러나 가장 중요한 점은 CPU 사용량이 NUMA 노드에 균등하게 분배되지는 않는다는 것입니다. (bpool 페이지 삽입 (읽기 또는 첫 페이지 쓰기)은 작업자가있는 스케줄러와 연관된 SQLOS 메모리 노드의 bpool로 이동하고 도난 된 페이지는 우선적으로 “로컬”SQLOS 메모리에서 가져옵니다. 노드도 마찬가지입니다.

   maxdop을 0에서 8 이하로 가져 오는 것이 도움이된다는 것을 알았습니다. 워크로드 프로파일 (주로 예상되는 장기 실행 가능한 동시 쿼리 수에 대한 imo)에 따라 MAXDOP = 2로 진행하는 것이 보증 될 수 있습니다.

   병렬 처리에 대한 비용 임계 값을 조정하는 것도 도움이 될 수 있습니다. 내가 작업하는 시스템은 비용이 많이 드는 쿼리로 소비되는 경향이 있고 50 또는 100 미만의 계획은 거의 발생하지 않으므로 비용 임계 값을 조정하는 것보다 maxdop (워크로드 그룹 레벨에서 oten)을 조정하여 더 많은 견인력을 얻었습니다.

   • 8 개의 NUMA 노드가있는 SQL Server PLE

     이 글에서는 작업 공간 메모리와 불규칙한 작업 분배에 대한 스핀 록 경합 조합에 대해 설명합니다. 참조-이 모든 것들이 함께 직조됩니다 🙂

   • 동시 SQL Server 병렬 쿼리 40 개 + (소켓 2 개 * 소켓 당 10 코어) = spinlock convoy

5. Bpool에서 관련 데이터 배치

   이것이 NUMA 서버를 다룰 ​​때 가장 직관적이라고 생각되는 조건입니다. 또한 가장 일반적으로 워크로드 성능에 큰 영향을 미치지 않습니다.

   테이블이 NUMA 노드 3의 bpool로 읽히고 나중에 NUMA 노드 4의 쿼리가 NUMA 노드에서 모든 bpool 조회를 수행하는 테이블을 스캔하면 어떻게됩니까?

   Linchi Shea는이 성능 영향에 대해 큰 글을 올렸습니다.

   • http://sqlblog.com/blogs/linchi_shea/archive/2012/01/30/performance-impact-the-cost-of-numa-remote-memory-access.aspx

   NUMA 노드에서 메모리에 액세스하면 약간의 추가 메모리 대기 시간이 발생합니다. 최적의 성능을 위해 추가 기본 메모리 대기 시간을 제거 해야하는 워크로드가 있다고 확신합니다.이 작업은 시스템에서 문제가되지 않았습니다.

   그러나 교차 노드 액세스는 잠재적으로 포화 될 수있는 또 다른 전송 지점을 제공합니다. 활동이 너무 많아서 NUMA 노드 사이의 메모리 대역폭이 포화 상태이면 노드 간의 메모리 대기 시간이 증가합니다. 동일한 작업에는 추가 CPU주기가 필요합니다.

   다시 말하지만 메모리 대역폭이 중요한 고려 사항이되는 워크로드가 있다고 확신합니다. 그러나 내 시스템의 경우 내가 나열하는 다른 고려 사항이 더 중요합니다.

6. 실제 메모리 배치

   이것은 드물지만 중요한 경우 실제로 중요합니다. 대부분의 서버에서 메모리 설치는 거의 자연스럽게 NUMA 노드간에 균형을 유지합니다. 그러나 경우에 따라 노드 간 메모리 균형을 맞추기 위해 특별한주의가 필요합니다. 메모리가 균형을 이루지 않는 방식으로 슬롯을 구성하면 일부 시스템의 성능이 완전히 손상 될 수 있습니다. 그러나 이것은 잊어 버렸습니다. 매우 바쁜 첫날 이후와는 대조적으로 몇 달간 생산 서비스 후 이와 같은 문제를 발견하는 것은 매우 드 rare니다. 🙂

큰 끝!

다른 사람은 오래된 통계로 인해 잘못된 계획 선택으로 인해 증상이 나타날 수 있다고 지적했습니다. 내 경험으로는 그렇지 않았습니다. 계획이 좋지 않으면 쿼리가 예상보다 쉽게 ​​시간이 오래 걸리지 만 일반적으로 필요한 것보다 많은 논리적 IO가 수행되고 있기 때문입니다. 또는 tempdb에 유출로 인해. 서버를 관찰 할 때 tempdb에 대한 대량 유출은 명백해야합니다. CPU가 많지 않고 유출 관련 디스크 쓰기에 대한 측정 대기 시간이 예상됩니다.

대신 관찰 한 상황이 NUMA와 관련된 경우 위에 열거 된 요소의 조합이 될 것으로 예상됩니다.

1. 작업 공간 메모리 사용 (논리적 IO 수에는 표시되지 않음)

2. 영구 외부 메모리 조건으로 인해 NUMA 교차 노드 일 수 있습니다 (이 경우에는 관련 수정 사항을 찾으십시오).

3. 부여에 대해 할당이 이루어질 때마다 NUMA 노드 내에서 스핀 록 경합이 발생할 수 있습니다 (T8048로 수정).

4. 다른 병렬 쿼리 작업자가 오버로드 한 논리 CPU에서 작업자가 수행 할 수 있음 (필요에 따라 maxdop 및 / 또는 병렬 처리 비용 임계 값 조정)

답변

SELECT parent_node_id, scheduler_id, cpu_id
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE [status] = N'VISIBLE ONLINE';

select COUNT(distinct Parent_node_id)
from sys.dm_os_schedulers
where [STATUS] = 'VISIBLE ONLINE'
    and Parent_node_ID < 64