Resolver a contenção de PAGELATCH_EX de inserção de última página no SQL Server

Artigo
06/15/2023

Versão original do produto: SQL Server
Número de KB original: 4460004

Este artigo apresenta como resolve contenção de inserção PAGELATCH_EX de última página no SQL Server.

Sintomas

Considere as seguintes situações:

Você tem uma coluna que inclui valores sequenciais, como uma coluna Identity ou uma coluna DateTime que é inserida por meio da função Getdate( ).
Você tem um índice clusterizado que tem a coluna sequencial como uma coluna líder.

Observação

O cenário mais comum é uma chave primária clusterada em uma coluna Identity. Com menos frequência, esse problema pode ser observado para índices não clusterizados.
Seu aplicativo faz operações INSERT ou UPDATE frequentes na tabela.
Você tem muitas CPUs no sistema. Normalmente, o servidor tem 16 CPUs ou mais. Essa configuração de hardware permite que várias sessões façam as operações INSERT na mesma tabela simultaneamente.

Nessa situação, você pode sofrer uma diminuição no desempenho do seu aplicativo. Ao examinar os tipos de espera no sys.dm_exec_requests, você observa esperas no tipo de espera PAGELATCH_EX e em muitas sessões que estão aguardando esse tipo de espera.

Outro problema ocorrerá se você executar a seguinte consulta de diagnóstico em seu sistema:

selecione session_id, wait_type, wait_time, wait_resource de sys.dm_exec_requests onde session_id > 50 e wait_type = 'pagelatch_ex'

Nesta situação, você pode obter resultados que se assemelham aos seguintes.

Session_id	wait_type	wait_time	wait_resource
60	PAGELATCH_EX	100	5:1:4144
75	PAGELATCH_EX	123	5:1:4144
79	PAGELATCH_EX	401	5:1:4144
80	PAGELATCH_EX	253	5:1:4144
81	PAGELATCH_EX	312	5:1:4144
82	PAGELATCH_EX	355	5:1:4144
84	PAGELATCH_EX	312	5:1:4144
85	PAGELATCH_EX	338	5:1:4144
87	PAGELATCH_EX	405	5:1:4144
88	PAGELATCH_EX	111	5:1:4144
90	PAGELATCH_EX	38	5:1:4144
92	PAGELATCH_EX	115	5:1:4144
94	PAGELATCH_EX	49	5:1:4144
101	PAGELATCH_EX	301	5:1:4144
102	PAGELATCH_EX	45	5:1:4144
103	PAGELATCH_EX	515	5:1:4144
105	PAGELATCH_EX	39	5:1:4144

Você observa que várias sessões estão todas aguardando o mesmo recurso que se assemelha ao seguinte padrão:

database_id = 5, file_id = 1, page_id de banco de dados = 4144

Observação

O database_id deve ser um banco de dados de usuário (o número de ID é maior ou igual a 5). Se o database_id for 2, você poderá, em vez disso, estar enfrentando o problema que é discutido em Arquivos, rastrear sinalizadores e atualizações no TEMPDB.

Motivo

PAGELATCH (trava em uma página de dados ou índice) é um mecanismo de sincronização de thread. Ele é usado para sincronizar o acesso físico de curto prazo às páginas de banco de dados localizadas no cache buffer.

PAGELATCH difere de um PAGEIOLATCH. Este último é usado para sincronizar o acesso físico às páginas quando são lidas ou gravadas em disco.

As travas de página são comuns em todos os sistemas porque garantem a proteção de página física. Um índice clusterizado ordena os dados pela coluna de chave líder. Por esse motivo, quando você cria o índice em uma coluna sequencial, todas as novas inserções de dados ocorrem na mesma página no final do índice até que essa página seja preenchida. No entanto, sob alta carga, as operações INSERT simultâneas podem causar contenção na última página da árvore B. Essa contenção pode ocorrer em índices clusterizados e não clusterizados. O motivo é que índices nãocluídos ordenam as páginas no nível da folha pela chave principal. Esse problema também é conhecido como contenção de inserção de última página.

Para obter mais informações, consulte Diagnosticar e resolver a contenção de travas no SQL Server.

Resolução

Você pode escolher uma das duas opções a seguir para resolve o problema.

Opção 1: executar as etapas diretamente em um notebook por meio do Azure Data Studio

Observação

Antes de tentar abrir este notebook, verifique se o Azure Data Studio está instalado no computador local. Para instalá-lo, acesse Saiba como instalar o Azure Data Studio.

Abrir Notebook no Azure Data Studio

Opção 2: siga as etapas manualmente

Para resolve essa contenção, a estratégia geral é impedir que todas as operações INSERT simultâneas acessem a mesma página de banco de dados. Em vez disso, faça com que cada operação INSERT acesse uma página diferente e aumente a simultaneidade. Portanto, qualquer um dos métodos a seguir que organizam os dados por uma coluna diferente da coluna sequencial atinge essa meta.

1. Confirme a contenção no PAGELATCH_EX e identifique o recurso de contenção

Esse script T-SQL ajuda você a descobrir se há PAGELATCH_EX esperas no sistema com várias sessões (5 ou mais) com tempo de espera significativo (10 ms ou mais). Ele também ajuda você a descobrir qual objeto e indexar a contenção está usando sys.dm_exec_requests e DBCC PAGE ou sys.fn_PageResCracker e sys.dm_db_page_info (somente 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. Escolha um método para resolve o problema

Você pode usar um dos métodos a seguir para resolve o problema. Escolha aquele que melhor se encaixe em suas circunstâncias.

Método 1: usar OPTIMIZE_FOR_SEQUENTIAL_KEY opção de índice (somente SQL Server 2019)

No SQL Server 2019, foi adicionada uma nova opção de índice (OPTIMIZE_FOR_SEQUENTIAL_KEY) que pode ajudar a resolve esse problema sem usar nenhum dos métodos a seguir. Consulte Bastidores no OPTIMIZE_FOR_SEQUENTIAL_KEY para obter mais informações.

Método 2: mover a chave primária para fora da coluna de identidade

Faça da coluna que contém valores sequenciais um índice nãocluso e, em seguida, mova o índice clusterizado para outra coluna. Por exemplo, para uma chave primária em uma coluna de identidade, remova a chave primária clusterada e, em seguida, recrie-a como uma chave primária não recclusa. Esse método é o seguinte mais fácil e atinge diretamente o objetivo.

Por exemplo, suponha que você tenha a tabela a seguir definida usando uma chave primária clusterada em uma coluna Identity.

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 );

Para alterar esse design, você pode remover o índice de chave primária e redefini-lo.

USE testdb;

ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;

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

Método 3: tornar a chave líder uma coluna não sequencial

Reordene a definição de índice clusterizado de forma que a coluna líder não seja a coluna sequencial. Esse método exige que o índice clusterizado seja um índice composto. Por exemplo, em uma tabela do cliente, você pode fazer com que uma coluna CustomerLastName seja a coluna líder, seguida pelo CustomerID. Recomendamos que você teste minuciosamente esse método para garantir que ele atenda aos requisitos de desempenho.

USE testdb;

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

Método 4: Adicionar um valor não sequencial como uma chave líder

Adicione um valor de hash não sequencial como a chave de índice líder. Essa técnica também ajuda a espalhar as inserções. Um valor de hash é gerado como um modulo que corresponde ao número de CPUs no sistema. Por exemplo, em um sistema de 16 CPU, você pode usar um modulo de 16. Esse método espalha uniformemente as operações INSERT em várias páginas de banco de dados.

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);

Método 5: usar um GUID como uma chave líder

Use um GUID como a coluna de chave líder de um índice para garantir a distribuição uniforme de inserções.

Observação

Embora atinja a meta, não recomendamos esse método porque ele apresenta vários desafios, incluindo uma chave de índice grande, divisões de página frequentes, baixa densidade de página e assim por diante.

Método 6: usar particionamento de tabela e uma coluna computada com um valor de hash

Use particionamento de tabela e uma coluna computada que tenha um valor de hash para espalhar as operações INSERT. Como esse método usa particionamento de tabela, ele só pode ser usado em edições enterprise de SQL Server.

Observação

Você pode usar tabelas particionadas no SQL Server 2016 SP1 Standard Edition. Para obter mais informações, confira a descrição de "Particionamento de tabela e índice" no artigo Edições e recursos com suporte de SQL Server 2016.

A seguir está um exemplo em um sistema que tem 16 CPUs.

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);

Método 7: alternar para In-Memory OLTP

Como alternativa, use In-Memory OLTP especialmente se a contenção de trava for alta. Essa tecnologia elimina a contenção de trava geral. No entanto, você precisa reprojetar e migrar as tabelas específicas, onde a contenção da trava de página é observada, para uma tabela com otimização de memória. Você pode usar o Assistente de Otimização de Memória e o Relatório de Análise de Desempenho de Transações para determinar se a migração é possível e qual seria o esforço para fazer a migração. Para obter mais informações sobre como In-Memory OLTP elimina a contenção de travas, baixe e examine o documento em OLTP na Memória – Padrões comuns de carga de trabalho e considerações de migração.

Referências

PAGELATCH_EX esperas e inserções pesadas

Compartilhar via

Resolver a contenção de PAGELATCH_EX de inserção de última página no SQL Server

Sintomas

Motivo

Resolução

Opção 1: executar as etapas diretamente em um notebook por meio do Azure Data Studio

Opção 2: siga as etapas manualmente

1. Confirme a contenção no PAGELATCH_EX e identifique o recurso de contenção

2. Escolha um método para resolve o problema

Método 1: usar OPTIMIZE_FOR_SEQUENTIAL_KEY opção de índice (somente SQL Server 2019)

Método 2: mover a chave primária para fora da coluna de identidade

Método 3: tornar a chave líder uma coluna não sequencial

Método 4: Adicionar um valor não sequencial como uma chave líder

Método 5: usar um GUID como uma chave líder

Método 6: usar particionamento de tabela e uma coluna computada com um valor de hash

Método 7: alternar para In-Memory OLTP

Referências

Comentários

Recursos adicionais