SQL Server 실행 계획 이해

실행 계획을 어떻게 읽습니까? 오른쪽에서 왼쪽으로, 왼쪽에서 오른쪽으로, 아니면 비용을 확인하여? 또는 인덱스 스캔, 테이블 스캔 및 조회와 같은 개체는 어떻습니까? 이 블로그에서는 Microsoft® SQL Server 실행 계획을 읽는 방법에 대해 설명합니다.

소개

SQL Server는 일반적으로 좋은 계획을 생성하지만 때로는 계획을 검증하고 잘못된 계획을 수정하는 것이 현명하지 않습니다.

SQL Server에서 예상 실행 계획과 실제 그래픽 실행 계획을 얻을 수 있습니다. ctrl M 명령을 사용하여 이러한 계획을 생성합니다. 또는 Ctrl L 또는 SSMS(SQL Server Management Studio)의 표준 도구 모음에서 실행 아이콘 오른쪽에 있는 아이콘을 사용합니다. SQL Server에는 다른 유형의 계획이 있지만 이러한 계획은 이 게시물에서 다루지 않습니다.

예상 및 실제 실행 계획

실행 계획에는 두 가지 유형이 있습니다.

예상 실행 계획 :예상 계획은 SQL 서버가 데이터를 가져오기 위해 수행할 것으로 예상되는 작업의 추정치를 제공합니다.
실제 실행 계획 :실제 실행 계획은 Transact-SQL 쿼리 또는 일괄 처리가 실행된 후에 생성됩니다. 이 때문에 실제 실행 계획에는 실제 리소스 사용량 및 런타임 경고와 같은 런타임 정보가 포함됩니다.

데이터 처리

동일한 쿼리에 대한 예상 계획과 실제 계획의 차이를 본 적이 있습니까? 대부분 동일하지만 통계 변경, 스키마 관련 변경 또는 데이터 변경으로 인해 다를 수 있습니다. 문제 해결 시 항상 실제 실행 계획을 확인해야 합니다.

실제 포인트를 식별하기 위해 실행 계획을 올바르게 읽으십시오. 비용보다는 데이터 흐름을 살펴보는 것부터 시작하십시오. 논리적 또는 물리적 읽기에 대해 생각하지 마십시오. 입/출력(I/O) 작업의 수를 줄이는 것이 중요합니다. 데이터베이스 관리자(DBA)는 저장소에 액세스하는 것이 하드웨어 리소스 중 가장 느리다는 것을 알고 있으므로 해당 작업을 최소화해야 합니다. 그렇다면 통계를 어떻게 탐색하고 실행 계획이 이를 보여줍니까? 예, 그렇습니다!오른쪽에서 왼쪽으로 마우스를 가져가서 방향선을 확인하십시오. 레코드 수와 데이터 크기를 표시합니다. 각 선은 다음 그림과 같이 작업에서 반환된 데이터 볼륨에 따라 더 굵거나 가늘어집니다.

개체가 많은 경우 각 작업에서 처리하는 데이터의 양에 대한 개요를 얻을 수 있는 더 나은 방법이 필요합니다. SentryOne 플랜 탐색기를 다운로드하고 이 도구를 사용하여 플랜을 확인하여 개요를 쉽게 얻을 수 있습니다.

시스템에서 SentryOne을 다운로드하고 구성한 후 실행 계획을 엽니다. SentryOne에는 필요에 따라 사용할 수 있는 많은 보기와 설명이 있습니다. 데이터 처리 보기를 얻으려면 Data size in MB를 선택하여 보기를 변경하십시오. 다음 이미지와 같이 옵션을 선택합니다. 귀하의 목표는 전체 데이터 처리를 줄일 수 있는 기회를 찾는 것입니다.

I/O 스트레스를 줄여야 하는 경우 SET STATISTICS IO ON을 볼 수 있습니다. 쿼리에 대한 I/O 사용량에 대한 전반적인 아이디어를 얻기 위한 T-SQL 값입니다. 결과가 메시지로 전환하려면 SSMS에서 쿼리를 실행하기 전에 이를 설정해야 합니다. 결과 탭 패널. 다음 결과와 유사해야 합니다.

(356 row(s) affected)

Table 'Table3'. Scan count 1, logical reads 5, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

논리적 읽기 5 결과는 SQL Server가 메모리에서 데이터를 가져오기 위해 40KB(5 * 8KB) 페이지를 읽는다는 것을 보여줍니다. 쿼리를 최적화할 때 논리적 읽기를 무시하지 마십시오. 라이브 서버에서 논리적 읽기와 물리적 읽기가 모두 발생할 수 있기 때문입니다. 참조하는 페이지가 메모리에 있는지 디스크에 있는지 알 수 없습니다. 목표는 누적 읽기 작업 수를 줄이는 것입니다.

예상 대 실제 계획

실제 계획이 생성된 후 실행 계획의 작업 또는 논리 단위 위에 마우스를 가져갑니다. 클러스터형 인덱스 검색 또는 스캔과 같은 물리적 작업 유형을 보려면 다음 이미지를 참조하세요. 결과. 실행 횟수와 실제 및 예상 행 수를 찾습니다. 개별 작업 비용은 이전 하드웨어를 기반으로 한 추정치이며 정확한 세부 정보를 제공하지 않을 수 있으므로 피하십시오. 다음 이미지에서 클러스터형 인덱스 검색 연산자는 100개의 레코드를 얻기 위해 한 번 실행되었으며 SQL은 356개의 레코드를 추정합니다. 그 차이는 오래된 통계나 쿼리 성능 때문일 수 있습니다.

실행 반복

다음 이미지는 하나의 레코드를 얻기 위한 클러스터 인덱스의 예상 실행 횟수가 1154121번임을 보여줍니다. 중요합니다. 운영자 비용이 더 낮고 단순히 무시하고 비용이 더 높은 다른 작업을 볼 수도 있지만 이것은 비용이 많이 드는 선택으로 판명되었을 수 있습니다. 이러한 작업은 클러스터 인덱스가 있음에도 불구하고 쿼리 성능에 큰 영향을 미칩니다. 행 ID(RID) 조회는 힙에 대한 유사한 작업입니다.

임시 데이터베이스

예상 레코드 수와 실제 레코드 수의 차이는 고려해야 할 또 다른 사항입니다. 잘못된 추정으로 인해 할당된 메모리가 제한된 쿼리가 발생할 수 있습니다. 그럴 때 임시 데이터베이스(tempdb)를 사용하여 작업을 수행합니다. 다음 이미지와 같이 SQL Server에서 잘못된 연산자 또는 계획을 선택하면 쿼리의 작업이 느려지고 추출이 느려질 수 있습니다. 실제 레코드 수는 동일하지만 추정치가 다릅니다. 이는 오래되었거나 누락된 통계 때문일 수 있습니다. 테이블 변수에는 통계가 없으므로 계획은 재컴파일 옵션을 사용할 때까지 새 릴리스에서 항상 1과 1K를 반환합니다. 따라서 테이블 변수는 많은 수의 레코드에 대해 좋은 선택이 아닙니다.

정렬 연산자

정렬의 영향을 고려해야 합니다. 정렬 연산자는 주로 집계, 병합 조인 또는 절로 정렬과 같은 기능에 사용됩니다. 이것은 몇 개의 레코드에만 영향을 미치지 않을 수 있지만 레코드가 추가될 때마다 처리 속도가 느려집니다. 정렬을 피하거나 order by 절을 사용하지 마십시오. 정렬이 필요한 경우 정렬된 데이터를 애플리케이션으로 보내는 대신 애플리케이션 그리드를 사용하여 정렬을 수행하십시오.

다음 이미지는 정렬 비용을 보여줍니다.

스풀 연산자

살펴봐야 할 또 다른 핵심 연산자는 스풀입니다. 스풀은 숨겨진 또는 임시 개체 또는 작업 테이블 intempdb를 저장하기 때문에 느린 연산자입니다. 이것은 또한 되감기를 리바인드하게 하는 특정 연산자로 인해 속도가 느려집니다. Eager, Lazy, Table/Index 등과 같은 SQL Server에는 많은 유형의 스풀이 있습니다. SQL Server는 중간 결과 집합에 대해 원본 테이블로 돌아가는 것보다 임시 작업 테이블을 참조하는 것이 더 나을 때 스풀을 사용합니다. 다음 이미지는 예를 보여줍니다.

스풀을 사용하면 되감기 및 되감기 횟수를 확인하는 것이 중요합니다. 되감기는 되감기보다 비용이 많이 듭니다. 예를 들어 다음 이미지에서 연산자는 2674회 되감기를 표시합니다. 이는 데이터를 가져오기 위해 쿼리가 2674번 다시 실행됨을 의미합니다. 각 레코드를 되감기 위해 테이블 스풀에서 테이블 스캔으로 각 연산자를 반환합니다. Rebind는 스풀에서 데이터를 가져오고 테이블 스캔을 위해 반환하지 않았음을 의미합니다.

해시 및 중첩 루프 연산자

작은 레코드 집합에서 잘 수행되기 때문에 해시 및 중첩 루프는 다음으로 고려해야 할 연산자입니다. 그러나 큰 레코드 집합의 경우 또는 예상 계획과 실제 계획 사이에 큰 차이가 있는 경우 이러한 연산자는 메모리 대신 tempdb를 사용할 수 있으므로 큰 영향을 줄 수 있습니다. SQL Server는 운영자 세부 정보에 다음과 같은 경고를 게시합니다. 이런 일이 발생하면 통계에주의하십시오. 잘못된 추정으로 루프는 메모리 할당에 부족하거나 반복을 계속합니다. 실행을 위해 할당된 메모리를 확인합니다. 메모리 할당을 보려면 실행 계획의 시작 지점(왼쪽에서 오른쪽으로)을 선택하여 속성 상자를 엽니다. 아무 이상이 없다면 쿼리 튜닝이 이상적인 선택이 되어야 합니다.

결론

쿼리 최적화의 첫 번째 목표는 전체 읽기 및 쓰기(즉, 디스크의 I/O)를 줄이는 것입니다. 메모리에서 읽기 및 쓰기를 위한 논리적 읽기를 잊지 마십시오. I/O를 줄이면 대부분의 문제가 해결되고 쿼리가 훨씬 빠르게 실행됩니다.

다음으로 ontempdb 활동으로 인해 비용이 많이 드는 다른 작업을 살펴봅니다. tempdb는 많은 작업에 사용되며 항상 비용이 많이 든다는 것을 기억하십시오. 되감기를 찾으십시오. 작업, 스풀, 정렬 및 루프의 실행 횟수입니다.

tempdb와 함께 사용하면 비용이 많이 듭니다. 좋은 단서를 제공하기 때문에 각 운영자에 대한 경고를 검토하는 것을 잊지 마십시오. 이 게시물에서 누락된 인덱스 연산자에 대해 논의하지 않지만 그렇다고 해서 무시할 수 있는 것은 아닙니다.

검토하되 맹목적으로 색인을 생성하지 마십시오. 동일한 열에서 사용 가능한 다른 인덱스를 확인하고 데이터베이스에서 실행 중인 쿼리에 미치는 영향을 고려하십시오.

피드백 탭을 사용하여 의견을 작성하거나 질문하십시오. 저희와 대화를 시작할 수도 있습니다.