SQL Server에서 쿼리 실행 계획을 얻으려면 어떻게 해야 합니까?
Microsoft SQL Server에서 쿼리/저장 프로시저에 대한 쿼리 실행 계획을 가져오려면 어떻게 해야 합니까?
실행 계획을 얻는 방법에는 여러 가지가 있으며, 사용자의 상황에 따라 어떤 방법을 사용할지가 달라집니다.일반적으로 SQL Server Management Studio를 사용하여 계획을 가져올 수 있지만 SQL Server Management Studio에서 쿼리를 실행할 수 없는 경우 SQL Server Profiler를 통해 계획을 얻거나 계획 캐시를 검사하는 것이 유용할 수 있습니다.
방법 1 - SQL Server Management Studio 사용
SQL Server에는 실행 계획을 매우 쉽게 캡처할 수 있는 몇 가지 깔끔한 기능이 제공됩니다. "쿼리" 메뉴 아래에 있는 "실제 실행 계획 포함" 메뉴 항목을 선택하고 쿼리를 정상적으로 실행하기만 하면 됩니다.
저장 프로시저에서 문에 대한 실행 계획을 가져오려면 다음과 같이 저장 프로시저를 실행해야 합니다.
exec p_Example 42
쿼리가 완료되면 결과 창에 "실행 계획"이라는 추가 탭이 나타납니다.많은 문을 실행한 경우 이 탭에 많은 계획이 표시될 수 있습니다.
여기서 SQL Server Management Studio에서 실행 계획을 검사하거나 계획을 마우스 오른쪽 단추로 클릭하고 "실행 계획을 다른 이름으로 저장..."을 선택하여 계획을 XML 형식의 파일로 저장할 수 있습니다.
방법 2 - SHOWPLAN 옵션 사용
이 방법은 방법 1과 매우 유사하지만(실제로는 SQL Server Management Studio가 내부적으로 수행하는 작업입니다), 완전성을 위해 또는 사용 가능한 SQL Server Management Studio가 없는 경우 포함했습니다.
쿼리를 실행하기 전에 다음 문 중 하나를 실행합니다.문은 배치의 유일한 문이어야 합니다. 즉, 다른 문을 동시에 실행할 수 없습니다.
SET SHOWPLAN_TEXT ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_XML ON
SET STATISTICS PROFILE ON
SET STATISTICS XML ON -- The is the recommended option to use
이 옵션은 연결 옵션이므로 연결당 한 번만 실행하면 됩니다.이때부터 실행되는 모든 문에는 원하는 형식의 실행 계획이 포함된 추가 결과 집합이 함께 제공됩니다. 일반적으로 계획을 볼 때처럼 쿼리를 실행하기만 하면 됩니다.
완료되면 다음 명령을 사용하여 이 옵션을 해제할 수 있습니다.
SET <<option>> OFF
실행 계획 형식 비교
이 강한 선호도를 있지 , 는 당이강선가지있지않다면고, ▁the다▁unless것니입▁recommendation▁is▁useference▁my▁to,를 사용하는 것을 추천합니다.STATISTICS XML이 계획 " 형식으로 합니다.이 옵션은 SQL Server Management Studio의 "실제 실행 계획 포함" 옵션과 동일하며 가장 편리한 형식으로 대부분의 정보를 제공합니다.
SHOWPLAN_TEXT계획을 합니다.SHOWPLAN_ALL견적과 합니다.SHOWPLAN_XML쿼리를 실행하지 않고 비용 견적을 포함한 XML 기반 예상 실행 계획을 표시합니다.이는 "예상 실행 계획 표시...의 " 에▁option▁in" 옵션입니다.SQL Server Management Studio는 SQL Server Management Studio입니다.STATISTICS PROFILE쿼리를 실행하고 텍스트 기반 실제 실행 계획을 표시합니다.STATISTICS XML쿼리를 실행하고 XML 기반 실제 실행 계획을 표시합니다.이는 SQL Server Management Studio의 "실제 실행 계획 포함" 옵션에 해당합니다.
방법 3 - SQL Server 프로파일러 사용
쿼리를 직접 실행할 수 없거나 쿼리를 직접 실행할 때 쿼리가 느리게 실행되지 않는 경우 SQL Server Profiler 추적을 사용하여 계획을 캡처할 수 있습니다.이 방법은 "Showplan" 이벤트 중 하나를 캡처하는 추적이 실행되는 동안 쿼리를 실행하는 것입니다.
로드에 따라 프로덕션 환경에서 이 방법을 사용할 수 있지만 주의해야 합니다.SQL 서버 프로파일링 메커니즘은 데이터베이스에 미치는 영향을 최소화하도록 설계되었지만 성능에 영향을 미치지는 않습니다.또한 데이터베이스를 많이 사용하는 경우 추적에서 올바른 계획을 필터링하고 식별하는 데 문제가 있을 수 있습니다.DBA에게 문의하여 소중한 데이터베이스에서 이러한 작업을 수행하는 것에 만족하는지 확인해야 합니다.
- SQL Server Profiler를 열고 추적을 기록할 데이터베이스에 연결하는 새 추적을 만듭니다.
- 이벤트 선택 탭에서 "모든 이벤트 표시"를 선택하고 "성능" -> "계획 XML 표시" 행을 선택한 후 추적을 실행합니다.
- 추적이 실행되는 동안 느린 실행 쿼리가 실행되도록 하려면 필요한 작업을 수행합니다.
- 쿼리가 완료될 때까지 기다렸다가 추적을 중지합니다.
- 추적을 저장하려면 SQL Server Profiler에서 계획 xml을 마우스 오른쪽 단추로 클릭하고 "이벤트 데이터 추출...계획을 XML 형식으로 파일에 저장합니다.
이 계획은 SQL Server Management Studio의 "실제 실행 계획 포함" 옵션과 동일합니다.
방법 4 - 쿼리 캐시 검사
쿼리를 직접 실행할 수 없고 프로파일러 추적도 캡처할 수 없는 경우에도 SQL 쿼리 계획 캐시를 검사하여 예상 계획을 얻을 수 있습니다.
SQL Server DMV를 쿼리하여 계획 캐시를 검사합니다.다음은 SQL 텍스트와 함께 모든 캐시된 쿼리 계획(xml로 표시)을 나열하는 기본 쿼리입니다.대부분의 데이터베이스에서는 원하는 계획에만 결과를 필터링하기 위해 필터링 절을 추가해야 합니다.
SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
이 쿼리를 실행하고 계획 XML을 클릭하여 새 창에서 계획을 엽니다. 마우스 오른쪽 단추를 클릭하고 "실행 계획을 다른 이름으로 저장...계획을 XML 형식으로 파일에 저장합니다.
주의:
관련 요소가 너무 많기 때문에(테이블 및 인덱스 스키마에서 저장된 데이터 및 테이블 통계에 이르기까지) 항상 관심 있는 데이터베이스(일반적으로 성능 문제가 발생하는 데이터베이스)에서 실행 계획을 얻으려고 시도해야 합니다.
암호화된 저장 프로시저에 대한 실행 계획을 캡처할 수 없습니다.
"전략적" 실행 계획과 "전략적" 실행 계획
실제 실행 계획은 SQL Server가 쿼리를 실제로 실행하는 계획인 반면, 예상 실행 계획인 SQL Server는 쿼리를 실행하지 않고 수행할 작업을 해결합니다.실제 실행 계획은 논리적으로 동일하지만 쿼리 실행 시 실제로 발생한 상황에 대한 추가 세부 정보와 통계가 포함되어 있으므로 훨씬 유용합니다.이는 SQL 서버 추정치가 잘못된 문제를 진단할 때(예: 통계가 최신이 아닌 경우) 중요합니다.
쿼리 실행 계획을 어떻게 해석합니까?
이것은 그 자체로 (무료) 책에 걸맞는 주제입니다.
참고 항목:
- 실행 계획 기본 사항
- SHOWPLAN 권한 및 Transact-SQL 배치
- SQL Server 2008 – Query Hash 및 Query Plan Hash 사용
- SQL Server 계획 캐시 분석
이미 게시된 포괄적인 답변 외에도 실행 계획에 프로그래밍 방식으로 액세스하여 정보를 추출할 수 있는 것이 유용할 수 있습니다.이에 대한 예제 코드는 아래와 같습니다.
DECLARE @TraceID INT
EXEC StartCapture @@SPID, @TraceID OUTPUT
EXEC sp_help 'sys.objects' /*<-- Call your stored proc of interest here.*/
EXEC StopCapture @TraceID
»StartCapture는.
CREATE PROCEDURE StartCapture
@Spid INT,
@TraceID INT OUTPUT
AS
DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)
EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL
exec sp_trace_setevent @TraceID, 122, 1, 1
exec sp_trace_setevent @TraceID, 122, 22, 1
exec sp_trace_setevent @TraceID, 122, 34, 1
exec sp_trace_setevent @TraceID, 122, 51, 1
exec sp_trace_setevent @TraceID, 122, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1
»StopCapture는.
CREATE PROCEDURE StopCapture
@TraceID INT
AS
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql),
CTE
as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData,
ObjectID,
ObjectName,
EventSequence,
/*costs accumulate up the tree so the MAX should be the root*/
MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM fn_trace_getinfo(@TraceID) fn
CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost',
'float') AS EstimatedTotalSubtreeCost
FROM xPlan.nodes('//sql:RelOp') T(relop)) ca
WHERE property = 2
AND TextData IS NOT NULL
AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' )
GROUP BY CAST(TextData AS VARCHAR(MAX)),
ObjectID,
ObjectName,
EventSequence)
SELECT ObjectName,
SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM CTE
GROUP BY ObjectID,
ObjectName
-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2
GO
Microsoft SQL Server Management Studio를 사용한다고 가정합니다.
- Estimated Query Plan(추정 쿼리 계획)에서 Ctrl + L 또는 다음 버튼을 누를 수 있습니다.
- 실제 쿼리 계획의 경우 쿼리를 실행하기 전에 Ctrl + M 또는 다음 버튼을 누를 수 있습니다.
- Live Query Plan(SSMS 2016에만 해당)의 경우 쿼리를 실행하기 전에 다음 버튼을 사용합니다.
이전 답변에 설명된 방법 외에도 무료 실행 계획 뷰어 및 쿼리 최적화 도구인 ApexSQL Plan(최근에 우연히 만난 적 있음)을 사용할 수 있습니다.
실행 계획을 SSMS에서 직접 볼 수 있도록 ApexSQL Plan을 설치하고 SQL Server Management Studio에 통합할 수 있습니다.
ApexSQL Plan에서 예상 실행 계획 보기
- SSMS에서 New Query(새 쿼리) 버튼을 클릭하고 쿼리 텍스트 창에 쿼리 텍스트를 붙여넣습니다.마우스 오른쪽 단추를 클릭하고 상황에 맞는 메뉴에서 "예상 실행 계획 표시" 옵션을 선택합니다.
- 실행 계획 다이어그램이 결과 섹션의 실행 계획 탭에 표시됩니다.다음으로 실행 계획을 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 "Open in ApexSQL Plan" 옵션을 선택합니다.
- Estimated 실행 계획은 ApexSQL Plan에서 열리며 쿼리 최적화를 위해 분석할 수 있습니다.
ApexSQL Plan에서 실제 실행 계획 보기
쿼리의 실제 실행 계획을 보려면 앞에서 언급한 두 번째 단계부터 계속 진행하지만, 이제 추정 계획이 표시되면 ApexSQL Plan의 메인 리본 표시줄에서 "실제" 버튼을 클릭합니다.
"실제" 버튼을 클릭하면 실제 실행 계획이 다른 실행 계획 데이터와 함께 비용 매개 변수의 상세 미리 보기와 함께 표시됩니다.
실행 계획 보기에 대한 자세한 내용은 이 링크를 클릭하여 확인할 수 있습니다.
쿼리 실행 계획을 가져오고 심층적으로 분석하는 데 가장 좋아하는 도구는 SQL Sentry 계획 Explorer입니다.SSMS보다 실행 계획의 세부 분석 및 시각화를 위해 훨씬 사용자 친화적이고 편리하며 포괄적입니다.
다음은 툴이 제공하는 기능을 파악할 수 있는 샘플 스크린샷입니다.
도구에서 사용할 수 있는 보기 중 하나입니다.앱 창 하단의 탭 집합을 확인하면 다양한 유형의 실행 계획 표현과 유용한 추가 정보를 얻을 수 있습니다.
게다가 무료 에디션이 일상적으로 사용하는 것을 방해하거나 결국 Pro 버전을 구매하도록 강요하는 어떠한 제한도 눈치채지 못했습니다.따라서 무료 버전을 고수하는 것이 좋다면, 그렇게 하는 것을 막을 수 있는 것은 아무것도 없습니다.
은 확장 수 .query_post_execution_showplan이벤트. 다음은 XEvent 세션의 예입니다.
/*
Generated via "Query Detail Tracking" template.
*/
CREATE EVENT SESSION [GetExecutionPlan] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)),
/* Remove any of the following events (or include additional events) as desired. */
ADD EVENT sqlserver.error_reported(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.module_end(SET collect_statement=(1)
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.rpc_completed(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_statement_completed(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0))))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
세션을 생성한 후 (SSMS에서) Object Explorer로 이동하여 Management | Extended Events | Sessions로 이동합니다.실행 계획 가져오기 세션을 마우스 오른쪽 단추로 클릭하고 시작합니다.마우스 오른쪽 단추를 다시 클릭하고 "라이브 데이터 보기"를 선택합니다.
그런 다음 새 쿼리 창을 열고 하나 이상의 쿼리를 실행합니다.다음은 AdventureWorks를 위한 것입니다.
USE AdventureWorks;
GO
SELECT p.Name AS ProductName,
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC;
GO
잠시 후 "실행 계획 가져오기: 실시간 데이터" 탭에 몇 가지 결과가 표시됩니다.그리드에서 query_post_execution_showplan 이벤트 중 하나를 클릭한 다음 그리드 아래의 "Query Plan" 탭을 클릭합니다.이는 다음과 유사해야 합니다.
편집: XEvent 코드와 스크린샷은 SP2가 설치된 SQL/SSMS 2012에서 생성되었습니다.SQL 2008/R2를 사용하는 경우 스크립트를 조정하여 실행할 수 있습니다.하지만 그 버전에는 GUI가 없기 때문에 showplan XML을 추출하여 *.sqlplan 파일로 저장하고 SSMS에서 열어야 합니다. 번거롭죠.XEvents는 SQL 2005 이전에는 존재하지 않았습니다.SQL 2012 이상 버전이 아니라면 여기에 게시된 다른 답변 중 하나를 적극 추천합니다.
SQL Server 2016+부터는 성능 모니터링을 위한 Query Store 기능이 도입되었습니다.쿼리 계획 선택 및 성능에 대한 통찰력을 제공합니다.추적 또는 확장 이벤트를 완전히 대체하는 것은 아니지만 버전 간에 발전함에 따라 SQL 서버의 향후 릴리스에서 완전하게 작동하는 쿼리 저장소를 얻을 수도 있습니다.쿼리 저장소의 기본 흐름
- SQL Server 기존 구성 요소는 Query Store Manager를 사용하여 쿼리 저장소와 상호 작용합니다.
- 쿼리 저장소 관리자는 사용할 저장소를 결정한 다음 해당 저장소에 실행을 전달합니다(계획 또는 런타임 상태 또는 쿼리 대기 상태).
- 계획 저장소 - 실행 계획 정보 유지
- Runtime Stats Store - 실행 통계 정보 유지
- 대기 통계 저장소 쿼리 - 대기 통계 정보를 유지하는 중입니다.
- 계획, 런타임 통계 및 대기 저장소는 SQL Server에 대한 확장으로 쿼리 저장소를 사용합니다.
쿼리 저장소 사용: 쿼리 저장소는 서버의 데이터베이스 수준에서 작동합니다.
- 쿼리 저장소는 기본적으로 새 데이터베이스에 대해 활성화되지 않습니다.
- 또는 "" "" "" " " " "에 할 수 .
tempdb데이터베이스 - 사용 가능한 DMV
sys.database_query_store_options(트랜잭션-SQL)
쿼리 저장소에서 정보 수집:우리는 쿼리 저장소 DMV(데이터 관리 보기)를 사용하여 세 개의 저장소에서 사용 가능한 모든 정보를 수집합니다.
Query Plan Store: 실행 계획 정보를 유지하며 쿼리 컴파일과 관련된 모든 정보를 캡처합니다.
sys.query_store_query(Transact-SQL)(Transact-SQL)(Transact-SQL)Runtime Stats Store: 실행 통계 정보를 유지하며 가장 자주 업데이트되는 저장소입니다.이러한 통계는 쿼리 실행 데이터를 나타냅니다.
sys.query_store_runtime_stats(트랜잭션-SQL)대기 통계 저장소 쿼리: 대기 통계 정보를 지속 및 캡처하는 중입니다.
sys.query_store_wait_stats(트랜잭션-SQL)
참고: 대기 상태 저장소 쿼리는 SQL Server 2017+에서만 사용할 수 있습니다.
예상실행계획
예상 실행 계획은 SQL 쿼리를 실행하지 않고 Optimizer에 의해 생성됩니다.
계획을 상실행계가면다활합야니다해화성을 .SHOWPLAN_ALL쿼리를 실행하기 전에 설정합니다.
SET SHOWPLAN_ALL ON
이제 다음 SQL 쿼리를 실행할 때:
SELECT p.id
FROM post p
WHERE EXISTS (
SELECT 1
FROM post_comment pc
WHERE
pc.post_id = p.id AND
pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY
SQL Server는 다음과 같은 예상 실행 계획을 생성합니다.
| NodeId | Parent | LogicalOp | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | EstimateExecutions |
|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|--------------------|
| 1 | 0 | NULL | 10 | NULL | NULL | NULL | 0.03374284 | NULL |
| 2 | 1 | Top | 10 | 0 | 3.00E-06 | 15 | 0.03374284 | 1 |
| 4 | 2 | Distinct Sort | 30 | 0.01126126 | 0.000504114 | 146 | 0.03373984 | 1 |
| 5 | 4 | Inner Join | 46.698 | 0 | 0.00017974 | 146 | 0.02197446 | 1 |
| 6 | 5 | Clustered Index Scan | 43 | 0.004606482 | 0.0007543 | 31 | 0.005360782 | 1 |
| 7 | 5 | Clustered Index Seek | 1 | 0.003125 | 0.0001581 | 146 | 0.0161733 | 43 |
예상 실행 계획을 가져오는 데 관심이 있는 쿼리를 실행한 후에는SHOWPLAN_ALL그렇지 않으면 현재 데이터베이스 세션은 제공된 SQL 쿼리를 실행하는 대신 예상 실행 계획만 생성합니다.
SET SHOWPLAN_ALL OFF
SQL Server Management Studio 예상 계획
Studio에서 SQL Server Management Studio SQL을 수 .CTRL+L단축키
실제실행계획
실제 SQL 실행 계획은 SQL 쿼리를 실행할 때 Optimizer에 의해 생성됩니다.데이터베이스 테이블 통계량이 정확한 경우 실제 계획은 추정된 계획과 크게 다르지 않아야 합니다.
Server에서 Server를 .STATISTICS IO, TIME, PROFILE설정(다음 SQL 명령에 표시됨):
SET STATISTICS IO, TIME, PROFILE ON
이전 쿼리를 실행할 때 SQL Server는 다음 실행 계획을 생성합니다.
| Rows | Executes | NodeId | Parent | LogicalOp | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost |
|------|----------|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|
| 10 | 1 | 1 | 0 | NULL | 10 | NULL | NULL | NULL | 0.03338978 |
| 10 | 1 | 2 | 1 | Top | 1.00E+01 | 0 | 3.00E-06 | 15 | 0.03338978 |
| 30 | 1 | 4 | 2 | Distinct Sort | 30 | 0.01126126 | 0.000478783 | 146 | 0.03338679 |
| 41 | 1 | 5 | 4 | Inner Join | 44.362 | 0 | 0.00017138 | 146 | 0.02164674 |
| 41 | 1 | 6 | 5 | Clustered Index Scan | 41 | 0.004606482 | 0.0007521 | 31 | 0.005358581 |
| 41 | 41 | 7 | 5 | Clustered Index Seek | 1 | 0.003125 | 0.0001581 | 146 | 0.0158571 |
SQL Server parse and compile time:
CPU time = 8 ms, elapsed time = 8 ms.
(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post'. Scan count 0, logical reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post_comment'. 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.
(6 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
실제 실행 계획을 가져오는 데 관심이 있는 쿼리를 실행한 후에는STATISTICS IO, TIME, PROFILE ON다음과 같은 설정:
SET STATISTICS IO, TIME, PROFILE OFF
SQL Server Management Studio 실제 계획
Studio에서 SQL Server Management Studio SQL을 수 .CTRL+M단축키
SQL Server Management Studio(이미 설명됨)와 마찬가지로 여기에 설명된 Datagrip으로도 가능합니다.
- SQL 문을 마우스 오른쪽 버튼으로 클릭하고 계획 설명을 선택합니다.
- [출력] 창에서 [계획]을 클릭합니다.
- 기본적으로 쿼리의 트리 표현이 표시됩니다.쿼리 계획을 보려면 시각화 표시 아이콘을 클릭하거나 Ctrl+Shift+Alt+를 누릅니다.u
여기 앞에서 말한 모든 것 외에 알아야 할 중요한 것이 하나 있습니다.
쿼리 계획은 너무 복잡하여 127 수준의 중첩 요소 제한이 있는 기본 제공 XML 열 유형으로 표시할 수 없는 경우가 많습니다.이것이 sys.dm _exec_message_plan이 반환되는 이유 중 하나입니다.NULL또는 이전 MS SQL 버전에서 오류를 발생시킬 수도 있으므로 일반적으로 sys.dm _exec_text_messages_plan을 대신 사용하는 것이 더 안전합니다.후자에는 전체 배치가 아닌 특정 문에 대한 계획을 선택하는 유용한 보너스 기능도 있습니다.이 도구를 사용하여 현재 실행 중인 문에 대한 계획을 보는 방법은 다음과 같습니다.
SELECT p.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_text_query_plan(
r.plan_handle,
r.statement_start_offset,
r.statement_end_offset) AS p
그러나 결과 테이블의 텍스트 열은 XML 열에 비해 유용하지 않습니다.때 없이 수.CAST(... AS XML)단의 행(:), 단,
SELECT Tag = 1, Parent = NULL, [ShowPlanXML!1!!XMLTEXT] = query_plan
FROM sys.dm_exec_text_query_plan(
-- set these variables or copy values
-- from the results of the above query
@plan_handle,
@statement_start_offset,
@statement_end_offset)
FOR XML EXPLICIT
SET STATistics XML ON을 사용하여 파워셸을 통해 실제 계획을 가져올 수도 있습니다.다중 진술 계획을 하나의 계획으로 통합하도록 작성했습니다.
########## BEGIN : SCRIPT VARIABLES #####################
[string]$server = '.\MySQLServer'
[string]$database = 'MyDatabase'
[string]$sqlCommand = 'EXEC sp_ExampleSproc'
[string]$XMLOutputFileName = 'sp_ExampleSproc'
[string]$XMLOutputPath = 'C:\SQLDumps\ActualPlans\'
########## END : SCRIPT VARIABLES #####################
#Set up connection
$connectionString = "Persist Security Info=False;Integrated Security=true;Connection Timeout=0;Initial Catalog=$database;Server=$server"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
#Set up commands
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$command.CommandTimeout = 0
$commandXMLActPlanOn = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML ON",$connection)
$commandXMLActPlanOff = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML OFF",$connection)
$connection.Open()
#Enable session XML plan
$result = $commandXMLActPlanOn.ExecuteNonQuery()
#Execute SP and return resultsets into a dataset
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
#Set up output file name and path
[string]$fileNameDateStamp = get-date -f yyyyMMdd_HHmmss
[string]$XMLOutputFilePath = "$XMLOutputPath$XMLOutputFileName`_$fileNameDateStamp.sqlplan"
#Pull XML plans out of dataset and merge into one multi-statement plan
[int]$cntr = 1
ForEach($table in $dataset.Tables)
{
if($table.Columns[0].ColumnName -eq "Microsoft SQL Server 2005 XML Showplan")
{
[string]$fullXMLPlan = $Table.rows[0]."Microsoft SQL Server 2005 XML Showplan"
if($cntr -eq 1)
{
[regex]$rx = "\<ShowPlanXML xmlns\=.{1,}\<Statements\>"
[string]$startXMLPlan = $rx.Match($fullXMLPlan).Value
[regex]$rx = "\<\/Statements\>.{1,}\<\/ShowPlanXML\>"
[string]$endXMLPlan = $rx.Match($fullXMLPlan).Value
$startXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
}
[regex]$rx = "\<StmtSimple.{1,}\<\/StmtSimple\>"
[string]$bodyXMLPlan = $rx.Match($fullXMLPlan).Value
$bodyXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
$cntr += 1
}
}
$endXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
#Disable session XML plan
$result = $commandXMLActPlanOff.ExecuteNonQuery()
$connection.Close()
실행 계획을 설명하는 것은 매우 상세하고 읽기 시간이 많이 소요될 수 있지만, 요약하자면, 쿼리 전에 '설명'을 사용하면 어떤 부분이 먼저 실행되었는지 등 많은 정보를 제공해야 합니다. 이에 대한 자세한 내용을 읽고 싶다면 올바른 참조를 가리키는 작은 블로그를 작성했습니다.https://medium.com/swlh/jetbrains-datagrip-explain-plan-ac406772c470
SQL Server Management Studio에서 다음을 수행합니다.
"Ctrl + M"은 실제 실행 계획을 생성합니다.
"Ctrl + L"은 예상 실행 계획을 생성합니다.
클라이언트 통계의 "Shift + Alt + S"
SQL Server Profiler에서 쿼리를 추적하기 위한 "Ctrl + Alt + P"입니다.
언급URL : https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan-in-sql-server
'source' 카테고리의 다른 글
| Eclipse에서 변수 이름을 빠르게 대문자로 바꿀 수 있는 방법이 있습니까? (0) | 2023.04.29 |
|---|---|
| 원하는 diff 도구/뷰어를 사용하여 'gitdiff' 출력을 보려면 어떻게 해야 합니까? (0) | 2023.04.29 |
| Mongoose에서 외부 키 관계 만들기 (0) | 2023.04.29 |
| 프로그래밍 방식으로 마스크 자동 크기 조정 및 인터페이스 작성기 / xib / nib (0) | 2023.04.29 |
| 이클립스를 인터페이스 방식의 유일한 구현으로 전환하는 방법은 무엇입니까? (0) | 2023.04.29 |