SQL Server에서 마지막 페이지 삽입 PAGELATCH_EX 경합 해결

원래 제품 버전: SQL Server
원래 KB 번호: 4460004

이 문서에서는 SQL Server에서 마지막 페이지 삽입 PAGELATCH_EX 경합을 해결하는 방법을 소개합니다.

증상

다음 시나리오를 고려하세요.

• Getdate() 함수를 통해 삽입되는 Id 열 또는 DateTime 열과 같은 순차적 값을 포함하는 열이 있습니다.

• 순차 열이 선행 열로 포함된 클러스터형 인덱스가 있습니다.

  참고 항목

  가장 일반적인 시나리오는 ID 열의 클러스터형 기본 키입니다. 비클러스터형 인덱스에 대해서는 이 문제를 자주 관찰할 수 없습니다.

• 애플리케이션은 테이블에 대해 INSERT 또는 UPDATE 작업을 자주 수행합니다.

• 시스템에 많은 CPU가 있습니다. 일반적으로 서버에는 16개 이상의 CPU가 있습니다. 이 하드웨어 구성을 사용하면 여러 세션에서 동일한 테이블에 대해 INSERT 작업을 동시에 수행할 수 있습니다.

이 경우 애플리케이션의 성능이 저하 될 수 있습니다. 대기 유형을 sys.dm_exec_requests검사할 때 PAGELATCH_EX 대기 유형 및 이 대기 유형에서 대기 중인 많은 세션에 대한 대기를 관찰합니다.

시스템에서 다음 진단 쿼리를 실행하는 경우 또 다른 문제가 발생합니다.

session_id 50 및 wait_type = 'pagelatch_ex'에서 sys.dm_exec_requests session_id, wait_type > , wait_time wait_resource 선택합니다.

이 경우 다음과 유사한 결과를 얻을 수 있습니다.

여러 세션이 모두 다음 패턴과 유사한 동일한 리소스를 기다리고 있음을 알 수 있습니다.

database_id = 5, file_id = 1, 데이터베이스 page_id = 4144

원인

PAGELATCH (데이터 또는 인덱스 페이지의 래치)는 스레드 동기화 메커니즘입니다. 버퍼 캐시에 있는 데이터베이스 페이지에 대한 단기 물리적 액세스를 동기화하는 데 사용됩니다.

PAGELATCH는 PAGEIOLATCH와 다릅니다. 후자는 디스크에서 읽거나 디스크에 쓸 때 페이지에 대한 물리적 액세스를 동기화하는 데 사용됩니다.

페이지 래치는 물리적 페이지 보호를 보장하기 때문에 모든 시스템에서 일반적입니다. 클러스터형 인덱스는 선행 키 열로 데이터를 정렬합니다. 따라서 순차 열에 인덱스를 만들 때 모든 새 데이터 삽입은 해당 페이지가 채워질 때까지 인덱스 끝의 동일한 페이지에서 발생합니다. 그러나 부하가 높은 상태에서 동시 INSERT 작업으로 인해 B-트리의 마지막 페이지에서 경합이 발생할 수 있습니다. 이 경합은 클러스터형 및 비클러스터형 인덱스에 발생할 수 있습니다. 그 이유는 비클러스터형 인덱스가 리프 수준 페이지를 선행 키로 정렬하기 때문입니다. 이 문제를 마지막 페이지 삽입 경합이라고도 합니다.

자세한 내용은 SQL Server에서 래치 경합 진단 및 해결을 참조 하세요.

해결

다음 두 가지 옵션 중 하나를 선택하여 문제를 해결할 수 있습니다.

옵션 1: Azure Data Studio를 통해 Notebook에서 직접 단계 실행

옵션 2: 수동으로 단계 수행

이 경합을 해결하기 위해 전체 전략은 모든 동시 INSERT 작업이 동일한 데이터베이스 페이지에 액세스하지 못하도록 하는 것입니다. 대신 각 INSERT 작업을 다른 페이지에 액세스하고 동시성을 높입니다. 따라서 순차 열 이외의 열로 데이터를 구성하는 다음 메서드 중에서 이 목표를 달성합니다.

1. PAGELATCH_EX 경합을 확인하고 경합 리소스를 식별합니다.

이 T-SQL 스크립트는 대기 시간이 10ms 이상인 여러 세션(5개 이상)이 있는 시스템에서 대기가 있는지 PAGELATCH_EX 를 검색하는 데 도움이 됩니다. 또한 sys.dm_exec_requests 및 DBCC PAGE 또는 sys.fn_PageResCracker 및 sys.dm_db_page_info 사용하여 경합이 있는 개체를 검색하고 인덱싱하는 데 도움이 됩니다(SQL Server 2019에만 해당).

SET NOCOUNT ON
DECLARE @dbname SYSNAME, @dbid INT, @objectid INT, @indexid INT, @indexname SYSNAME, @sql VARCHAR(8000), @manul_identification VARCHAR(8000)

IF (CONVERT(INT, SERVERPROPERTY('ProductMajorVersion')) >= 15)
BEGIN

    DROP TABLE IF EXISTS #PageLatchEXContention

    SELECT DB_NAME(page_info.database_id) DbName, r.db_id DbId, page_info.[object_id] ObjectId, page_info.index_id IndexId
    INTO #PageLatchEXContention
    FROM sys.dm_exec_requests AS er
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
        CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r
        CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info
    WHERE er.wait_type = 'PAGELATCH_EX' AND page_info.database_id not in (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
    GROUP BY DB_NAME(page_info.database_id), r.db_id, page_info.[object_id], page_info.index_id
    HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10

    SELECT * FROM #PageLatchEXContention
    IF EXISTS (SELECT 1 FROM #PageLatchEXContention)
    BEGIN
        DECLARE optimize_for_seq_key_cursor CURSOR FOR
            SELECT DbName, DbId, ObjectId, IndexId FROM #PageLatchEXContention
            
        OPEN optimize_for_seq_key_cursor
        FETCH NEXT FROM optimize_for_seq_key_cursor into @dbname, @dbid, @objectid , @indexid
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT 'Consider using below statement to enable OPTIMIZE_FOR_SEQUENTIAL_KEY for the indexes in the "' + @dbname + '" database' AS Recommendation
            SELECT @sql =  'select ''use ' + @dbname + '; ALTER INDEX '' + i.name + '' ON ' + OBJECT_NAME(@objectid, @dbid) + ' SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON )'' AS Corrective_Action from #PageLatchEXContention pl JOIN ' + @dbname+'.sys.indexes i ON pl.ObjectID = i.object_id WHERE object_id = ' + CONVERT(VARCHAR, @objectid) + ' AND index_id = ' + CONVERT(VARCHAR, @indexid)

            EXECUTE (@sql)
            FETCH NEXT FROM optimize_for_seq_key_cursor INTO @dbname, @dbid, @objectid , @indexid

        END

        CLOSE optimize_for_seq_key_cursor
        DEALLOCATE optimize_for_seq_key_cursor
    
    END
    ELSE
        SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'
END
ELSE
BEGIN
    
    IF OBJECT_ID('tempdb..#PageLatchEXContentionLegacy') IS NOT NULL
        DROP TABLE #PageLatchEXContentionLegacy
    
    SELECT 'dbcc traceon (3604); dbcc page(' + replace(wait_resource,':',',') + ',3); dbcc traceoff (3604)' TSQL_Command
    INTO #PageLatchEXContentionLegacy
    FROM sys.dm_exec_requests er
    WHERE er.wait_type = 'PAGELATCH_EX' AND er.database_id NOT IN (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
    GROUP BY wait_resource
    HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10

    SELECT * FROM #PageLatchEXContentionLegacy
    
    IF EXISTS(SELECT 1 FROM #PageLatchEXContentionLegacy)
    BEGIN
        SELECT 'On SQL Server 2017 or lower versions, you can manually identify the object where contention is occurring using DBCC PAGE locate the m_objId = ??. Then SELECT OBJECT_NAME(object_id_identified) and locate indexes with sequential values in this object' AS Recommendation
        
        DECLARE get_command CURSOR FOR
            SELECT TSQL_Command from #PageLatchEXContentionLegacy

        OPEN get_command
        FETCH NEXT FROM get_command into @sql
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @sql AS Step1_Run_This_Command_To_Find_Object
            SELECT 'select OBJECT_NAME(object_id_identified)' AS Step2_Find_Object_Name_From_ID
            FETCH NEXT FROM get_command INTO @sql
        END

        CLOSE get_command
        DEALLOCATE get_command

        SELECT 'Follow https://learn.microsoft.com/troubleshoot/sql/performance/resolve-pagelatch-ex-contention for resolution recommendations that fits your environment best' Step3_Apply_KB_article
        
    END
    ELSE
        SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'

END

2. 문제를 해결하는 방법 선택

다음 방법 중 하나를 사용하여 문제를 해결할 수 있습니다. 상황에 가장 적합한 것을 선택합니다.

방법 1: OPTIMIZE_FOR_SEQUENTIAL_KEY 인덱스 옵션 사용(SQL Server 2019에만 해당)

SQL Server 2019에서는 다음 방법 중 하나도 사용하지 않고 이 문제를 해결하는 데 도움이 되는 새 인덱스 옵션(OPTIMIZE_FOR_SEQUENTIAL_KEY)이 추가되었습니다. 자세한 내용은 OPTIMIZE_FOR_SEQUENTIAL_KEY 백그라운드를 참조하세요.

방법 2: ID 열에서 기본 키 이동

순차 값이 포함된 열을 비클러스터형 인덱스로 만들고 클러스터형 인덱스를 다른 열로 이동합니다. 예를 들어 ID 열의 기본 키에 대해 클러스터된 기본 키를 제거한 다음 비클러스터형 기본 키로 다시 만듭니다. 이 메서드는 가장 쉬운 팔로우이며 목표를 직접 달성합니다.

예를 들어 ID 열에 클러스터형 기본 키를 사용하여 정의된 다음 테이블이 있다고 가정합니다.

USE testdb;

CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );

이 디자인을 변경하려면 기본 키 인덱스 제거 및 다시 정의할 수 있습니다.

USE testdb;

ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;

ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY NONCLUSTERED (CustomerID)

방법 3: 선행 키를 비순차 열로 만들기

선행 열이 순차 열이 아닌 방식으로 클러스터형 인덱스 정의의 순서를 다시 지정합니다. 이 메서드를 사용하려면 클러스터형 인덱스가 복합 인덱스입니다. 예를 들어 고객 테이블에서 CustomerLastName 열을 선행 열로, 그 뒤에 CustomerID를 지정할 수 있습니다. 이 메서드를 철저히 테스트하여 성능 요구 사항을 충족하는지 확인하는 것이 좋습니다.

USE testdb;

ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY CLUSTERED (CustomerLastName, CustomerID)

메서드 4: 순차적이지 않은 값을 선행 키로 추가

순차적이지 않은 해시 값을 선행 인덱스 키로 추가합니다. 이 기술은 삽입을 분산하는 데도 도움이 됩니다. 해시 값은 시스템의 CPU 수와 일치하는 모듈로로 생성됩니다. 예를 들어 16-CPU 시스템에서는 16의 모듈로를 사용할 수 있습니다. 이 메서드는 여러 데이터베이스 페이지에 대해 INSERT 작업을 균일하게 분산합니다.

USE testdb;

CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );

ALTER TABLE Customers
ADD [HashValue] AS (CONVERT([TINYINT], abs([CustomerID])%16)) PERSISTED NOT NULL;

ALTER TABLE Customers
ADD CONSTRAINT pk_table1
PRIMARY KEY CLUSTERED (HashValue, CustomerID);

방법 5: GUID를 선행 키로 사용

GUID를 인덱스의 선행 키 열로 사용하여 삽입의 균일한 분포를 보장합니다.

방법 6: 해시 값과 함께 테이블 분할 및 계산 열 사용

테이블 분할 및 해시 값이 있는 계산 열을 사용하여 INSERT 작업을 분산합니다. 이 메서드는 테이블 분할을 사용하므로 SQL Server의 Enterprise 버전에서만 사용할 수 있습니다.

다음은 CPU가 16개인 시스템의 예입니다.

USE testdb;

CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );

ALTER TABLE Customers
ADD [HashID] AS CONVERT(TINYINT, ABS(CustomerID % 16)) PERSISTED NOT NULL;

CREATE PARTITION FUNCTION pf_hash (TINYINT) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ;

CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY]);

CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON Customers (CustomerID, HashID) ON ps_hash(HashID);

방법 7: 메모리 내 OLTP로 전환

또는 특히 래치 경합이 높은 경우 메모리 내 OLTP를 사용합니다. 이 기술은 래치 경합을 전반적으로 제거합니다. 그러나 페이지 래치 경합이 관찰되는 특정 테이블을 메모리 최적화 테이블로 다시 디자인하고 마이그레이션해야 합니다. 메모리 최적화 관리자 및 트랜잭션 성능 분석 보고서를 사용하여 마이그레이션이 가능한지 여부와 마이그레이션을 수행할 작업을 결정할 수 있습니다. 메모리 내 OLTP가 래치 경합을 제거하는 방법에 대한 자세한 내용은 메모리 내 OLTP - 일반적인 워크로드 패턴 및 마이그레이션 고려 사항에서 문서를 다운로드하여 검토합니다.

참조

PAGELATCH_EX 대기 및 무거운 삽입