Rozwiązywanie rywalizacji PAGELATCH_EX wstawiania ostatniej strony w SQL Server
Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 4460004
W tym artykule przedstawiono sposób rozwiązywania problemów z rywalizacją o wstawianie PAGELATCH_EX na ostatniej stronie w SQL Server.
Symptomy
Rozważ następujące scenariusze:
Masz kolumnę zawierającą wartości sekwencyjne, takie jak kolumna Tożsamość lub kolumna DateTime wstawiona za pośrednictwem funkcji Getdate( ).
Masz indeks klastrowany, który ma kolumnę sekwencyjną jako kolumnę wiodącą.
Uwaga
Najbardziej typowym scenariuszem jest klastrowany klucz podstawowy w kolumnie Tożsamość. Rzadziej ten problem można zaobserwować w przypadku indeksów nieklastruowanych.
Aplikacja wykonuje częste operacje INSERT lub UPDATE względem tabeli.
W systemie jest wiele procesorów CPU. Zazwyczaj serwer ma co najmniej 16 procesorów CPU. Ta konfiguracja sprzętu umożliwia jednoczesne wykonywanie operacji INSERT w tej samej tabeli w wielu sesjach.
W takiej sytuacji może wystąpić spadek wydajności aplikacji. Podczas sprawdzania typów oczekiwania w sys.dm_exec_requestsprogramie obserwujesz oczekiwania na typ oczekiwania PAGELATCH_EX i wiele sesji oczekujących na ten typ oczekiwania.
Inny problem występuje, jeśli w systemie zostanie uruchomione następujące zapytanie diagnostyczne:
wybierz session_id, wait_type, wait_time, wait_resource, z sys.dm_exec_requests których session_id > 50 i wait_type = "pagelatch_ex"
W takiej sytuacji mogą zostać wyświetlone wyniki podobne do następujących.
| 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 |
Można zauważyć, że wiele sesji czeka na ten sam zasób podobny do następującego wzorca:
database_id = 5, file_id = 1, baza danych page_id = 4144
Uwaga
Database_id powinna być bazą danych użytkownika (numer identyfikatora jest większy lub równy 5). Jeśli database_id to 2, zamiast tego może wystąpić problem omówiony w temacie Pliki, flagi śledzenia i aktualizacje bazy danych TEMPDB.
Przyczyna
PAGELATCH (zatrzask na stronie danych lub indeksu) to mechanizm synchronizacji wątków. Służy do synchronizowania krótkoterminowego dostępu fizycznego 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, gdy są one odczytywane z dysku lub zapisywane na dysku.
Zatrzaśnienia stron są powszechne w każdym systemie, ponieważ zapewniają fizyczną ochronę strony. Indeks klastrowany porządkuje dane według kolumny klucza wiodącego. Z tego powodu podczas tworzenia indeksu w kolumnie sekwencyjnej wszystkie nowe operacje wstawiania danych występują na tej samej stronie na końcu indeksu, dopóki ta strona nie zostanie wypełniona. Jednak w przypadku dużego obciążenia współbieżne operacje INSERT mogą powodować rywalizację na ostatniej stronie drzewa B. Ta rywalizacja może wystąpić w przypadku indeksów klastrowanych i nieklastruowanych. Powodem jest to, że indeksy nieklastryzowane porządkują strony na poziomie liścia według klucza wiodącego. Ten problem jest również znany jako rywalizacja o wstawianie na ostatniej stronie.
Aby uzyskać więcej informacji, zobacz Diagnosing and Resolving Latch Contention on SQL Server (Diagnozowanie i rozwiązywanie rywalizacji o zatrzaski w 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 usługi Azure Data Studio
Uwaga
Przed podjęciem próby otwarcia tego notesu upewnij się, że usługa Azure Data Studio jest zainstalowana na komputerze lokalnym. Aby go zainstalować, przejdź do artykułu Dowiedz się, jak zainstalować usługę Azure Data Studio.
Opcja 2. Wykonaj kroki ręcznie
Aby rozwiązać ten problem, ogólną strategią jest uniemożliwienie wszystkim współbieżnym operacjom INSERT uzyskiwania dostępu do tej samej strony bazy danych. Zamiast tego należy sprawić, by 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 następujących metod, które organizują 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 ustalić, czy w systemie czeka PAGELATCH_EX wiele sesji (co najmniej 5) ze znacznym czasem oczekiwania (10 ms lub więcej). Pomaga również w odnalezieniu 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 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 tę, która najlepiej pasuje do Twoich okoliczności.
Metoda 1. Użyj opcji indeksu OPTIMIZE_FOR_SEQUENTIAL_KEY (tylko SQL Server 2019 r.)
W SQL Server 2019 r. dodano nową opcję indeksu (OPTIMIZE_FOR_SEQUENTIAL_KEY), która może pomóc rozwiązać ten problem bez użycia żadnej z następujących metod. Aby uzyskać więcej informacji , zobacz Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY (Za kulisami na OPTIMIZE_FOR_SEQUENTIAL_KEY ).
Metoda 2. Przenoszenie klucza podstawowego poza kolumnę tożsamości
Ustaw kolumnę zawierającą wartości sekwencyjne jako indeks nieklastruowany, a następnie przenieś indeks klastrowany do innej kolumny. Na przykład w przypadku klucza podstawowego w kolumnie tożsamości usuń klastrowany klucz podstawowy, a następnie utwórz go ponownie jako klucz podstawowy nieklastruowany. 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 go zdefiniować.
USE testdb;
ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;
ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY NONCLUSTERED (CustomerID)
Metoda 3. Ustaw klucz wiodący jako kolumnę niesekwencyjną
Zmień kolejność definicji indeksu klastrowanego w taki sposób, aby kolumna wiodąca nie była kolumną sekwencyjną. Ta metoda wymaga, aby indeks klastrowany był indeksem złożonym. Na przykład w tabeli klienta można utworzyć 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 niesekwencyjną wartość skrótu jako wiodący klucz indeksu. Ta technika pomaga również rozłożyć wstawki. Wartość skrótu jest generowana jako modulo zgodne z liczbą procesorów CPU w systemie. Na przykład w systemie 16-CPU można użyć modulo 16. Ta metoda równomiernie rozłoża operacje INSERT 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 wstawień.
Uwaga
Mimo że cel jest osiągany, 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 tabel i obliczonej kolumny, która ma 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 SQL Server.
Uwaga
Tabel partycjonowanych można używać w wersji standardowej SQL Server 2016 SP1. Aby uzyskać więcej informacji, zobacz opis "Partycjonowanie tabel i indeksów" w artykule Wersje i obsługiwane funkcje SQL Server 2016.
Poniżej przedstawiono przykład w systemie, który ma 16 procesorów 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 do In-Memory OLTP
Alternatywnie należy użyć In-Memory OLTP, szczególnie jeśli rywalizacja o zatrzaśnięcie jest wysoka. Ta technologia eliminuje ogólną rywalizację o zatrzaski. Należy jednak przeprojektować i zmigrować określone tabele, w których zaobserwowano rywalizację o zatrzaśnięcie strony, do tabeli zoptymalizowanej pod kątem pamięci. Aby określić, czy migracja jest możliwa i jaka będzie próba migracji, można użyć doradcy optymalizacji pamięci i raportu analizy wydajności transakcji . Aby uzyskać więcej informacji na temat sposobu, w jaki In-Memory OLTP eliminuje rywalizację o zatrzaski, pobierz i przejrzyj dokument w temacie OLTP w pamięci — typowe wzorce obciążeń i zagadnienia dotyczące migracji.
Informacje
Opinia
Dostępne już wkrótce: W 2024 r. będziemy stopniowo wycofywać zgłoszenia z serwisu GitHub jako mechanizm przesyłania opinii na temat zawartości i zastępować go nowym systemem opinii. Aby uzyskać więcej informacji, sprawdź: https://aka.ms/ContentUserFeedback.
Prześlij i wyświetl opinię dla