안녕하세요 여러분! 최근 문서 처리에 시간이 오래 걸리는 문제를 발견했습니다.

입력 데이터: 구성 "제조 기업 관리, 버전 1.3(1.3.52.1)", 문서 "지불 주문" 문서. 불만 사항: 작업 데이터베이스에 보관 기간은 20~30초인데, 이는 흥미로운 사실입니다. 데이터베이스 복사본에서는 동일한 문서가 2~4초 동안 보관됩니다. 아래에서 조사 내용과 이러한 행동의 이유를 읽어보세요.

그래서 도움을 받아 성능 측정나는 모두가 그것을 사용하는 방법을 알고 있다고 생각합니다. 범인이 발견되었습니다.

이 경우 레코더에는 빈 레코드 세트가 기록되었습니다. 즉, 실행 전에 동작이 삭제되었습니다. 이 프로시저가 26번 호출되었다는 점은 주목할 가치가 있습니다. 우리 문서가 쓸 수 있는 각 레지스터에 대해.

성능 측정에 따르면 이 작업에는 13초가 걸렸으며, 평균을 계산하면 레지스터당 0.5초, 즉 영원합니다!

우리 모두 알고 있듯이 녹음을 최적화할 수는 없지만 여기에는 분명히 잘못된 것이 있습니다.
추가 분석을 위해 다음을 엽니다. SQL 서버프로파일러그리고 . 분석을 위해 이벤트 클래스를 사용했습니다.

• 실행 계획 통계 프로필
• 실행 계획 XML 통계 프로필
• RPC 완료됨
• SQL:배치 완료됨.

추적 설정에는 SPID로 필터링:

SPID는 데이터베이스 서버의 프로세스 ID입니다. 1C의 경우 본질적으로 1C 서버와 DBMS 간의 연결이며, 1C 서버 관리 콘솔의 "DBMS에 연결"열에서 볼 수 있습니다.

다음과 같은 경우에 표시됩니다. 이 순간데이터베이스에 대한 연결은 세션에 의해 캡처됩니다. 즉, DBMS 호출이 이루어지고 있거나 트랜잭션이 열려 있거나 적어도 하나의 임시 테이블이 생성된 "임시 테이블 관리자" 개체가 보유되어 있습니다.

SPID를 보유하기 위한 처리를 작성해 보겠습니다. 여기에는 하나의 프로시저가 포함됩니다.

보유 중인 연결 개체(이 경우 임시 테이블 관리자)를 처리 변수로 정의하는 것이 중요합니다. 처리를 열고 프로시저를 실행하면 해당 프로시저가 열려 있으면 SPID가 고정됩니다. 1C 서버 관리 콘솔을 엽니다.

따라서 SPID가 수신되었으므로 해당 값을 필터에 입력하고 세션에 대해 현재 작업 중인 데이터베이스에서 추적을 가져옵니다. 추적을 분석할 때 완료하는 데 11초가 소요되는 작업이 발견되었습니다.

그리고 또 눈에 띄는 건 독서량이었어요- 1872578 , 그러나 나는 이것에 즉시 중요성을 부여하지 않았고 여기서 무슨 일이 일어나고 있는지, 어떤 테이블로 수행되고 있는지 파악하기 시작했습니다.

exec sp_executesql <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))’,N’@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)’,0x8A2F00155DBF491211E87F56DD1A416E,’4018-05-31 23:59:59′,0x00000000000000000000000000000000,’4018-05-31 23:59:59′,0x9A95A0369F30F8DB11E46684B4F0A05F,’4018-05-31 23:59:59"

SQL 쿼리에서 볼 수 있듯이 테이블이 처리됩니다. "AccRg1465"이것은 자립 회계 등록부의 테이블입니다. 쿼리 실행 계획의 텍스트 표현:

SQL 쿼리의 실행 계획을 보면 알 수 있듯이 나쁜 일이 일어나지 않고, 테이블 “ AccRg1465", 클러스터형 인덱스 검색은 모든 곳에서 사용됩니다.

또한 그래픽 계획에서 잘못된 점을 발견하지 못했습니다. 비록 그것이 나에게 너무 부풀어 오른 것처럼 보였지만, 합병이 있었고 뚜렷한 이유 없이 두 개의 중첩 루프가 있었습니다. 이 판독 횟수와 엄청난 실행 시간은 어디에서 오는 걸까요?

위에서 언급한 것처럼 문제는 데이터베이스의 새로운 복사본에서 재현되지 않았습니다. 문제가 발생한 후 작업 데이터베이스에서 복사본을 가져왔으므로 동일한 문서의 SQL Server Profiler에서 해당 동작을 분석하기로 결정했습니다.
결과는 다음과 같습니다.

SQL의 쿼리 텍스트:

EXEC sp_executesql N"dbo._AccRg1465 T1 WHERE (T1._RecorderTRef = 0x0000022D AND T1._RecorderRRef = @P1) AND ((((T1._Period)에서 TOP 1 0x01 선택<= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))" , N"@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)", 0x8A2F00155DBF491211E87F56DD1A416E, "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , 0x9A95A0369F30F8DB11E46684B4F0A05F, "401 8-05-31 23:59:59"

쿼리 계획의 그래픽 표현:

요청 텍스트는 동일하지만 실행 계획은 근본적으로 다릅니다. 무슨 문제가 있는 걸까요? SQL의 통계에 대해 틀렸지만 데이터베이스 작업과 복사본 간에는 동일하며 통계는 각 테이블의 데이터베이스에 저장됩니다.

더 자세히 분석해 보겠습니다. 통계는 동일하지만 쿼리 계획이 다른 경우 이는 최적화 프로그램이 쿼리 계획을 작성하기 위해 통계에 액세스하지 않지만 이를 사용하는 캐시된 계획이 있음을 의미합니다. 데이터베이스에서 절차적 캐시를 지웁니다. 이를 위해 다음 명령을 사용합니다.

DBCC FLUSHPROCINDB(< database_id >)

어디< database_id >데이터베이스 식별자입니다. 데이터베이스 ID를 찾으려면 스크립트를 실행해야 합니다.

sys 에서 이름, Database_id를 선택하세요. 데이터베이스

데이터베이스와 해당 식별자 목록이 반환됩니다.

우리는 다시 추적을 얻습니다:

쿼리 계획의 텍스트 표현:

쿼리 계획의 그래픽 표현:

보시다시피, 최적화 프로그램이 쿼리 계획을 다시 가져왔고, 이전에 캐시된 계획은 사용되지 않았으며, 실행 시간도 읽기 횟수와 마찬가지로 정상으로 돌아왔습니다. 그 원인이 무엇인지 명확하지 않습니다. 아마도 많은 수의 교환이나 이전 기간의 폐쇄 등이 있을 수 있습니다. 말하기는 어렵습니다. 정기적인 데이터베이스 유지 관리가 구성되었습니다. 캐시된 쿼리 실행 계획 사기를 본 것은 이번이 처음입니다.

관심을 가져주셔서 감사합니다!

이 기사가 도움이 되었나요?

답변 6개

상황에 따라 실행 계획을 얻는 방법에는 여러 가지가 있습니다. 일반적으로 SQL Server Management Studio를 사용하여 계획을 얻을 수 있지만 어떤 이유로 SQL Server Management Studio에서 쿼리를 실행할 수 없는 경우 SQL Server Profiler를 통해 또는 계획을 확인하여 계획을 얻는 것이 유용할 수 있습니다. 은닉처.

방법 1 - SQL Server Management Studio 사용

SQL Server에는 실행 계획을 쉽게 수집할 수 있는 몇 가지 멋진 기능이 있습니다. "실제 실행 계획 포함" 메뉴 항목(쿼리 메뉴에 있음)이 선택되어 있고 정상적으로 실행되는지 확인하세요.

저장 프로시저의 명령문에 대한 실행 계획을 얻으려는 경우 다음과 같이 저장 프로시저를 실행합니다.

Exec p_예제 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 -- 권장되는 옵션입니다.

이는 연결 매개변수이므로 각 연결에 대해 한 번만 실행하면 됩니다. 이제부터 출시된 모든 성명에는 다음이 수반됩니다. 추가 결과 세트필요한 형식으로 실행 계획을 포함합니다. 계획을 보려면 평소처럼 쿼리를 실행하면 됩니다.

완료되면 다음 명령문을 사용하여 이 옵션을 비활성화할 수 있습니다.

세트<> 꺼짐

실행 계획 형식 비교

강력한 선호 사항이 있는 경우 STATISTICS XML 옵션을 사용하는 것이 좋습니다. 이 옵션은 SQL Server Management Studio의 "실제 실행 계획 포함" 옵션과 동일하며 가장 유용한 형식으로 대부분의 정보를 제공합니다.

• SHOWPLAN_TEXT - 쿼리를 실행하지 않고 기본 텍스트 기반 예상 실행 계획을 표시합니다.
• SHOWPLAN_ALL - 쿼리를 실행하지 않고 예상 비용과 함께 예상 텍스트 기반 실행 계획을 표시합니다.
• SHOWPLAN_XML - 쿼리를 실행하지 않고 비용 견적과 함께 XML 기반 예상 실행 계획을 표시합니다. 이는 SQL Server Management Studio의 "예제 실행 계획 표시..." 옵션과 동일합니다.
• STATISTICS PROFILE - 쿼리를 실행하고 텍스트를 기반으로 실제 실행 계획을 표시합니다.
• STATISTICS XML - 쿼리를 실행하고 XML을 기반으로 실제 실행 계획을 표시합니다. 이는 SQL Server Management Studio의 "실제 실행 계획 포함" 옵션과 동일합니다.

방법 3 - SQL Server 프로파일러 사용

쿼리를 직접 실행할 수 없는 경우(또는 쿼리를 직접 실행할 때 쿼리가 느리게 실행되지 않는 경우 - 쿼리 계획이 제대로 실행되지 않기를 원함) SQL Server Profiler를 사용하여 계획을 캡처할 수 있습니다. 아이디어는 "실행 계획" 이벤트 중 하나를 캡처하는 추적이 실행되는 동안 쿼리를 실행하는 것입니다.

부하에 따라서는 주의해 주십시오 당신은 할 수프로덕션 환경에서는 이 방법을 사용하지만 분명히 주의해야 합니다. SQL Server의 프로파일링 메커니즘은 데이터베이스에 대한 영향을 최소화하도록 설계되었지만 이것이 성능에 영향을 미치지 않는다는 의미는 아닙니다. 데이터베이스 사용량이 많은 경우 추적에서 올바른 계획을 필터링하고 결정하는 데 문제가 있을 수도 있습니다. 귀하의 소중한 데이터베이스에 이 작업을 수행하는 것에 대해 DBA가 만족하는지 분명히 확인해야 합니다!

• SQL Server 프로파일러를 열고 추적을 기록하려는 원하는 데이터베이스에 연결하는 새 추적을 만듭니다.
• 이벤트 선택 탭에서 모든 이벤트 표시 확인란을 선택하고 성능 -> 실행 계획 XML 줄을 확인한 후 추적을 실행합니다.
• 추적이 실행되는 동안 느린 쿼리를 실행하기 위해 필요한 모든 작업을 수행하세요.
• 요청이 완료되고 추적이 중지될 때까지 기다립니다.
• 추적을 저장하려면 SQL Server 프로필에서 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 Server 평가가 비활성화된 경우(예: 통계가 오래된 경우) 문제를 진단할 때 중요합니다.

쿼리 실행 계획을 해석하는 방법은 무엇입니까?

이것은 무료 도서로 충분히 가치 있는 주제입니다.

때때로 이미 게시된 포괄적인 답변 외에도 프로그래밍 방식으로 실행 계획에 액세스하여 정보를 추출할 수 있는 것이 유용합니다. 이에 대한 샘플 코드는 다음과 같습니다.

DECLARE @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help "sys.objects" /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

쿼리 실행 계획을 얻고 심층 분석하는 데 제가 가장 좋아하는 도구는 다음과 같습니다. SQL Sentry 계획 탐색기. SSMS보다 실행 계획의 상세한 분석 및 시각화가 훨씬 더 편리하고 사용자 친화적이며 완벽합니다.

다음은 도구가 제공하는 기능을 이해하기 위한 예시 화면입니다.

이는 도구에서 사용할 수 있는 보기 중 하나일 뿐입니다. 다양한 유형의 실행 계획 보기와 유용한 추가 정보를 얻을 수 있는 애플리케이션 창 하단의 탭 세트를 확인하세요.

또한 무료 버전에서는 매일 사용하지 못하게 하거나 결국에는 Pro 버전을 구입해야 하는 제한 사항도 발견하지 못했습니다. 따라서 무료 버전을 계속 사용하려는 경우에는 금지되는 사항이 없습니다.

이전 답변에서 설명한 방법 외에도 무료 실행 계획 뷰어 및 쿼리 최적화 도구인 ApexSQL Plan(최근에 접한)을 사용할 수도 있습니다.

ApexSQL 계획을 SQL Server Management Studio에 설치하고 통합할 수 있으므로 SSMS에서 실행 계획을 직접 볼 수 있습니다.

ApexSQL Plan에서 예상 실행 계획 보기

• 버튼을 클릭하세요 새로운 요청 SSMS에서 쿼리 텍스트를 쿼리 텍스트 상자에 붙여넣습니다. 마우스 오른쪽 버튼을 클릭하고 컨텍스트 메뉴에서 "샘플 실행 계획 표시"를 선택합니다.

1. 실행 계획 다이어그램의 결과 섹션에 실행 계획 탭이 표시됩니다. 그런 다음 실행 계획을 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 "ApexSQL 계획에서 열기" 옵션을 선택합니다.

1. 예상 실행 계획은 ApexSQL 계획에서 열리고 분석되어 쿼리를 최적화할 수 있습니다.

ApexSQL 계획에서 실제 실행 계획 보기

실제 쿼리 실행 계획을 보려면 앞서 언급한 두 번째 단계로 이동하세요. 하지만 이제 예상 계획이 나타나면 ApexSQL 계획의 기본 리본 바에 있는 "실제" 버튼을 클릭하세요.

실제 버튼을 클릭하면 실제 실행 계획이 다른 실행 계획 데이터와 함께 비용 매개변수에 대한 자세한 미리보기와 함께 표시됩니다.

실행 계획 보기에 대한 자세한 내용은 이 링크를 따라가면 확인할 수 있습니다.

쿼리 계획은 query_post_execution_showplan 이벤트를 통해 확장 이벤트 세션에서 얻을 수 있습니다. XEvent 세션의 예는 다음과 같습니다.

/* "쿼리 세부 정보 추적" 템플릿을 통해 생성됩니다. */ 서버에서 이벤트 세션 생성 이벤트 추가 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)), / * 원하는 대로 다음 이벤트를 제거하거나 추가 이벤트를 포함합니다. */ 이벤트 추가 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.t sql_frame,sqlserver.tsql_stack ) WHERE (.(.,(4)) AND .(.,(0)))), 이벤트 추가 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 (.(.,(4)) AND .(.,(0))), 이벤트 추가 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 (. ( .,(4)) AND .(.,(0))), 이벤트 추가 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 (.(.,(4)) AND .(.,(0)))), 이벤트 추가 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_st ack) WHERE (.(.,( 4 )) AND .(.,(0)))), 이벤트 추가 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 (.(.,(4)) AND .(.,(0))) 추가 TARGET package0.ring_buffer WITH (MAX_MEMORY=4096KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_L ATENCY =30초,MAX_EVENT_SIZE=0KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO

SSMS에서 세션이 생성되면 개체 브라우저로 이동하여 관리 | 확장 이벤트 | 세션. "GetExecutionPlan" 세션을 마우스 오른쪽 버튼으로 클릭하고 실행합니다. 마우스 오른쪽 버튼을 클릭하고 "실시간 데이터 보기"를 선택하세요.

그런 다음 새 쿼리 창을 열고 하나 이상의 쿼리를 실행합니다. AdventureWorks에 대한 내용은 다음과 같습니다.

AdventureWorks를 사용하세요. GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), 할인 = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY 제품명 DESC; 가다

1~2분 후에 "GetExecutionPlan: Live Data" 탭에 몇 가지 결과가 표시됩니다. 그리드에서 query_post_execution_showplan 이벤트 중 하나를 선택한 다음 그리드 아래의 쿼리 계획 탭을 클릭합니다. 다음과 같아야 합니다.

편집하다: XEvent 코드와 스크린샷은 SQL/SSMS 2012 w/SP2에서 생성되었습니다. SQL 2008/R2를 사용하는 경우 이를 실행하기 위한 스크립트를 설정할 수 있습니다. 하지만 이 버전에는 GUI가 없으므로 실행 계획 XML 파일을 추출하여 *.sqlplan 파일로 저장한 후 SSMS에서 열어야 합니다. 번거롭습니다. XEvent는 SQL 2005 이전 버전에는 존재하지 않았습니다. 따라서 SQL 2012 이상이 아닌 경우 여기에 게시된 다른 답변 중 하나를 강력히 제안합니다.

공유하다

SQL Server 2005의 쿼리 최적화, SQL Server 2005 데이터베이스 통계, CREATE STATISTICS, UPDATE STATISTICS, SET NOCOUNT ON, 쿼리 실행 계획, 논리적 읽기 수, 최적화 프로그램 힌트, MAXDOP, OPTIMIZE FOR, 튜토리얼 실행 계획(계획 지침), sp_create_plan_guide

성능을 최적화하는 다른 모든 방법이 이미 소진된 경우 SQL Server 개발자와 관리자는 마지막 남은 조치를 취할 수 있습니다. 바로 개별 쿼리 실행을 최적화하는 것입니다. 예를 들어, 작업에서 하나의 특정 보고서 생성 속도를 반드시 높여야 하는 경우 이 보고서를 생성하는 데 사용된 쿼리를 분석하고 최적이 아닌 경우 계획을 변경할 수 있습니다.

많은 전문가들은 쿼리 최적화에 대해 모호한 태도를 가지고 있습니다. 한편, 쿼리 실행 계획을 생성하는 쿼리 최적화 소프트웨어 모듈의 작동은 SQL Server 2000과 SQL Server 2005 모두에서 많은 비판을 불러일으킵니다. 쿼리 최적화 프로그램은 종종 가장 최적의 쿼리 실행 계획을 선택하지 않으며 어떤 상황에서는 손실을 입습니다. Oracle 및 Informix의 유사한 모듈로. 반면에 수동 쿼리 최적화는 매우 노동 집약적인 프로세스입니다. 이러한 최적화에 많은 시간을 소비할 수 있으며 결국 아무것도 최적화되지 않았다는 사실을 알게 됩니다. Query Optimizer가 처음에 제안한 계획이 가장 최적인 것으로 나타났습니다(대부분의 경우에 발생함). 또한 일정 시간이 지난 후(데이터베이스에 새 정보를 추가한 후) 수동으로 생성한 쿼리 실행 계획이 최적이 아닌 것으로 판명되어 쿼리를 실행할 때 성능이 저하되는 경우가 발생할 수 있습니다.

또한 쿼리 최적화 프로그램에서는 최상의 쿼리 계획을 선택하기 위해 올바른 통계 정보가 필요합니다. 작성자의 경험에 따르면 모든 관리자가 그것이 무엇인지 아는 것은 아니기 때문에 통계에 대해 더 자세히 알려 드리겠습니다.

통계- 테이블 컬럼의 데이터 분포에 관한 특별 서비스 정보입니다. 예를 들어, 상트페테르부르크에 살고 있는 모든 Ivanovs를 반환해야 하는 쿼리가 실행되고 있다고 가정해 보겠습니다. 이 테이블의 레코드 중 90%가 해당 열에 동일한 값을 가지고 있다고 가정해 보겠습니다. 도시 - "상트 페테르부르크". 물론 쿼리 실행의 관점에서 볼 때 먼저 테이블에서 모든 Ivanov를 선택한 다음(분명히 90%는 아님) 열의 값을 확인하는 것이 더 유리합니다. 도시선택한 각 레코드에 대해. 그러나 한 열의 값이 어떻게 분포되어 있는지 알아보려면 먼저 쿼리를 실행해야 합니다. 따라서 SQL Server는 독립적으로 이러한 쿼리 실행을 시작한 다음 데이터베이스 서비스 테이블에 데이터 분포에 대한 정보(통계라고 함)를 저장합니다.

SQL Server 2005 데이터베이스의 경우 기본 설정은 다음과 같습니다. AUTO_CREATE_STATISTICS그리고 AUTO_UPDATE_STATISTICS. 이 경우 데이터베이스 열에 대한 통계가 자동으로 생성되고 업데이트됩니다. 가장 크고 가장 중요한 데이터베이스의 경우 통계를 생성하고 업데이트하는 작업이 현재 사용자 경험을 방해할 수 있습니다. 따라서 이러한 데이터베이스의 경우 때때로 이러한 매개변수가 비활성화되고 통계 생성 및 업데이트 작업이 밤에 수동으로 수행됩니다. 이에 사용되는 명령은 다음과 같습니다. 통계 생성그리고 업데이트 통계.

이제 쿼리 최적화에 대해 이야기해 보겠습니다.

가장 먼저 해야 할 일은 주로 최적화 대상인 쿼리를 찾는 것입니다. 이를 수행하는 가장 쉬운 방법은 프로파일러의 도움을 받아 요청 기간 동안 필터를 설정하는 것입니다(필터 지속창문에서 편집하다필터(필터 편집) 버튼을 사용하여 열 수 있습니다. 열필터탭에 이벤트선택추적 세션 속성 창). 예를 들어, 최적화 후보에는 실행 시간이 5초를 초과한 쿼리가 포함될 수 있습니다. Database Tuning Advisor에서 제공하는 쿼리 정보를 사용할 수도 있습니다.

다음으로 연결, 저장 프로시저 및 함수에 대한 매개변수가 설정되어 있는지 확인해야 합니다. 개수 없음. 명령을 사용하여 설치할 수 있습니다 카운트 없음 설정. 이 매개변수를 설정하면 먼저 서버로부터의 반환과 쿼리 결과의 행 수에 대한 정보 표시가 비활성화됩니다(예: 행 "N개 행이 영향을 받았습니다."탭에 메시지(C 메시지) Management Studio에서 요청을 실행할 때 코드 작업을 위한 창). 둘째, 특수 서버 메시지 전송이 비활성화됩니다. DONE_IN_PROC, 각 저장 프로시저 단계에 대해 기본적으로 반환됩니다. 대부분의 저장 프로시저를 호출할 때는 실행 결과만 필요하며 각 단계에서 처리되는 행 수에는 관심이 없습니다. 따라서 매개변수를 설정하면 개수 없음저장 프로시저의 경우 성능이 크게 향상될 수 있습니다. 일반 쿼리의 실행 속도도 증가하지만 그 정도는 더 낮습니다(최대 10%).

그런 다음 쿼리 실행 계획 작업을 시작할 수 있습니다.

쿼리 실행 계획을 보는 가장 쉬운 방법은 SQL Server Management Studio를 이용하는 것입니다. 예상되는 쿼리 실행 계획에 대한 정보를 얻으려면 메뉴를 사용할 수 있습니다. 질문(쿼리) 선택 명령 표시하다추정된실행계획(예상 실행 계획 표시) 쿼리를 실행하기 위한 실제 계획을 알고 싶다면 쿼리를 실행하기 전에 동일한 메뉴에서 매개 변수를 설정하면 됩니다. 포함하다실제실행계획(실제 실행 계획 포함) 이 경우 쿼리를 실행한 후 SQL Server Management Studio의 결과 창에 다른 탭이 나타납니다. 실행계획(실행 계획) - 실제 쿼리 실행 계획을 표시합니다. 각 단계 위에 마우스를 올리면 해당 단계에 대한 추가 정보를 얻을 수 있습니다(그림 11.15).

쌀. 11.15. SQL Server Management Studio의 쿼리 실행 계획

그림에서 볼 수 있듯이 쿼리 실행 계획에는 많은 요소가 있을 수 있습니다. 이를 이해하고 다른 실행 계획을 제안하는 것은 다소 어려운 작업입니다. 가능한 각 요소는 해당 상황에서 최적이라고 말해야 합니다. 따라서 일반적으로 쿼리 최적화 단계는 다음과 같습니다.

q 먼저 Management Studio 창에서 다음 명령을 실행합니다. 통계 IO를 켜짐으로 설정. 결과적으로 요청을 실행할 때마다 추가 정보가 표시됩니다. 여기에서 우리는 단 하나의 매개변수 값에만 관심이 있습니다. 논리적 읽기. 이 매개변수는 쿼리 실행 시 논리적 읽기 수, 즉 캐시의 영향(캐시와 디스크 모두에서 읽기 수)을 ​​고려하지 않고 특정 쿼리를 실행할 때 수행해야 하는 읽기 작업 수를 의미합니다. 이것이 가장 중요한 매개 변수입니다. 물리적 읽기(디스크에서만 읽기) 횟수는 이전에 해당 테이블에 액세스했는지 여부에 따라 달라지므로 대표적인 정보가 아닙니다. 시간 통계도 가변적이며 해당 시간에 서버가 수행하는 다른 작업에 따라 달라집니다. 그러나 논리적 읽기 수는 가장 객관적인 지표이며 추가 요인의 영향을 가장 적게 받습니다.

q 그런 다음 쿼리 실행 계획을 변경하고 각 계획에 대한 총 논리적 읽기 수를 알아보세요. 일반적으로 쿼리 실행 계획은 최적화 프로그램 힌트를 사용하여 변경됩니다. 이는 사용할 실행 계획을 최적화 프로그램에 명시적으로 알려줍니다.

SQL Server 2005에는 많은 최적화 프로그램 힌트가 있습니다. 온라인 도서(탭 목록)에서 관련 정보를 읽을 수 있습니다. 색인(색인)을 선택해야 합니다. 질문힌트 [SQL섬기는 사람](쿼리 힌트), 가입하다힌트(참여 힌트) 또는 테이블힌트 [SQL섬기는 사람](표 힌트)). 가장 일반적으로 사용되는 힌트는 다음과 같습니다.

큐 NOLOCK, 놋좆, 패글락, 탭블록, 홀드락, 커밋된 잠금 읽기, 업잠금, XLOCK- 이 힌트는 잠금을 관리하는 데 사용됩니다. (섹션 11.5.7 참조);

큐 빠른 줄 수 - 지정된 행 수(레코드 세트의 시작 부분부터 첫 번째 행)가 가능한 한 빨리 표시되는 쿼리 실행 계획이 선택됩니다. 사용자가 정확히 첫 번째 레코드(예: 최신 주문)가 필요한 경우 이 힌트를 사용하여 가능한 한 빨리 해당 레코드를 애플리케이션 창에 로드할 수 있습니다.

큐 강제 명령- 쿼리 실행 시 테이블 조인은 해당 테이블이 쿼리에 나열된 순서대로 정확하게 수행됩니다.

큐 맥스돕(최대 병렬 처리 수준 - 요청의 최대 병렬 처리 수준) - 이 힌트를 사용하면 요청을 실행하는 데 사용할 수 있는 최대 프로세서 수가 표시됩니다. 일반적으로 이 힌트는 두 가지 상황에서 사용됩니다.

· 프로세서 간 전환으로 인해 ( 문맥스위칭) 쿼리 실행 속도가 크게 감소합니다. 이 동작은 다중 프로세서 시스템의 SQL Server 2000에서 일반적이었습니다.

· 과도한 요청이 현재 사용자 경험에 최소한의 영향을 주기를 원하는 경우;

큐 최적화- 이 힌트를 사용하면 요청에 전달된 매개변수의 특정 값에 대해 요청이 최적화되도록 지정할 수 있습니다(예: 어디);

큐 사용계획- 가장 강력한 기회입니다. 이러한 힌트를 사용하면 계획을 XML 형식의 문자열 값으로 전달하여 쿼리 실행 계획을 명시적으로 정의할 수 있습니다. 힌트 사용계획 SQL Server 2005에서만 나타났습니다(이전 버전에서는 쿼리 실행 계획을 명시적으로 정의할 수 있었지만 이는 다른 수단을 사용하여 수행되었습니다). XML 형식의 계획은 수동으로 작성하거나 자동으로 생성할 수 있습니다(예: 그림 11.15에 표시된 실행 계획이 있는 그래픽 화면을 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 명령을 선택). 구하다실행계획처럼(실행 계획을 다른 이름으로 저장)).

SQL Server 2005에는 쿼리 텍스트를 조작하지 않고도 쿼리 실행 계획을 수동으로 변경할 수 있는 중요한 새 기능이 도입되었습니다. 요청 코드를 변경할 수 없는 경우가 종종 발생합니다. 요청 코드는 컴파일된 애플리케이션의 코드에 내장되어 있습니다. 이 문제를 해결하기 위해 SQL Server 2005에서는 저장 프로시저를 도입했습니다. sp_create_plan_guide. 이를 통해 소위를 만들 수 있습니다. 실행 계획 가이드 (계획가이드) 일치하는 요청에 자동으로 적용됩니다.

애플리케이션이 데이터베이스에 보낸 쿼리를 분석하는 경우 먼저 다음 사항에 주의하는 것이 좋습니다.

q 쿼리 실행 계획에 작업이 나타나는 빈도 테이블주사(전체 테이블 스캔). 인덱스를 사용하여 테이블에 액세스하는 것이 더 효율적일 수도 있습니다.

q 코드에 커서가 사용되는지 여부. 커서는 프로그램 구문 측면에서는 매우 단순하지만 성능 측면에서는 매우 비효율적입니다. 다른 구문 구조를 사용하면 커서 사용을 피할 수 있고 속도가 크게 향상되는 경우가 많습니다.

q 코드가 임시 테이블을 사용하는지 아니면 데이터 유형을 사용하는지 여부 테이블. 임시 테이블을 생성하고 작업하려면 많은 리소스가 필요하므로 가능하면 이를 피하는 것이 좋습니다.

q 임시 테이블을 생성하는 것 외에도 구조를 변경하려면 시스템 리소스를 많이 소모해야 합니다. 따라서 임시 테이블의 구조를 변경하는 명령은 즉시 주의를 끌 것입니다. 일반적으로 필요한 모든 열이 포함된 임시 테이블을 즉시 생성하는 것이 가능합니다.

q 때때로 쿼리는 응용 프로그램에 실제로 필요한 것보다 더 많은 데이터(추가 열 또는 행)를 반환합니다. 물론 이것이 생산성을 향상시키지는 않습니다.

q 애플리케이션이 서버에 명령을 보내는 경우 실행하다, 그런 다음 저장 프로시저 호출로 대체하는 것을 고려하는 것이 합리적입니다. sp_executesql. 일반 명령에 비해 성능상의 이점이 있습니다. 실행하다;

q 저장 프로시저를 다시 컴파일하고 새로운 쿼리 실행 계획을 구축할 필요가 없어져 성능이 향상되는 경우도 있습니다. 매개변수 사용에 주의해야 하며, 저장 프로시저 코드에 DML과 DDL 명령을 혼합하지 말고, 연결 매개변수가 올바른지 확인해야 합니다. ANSI_DEFAULT 설정, ANSI_NULLS 설정, ANSI_PADDING 설정, ANSI_WARNINGS 설정그리고 SET CONCAT_NULL_YIELDS_NULL요청 간에 변경되지 않았습니다(이러한 매개변수를 변경하면 이전 실행 계획이 무효화됩니다). 일반적으로 이러한 매개변수가 개별 요청 수준이나 저장 프로시저 코드에서 설정되면 문제가 발생할 수 있습니다.

어떤 경우든 수동으로 쿼리 실행 계획을 만들고 힌트를 사용하는 것은 최후의 수단이므로 가능하면 피해야 합니다.

SQL 쿼리 실행 계획,또는 쿼리 계획은 SQL 쿼리를 실행하는 데 필요한 일련의 단계 또는 DBMS 명령입니다. 각 단계에서 이 SQL 쿼리 실행 단계를 시작한 작업은 최종 결과를 형성하거나 추가 처리에 사용될 수 있는 데이터 행을 검색합니다. SQL 쿼리 실행 계획 지침은 DBMS FOR SQL 문 SELECT, INSERT, 삭제하고 업데이트. 쿼리 계획의 내용은 일반적으로 트리 구조로 표시되며 다음 정보를 포함합니다.

• 데이터 소스(테이블, 뷰 등) 연결 순서
• 각 데이터 소스에 대한 액세스 방법;
• 데이터 소스 연결 방법;
• 데이터 선택, 정렬 및 집계를 제한하는 작업;
• 각 작업의 비용 및 심각도;
• 분할 및 병렬 처리를 사용할 수 있습니다. SQL 쿼리 실행 계획에서 제공하는 정보를 통해 개발자는 최적화 프로그램이 SQL 작업을 수행하기 위해 어떤 접근 방식과 방법을 선택하는지 확인할 수 있습니다.

SQL 쿼리 실행 계획 해석

SQL 쿼리 실행 계획의 시각화는 도구 및 개발 도구에 따라 달라집니다. 이는 쿼리가 분석 대상인 DBMS의 일부이거나 특정 DBMS와 직접적으로 관련되지 않은 별도의 상용 또는 무료 배포 소프트웨어 제품일 수 있습니다. 제조업체. 하나 또는 다른 쿼리 계획 시각화 도구를 사용하면 일반적으로 제시된 쿼리 계획이 설명하는 내용에 대한 인식에 큰 영향을 미치지 않습니다. 특정 쿼리를 실행할 때 최적화 프로그램이 어떤 경로를 취할 것인지 분석하는 과정에서 결정적인 요소는 쿼리 계획에 제시된 정보를 올바르게 해석하는 능력입니다.

이미 언급했듯이 SQL 쿼리 계획에는 SQL 작업 실행 순서뿐만 아니라 이러한 작업 간의 관계도 설명하는 트리 구조가 있습니다. 쿼리 계획 트리의 각 노드는 정렬이나 테이블 액세스 방법과 같은 작업입니다. 노드 사이에는 부모-자식 관계가 있습니다. 상위-하위 관계에는 다음 규칙이 적용됩니다.

• 부모는 한 명 이상의 자녀를 가질 수 있습니다.
• 아이에게는 부모가 한 명뿐입니다.
• 상위 작업이 없는 작업은 트리의 맨 위에 있습니다.
• SQL 쿼리 계획을 시각화하는 방법에 따라 자식은 부모를 기준으로 들여쓰기되어 배치됩니다. 한 부모의 자손은 부모로부터 같은 거리에 위치합니다.

SQL 쿼리 실행 계획에서 제공하는 정보를 자세히 살펴보겠습니다. 제공된 예제는 Oracle DBMS 환경에서 수행되었습니다. Oracle SQL Developer는 쿼리를 실행하고 SQL 쿼리 계획을 시각화하기 위한 도구로 사용되었습니다. SQL 쿼리 계획의 일부가 그림 1에 나와 있습니다. 10.11.

I I I I 운영

• 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK 제품 정보

SELECT 문 정렬 순서 중첩 루프별 중첩 루프 테이블 액세스 전체 인덱스 고유 스캔 테이블 액세스 인덱스 ROWID

쌀. 10.11. Oracle DBMS 환경의 SQL 쿼리 실행 계획의 일부

쿼리 계획 작업의 관계 규칙을 사용하여 다음과 같은 공식적인 설명을 정의할 수 있습니다.

작업 0은 쿼리 계획 트리의 루트입니다. 루트에는 하나의 하위 항목(작업 1)이 있습니다.

작업 1 - 작업에는 하나의 하위 항목이 있습니다: 작업 2.

작업 2 - 작업에는 작업 3과 작업 6이라는 두 개의 하위 항목이 있습니다.

작업 3 - 작업에는 작업 4와 작업 5라는 두 개의 하위 항목이 있습니다.

작업 4 - 작업에 하위가 없습니다.

작업 5 - 작업에 하위가 없습니다.

작업 6 - 작업에 하위가 없습니다.

쿼리 계획 작업 간의 부모-자식 상호 작용은 그림 1에 나와 있습니다. 10.12.

쿼리 계획에서 수행되는 작업은 독립 실행형, 바인딩되지 않은 조인 작업, 연결된 조인 작업의 세 가지 유형으로 나눌 수 있습니다(그림 10.13).

자발적인		관련 없는 작업		관련 업무
운영		협회		협회

쌀. 10.12.

쌀. 10.13.

자율 운영 -이는 최대 하나의 하위 작업이 있는 작업입니다.

자율적인 동작이 수행되는 규칙은 다음과 같이 공식화될 수 있다.

• 2. 각 하위 작업은 한 번만 실행됩니다.
• 3. 각 하위 작업은 해당 결과를 상위 작업에 반환합니다.

그림에서. 그림 10.14는 다음 쿼리에 대한 계획을 보여줍니다.

SELECT o.order_id ,o.order_status FROM 주문 o ORDER BY o.order_status

이 쿼리에는 독립 실행형 작업만 포함되어 있습니다.

자율 운영을 따르는 규칙을 고려하면 실행 순서는 다음과 같습니다.

• 1. 다음 자율 작업 규칙 1번에 따라 ID = 2인 작업이 먼저 실행되고 주문 테이블의 모든 행이 순차적으로 읽혀집니다.
• 2. 다음으로 ID = 1 의 연산을 수행하고, ID = 2 의 연산으로 반환된 행을 ORDER BY 정렬 절의 조건에 따라 정렬합니다.
• 3. ID = 0인 작업이 수행되고 결과 데이터 세트가 반환됩니다.

언바운드 연합 운영

언바운드 연합 운영독립적으로 실행되는 하위 작업이 두 개 이상 있는 작업입니다. 예: HASH JOIN, MERGE JOIN, INTERSECTION, MINUS, UNION ALL.

언바운드 조인 작업이 작동하는 다음 규칙은 다음과 같이 공식화될 수 있습니다.

• 1. 하위 작업은 상위 작업보다 먼저 실행됩니다.
• 2. 하위 작업은 가장 작은 작업 ID 값부터 시작하여 해당 값의 오름차순으로 순차적으로 실행됩니다.
• 3. 각 후속 하위 작업이 시작되기 전에 현재 작업이 완전히 완료되어야 합니다.
• 4. 각 하위 작업은 다른 하위 작업에 관계없이 한 번만 실행됩니다.
• 5. 각 하위 작업은 해당 결과를 상위 작업에 반환합니다.

그림에서. 그림 10.15는 다음 쿼리에 대한 계획을 보여줍니다.

주문에서 o.order_id를 선택합니다. o UNION ALL

order_items oi에서 oi.order_id를 선택하세요.

이 쿼리에는 바인딩되지 않은 조인 작업이 포함되어 있습니다. 유니온 모두. 나머지 두 작업은 자율적입니다.

쌀. 10.15. 언바운드 조인 작업, 쿼리 계획

	1 진술 I을 선택하세요

관련되지 않은 조인 작업을 따르는 규칙을 고려하면 실행 순서는 다음과 같습니다.

• 1. 다음의 관련 없는 조인 연산의 규칙 1, 2에 따라 ID = 2인 연산을 먼저 수행하고 주문 테이블의 모든 행을 순차적으로 읽습니다.
• 2. 규칙 5에 따라 ID = 2인 작업은 1단계에서 읽은 ID = 1인 상위 작업의 행을 반환합니다.
• 3. ID = 3인 작업은 ID = 2인 작업이 종료된 후에만 실행을 시작합니다.
• 4. ID = 2인 작업이 완료되면 ID = 3인 작업이 시작되며, order_items 테이블의 모든 행을 순차적으로 읽습니다.
• 5. 규칙 5에 따라 ID = 3인 작업은 4단계에서 읽은 ID = 1인 상위 작업의 행을 반환합니다.
• 6. ID = 1인 작업은 모든 하위 작업(ID = 2 및 ID = 3)에서 수신된 데이터를 기반으로 데이터 결과 집합을 생성합니다.
• 7. ID = 0인 작업이 수행되고 결과 데이터 세트가 반환됩니다.

따라서 독립 조인 작업은 하위 작업을 순차적으로 실행한다는 점을 알 수 있습니다.

연결된 조인 작업

연결된 조인 작업 -이는 둘 이상의 하위 작업이 있는 작업으로, 작업 중 하나가 다른 작업의 실행을 제어합니다. 예: 중첩 루프, 업데이트.

체인 조인 작업이 작동하는 규칙은 다음과 같이 공식화될 수 있습니다.

• 1. 하위 작업은 상위 작업보다 먼저 실행됩니다.
• 2. 작업 번호(ID)가 가장 낮은 하위 작업이 나머지 하위 작업의 실행을 제어합니다.
• 3. 공통된 상위 작업이 있는 하위 작업은 작업 ID의 가장 낮은 값부터 시작하여 해당 값의 오름차순으로 실행됩니다. 나머지 하위 작업은 순차적으로 실행되지 않습니다.
• 4. 첫 번째 하위 작업만 한 번 실행됩니다. 다른 모든 하위 작업은 여러 번 실행되거나 전혀 실행되지 않습니다.

그림에서. 그림 10.16은 다음 쿼리에 대한 계획을 보여줍니다.

FROM order_items oi, 주문 o

어디서 o.order_id= oi.order_id

그리고 oi.product_id>100

그리고 100과 1000 사이의 o.customer_id

이 쿼리에는 관련 조인 작업인 NESTED LOOPS가 포함되어 있습니다.

I I I I 운영

		선택문 |

쌀. 10.16. 연결된 조인 작업, 쿼리 계획

체인 조인 작업을 따르는 규칙을 고려하면 실행 순서는 다음과 같습니다.

• 1. 다음 Chained Join 연산의 Rule 1, 2에 따르면 ID = 2인 연산이 먼저 수행되어야 하지만, 1D = 2, 1D = 3인 연산은 자율적이며, 다음 자율 연산의 Rule 1에 따르면 ID = 2인 작업이 먼저 수행됩니다. ID = 3. ORDCUSTOMERIX 인덱스 범위는 다음 조건에 따라 표시됩니다. o. 고객 ID는 100에서 1000 사이입니다.
• 2. ID=3인 연산은 1단계에서 얻은 Rowld 행 식별자 목록을 상위 연산(Ш=2)으로 반환합니다.
• 3. ID = 2인 작업은 Rowld 값이 2단계에서 얻은 Rowld 값 목록과 일치하는 주문 테이블의 행을 읽습니다.
• 4. ID = 2인 작업은 상위 작업(ID = 1)의 읽기 행을 반환합니다.
• 5. ID = 2인 작업에서 반환된 각 행에 대해 해당 작업의 두 번째 하위 작업(ID = 4)이 실행됩니다. 중첩 루프. 즉, ID = 2인 작업에서 반환된 각 행에 대해 order_items 테이블의 전체 순차 스캔이 수행되어 조인 속성과 일치하는 항목을 찾습니다.
• 6. ID = 2인 연산에서 반환된 행 수만큼 5단계를 반복합니다.
• 7. ID = 1인 작업은 상위 작업(ID = 0)의 결과를 반환합니다.
• 8. ID = 0인 작업이 수행되고 결과 데이터 세트가 반환됩니다.

분석된 쿼리의 복잡성에 따라 실행 계획의 구조가 다소 복잡해 언뜻 해석하기 어려울 수도 있습니다. 위에서 설명한 규칙을 체계적으로 구현하고 작업을 분해하면 복잡한 SQL 쿼리의 실행 계획을 효과적으로 분석할 수 있습니다. 고객 목록, 고객이 구매한 상품 수 및 총 비용을 생성하는 쿼리의 예를 살펴보겠습니다.

SELECT s. cust_first_name 고객_이름,

COUNT(DISTINCT oi.product_id)를 product_qty로,

SUM(oi.quantity* oi.unit_price) as total_cost FROM oe.orders o INNER JOIN 고객 c ON

o.customer_id=c.customer_id

INNER JOIN oe.order_items oi ON o.order_id= oi.order_id GROUP BY c. 고객 이름

이 쿼리 계획의 작업 순서는 그림 1에 나와 있습니다. 10.17.

진술 I을 선택하세요

그룹 정렬 기준 YG

	테이블 액세스가 가득 참
	인덱스 범위 스캔
인덱스 ROWIDd에 따른 테이블 액세스
테이블 액세스가 가득 참

쌀. 10.17. 쿼리 계획, 작업 순서

그림 1에 제시된 80b 쿼리의 실행 계획을 해석하는 가능한 접근 방식을 설명하겠습니다. 10.17. 이 접근 방식에는 작업을 블록으로 분해하고 작업 순서를 결정하는 두 가지 주요 단계가 포함됩니다.

첫 번째 단계에서는 수행되는 작업을 블록으로 분해해야 합니다. 이를 위해 우리는 모든 결합 작업을 찾습니다. 하나 이상의 하위 작업(그림 10.17에서는 작업 2, 3, 4)이 있는 작업을 만들고 이러한 하위 작업을 블록으로 분리합니다. 결과적으로 그림의 예를 사용하면 다음과 같습니다. 10.17에서는 3개의 Union 연산과 7개의 연산 블록을 얻습니다.

두 번째 단계에서는 작업 블록의 실행 순서가 결정됩니다. 이렇게 하려면 위에서 설명한 다음 작업에 대한 규칙을 적용해야 합니다. 식별 번호(III)를 기준으로 각 작업의 실행과 관련된 일련의 고려 사항을 수행해 보겠습니다.

작업 Ш = 0은 자율적이며 작업 сШ = 1의 상위입니다.

Yu = 1 작전도 자율적입니다. 연산 W = 2의 부모이며 연산 Y = 0 이전에 실행됩니다.

작업 GO = 2는 관련 없는 통합 작업이며 작업 Yu = 3, Yu = 8의 상위 작업입니다. 작업 GO = 2는 작업 GO = 1 전에 수행됩니다.

작업 GO = 3은 연결된 통합 작업이며 작업 GO = 4, GO = 7의 상위 작업입니다. 작업 GO = 3은 작업 GO = 2 전에 수행됩니다.

작업 GO = 4는 연결된 통합 작업이며 작업 GO = 5, GO = 6의 상위 작업입니다. 작업 GO = 4는 작업 GO = 3 전에 수행됩니다.

GO = 5 작업은 GO = 4 작업 이전에 수행되는 자율 작업입니다.

GO = 6 작업은 GO = 5 작업 이전에 수행되는 자율 작업입니다.

작업 GO = 7은 작업 블록 "C"가 실행된 후에 수행되는 자율 작업입니다.

작업 GO = 8은 작업 블록 "E" 이후에 수행되는 자율 작업입니다.

위의 추론과 다음 규칙을 기반으로 수행되는 작업 순서를 공식화합니다.

• 1. 자율 작업 GO = 5가 먼저 수행됩니다. 연관된 조인 작업 순서에 대한 규칙을 참조하세요. 전체 테이블을 순차적으로 읽습니다.
• 2. GO = 5 작업의 결과(읽은 테이블 행)는 GO = 4 작업으로 전송됩니다.
• 3. GO = 4 작업이 수행됩니다. GO = 5 작업에서 반환된 각 행에 대해 GO = 6 작업이 수행됩니다. 즉, 조인 속성에 대해 인덱스 범위가 검색됩니다. 행 식별자 Yaou1s1의 목록을 가져옵니다.
• 4. 연산 GO = 4의 결과는 연산 GO = 3으로 전송됩니다. 즉, 행 식별자 Kosh1s1의 목록이 전송됩니다.
• 5. GO = 3 작업이 수행됩니다. 작업 "C" 블록의 작업 결과로 반환된 각 값 11оу1с1에 대해 GO = 7 작업이 수행됩니다. 테이블 행은 Ш = 4 작업을 수행한 후 얻은 ITMI 행 식별자의 주어진 목록에서 읽혀집니다.
• 6. 자율 작업 GO = 8이 수행됩니다 - 전체 테이블을 순차적으로 읽습니다.
• 7. 관련되지 않은 조인 연산 GO = 2가 수행됩니다. 조인은 연산 블록 "E"와 "E"의 결과를 해싱하여 수행됩니다.
• 8. GO = 2 작업의 결과는 GO = 1 작업으로 전송됩니다.
• 9. 관련되지 않은 병합 작업 GO = 1이 수행됩니다. 작업 GO = 2의 결과로 얻은 데이터가 집계되고 정렬됩니다.
• 10. GO = 0 연산이 수행되고 결과 데이터 세트가 반환됩니다.

주요 작업 유형에 대해 공식화된 다음 규칙은 BSGO 쿼리 실행을 위한 대부분의 계획에 적용 가능합니다. 그러나 BSGO 쿼리에는 다음 규칙에 설명된 작업 순서 위반을 암시하는 구성이 사용됩니다. 이러한 상황은 예를 들어 하위 쿼리나 조인 방지 조건자를 사용한 결과로 발생할 수 있습니다. 어떤 경우든 BSGO 쿼리의 실행 계획을 해석하는 프로세스는 BSGO 쿼리를 실행할 때 최적화 프로그램이 수행할 작업에 대한 가장 정확한 분석을 보장하는 여러 규칙의 사용만을 의미하지 않습니다. 또 다른 BSGO 요청은 항상 개별 사례입니다. DBMS에서 실행되는 방법은 DBMS 버전, DBMS 인스턴스가 배포되는 운영 체제의 버전 및 유형, 사용된 하드웨어, 80b 쿼리 작성자의 자격, 등.

1 msdevcon.ru #msdevcon

3 Olontsev Sergey SQL Server MCM, MVP Kaspersky Lab

4 구조화된 쿼리 언어

5 예제 쿼리에서는 HumanResources.Employee에서 pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber를 emp 내부 조인으로 선택합니다. Person.Person은 pers.businessentityid = emp.businessentityid에서 pers.firstname = N"John"이고 emp.hiredate >= " "

6 논리 쿼리 트리 프로젝트 pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber D A T A 필터 조인 pers.firstname = N"John" 및 emp.hiredate >= " " pers.businessentityid = emp.businessentityid Person.Person as pers 데이터 가져오기 데이터 가져오기 HumanResources.Employee를 emp로 가져오기

7 쿼리 계획 T-SQL 쿼리가 물리적 수준에서 실행되는 방식을 보여줍니다.

8 여러 가지 방법

9 DEMO Simple plan 테이블에서 모든 데이터 선택, 쿼리 계획을 얻는 방법

11 Init() 연산자 메서드 Init() 메서드는 물리 연산자가 자신을 초기화하고 필요한 데이터 구조를 준비하도록 합니다. 물리 연산자는 일반적으로 하나만 수신하지만 Init()에 대한 많은 호출을 수신할 수 있습니다. GetNext() GetNext() 메서드는 물리 연산자가 데이터의 첫 번째 또는 후속 행을 가져오도록 합니다. 물리적 운영자는 GetNext() 호출을 많이 받을 수도 있고 전혀 받지 못할 수도 있습니다. GetNext() 메서드는 한 행의 데이터를 반환하며 호출 횟수는 Showplan 문의 출력에 있는 ActualRows 값으로 표시됩니다. Close() Close() 메서드가 호출되면 물리 연산자는 일부 정리 작업을 수행하고 닫힙니다. 물리 연산자는 Close()에 대한 호출을 한 번만 받습니다.

12 연산자 간 상호작용 연산자 1 연산자 2 연산자 3

13 연산자 간 상호 작용 1. 요청 행 연산자 1 연산자 2 연산자 3

14 연산자 간 상호 작용 1. 요청 행 2. 요청 행 연산자 1 연산자 2 연산자 3

15 연산자 간 상호 작용 1. 요청 행 2. 요청 행 연산자 1 연산자 2 연산자 3 3. 전송 행

16 연산자 간 상호 작용 1. 요청 행 2. 요청 행 연산자 1 연산자 2 연산자 3 4. 전송 행 3. 전송 행

17 연산자 간 상호 작용 1. 요청 행 2. 요청 행 연산자 1 연산자 2 연산자 3 4. 전송 행 3. 전송 행

18 DEMO Operator TOP 연산자를 반복자(iterator)라고 부르는 것이 더 좋은 이유

19 테이블이 존재하지 않습니다!

20 HoBT 페이지 1 페이지 2 페이지 3 페이지 4 행 1 행 3 행 5 행 7 행 2 행 4 행 6 행 8 행

21 HoBT 페이지 페이지 페이지 페이지 페이지 페이지 페이지

22 DEMO 데이터 액세스 연산자 Scan, Seek, Lookup

23 데이터베이스에 테이블이 하나만 있는 사람은 누구입니까?

24 중첩 루프, 해시 조인 및 병합 조인

25 조인 연산자 중첩 루프 내부 조인, 왼쪽 외부 조인, 왼쪽 세미 조인, 왼쪽 안티 세미 조인 병합 조인 내부 조인, 왼쪽 외부 조인, 왼쪽 세미 조인, 왼쪽 안티 세미 조인, 오른쪽 외부 조인, 오른쪽 세미 조인, 오른쪽 안티 세미 조인 , Union Hash 모든 유형의 논리 연산을 결합합니다.

26 데모 조인, 정렬 및 첫 번째 연산자 중첩 루프, 병합 조인, 해시 조인, 정렬, 첫 번째 연산자

27 경고

28 DEMO 쿼리 계획의 오류 및 경고

29 나는 아무것도 모른다는 것을 안다. 소크라테스

30 DEMO 불분명한 것에 대한 작은 예

31 쿼리 계획 진단 -- 가장 많은 CPU를 소비하는 TOP 10 쿼리와 해당 계획은 top(10) substring(t.text, qs.statement_start_offset / 2, qs.statement_end_offset = -1이면 len(t.text) else를 선택합니다. (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, 캐스트(qs.total_worker_time / as 십진수(18, 2)) as total_worker_time_ms, Cast(qs.total_worker_time * 1. / qs.execution_count / as 십진수 (18, 2)) as avg_worker_time_ms, sys.dm_exec_query_stats에서 query_plan으로 캐스팅(p.query_plan as xml) as qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as t cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset , qs.statement_end_offset) qs.total_worker_time desc의 p 순서로; 가다

32 대규모 쿼리 계획을 읽는 기술 쿼리를 논리적 블록으로 나누고 점진적으로 분석해 보세요. SSMS에서 계획이 그래픽으로 표시되면 쿼리 계획을 더 쉽게 탐색할 수 있도록 오른쪽 하단에 버튼이 나타납니다. XQuery\XPath를 사용할 수 있습니다.

33 DEMO 대규모 쿼리 계획

35 DEMO SQL Sentry 계획 탐색기

36 요약하자면 첫 번째 연산자 최적화 수준 컴파일 시간 캐시 내 크기 매개변수, 컴파일 값 조기 종료 이유 반복자의 비용 비용이 가장 높은 연산자를 먼저 살펴보세요. 이는 단지 추정된 값일 뿐임을 명심하세요(실제 실행 계획에서도).

37 북마크\키 조회를 요약해 보겠습니다. 개수가 적다면 문제가 없을 가능성이 높습니다. 그러한 항목이 많은 경우 포함 색인을 생성하면 이를 제거하는 데 도움이 됩니다. 경고 발생 원인을 확인하고 필요한 경우 조치를 취해야 합니다.

38 요약하자 연산자 간 연결(데이터 흐름) 연결이 두꺼울수록 연산자 간에 전달되는 데이터가 더 많아집니다. 어떤 단계에서 데이터 흐름이 급격히 증가하는 경우 특히 주의할 가치가 있습니다. 테이블 조인 순서 데이터 스트림이 작을수록 조인하기가 더 쉽습니다. 따라서 먼저 결과 데이터 흐름이 더 작은 테이블을 조인해야 합니다.

39 요약 검사 검사를 한다고 해서 문제가 있다는 의미는 아닙니다. 보다 효율적인 검색을 수행하기에는 테이블에 인덱스가 충분하지 않을 수 있습니다. 반면에 테이블 전체 또는 큰 부분을 선택해야 하는 경우 스캔이 더 효율적입니다. 검색한다고 해서 모든 것이 잘 되는 것은 아닙니다. 비클러스터형 인덱스에 대한 검색 수가 많으면 문제가 될 수 있습니다. 계획에 대해 모르는 것이 있으면 잠재적으로 문제가 될 수 있습니다.

40개의 질문

41 연락처 Olontsev Sergey Kaspersky Lab

42 2013년 마이크로소프트사. 판권 소유. Microsoft, Windows, Windows Vista 및 기타 제품 이름은 미국의 등록 상표 및/또는 상표이거나 그럴 수 있습니다. 및/또는 다른 국가. 여기에 포함된 정보는 정보 제공의 목적으로만 제공되며 이 프레젠테이션 날짜를 기준으로 Microsoft Corporation의 현재 관점을 나타냅니다. Microsoft는 변화하는 시장 상황에 대응해야 하므로 이를 Microsoft의 약속으로 해석해서는 안 되며 Microsoft는 이 프레젠테이션 날짜 이후 제공된 정보의 정확성을 보장할 수 없습니다. MICROSOFT는 이 프레젠테이션의 정보에 대해 명시적, 묵시적 또는 법적 보증을 하지 않습니다.