다음을 통해 공유

메모리 최적화 테이블에 대한 쿼리 처리 가이드

  • 아티클

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

메모리 내 OLTP는 SQL Server에서 메모리 최적화 테이블과 고유하게 컴파일된 저장 프로시저를 도입합니다. 이 문서에서는 메모리 최적화 테이블과 고유하게 컴파일된 저장 프로시저 모두에 대한 쿼리 처리 개요를 제공합니다.

이 문서에서는 다음 내용을 포함하여 메모리 최적화 테이블에 대한 쿼리를 컴파일하고 실행하는 방법에 대해 설명합니다.

  • 디스크 기반 테이블에 대한 SQL Server의 쿼리 처리 파이프라인

  • 쿼리 최적화, 메모리 최적화 테이블에 대한 통계 역할 및 잘못된 쿼리 계획 문제를 해결하기 위한 지침

  • 메모리 최적화 테이블에 액세스하기 위한 해석된 Transact-SQL 사용

  • 메모리 최적화 테이블 액세스에 대한 쿼리 최적화에 대한 고려 사항

  • 고유하게 컴파일된 저장 프로시저 컴파일 및 처리

  • 최적화 프로그램에서 비용 예측을 위해 사용되는 통계

  • 잘못된 쿼리 계획을 수정하는 방법

예제 쿼리

다음 예제는 이 문서에서 설명하는 쿼리 처리 개념을 설명하는 데 사용됩니다.

고객 및 주문의 두 테이블을 고려합니다. 다음 Transact-SQL 스크립트에는 기존의 디스크 기반 형태로 이러한 두 테이블과 관련 인덱스에 대한 정의가 포함됩니다.

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO

이 문서에 표시된 쿼리 계획을 생성하기 위해 두 테이블에는 Northwind 샘플 데이터베이스의 샘플 데이터가 입력되었습니다. 이 데이터베이스는 SQL Server 2000의 Northwind 및 pubs 샘플 데이터베이스에서 다운로드할 수 있습니다.

다음 쿼리를 살펴보십시오. 이 쿼리는 Customer 및 Order 테이블을 조인하고 주문 ID와 연관된 고객 정보를 반환합니다.

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

SQL Server Management Studio에 표시되는 예상 실행 계획은 다음과 같습니다.

디스크 기반 테이블 조인을 위한 쿼리 계획.
디스크 기반 테이블 조인을 위한 쿼리 계획.

이 쿼리 계획 정보:

  • Customer 테이블의 행은 기본 데이터 구조이고 전체 테이블 데이터가 있는 클러스터형 인덱스에서 검색됩니다.

  • Order 테이블의 데이터는 CustomerID 열의 비클러스터형 인덱스로 검색됩니다. 이 인덱스에는 조인에 사용되는 CustomerID 열과 사용자에게 반환되는 기본 키 열인 OrderID가 모두 포함됩니다. Order 테이블에서 추가 열을 반환하려면 Order 테이블에 대한 클러스터형 인덱스에서 조회가 필요합니다.

  • 논리 연산자 Inner Join은 물리 연산자 Merge Join에 의해 구현됩니다. 다른 물리적 조인 유형은 Nested Loops 및 Hash Join입니다. Merge Join 연산자는 두 인덱스가 모두 CustomerID 조인 열에 정렬된다는 사실을 활용합니다.

OrderID 열뿐만 아니라 Order 테이블의 모든 열을 반환하는 이 쿼리의 약간의 변형을 고려합니다.

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

이 쿼리의 예상 계획은 다음과 같습니다.

디스크 기반 테이블의 해시 조인을 위한 쿼리 계획.
디스크 기반 테이블의 해시 조인을 위한 쿼리 계획.

이 쿼리에서는 클러스터형 인덱스를 사용하여 Order 테이블의 행을 검색합니다. Hash Match 물리 연산자는 이제 Inner Join에 사용됩니다. Order의 클러스터형 인덱스는 CustomerID에서 정렬되지 않으므로 Merge Join에는 성능에 영향을 미치는 정렬 연산자가 필요합니다. Hash Match 연산자의 상대적 비용(75%)을 이전 예제의 Merge Join 연산자 비용(46%)과 비교합니다. 이전 예에서 최적화 프로그램에는 Hash Match 연산자도 고려되었겠지만 Merge Join 연산자가 더 나은 성능을 제공하는 것으로 결정되었습니다.

디스크 기반 테이블에 대한 SQL Server 쿼리 처리

다음 다이어그램에서는 임시 쿼리에 대한 SQL Server의 쿼리 처리 흐름을 간략하게 설명합니다.

SQL Server 쿼리 처리 파이프라인.
SQL Server 쿼리 처리 파이프라인.

이 시나리오에서는

  1. 사용자가 쿼리를 실행합니다.

  2. 파서 및 대수 변환기는 사용자가 제출한 Transact-SQL 텍스트를 기반으로 논리 연산자를 사용하여 쿼리 트리를 생성합니다.

  3. 최적화 프로그램은 물리 연산자(예: 중첩 루프 조인)를 포함하는 최적화된 쿼리 계획을 만듭니다. 최적화 후 계획은 계획 캐시에 저장될 수 있습니다. 계획 캐시에 이 쿼리에 대한 계획이 이미 포함되어 있으면 이 단계가 무시됩니다.

  4. 쿼리 실행 엔진은 쿼리 계획의 해석을 처리합니다.

  5. 각 인덱스 검색, 인덱스 스캔 및 테이블 검색 연산자에 대해 실행 엔진은 Access Methods에서 각 인덱스 및 테이블 구조의 행을 요청합니다.

  6. Access Methods는 버퍼 풀의 인덱스 및 데이터 페이지에서 행을 검색하고 필요에 따라 디스크에서 버퍼 풀로 페이지를 로드합니다.

첫 번째 예제 쿼리에서 실행 엔진은 Access Methods에서 Customer의 클러스터형 인덱스 및 Order의 비클러스터형 인덱스에 있는 행을 요청합니다. Access Methods는 B-트리 인덱스 구조를 트래버스하여 요청된 행을 검색합니다. 이 경우 계획에서 전체 인덱스 스캔을 호출할 때 모든 행이 검색됩니다.

참고 항목

설명서는 인덱스를 지칭할 때 B-트리라는 용어를 사용합니다. rowstore 인덱스에서 데이터베이스 엔진은 B+ 트리를 구현합니다. 이는 columnstore 인덱스나 메모리 최적화 테이블 인덱스에는 적용되지 않습니다. 자세한 내용은 SQL Server 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

메모리 최적화 테이블에 대한 해석된 Transact-SQL 액세스

Transact-SQL 임시 일괄 처리 및 저장 프로시저는 해석된 Transact-SQL이라고도 부릅니다. 해석된 쿼리 계획은 쿼리 계획의 각 연산자에 대한 쿼리 실행 엔진에 의해 해석된다는 사실을 나타냅니다. 실행 엔진은 연산자와 해당 매개 변수를 읽고 작업을 수행합니다.

해석된 Transact-SQL을 사용하여 메모리 최적화 테이블과 디스크 기반 테이블에 모두 액세스할 수 있습니다. 다음 그림에서는 메모리 최적화 테이블에 대한 해석된 Transact-SQL 액세스에 대한 쿼리 처리를 보여 줍니다.

해석된 tsql에 대한 쿼리 처리 파이프라인
메모리 최적화 테이블에 대한 해석된 Transact-SQL 액세스의 쿼리 처리 파이프라인

그림에서 설명한 것처럼 쿼리 처리 파이프라인은 대부분 변경되지 않습니다.

  • 파서 및 algebrizer가 쿼리 트리를 생성합니다.

  • 최적화 프로그램은 실행 계획을 만듭니다.

  • 쿼리 실행 엔진은 실행 계획을 해석합니다.

기존 쿼리 처리 파이프라인(그림 2)의 주요 차이점은 메모리 최적화 테이블의 행이 Access Methods를 사용하여 버퍼 풀에서 검색되지 않는다는 것입니다. 대신 메모리 내 데이터 구조에서 메모리 내 OLTP 엔진을 통해 행을 검색합니다. 데이터 구조의 차이점으로 인해 다음 예에 표시된 것처럼 경우에 따라 최적화 프로그램이 서로 다른 계획을 선택하게 됩니다.

다음 Transact-SQL 스크립트에는 해시 인덱스를 사용하는 Order 및 Customer 테이블의 메모리 최적화 버전이 포함되어 있습니다.

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO

메모리 최적화 테이블에서 실행되는 동일한 쿼리를 고려합니다.

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

예상 계획은 다음과 같습니다.

메모리 최적화 테이블의 조인에 대한 쿼리 계획
메모리 최적화 테이블의 조인에 대한 쿼리 계획

디스크 기반 테이블에서 동일한 쿼리에 대한 계획과 다음과 같은 차이점을 관찰합니다(그림 1).

  • 이 계획에는 Customer 테이블에 대한 클러스터형 인덱스 스캔 대신 테이블 검색이 포함됩니다.

    • 테이블 정의에는 클러스터형 인덱스가 포함되어 있지 않습니다.

    • 클러스터형 인덱스는 메모리 최적화 테이블에서 지원되지 않습니다. 대신 메모리 최적화 모든 테이블에 적어도 하나 이상의 비클러스터형 인덱스가 있어야 하고 메모리 최적화 테이블의 모든 인덱스는 인덱스에 행을 저장하거나 클러스터형 인덱스를 참조할 필요 없이 테이블의 모든 열에 효율적으로 액세스할 수 있습니다.

  • 이 계획에는 Merge Join이 아닌 Hash Match 항목이 포함됩니다. Order 테이블과 Customer 테이블의 인덱스는 해시 인덱스이므로 순서가 지정되지 않습니다. Merge Join 을 사용하기 위해서는 성능 저하가 발생하는 sort 연산자가 필요합니다.

고유하게 컴파일된 저장 프로시저

고유하게 컴파일된 저장 프로시저는 쿼리 실행 엔진에서 해석하지 않고 컴퓨터 코드로 컴파일된 Transact-SQL 저장 프로시저입니다. 다음 스크립트는 예제 쿼리(예제 쿼리 섹션 참조)를 실행하는 기본적으로 컴파일되는 저장 프로시저를 만듭니다.

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END

고유하게 컴파일된 저장 프로시저는 생성 시 컴파일되는 반면 해석된 저장 프로시저는 처음 실행 시 컴파일됩니다. (컴파일의 일부, 특히 구문 분석 및 대수화는 생성 시 수행됩니다. 그러나 해석된 저장 프로시저의 경우 쿼리 계획의 최적화는 첫 번째 실행 시 수행됩니다.) 다시 컴파일 논리는 비슷합니다. 서버를 다시 시작하는 경우 프로시저를 처음 실행할 때 고유하게 컴파일된 저장 프로시저가 다시 컴파일됩니다. 해석된 저장 프로시저는 계획 캐시에 계획이 더 이상 없는 경우에 다시 컴파일됩니다. 다음 표에서는 고유하게 컴파일된 저장 프로시저와 해석된 저장 프로시저 모두의 컴파일 및 다시 컴파일 사례를 요약해서 보여줍니다.

컴파일 형식 네이티브 컴파일 완료 해석됨
초기 컴파일 생성 시 처음 실행 시
자동 다시 컴파일 데이터베이스 또는 서버를 다시 시작한 후 프로시저를 처음 실행합니다. 서버를 다시 시작함 또는 일반적으로 스키마 또는 통계 변경이나 메모리 압력에 따라 계획 캐시에서 계획이 제거될 때
수동 다시 컴파일 sp_recompile을 사용합니다. sp_recompile을 사용합니다. 예를 들어 DBCC FREEPROCCACHE를 통해 캐시에서 계획을 수동으로 제거할 수 있습니다. 다시 컴파일로 저장 프로시저를 만들 수도 있으며 저장 프로시저는 실행될 때마다 다시 컴파일됩니다.

컴파일 및 쿼리 처리

다음 다이어그램에서는 고유하게 컴파일된 저장 프로시저에 대한 컴파일 프로세스를 보여 줍니다.

저장 프로시저의 고유 컴파일
저장 프로시저의 고유 컴파일

프로세스는 다음과 같이 설명됩니다.

  1. 사용자가 SQL Server에 CREATE PROCEDURE 문을 발행합니다.

  2. 파서 및 대수 변환기는 프로시저에 대한 처리 흐름과 저장 프로시저의 Transact-SQL 쿼리에 대한 쿼리 트리를 만듭니다.

  3. 최적화 프로그램은 저장 프로시저의 모든 쿼리에 대해 최적화된 쿼리 실행 계획을 만듭니다.

  4. 메모리 내 OLTP 컴파일러는 포함된 최적화된 쿼리 계획을 통해 처리 흐름을 사용하고 저장 프로시저를 실행하기 위한 컴퓨터 코드가 포함된 DLL을 생성합니다.

  5. 생성된 DLL이 메모리에 로드됩니다.

고유하게 컴파일된 저장 프로시저의 호출은 DLL의 함수 호출과 같습니다.

고유하게 컴파일된 저장 프로시저의 실행
고유하게 컴파일된 저장 프로시저의 실행

고유하게 컴파일된 저장 프로시저의 호출에 대한 설명은 다음과 같습니다.

  1. 사용자가 EXEC usp_myproc 문을 발행합니다.

  2. 파서는 이름 및 저장 프로시저 매개 변수를 추출합니다.

    예를 들어 sp_prep_exec를 사용하여 문을 준비한 경우 파서는 실행 시 프로시저 이름과 매개 변수를 추출할 필요가 없습니다.

  3. 메모리 내 OLTP 런타임은 저장 프로시저에 대한 DLL 진입점을 찾습니다.

  4. DLL의 컴퓨터 코드가 실행되고 결과가 클라이언트에 반환됩니다.

매개 변수 스니핑

해석된 Transact-SQL 저장 프로시저는 생성 시 컴파일되는 고유하게 컴파일된 저장 프로시저와 달리 처음 실행 시 컴파일됩니다. 해석된 저장 프로시저가 호출 시 컴파일되는 경우 이 호출에 제공된 매개 변수 값은 실행 계획을 생성할 때 최적화 프로그램에서 사용됩니다. 컴파일 중에 매개 변수를 사용하는 것을 매개 변수 스니핑이라고 합니다.

매개 변수 스니핑은 고유하게 컴파일된 저장 프로시저를 컴파일하는 데 사용되지 않습니다. 저장 프로시저에 대한 모든 매개 변수는 UNKNOWN 값을 갖는 것으로 간주됩니다. 해석된 저장 프로시저와 마찬가지로 고유하게 컴파일된 저장 프로시저도 OPTIMIZE FOR 힌트를 지원합니다. 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하세요.

고유하게 컴파일된 저장 프로시저에 대한 쿼리 실행 검색

고유하게 컴파일된 저장 프로시저에 대한 쿼리 실행 계획은 Management Studio에서 예상 실행 계획을 사용하거나 Transact-SQL에서 SHOWPLAN_XML 옵션을 사용하여 검색할 수 있습니다. 예시:

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO

쿼리 최적화 프로그램에서 생성되는 실행 계획은 트리의 노드 및 리프에 대한 쿼리 연산자가 포함된 트리로 구성됩니다. 트리의 구조는 연산자 간의 상호 작용(한 연산자에서 다른 연산자로의 행 흐름)을 결정합니다. SQL Server Management Studio의 그래픽 보기에서 흐름은 오른쪽에서 왼쪽으로 이동합니다. 예를 들어, 그림 1의 쿼리 계획에는 merge join 연산자에 행을 제공하는 2개의 index scan 연산자가 포함됩니다. Merge join 조인 연산자는 select 연산자에 행을 제공합니다. select 연산자가 마지막으로 클라이언트에 행을 반환합니다.

고유하게 컴파일된 저장 프로시저의 쿼리 연산자

다음 표에는 고유하게 컴파일된 저장 프로시저 내에서 지원되는 쿼리 연산자가 요약되어 있습니다.

연산자 샘플 쿼리 주의
SELECT SELECT OrderID FROM dbo.[Order]
INSERT INSERT dbo.Customer VALUES ('abc', 'def')
UPDATE UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
Delete DELETE dbo.Customer WHERE CustomerID='abc'
Compute Scalar SELECT OrderID+1 FROM dbo.[Order] 이 연산자는 내장 함수 및 형식 변환에 모두 사용됩니다. 고유하게 컴파일된 저장 프로시저 내에서 모든 함수 및 형식 변환이 지원되는 것은 아닙니다.
중첩 루프 조인 SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c Nested Loops는 고유하게 컴파일된 저장 프로시저에서 지원되는 유일한 조인 연산자입니다. 해석된 Transact-SQL로 실행된 것과 동일한 쿼리에 대한 계획에 해시 또는 병합 조인이 포함되더라도 조인을 포함하는 모든 계획에는 Nested Loops 연산자가 사용됩니다.
정렬 SELECT ContactName FROM dbo.Customer ORDER BY ContactName
위 SELECT TOP 10 ContactName FROM dbo.Customer
최상위 정렬 SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName TOP 식(반환할 행의 수)은 8,000행을 초과할 수 없습니다. 쿼리에 join 및 aggregation 연산자도 있는 경우 더 적습니다. join 및 aggregation은 일반적으로 기본 테이블의 행 수에 비해 정렬할 행 수를 줄입니다.
Stream Aggregate SELECT count(CustomerID) FROM dbo.Customer Hash Match 연산자는 집계에 지원되지 않습니다. 따라서 해석된 Transact-SQL에서 동일한 쿼리에 대한 계획이 Hash Match 연산자를 사용하는 경우에도 고유하게 컴파일된 저장 프로시저의 모든 집계는 Stream Aggregate 연산자를 사용합니다.

열 통계 및 조인

SQL Server는 인덱스 스캔 및 인덱스 검색과 같은 특정 작업의 비용을 예측하는 데 도움이 되도록 인덱스 키 열의 값에 대한 통계를 유지 관리합니다. (SQL Server는 인덱스가 아닌 키 열을 명시적으로 만들거나 쿼리 최적화 프로그램에서 조건자가 있는 쿼리에 대한 응답으로 해당 열을 만드는 경우에도 비인덱스 키 열에 대한 통계를 만듭니다.) 비용 예측의 주요 메트릭은 단일 연산자가 처리하는 행의 수입니다. 디스크 기반 테이블의 경우 특정 운영자가 액세스하는 페이지 수가 비용 예측에서 중요합니다. 그러나 메모리 최적화 테이블(항상 0)에는 페이지 수가 중요하지 않으므로 이 토론은 행 수에 중점을 둡니다. 예측은 계획에서 index seek 및 scan 연산자로 시작한 다음 join 연산자와 같은 다른 연산자를 포함하도록 확장됩니다. Join 연산자가 처리할 예측 행 수는 기본 index, seek, scan 연산자에 대한 예측을 기반으로 합니다. 메모리 최적화 테이블에 대한 해석된 Transact-SQL 액세스의 경우 실제 실행 계획을 관찰하면 계획에서 연산자에 대한 예측 및 실제 행 수 간의 차이를 볼 수 있습니다.

그림 1의 예제에서

  • Customer에 대한 클러스터형 인덱스 검색의 예측은 91이고 실제도 91입니다.
  • CustomerID에 대한 비클러스터형 인덱스 검색의 예측은 830이고 실제도 830입니다.
  • Merge Join 연산자의 예측은 815이고 실제는 830입니다.

인덱스 검색에 대한 예측은 정확합니다. SQL Server는 디스크 기반 테이블에 대한 행 수를 유지 관리합니다. 전체 테이블 및 인덱스 검색에 대한 예측은 항상 정확합니다. 조인에 대한 예측도 상당히 정확한 편입니다.

이러한 예측이 변경되면 다른 계획 대안에 대한 비용 고려 사항도 변경됩니다. 예를 들어 조인의 측면 중 하나에 예측 행 수가 1개 또는 몇 개만 있는 경우 중첩 루프 조인을 사용하면 비용이 적게 듭니다. 다음과 같은 쿼리를 고려해 보세요.

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

Customer 테이블에서 행 1개를 제외한 모든 행을 삭제하면 다음 쿼리 계획이 생성됩니다.

열 통계 및 조인.

이 쿼리 계획과 관련하여 다음을 수행했습니다.

  • Hash Match가 중첩 루프 물리 join 연산자로 대체되었습니다.
  • IX_CustomerID 전체 인덱스 스캔이 인덱스 검색으로 대체되었습니다. 그 결과 전체 인덱스 검색에 필요한 830개 행 대신 5개 행이 검색되었습니다.

참고 항목

메모리 최적화 테이블