Resolver a contenção de PAGELATCH_EX de inserção de última página no SQL Server
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.
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
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de