예상 및 실제 쿼리 계획 설명
실제 실행 계획과 예상 실행 계획은 혼동될 수 있습니다. 예상 계획에서는 캡처되지 않는 런타임 통계가 실제 계획에는 포함된다는 차이가 있습니다. 사용된 연산자와 실행 순서는 거의 모든 경우에 예상 계획과 동일합니다. 또 다른 고려 사항은 실제 실행 계획을 캡처하려면 쿼리를 실행해야 하므로 시간이 많이 걸리거나 불가능할 수 있습니다. 예를 들어 문은 UPDATE 한 번만 실행할 수 있습니다. 그러나 쿼리 결과와 계획을 확인해야 하는 경우 실제 계획 옵션 중 하나를 사용해야 합니다.
표시된 것처럼 예상 쿼리 계획 상자로 표시된 단추를 선택하거나 키보드 명령 Control+L을 사용하여 SSMS에서 예상 계획을 생성할 수 있습니다. 표시된 아이콘을 선택하거나 키보드 명령 Control+M을 사용하여 쿼리를 실행하여 실제 계획을 생성할 수 있습니다. 두 옵션 단추는 다르게 작동합니다. 예상 쿼리 계획 포함 단추는 강조 표시된 쿼리(또는 강조 표시된 쿼리가 없는 경우 전체 작업 영역)에 즉시 응답하는 반면 실제 쿼리 계획 포함 단추는 쿼리를 실행해야 합니다.
쿼리 실행과 예상 실행 계획 생성에는 모두 오버헤드가 발생하므로, 프로덕션 환경에서는 실행 계획 보기를 신중하게 수행해야 합니다.
일반적으로 쿼리를 작성하는 동안 예상 실행 계획을 사용하여 성능 특성을 이해하거나 누락된 인덱스를 식별하거나 쿼리 변칙을 검색할 수 있습니다. 실제 실행 계획은 쿼리의 런타임 성능을 이해하는 데 가장 적합하며, 가장 중요한 것은 쿼리 최적화 프로그램이 사용 가능한 데이터에 따라 최적이 되지 않은 선택을 하도록 만드는 통계 데이터의 간격입니다.
쿼리 계획 읽기
실행 계획은 쿼리를 충족하는 데 필요한 데이터를 검색하는 동안 데이터베이스 엔진이 수행하는 작업을 보여 줍니다. 계획을 자세히 살펴보겠습니다.
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;
이 쿼리는 StockItemID 열의 값이 같은 StockItemHoldings 테이블에 StockItems 테이블을 조인합니다. 데이터베이스 엔진은 먼저 해당 행의 ID를 확인해야 쿼리의 나머지를 처리할 수 있습니다.
계획의 각 아이콘은 실행 계획을 구성하는 다양한 작업 및 결정에 해당하는 특정 작업을 나타냅니다. SQL Server 데이터베이스 엔진에는 실행 계획의 일부가 될 수 있는 100개가 넘는 쿼리 연산자가 있습니다. 각 연산자 아이콘 아래에는 쿼리의 총 비용을 기준으로 비용 백분율이 있습니다. 0% 비용을 보여 주는 작업도 여전히 일부 비용을 나타냅니다. 사실, 그래픽 계획 비용은 항상 정수로 표시되고 실제 백분율은 0.5%미만이므로 0% 반올림 때문입니다.
실행 계획의 실행 흐름은 오른쪽에서 왼쪽으로, 위에서 아래로 이동하므로 이 계획에서는 StockItemHoldings.PK_Warehouse_StockItemHoldings 클러스터형 인덱스의 클러스터형 인덱스 검색 작업이 쿼리의 첫 번째 작업입니다. 연산자를 연결하는 선의 너비는 다음 연산자로 진행하는 데이터에 대한 예상 행 수를 기반으로 합니다. 굵은 화살표는 대규모의 연산자 간 전송을 나타내며 쿼리 튜닝 기회를 나타낼 수도 있습니다. 연산자 위에 마우스를 놓고 도구 설명에서 추가 정보를 볼 수도 있습니다.
도구 설명은 예상 계획에 대한 비용 및 예상을 강조 표시하며 실제 계획에는 실제 행 및 비용 비교가 포함됩니다. 각 연산자에는 도구 설명보다 더 자세한 정보를 제공하는 속성도 있습니다. 특정 연산자를 마우스 오른쪽 단추로 클릭하면 상황에 맞는 메뉴에서 속성 옵션을 선택하여 전체 속성 목록을 볼 수 있습니다. 이 옵션은 SQL Server Management Studio에서 별도의 속성 창을 엽니다. 이 창은 기본적으로 오른쪽에 있습니다. 속성 창이 열리면 연산자를 선택하면 속성 목록이 해당 연산자에 대한 세부 정보로 채워집니다. 또는 기본 SQL Server Management Studio 메뉴에서 보기를 선택하고 속성을 선택하여 속성 창을 열 수 있습니다.
속성 창에는 추가 정보가 포함되며 다음 연산자에 전달되는 열을 자세히 설명하는 출력 목록이 표시됩니다. 이러한 열은 클러스터형 인덱스 검색을 사용하여 분석할 때 쿼리 성능을 향상시키기 위해 비클러스터형 인덱스가 필요하다는 것을 나타낼 수 있습니다. 클러스터형 인덱스 검색 작업은 전체 테이블을 읽기 때문에 이 시나리오에서는 각 테이블의 StockItemID 열에 있는 비클러스터형 인덱스가 더 효율적일 수 있습니다.
간단한 쿼리 프로파일링
SSMS 또는 확장 이벤트 모니터링 인프라를 사용하든 실제 실행 계획을 생성하면 상당한 오버헤드가 발생할 수 있습니다. 따라서 이 프로세스는 일반적으로 라이브 사이트 문제 해결을 위해 예약됩니다. 알려진 관찰자 오버헤드는 실행 중인 애플리케이션을 모니터링하는 비용입니다. 일부 시나리오에서 이 비용은 CPU 사용률의 몇 퍼센트에 불과할 수 있지만 실제 실행 계획 캡처와 같은 다른 경우에는 개별 쿼리 성능이 크게 느려질 수 있습니다. SQL Server 엔진의 레거시 프로파일링은 쿼리 정보를 캡처하기 위해 최대 75개의% 오버헤드를 생성할 수 있지만 경량 프로파일링의 최대 오버헤드는 약 2%입니다.
간단한 프로파일링의 첫 번째 버전에서는 이 행 수 및 I/O 사용률 정보(지정된 쿼리를 충족하기 위해 데이터베이스 엔진에서 수행하는 논리적/물리적 읽기 및 쓰기 수)를 수집했습니다. 또한 쿼리 계획의 각 연산자에서 데이터를 검사할 수 있도록 query_thread_profile 이라는 새로운 확장 이벤트가 도입되었습니다. 간단한 프로파일링의 초기 버전에서 이 기능을 사용하려면 추적 플래그 7412를 전역적으로 사용하도록 설정해야 합니다.
경량 프로파일링을 전역적으로 사용하도록 설정하지 않은 경우 쿼리 힌트를 USE HINTQUERY_PLAN_PROFILE 사용하여 쿼리 수준에서 간단한 프로파일링을 사용하도록 설정할 수 있습니다. 이 힌트가 있는 쿼리가 실행을 완료하면 실제 실행 계획을 제공하는 query_plan_profile 확장 이벤트가 생성됩니다. 다음은 이 힌트가 포함된 쿼리의 예입니다.
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[ QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItems] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox
OPTION(USE HINT ('QUERY_PLAN_PROFILE'));
마지막 쿼리 계획 통계
경량 프로파일링은 SQL Server 2019와 Azure SQL Database 및 관리되는 인스턴스 모두에서 기본적으로 사용하도록 설정됩니다. 간단한 프로파일링은 LIGHTWEIGHT_QUERY_PROFILING이라는 데이터베이스 범위 구성 옵션으로 사용할 수도 있습니다. 데이터베이스 범위 옵션을 사용하여 서로 독립적인 어떤 사용자 데이터베이스에 대해서도 이 기능을 사용하지 않도록 설정할 수 있습니다.
또한 지정된 계획 핸들에 대해 마지막으로 알려진 실제 쿼리 실행 계획을 표시할 수 있는 동적 관리 함수가 있습니다 sys.dm_exec_query_plan_stats. 이 함수를 통해 마지막으로 알려진 실제 쿼리 계획을 보려면 서버 전체에서 추적 플래그 2451을 사용하도록 설정합니다. 또는 LAST_QUERY_PLAN_STATS라는 데이터베이스 범위 구성 옵션을 사용하여 이 기능을 사용하도록 설정할 수도 있습니다.
이 함수를 다른 개체와 결합하여 캐시된 모든 쿼리에 대한 마지막 실행 계획을 가져올 수 있습니다.
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO
이 기능을 사용하면 최소한의 오버헤드로 시스템에 있는 쿼리의 마지막 실행에 대한 런타임 통계를 빠르게 식별할 수 있습니다. 다음 이미지는 계획을 검색하는 방법을 보여줍니다. 결과의 첫 번째 열이 될 실행 계획 XML을 선택하면 아래의 두 번째 이미지에 표시된 실행 계획이 표시됩니다.
다음 이미지의 Columnstore 인덱스 스캔 속성에서 볼 수 있듯이, 캐시에서 가져온 실행 계획에는 쿼리된 실제 행 수가 포함되어 있습니다.
