Rozwiązywanie problemu ze wstawianiem ostatniej strony PAGELATCH_EX w programie SQL Server
Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 4460004
W tym artykule przedstawiono sposób rozwiązywania rywalizacji o wstawianie PAGELATCH_EX ostatniej strony w programie SQL Server.
Symptomy
Rozważ następujące scenariusze:
Masz kolumnę zawierającą wartości sekwencyjne, takie jak kolumna Identity lub kolumna DateTime wstawiona za pośrednictwem funkcji Getdate().
Masz indeks klastrowany, który ma kolumnę sekwencyjną jako kolumnę wiodącą.
Uwaga 16.
Najbardziej typowym scenariuszem jest klasterowany klucz podstawowy w kolumnie Tożsamość. Rzadziej ten problem można zaobserwować w przypadku indeksów nieklastrowanych.
Aplikacja często wykonuje operacje INSERT lub UPDATE względem tabeli.
Masz wiele procesorów CPU w systemie. Zazwyczaj serwer ma co najmniej 16 procesorów CPU. Ta konfiguracja sprzętowa umożliwia wykonywanie wielu sesji operacji INSERT względem tej samej tabeli jednocześnie.
W takiej sytuacji może wystąpić spadek wydajności aplikacji. Podczas sprawdzania typów oczekiwania w programie sys.dm_exec_requestsobserwujesz oczekiwania na typ oczekiwania PAGELATCH_EX i wiele sesji oczekujących na ten typ oczekiwania.
Inny problem występuje w przypadku uruchomienia następującego zapytania diagnostycznego w systemie:
wybierz session_id, wait_type, wait_time, wait_resource gdzie sys.dm_exec_requests session_id > 50 i wait_type = "pagelatch_ex"
W takiej sytuacji mogą pojawić się wyniki podobne do poniższych.
| 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 | 210 | 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 |
Zauważysz, że wiele sesji oczekuje na ten sam zasób podobny do następującego wzorca:
database_id = 5, file_id = 1, page_id bazy danych = 4144
Uwaga 16.
Database_id powinna być bazą danych użytkownika (liczba identyfikatorów jest większa lub równa 5). Jeśli database_id ma wartość 2, możesz zamiast tego napotkać problem omówiony w temacie Pliki, flagi śledzenia i aktualizacje w bazie danych TEMPDB.
Przyczyna
PAGELATCH (zatrzask na stronie danych lub indeksu) to mechanizm synchronizacji wątków. Służy do synchronizowania krótkoterminowego fizycznego dostępu do stron bazy danych znajdujących się w pamięci podręcznej buforu.
PAGELATCH różni się od PAGEIOLATCH. Ten ostatni służy do synchronizowania fizycznego dostępu do stron podczas ich odczytu lub zapisu na dysku.
Zatrzaski stron są powszechne w każdym systemie, ponieważ zapewniają ochronę strony fizycznej. Indeks klastrowany porządkuje dane według kolumny klucza wiodącego. Z tego powodu podczas tworzenia indeksu w kolumnie sekwencyjnej wszystkie nowe wstawki danych są wykonywane na tej samej stronie na końcu indeksu do momentu wypełnienia tej strony. Jednak w przypadku dużego obciążenia równoczesne operacje INSERT mogą powodować rywalizację na ostatniej stronie drzewa B. Ta rywalizacja może wystąpić w indeksach klastrowanych i nieklastrowanych. Przyczyną jest to, że indeksy nieklastrowane porządkują strony na poziomie liścia według klucza wiodącego. Ten problem jest również znany jako rywalizacja o wstawianie ostatniej strony.
Aby uzyskać więcej informacji, zobacz Diagnozowanie i rozwiązywanie rywalizacji o zatrzasanie w programie SQL Server.
Rozwiązanie
Aby rozwiązać ten problem, możesz wybrać jedną z następujących dwóch opcji.
Opcja 1. Wykonywanie kroków bezpośrednio w notesie za pośrednictwem programu Azure Data Studio
Uwaga 16.
Przed podjęciem próby otwarcia tego notesu upewnij się, że program Azure Data Studio jest zainstalowany na komputerze lokalnym. Aby go zainstalować, przejdź do artykułu Dowiedz się, jak zainstalować program Azure Data Studio.
Opcja 2. Wykonaj kroki ręcznie
Aby rozwiązać ten problem, ogólna strategia polega na zapobieganiu uzyskiwaniu dostępu do tej samej strony bazy danych przez wszystkie współbieżne operacje INSERT. Zamiast tego, aby każda operacja INSERT uzyskiwała dostęp do innej strony i zwiększała współbieżność. W związku z tym każda z poniższych metod organizujących dane według kolumny innej niż kolumna sekwencyjna osiąga ten cel.
1. Potwierdź rywalizację o PAGELATCH_EX i zidentyfikuj zasób rywalizacji
Ten skrypt języka T-SQL pomaga wykryć, czy PAGELATCH_EX w systemie występują oczekiwania z wieloma sesjami (co najmniej 5) ze znacznym czasem oczekiwania (10 ms lub więcej). Ułatwia to również odnajdywanie obiektu i indeksu rywalizacji przy użyciu sys.dm_exec_requests i DBCC PAGE lub sys.fn_PageResCracker i sys.dm_db_page_info (tylko program 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. Wybierz metodę, aby rozwiązać problem
Aby rozwiązać ten problem, możesz użyć jednej z następujących metod. Wybierz ten, który najlepiej pasuje do Twoich okoliczności.
Metoda 1. Użyj opcji indeksu OPTIMIZE_FOR_SEQUENTIAL_KEY (tylko program SQL Server 2019)
W programie SQL Server 2019 dodano nową opcję indeksu (OPTIMIZE_FOR_SEQUENTIAL_KEY), która może pomóc rozwiązać ten problem bez użycia żadnej z poniższych metod. Aby uzyskać więcej informacji, zobacz Temat Za kulisami OPTIMIZE_FOR_SEQUENTIAL_KEY .
Metoda 2. Przenoszenie klucza podstawowego z kolumny tożsamości
Ustaw kolumnę zawierającą wartości sekwencyjne jako indeks nieklastrowany, a następnie przenieś indeks klastrowany do innej kolumny. Na przykład w przypadku klucza podstawowego w kolumnie tożsamości usuń klasterowany klucz podstawowy, a następnie utwórz go ponownie jako nieklastrowany klucz podstawowy. Ta metoda jest najłatwiejsza i bezpośrednio osiąga cel.
Załóżmy na przykład, że masz następującą tabelę, która została zdefiniowana przy użyciu klastrowanego klucza podstawowego w kolumnie Tożsamość.
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 );
Aby zmienić ten projekt, możesz usunąć indeks klucza podstawowego i ponownie zdefiniować go.
USE testdb;
ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;
ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY NONCLUSTERED (CustomerID)
Metoda 3. Tworzenie klucza wiodącego jako kolumny niesekwencyjnej
Zmień kolejność definicji indeksu klastrowanego w taki sposób, że kolumna wiodąca nie jest kolumną sekwencyjną. Ta metoda wymaga, aby indeks klastrowany był indeksem złożonym. Na przykład w tabeli klienta możesz ustawić kolumnę CustomerLastName jako kolumnę wiodącą, a następnie identyfikator CustomerID. Zalecamy dokładne przetestowanie tej metody, aby upewnić się, że spełnia ona wymagania dotyczące wydajności.
USE testdb;
ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY CLUSTERED (CustomerLastName, CustomerID)
Metoda 4. Dodawanie wartości niesekwencyjnej jako klucza wiodącego
Dodaj niekwenicową wartość skrótu jako wiodący klucz indeksu. Ta technika pomaga również rozłożyć wstawki. Wartość skrótu jest generowana jako modulo, która odpowiada liczbie procesorów CPU w systemie. Na przykład w systemie 16 procesorów można użyć modulo 16. Ta metoda rozdziela operacje INSERT równomiernie na wiele stron bazy danych.
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);
Metoda 5. Używanie identyfikatora GUID jako klucza wiodącego
Użyj identyfikatora GUID jako kolumny klucza wiodącego indeksu, aby zapewnić jednolity rozkład wstawiania.
Uwaga 16.
Mimo że osiąga cel, nie zalecamy tej metody, ponieważ stanowi ona wiele wyzwań, w tym duży klucz indeksu, częste podziały stron, niską gęstość stron itd.
Metoda 6. Używanie partycjonowania tabel i obliczonej kolumny z wartością skrótu
Użyj partycjonowania tabeli i obliczonej kolumny zawierającej wartość skrótu, aby rozłożyć operacje INSERT. Ponieważ ta metoda używa partycjonowania tabel, można jej używać tylko w wersjach Enterprise programu SQL Server.
Uwaga 16.
Tabele podzielone na partycje można używać w programie SQL Server 2016 SP1 Standard Edition. Aby uzyskać więcej informacji, zobacz opis "Partycjonowanie tabel i indeksów" w artykule Wersje i obsługiwane funkcje programu SQL Server 2016.
Poniżej przedstawiono przykład systemu z 16 procesorami CPU.
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);
Metoda 7. Przełączanie na olTP w pamięci
Alternatywnie należy użyć olTP w pamięci, szczególnie jeśli rywalizacja o zatrzask jest wysoka. Ta technologia eliminuje ogólną rywalizację o zatrzasanie. Należy jednak przeprojektować i zmigrować określone tabele, w których zaobserwowano rywalizację o zatrzasanie strony, do tabeli zoptymalizowanej pod kątem pamięci. Możesz użyć doradcy optymalizacji pamięci i raportu analizy wydajności transakcji, aby określić, czy migracja jest możliwa i jaki będzie nakład pracy w celu przeprowadzenia migracji. Aby uzyskać więcej informacji na temat sposobu, w jaki olTP w pamięci eliminuje rywalizację o zatrzaśnięcia, pobierz i zapoznaj się z dokumentem w artykule In-Memory OLTP — typowe wzorce obciążeń i zagadnienia dotyczące migracji.