SQL Server'da son sayfa ekleme PAGELATCH_EX çekişme sorununu çözme
Özgün ürün sürümü: SQL Server
Özgün KB numarası: 4460004
Bu makalede, SQL Server'da son sayfa ekleme PAGELATCH_EX çekişmesi sorununun nasıl çözüleceğini açıklar.
Belirtiler
Aşağıdaki senaryoları göz önünde bulundurun:
Getdate() işlevi aracılığıyla eklenen Identity sütunu veya DateTime sütunu gibi sıralı değerler içeren bir sütuna sahipsiniz.
Sıralı sütunu baştaki sütun olarak içeren kümelenmiş bir dizininiz var.
Not
En yaygın senaryo, Bir Identity sütunundaki kümelenmiş birincil anahtardır. Daha az sıklıkta, bu sorun kümelenmemiş dizinler için gözlemlenebilir.
Uygulamanız tabloya karşı sık sık INSERT veya UPDATE işlemleri yapar.
Sistemde birçok CPU'nuz var. Genellikle sunucuda 16 CPU veya daha fazla cpu bulunur. Bu donanım yapılandırması, insert işlemlerini aynı tabloda eşzamanlı olarak birden çok oturumun gerçekleştirmesine olanak tanır.
Bu durumda, uygulamanızın performansında düşüş yaşayabilirsiniz. içinde sys.dm_exec_requestsbekleme türlerini incelediğinizde, PAGELATCH_EX bekleme türünde beklemeleri ve bu bekleme türünde bekleyen birçok oturumu gözlemlersiniz.
Sisteminizde aşağıdaki tanılama sorgusunu çalıştırırsanız başka bir sorun oluşur:
session_id, wait_type, wait_time, wait_resource sys.dm_exec_requests session_id 50 ve wait_type > = 'pagelatch_ex' seçeneğini belirleyin
Bu durumda, aşağıdakine benzer sonuçlar alabilirsiniz.
| 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 |
Birden çok oturumun aşağıdaki desene benzeyen aynı kaynağı beklediğini fark edeceksiniz:
database_id = 5, file_id = 1, veritabanı page_id = 4144
Not
database_id bir kullanıcı veritabanı olmalıdır (kimlik numarası 5'ten büyük veya buna eşit). database_id 2 ise, bunun yerine TEMPDB'deki Dosyalar, izleme bayrakları ve güncelleştirmeler bölümünde açıklanan sorunla karşılaşabilirsiniz.
Neden
PAGELATCH (veri veya dizin sayfasındaki mandal), bir iş parçacığı eşitleme mekanizmasıdır. Arabellek önbelleğinde bulunan veritabanı sayfalarına kısa süreli fiziksel erişimi eşitlemek için kullanılır.
PAGELATCH , PAGEIOLATCH'tan farklıdır. İkincisi, diskten okunan veya diske yazılan sayfalara fiziksel erişimi eşitlemek için kullanılır.
Sayfa mandalları her sistemde yaygındır çünkü fiziksel sayfa koruması sağlarlar. Kümelenmiş dizin, verileri baştaki anahtar sütununa göre sıralar. Bu nedenle, dizini sıralı bir sütunda oluşturduğunuzda, tüm yeni veri eklemeleri, o sayfa doldurulana kadar dizinin sonunda aynı sayfada gerçekleşir. Ancak, yüksek yük altında eşzamanlı INSERT işlemleri B ağacının son sayfasında çekişmelere neden olabilir. Bu çekişme kümelenmiş ve kümelenmemiş dizinlerde oluşabilir. Bunun nedeni, kümelenmemiş dizinlerin yaprak düzeyi sayfaları baştaki anahtara göre sıralamasıdır. Bu sorun, son sayfa ekleme çekişmesi olarak da bilinir.
Daha fazla bilgi için bkz. SQL Server'da Mandal Çekişmesi Tanılama ve Çözme.
Çözüm
Sorunu çözmek için aşağıdaki iki seçenek arasından birini belirleyebilirsiniz.
1. Seçenek: Azure Data Studio aracılığıyla adımları doğrudan bir not defterinde yürütme
Not
Bu not defterini açmayı denemeden önce yerel makinenizde Azure Data Studio'yu yüklediğinizden emin olun. Yüklemek için Azure Data Studio'yu yüklemeyi öğrenin bölümüne gidin.
2. Seçenek: Adımları el ile izleyin
Bu çekişme sorununu çözmek için genel strateji, tüm eşzamanlı INSERT işlemlerinin aynı veritabanı sayfasına erişmesini engellemektir. Bunun yerine, her INSERT işleminin farklı bir sayfaya erişmesini sağlayın ve eşzamanlılığı artırın. Bu nedenle, verileri sıralı sütun dışında bir sütuna göre düzenleyen aşağıdaki yöntemlerden herhangi biri bu hedefe ulaşır.
1. PAGELATCH_EX çekişmesi onaylayın ve çekişme kaynağını tanımlayın
Bu T-SQL betiği, sistemde önemli bekleme süresi (10 ms veya daha fazla) olan birden çok oturuma (5 veya daha fazla) sahip beklemeler olup olmadığını PAGELATCH_EX keşfetmenize yardımcı olur. Ayrıca, sys.dm_exec_requests ve DBCC PAGEveya sys.fn_PageResCracker ve sys.dm_db_page_info (yalnızca SQL Server 2019) kullanarak çekişmenin hangi nesne ve dizinde olduğunu bulmanıza yardımcı olur.
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. Sorunu çözmek için bir yöntem seçin
Sorunu çözmek için aşağıdaki yöntemlerden birini kullanabilirsiniz. Koşullarınıza en uygun olanı seçin.
Yöntem 1: OPTIMIZE_FOR_SEQUENTIAL_KEY dizin seçeneğini kullanın (yalnızca SQL Server 2019)
SQL Server 2019'da, aşağıdaki yöntemlerden herhangi birini kullanmadan bu sorunun çözülmesine yardımcı olabilecek yeni bir dizin seçeneği (OPTIMIZE_FOR_SEQUENTIAL_KEY) eklendi. Daha fazla bilgi için bkz. OPTIMIZE_FOR_SEQUENTIAL_KEY Sahne Arkası .
Yöntem 2: Birincil anahtarı kimlik sütunundan taşıma
Sıralı değerler içeren sütunu kümelenmemiş dizin yapın ve kümelenmiş dizini başka bir sütuna taşıyın. Örneğin, bir kimlik sütunundaki birincil anahtar için kümelenmiş birincil anahtarı kaldırın ve sonra kümelenmemiş birincil anahtar olarak yeniden oluşturun. Bu yöntem en kolay takip edilen yöntemdir ve hedefe doğrudan ulaşır.
Örneğin, bir Identity sütununda kümelenmiş birincil anahtar kullanılarak tanımlanan aşağıdaki tabloya sahip olduğunuzu varsayalım.
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 );
Bu tasarımı değiştirmek için birincil anahtar dizinini kaldırabilir ve yeniden tanımlayabilirsiniz.
USE testdb;
ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;
ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY NONCLUSTERED (CustomerID)
Yöntem 3: Baştaki anahtarı sıralı olmayan bir sütun yapma
Kümelenmiş dizin tanımını, baştaki sütun sıralı sütun olmayacak şekilde yeniden sıralar. Bu yöntem, kümelenmiş dizinin bileşik bir dizin olmasını gerektirir. Örneğin, bir müşteri tablosunda CustomerLastName sütununun baştaki sütun olmasını ve ardından CustomerID değerinin olmasını sağlayabilirsiniz. Performans gereksinimlerini karşıladığından emin olmak için bu yöntemi kapsamlı bir şekilde test etmenizi öneririz.
USE testdb;
ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY CLUSTERED (CustomerLastName, CustomerID)
Yöntem 4: Sıralı olmayan bir değeri öncü anahtar olarak ekleme
Baştaki dizin anahtarı olarak sıralı olmayan bir karma değer ekleyin. Bu teknik, eklemelerin yayılmasına da yardımcı olur. Karma değer, sistemdeki CPU sayısıyla eşleşen bir modül olarak oluşturulur. Örneğin, 16 CPU'lu bir sistemde 16 modulo kullanabilirsiniz. Bu yöntem, INSERT işlemlerini birden çok veritabanı sayfasında tekdüzen olarak yayar.
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);
Yöntem 5: Guid'i önde gelen anahtar olarak kullanma
Eklemelerin tekdüzen dağılımını güvence altına almak için bir dizinin önde gelen anahtar sütunu olarak GUID kullanın.
Not
Hedefe ulaşsa da, büyük bir dizin anahtarı, sık sayfa bölmeleri, düşük sayfa yoğunluğu vb. gibi birçok zorluk sunduğundan bu yöntemi önermeyiz.
Yöntem 6: Tablo bölümleme ve karma değer içeren hesaplanan sütun kullanma
INSERT işlemlerini yaymak için tablo bölümleme ve karma değeri olan hesaplanan bir sütun kullanın. Bu yöntem tablo bölümleme kullandığından, yalnızca SQL Server Enterprise sürümlerinde kullanılabilir.
Not
bölümlenmiş tabloları SQL Server 2016 SP1 Standard Edition'da kullanabilirsiniz. Daha fazla bilgi için SQL Server 2016'nın sürümleri ve desteklenen özellikleri makalesindeki "Tablo ve dizin bölümleme" açıklamasına bakın.
Aşağıda, 16 CPU'su olan bir sistemde bir örnek verilmiştir.
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);
Yöntem 7: In-Memory OLTP'ye geçme
Alternatif olarak, özellikle mandal çekişmesi yüksekse OLTP In-Memory kullanın. Bu teknoloji, mandal çekişmelerini genel olarak ortadan kaldırır. Ancak, sayfa mandal çekişmesi gözlemlenen belirli tabloları yeniden tasarlamanız ve bellek için iyileştirilmiş bir tabloya geçirmeniz gerekir. Bellek İyileştirme Danışmanı'nı ve İşlem Performansı Analizi Raporu'nı kullanarak geçişin mümkün olup olmadığını ve geçişi gerçekleştirme çabasının ne olacağını belirleyebilirsiniz. In-Memory OLTP'nin mandal çekişmelerini nasıl ortadan kaldırıyor hakkında daha fazla bilgi için Bellek İçi OLTP - Yaygın İş Yükü Desenleri ve Geçiş Konuları bölümünde belgeyi indirin ve gözden geçirin.
Başvurular
Geri Bildirim
Çok yakında: 2024 boyunca, içerik için geri bildirim mekanizması olarak GitHub Sorunları’nı kullanımdan kaldıracak ve yeni bir geri bildirim sistemiyle değiştireceğiz. Daha fazla bilgi için bkz. https://aka.ms/ContentUserFeedback.
Gönderin ve geri bildirimi görüntüleyin