SET SHOWPLAN_ALL(Transact-SQL)

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Microsoft SQL Server가 Transact-SQL 문을 실행하지 않도록 합니다. 대신 SQL Server는 명령문이 실행되는 방법에 대한 자세한 정보(쿼리 계획)를 반환하고 문 및 예상 행에 대한 리소스 요구 사항의 추정치를 제공합니다(카드진수 추정).

Transact-SQL 구문 표기 규칙

Syntax

  
SET SHOWPLAN_ALL { ON | OFF }

참고 항목

SQL Server 2014(12.x) 및 이전 버전에 대한 Transact-SQL 구문을 보려면 이전 버전 설명서를 참조 하세요.

설명

SET SHOWPLAN_ALL 옵션은 실행 시간 또는 런타임에 설정되며, 구문 분석 시에는 설정되지 않습니다.

SET SHOWPLAN_ALL 옵션을 ON으로 설정하면 SQL Server가 각 문을 실행하지 않고 문에 대한 실행 정보를 반환하고 Transact-SQL 문은 실행되지 않습니다. 이 옵션을 ON으로 설정할 경우 다시 OFF로 설정할 때까지 이후 모든 Transact-SQL 문에 대한 정보가 반환됩니다. 예를 들어, SET SHOWPLAN_ALL을 ON으로 설정한 상태에서 CREATE TABLE 문을 실행하면 SQL Server가 같은 테이블이 사용되는 이후 SELECT 문에서 지정한 테이블이 없음을 알리는 오류 메시지를 반환합니다. 따라서 이후 이 테이블을 참조하는 작업은 실패합니다. SET SHOWPLAN_ALL 옵션을 OFF로 설정하면 SQL Server가 보고서를 생성하지 않고 문을 실행합니다.

SET SHOWPLAN_ALL은 그 출력을 처리하도록 작성된 애플리케이션에서 사용하기 위한 옵션입니다. SET SHOWPLAN_TEXT 옵션을 사용하여 osql 유틸리티와 같은 Microsoft Win32 명령 프롬프트 애플리케이션에 읽을 수 있는 출력을 반환할 수 있습니다.

SET SHOWPLAN_TEXT와 SET SHOWPLAN_ALL 옵션을 저장 프로시저 내에 지정할 수 없으며 일괄 처리에서 유일한 문이어야 합니다.

SET SHOWPLAN_ALL은 SQL Server 쿼리 프로세서가 각 문을 실행할 때 수행한 단계를 나타내는 계층적 트리를 구성하는 행 세트로 정보를 반환합니다. 출력에 반영된 각 문에는 문의 텍스트가 있는 단일 행이 포함되고, 이 단일 행 뒤에는 실행 단계에 대한 자세한 정보가 있는 몇 개의 행이 있습니다. 아래 표에서는 출력에 포함된 열을 보여 줍니다.

열 이름 Description
StmtText PLAN_ROW 형식이 아닌 행에 대해 이 열에는 Transact-SQL 문의 텍스트가 포함됩니다. PLAN_ROW 유형의 행에 대해서는 이 열에 작업의 설명이 포함됩니다. 이 열에는 물리적 연산자가 포함되며 논리 연산자가 포함될 경우도 있습니다. 이 열 다음에 물리적 연산자가 결정한 설명이 나올 경우도 있습니다. 자세한 내용은 실행 계획 논리 및 물리 연산자 참조를 참조하세요.
StmtId 현재 일괄 처리에 있는 문의 수입니다.
NodeId 현재 쿼리의 노드 ID입니다.
Parent 부모 단계의 노드 ID입니다.
PhysicalOp 노드에 대한 물리적 구현 알고리즘입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
LogicalOp 이 노드가 나타내는 관계형 대수 연산자입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
Argument 수행되는 작업에 대한 추가 정보를 제공합니다. 물리적 연산자에 따라 이 열의 내용이 달라집니다.
DefinedValues 이 연산자가 사용하는 값에 대한 쉼표로 구분된 목록을 포함합니다. 이 값은 현재 쿼리에 있었던 계산된 식(예: SELECT 목록이나 WHERE 절에 있음)이거나 이 쿼리를 처리하기 위해 쿼리 프로세서에서 사용한 내부 값입니다. 쿼리 내의 어디에서든 정의된 이 값이 참조될 수 있습니다. PLAN_ROWS 형식의 행에만 해당됩니다.
EstimateRows 이 연산자가 생성한 출력의 예상 행 수입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
EstimateIO 이 연산자의 예상 I/O 비용입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
EstimateCPU 이 연산자의 예상 CPU 비용입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
AvgRowSize 이 연산자를 통해 통과되는 행의 예상 평균 행 크기(바이트)입니다.
TotalSubtreeCost 이 작업과 모든 자식 작업의 예상(누적) 비용입니다.
OutputList 현재 작업에서 예상하고 있는 열에 대한 쉼표로 구분된 목록을 포함합니다.
Warnings 현재 작업과 연관된 경고 메시지에 대한 쉼표로 구분된 목록을 포함합니다. 경고 메시지에 열 목록과 함께 "NO STATS:()" 문자열이 포함될 경우도 있습니다. 이 경고 메시지는 쿼리 최적화 프로그램이 이 열의 통계에 기초하여 결정을 내리려고 했지만 사용 가능한 통계가 없었음을 나타냅니다. 따라서 쿼리 최적화 프로그램이 추측을 해야 했고 결과적으로 비효율적인 쿼리 계획을 선택했을 수도 있습니다. 쿼리 최적화 프로그램이 더 효율적인 쿼리 계획을 선택할 수 있도록 통계를 만들거나 업데이트하는 방법은 UPDATE STATISTICS를 참조하세요. 어떤 경우에는 이 열에 조인 조건자 없이 조인(테이블을 수반하는)이 일어났음을 나타내는 "MISSING JOIN PREDICATE" 문자열이 포함되기도 합니다. 실수로 조인 조건자를 삭제하면 예상보다 실행 시간이 긴 쿼리가 만들어지고 큰 결과 집합이 반환됩니다. 이 경고가 나타나면 조인 조건자를 의도적으로 사용하지 않았는지 확인하세요.
Type 노드 유형. 각 쿼리의 부모 노드에 대해서는 노드 유형이 Transact-SQL 문 유형(예: SELECT, INSERT, EXECUTE 등)입니다. 실행 계획을 나타내는 하위 노드에 대해서는 PLAN_ROW 유형입니다.
Parallel 0 = 연산자가 병렬로 실행되지 않습니다.

1 = 연산자가 병렬로 실행됩니다.
EstimateExecutions 현재 쿼리를 실행하는 동안 이 연산자가 실행될 예상 횟수입니다.

비용 단위는 벽 시계 시간(wall-clock time)이 아닌 내부 측정 시간을 기반으로 합니다. 다른 계획과 비교하여 계획의 상대 비용을 결정하는 데 사용합니다.

사용 권한

SET SHOWPLAN_ALL을 사용하려면 SET SHOWPLAN_ALL이 실행되는 문을 실행할 수 있는 권한이 있어야 하며 참조된 개체를 포함하는 모든 데이터베이스에 대한 SHOWPLAN 권한이 있어야 합니다.

SELECT, INSERT, UPDATE, DELETE, EXEC stored_procedure 및 EXEC user_defined_function 문의 경우 실행 계획을 생성하려면 사용자에게 다음 권한이 있어야 합니다.

  • Transact-SQL 문을 실행할 수 있는 적절한 권한

  • 테이블이나 뷰와 같은 Transact-SQL 문이 참조하는 개체가 포함된 모든 데이터베이스에 대한 SHOWPLAN 권한

DDL, USE database_name, SET, DECLARE, 동적 SQL 등 다른 모든 문의 경우 Transact-SQL 문을 실행할 수 있는 권한만 있으면 됩니다.

예

다음의 두 문은 SQL Server가 쿼리에서의 인덱스 사용을 분석하고 최적화하는 방법을 보여 주기 위해 SET SHOWPLAN_ALL 설정을 사용합니다.

첫 번째 쿼리에서는 인덱싱된 열의 WHERE 절에 Equals 비교 연산자(=)를 사용합니다. 그 결과 LogicalOp 열에 클러스터형 인덱스 검색 값이 들어가고 Argument 열에는 인덱스 이름이 들어갑니다.

두 번째 쿼리에서는 WHERE 절에 LIKE 연산자를 사용합니다. 이렇게 하면 SQL Server가 클러스터형 인덱스 검색을 사용하여 WHERE 절 조건을 충족하는 데이터를 찾을 수 있습니다. 그 결과 Argument 열에 인덱스 이름이 있는 상태에서 LogicalOp 열에 클러스터형 인덱스 검색 값이 들어가고, Argument 열에 WHERE 절 조건이 있는 상태에서 LogicalOp 열에 필터 값이 들어갑니다.

처음 인덱싱된 쿼리의 경우에는 EstimateRows 및 TotalSubtreeCost 특성의 값이 더 작습니다. 즉, 인덱싱된 쿼리가 인덱싱되지 않은 쿼리에 비해 훨씬 빨리 처리되고 리소스도 더 적게 사용한다는 의미입니다.

USE AdventureWorks2022;  
GO  
SET SHOWPLAN_ALL ON;  
GO  
-- First query.  
SELECT BusinessEntityID   
FROM HumanResources.Employee  
WHERE NationalIDNumber = '509647174';  
GO  
-- Second query.  
SELECT BusinessEntityID, EmergencyContactID   
FROM HumanResources.Employee  
WHERE EmergencyContactID LIKE '1%';  
GO  
SET SHOWPLAN_ALL OFF;  
GO

참고 항목

SET 문(Transact-SQL)
SET SHOWPLAN_TEXT(Transact-SQL)
SET SHOWPLAN_XML(Transact-SQL)