SET SHOWPLAN_XML(Transact-SQL)

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics(전용 SQL 풀에만 해당)

SQL Server가 Transact-SQL 문을 실행하지 않게 합니다. 대신 SQL Server는 명령문이 잘 정의된 XML 문서 형식으로 실행되는 방법에 대한 자세한 정보를 반환합니다.

Transact-SQL 구문 표기 규칙

Syntax

SET SHOWPLAN_XML { ON | OFF }

참고 항목

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

설명

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

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

SET SHOWPLAN_XML은 sqlcmd 유틸리티 같은 애플리케이션에 대한 출력을 nvarchar(max) 형식으로 반환합니다. 여기서 XML 출력은 이후에 다른 도구가 쿼리 계획 정보를 표시하고 처리하는 데 사용합니다.

참고 항목

동적 관리 뷰sys.dm_exec_query_plan는 XML 데이터 형식의 SET SHOWPLAN XML과 동일한 정보를 반환합니다. 이 정보는 의 열sys.dm_exec_query_plan에서 query_plan 반환됩니다. 자세한 내용은 sys.dm_exec_query_plan (Transact-SQL)을 참조하세요.

SET SHOWPLAN_XML은 저장 프로시저 내부에서 지정할 수 없으며 일괄 처리에서 유일한 문이어야 합니다.

SET SHOWPLAN_XML은 XML 문서 집합으로 정보를 반환합니다. SET SHOWPLAN_XML ON 문 뒤에 오는 각 일괄 처리는 단일 문서로 출력에 반영됩니다. 각 문서에는 일괄 처리에 있는 문의 텍스트가 먼저 오고 그 뒤에 실행 단계에 대한 세부 정보가 옵니다. 문서는 예상 비용, 행 수, 액세스한 인덱스, 수행한 연산자 유형, 조인 순서 및 실행 계획에 대한 자세한 정보를 보여 줍니다.

참고

SQL Server Management Studio에서 실제 실행 계획 포함을 선택하면 이 SET 옵션에서 XML 실행 계획 출력을 생성하지 않습니다. 이 SET 옵션을 사용하기 전에 실제 실행 계획 포함 단추의 선택을 취소하세요.

SSMS 및 SET SHOWPLAN_XML 통한 예상 실행 계획은 Azure Synapse Analytics의 전용 SQL 풀(이전의 SQL DW) 및 전용 SQL 풀에 사용할 수 있습니다. Azure Synapse Analytics에서 전용 SQL 풀(이전의 SQL DW) 및 전용 SQL 풀에 대한 실제 실행 계획을 검색하려면 다른 명령이 있습니다. 자세한 내용은 DMV를 사용하여 Azure Synapse Analytics 전용 SQL 풀 워크로드 모니터링을 참조하세요.

실행 계획 출력 위치

SET SHOWPLAN_XML에 의한 XML 출력의 XML 스키마가 포함된 문서는 설치하는 동안 Microsoft SQL Server가 설치되어 있는 컴퓨터의 로컬 디렉터리에 복사됩니다. 이 문서는 SQL Server 설치 파일이 포함된 드라이브에서 다음과 유사한 경로를 찾을 수 있습니다.

  • \Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

앞의 경로에서 노드 130\은(는) SQL Server 2016에서 사용됩니다. 숫자 130은 SELECT @@VERSION에 의해 반환되는 값의 첫 번째 노드인 13에서 파생된 것입니다. SQL Server 2017의 경우 값의 @@VERSION 첫 번째 노드가 14이므로 경로가 사용합니다140\. SQL Server 2019의 @@VERSION 첫 번째 값은 15입니다. SQL Server 2022의 @@VERSION 첫 번째 값은 16입니다.

실행 계획 스키마는 Microsoft SQL Server XML 스키마에서도 찾을 수 있습니다.

사용 권한

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

, INSERT, UPDATE, DELETEEXEC *stored_procedure*및 EXEC *user_defined_function* 문의 경우 SELECT사용자가 Showplan을 생성하려면 다음을 수행해야 합니다.

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

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

DDL, USE *database_name*, , SETDECLARE동적 SQL 등과 같은 다른 모든 문의 경우 Transact-SQL 문을 실행할 적절한 권한만 필요합니다.

예제

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

첫 번째 쿼리는 인덱싱된 열의 WHERE 절에서 Equals 비교 연산자(=)를 사용합니다. 두 번째 쿼리에서는 WHERE 절에 LIKE 연산자를 사용합니다. 이렇게 하면 SQL Server가 클러스터형 인덱스 검색을 사용하고 WHERE 절 조건을 충족하는 데이터를 찾도록 합니다. 첫 번째 인덱싱된 쿼리에 대한 값과 EstimatedTotalSubtreeCost 특성의 값 EstimateRows 은 더 작습니다. 이는 인덱싱되지 않은 쿼리보다 훨씬 빠르게 처리되고 더 적은 리소스를 사용함을 나타냅니다.

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

다음 단계