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

Artigo
12/14/2024

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

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

Sintomas

Considere os seguintes cenário:

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

Observação

O cenário mais comum é uma chave primária clusterizada em uma coluna Identidade. 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 experimentar 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 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'

Nessa situação, você pode obter resultados semelhantes 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 aguardando o mesmo recurso que se assemelha ao seguinte padrão:

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

Observação

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

Causa

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

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

As travas de página são comuns em todos os sistemas porque garantem a proteção física da página. Um índice clusterizado ordena os dados pela coluna de chave à esquerda. 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. Mas, com carga alta, as operações INSERT simultâneas podem levar à contenção na última página da árvore B. Essa contenção pode ocorrer em índices clusterizados e não clusterizados. Isso ocorre porque os índices não clusterizados ordenam as páginas em nível de folha pela chave à esquerda. Esse problema também é conhecido como contenção de inserção de última página.

Para obter mais informações, consulte Diagnosticando e resolvendo a contenção de trava no SQL Server.

Solução

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

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

Observação

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

Abrir o notebook no Azure Data Studio

Opção 2: siga as etapas manualmente

Para resolver essa contenção, a estratégia geral é impedir que todas as operações INSERT simultâneas acessem a mesma página do 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 esse objetivo.

1. Confirme a contenção em 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 em qual objeto e índice 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 resolver o problema

Você pode usar um dos seguintes métodos para resolver o problema. Escolha o que melhor se adapta às 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 resolver 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 da coluna de identidade

Transforme a coluna que contém valores sequenciais em um índice não clusterizado e mova o índice clusterizado para outra coluna. Por exemplo, para uma chave primária em uma coluna de identidade, remova a chave primária clusterizada e recrie-a como uma chave primária não clusterizada. Este método é o mais fácil de seguir e atinge diretamente o objetivo.

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

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: Transformar a chave principal em uma coluna não sequencial

Reordene a definição de índice clusterizado de forma que a coluna à esquerda não seja a coluna sequencial. Esse método requer que o índice clusterizado seja um índice composto. Por exemplo, em uma tabela de clientes, você pode fazer com que uma coluna CustomerLastName seja a coluna principal, seguida pela CustomerID. Recomendamos que você teste completamente 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 chave principal

Adicione um valor de hash não sequencial como a chave de índice principal. Essa técnica também ajuda a espalhar as inserções. Um valor de hash é gerado como um módulo que corresponde ao número de CPUs no sistema. Por exemplo, em um sistema de 16 CPUs, você pode usar um módulo de 16. Esse método distribui as operações INSERT uniformemente em várias páginas do 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 chave principal

Use um GUID como a coluna de chave à esquerda 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áginas e assim por diante.

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

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

Observação

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

Veja a seguir 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 OLTP na memória

Como alternativa, use o OLTP na memória, especialmente se a contenção de trava for alta. Essa tecnologia elimina a contenção geral da trava. No entanto, você precisa reprojetar e migrar as tabelas específicas, onde a contenção de trava de página é observada, para uma tabela com otimização de memória. Você pode usar o Supervisor de Otimização de Memória e o Relatório de Análise de Desempenho de Transação 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 o OLTP in-memory elimina a contenção de trava, baixe e examine o documento em OLTP in-memory - Common Workload Patterns and Migration Considerations.

Referências

PAGELATCH_EX esperas e inserções pesadas

Compartilhar via

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

Sintomas

Causa

Solução

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

Opção 2: siga as etapas manualmente

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

2. Escolha um método para resolver 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 da coluna de identidade

Método 3: Transformar a chave principal em uma coluna não sequencial

Método 4: Adicionar um valor não sequencial como chave principal

Método 5: Usar um GUID como chave principal

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

Método 7: Alternar para OLTP na memória

Referências

Comentários

Recursos adicionais