SQL Server'da dar ve geniş planlarla ilgili UPDATE performans sorunlarını giderme

  • Makale

Şunlar için geçerlidir: SQL Server

Bir UPDATE ifade bazı durumlarda daha hızlı, bazı durumlarda ise daha yavaş olabilir. Güncelleştirilen satır sayısı ve sistemdeki kaynak kullanımı (engelleme, CPU, bellek veya G/Ç) dahil olmak üzere böyle bir varyansa yol açabilecek birçok faktör vardır. Bu makalede varyansın belirli bir nedeni ele alınacaktır: SQL Server tarafından yapılan sorgu planı seçimi.

Dar ve geniş planlar nelerdir?

Kümelenmiş dizin sütununa karşı bir UPDATE deyim yürütürseniz, SQL Server kümelenmiş dizinin kendisini değil, kümelenmemiş dizinler küme dizin anahtarını içerdiğinden kümelenmemiş tüm dizinleri de güncelleştirir.

SQL Server güncelleştirmeyi yapmak için iki seçeneği vardır:

  • Dar plan: Kümelenmemiş dizin güncelleştirmesini kümelenmiş dizin anahtarı güncelleştirmesi ile birlikte yapın. Bu basit yaklaşımı anlamak kolaydır; kümelenmiş dizini güncelleştirin ve ardından kümelenmemiş tüm dizinleri aynı anda güncelleştirin. SQL Server bir satırı güncelleştirir ve tümü tamamlanana kadar sonraki satıra geçer. Bu yaklaşıma dar plan güncelleştirmesi veya Per-Row güncelleştirmesi denir. Ancak, güncelleştirilecek kümelenmemiş dizin verilerinin sırası kümelenmiş dizin verilerinin sırasına uygun olmadığından bu işlem nispeten pahalıdır. Güncelleştirmeye birçok dizin sayfası dahil edilirse, veriler diskteyken çok sayıda rastgele G/Ç isteği oluşabilir.

  • Geniş plan: Performansı iyileştirmek ve rastgele G/Ç'yi azaltmak için SQL Server geniş bir plan seçebilir. Kümelenmemiş dizinler güncelleştirmesinin yanı sıra kümelenmiş dizin güncelleştirmesini birlikte yapmaz. Bunun yerine, önce bellekteki tüm kümelenmemiş dizin verilerini sıralar ve ardından tüm dizinleri bu sırayla güncelleştirir. Bu yaklaşım geniş plan (Per-Index güncelleştirmesi olarak da adlandırılır) olarak adlandırılır.

İşte dar ve geniş planların ekran görüntüsü:

Dar ve geniş planların ekran görüntüsü.

SQL Server ne zaman geniş bir plan seçer?

SQL Server geniş bir plan seçmesi için iki ölçüt karşılanmalıdır:

  • Etkilenen satır sayısı 250'den fazladır.
  • Kümelenmemiş dizinlerin yaprak düzeyinin boyutu (dizin sayfası sayısı * 8 KB), en fazla sunucu belleği ayarının en az 1/1000'idir.

Dar ve geniş planlar nasıl çalışır?

Dar ve geniş planların nasıl çalıştığını anlamak için aşağıdaki ortamda aşağıdaki adımları izleyin:

  • SQL Server 2019 CU11
  • En fazla sunucu belleği = 1.500 MB

  1. Aşağıdaki betiği çalıştırarak sırasıyla 41.501 satırı, sütununda c1bir kümelenmiş dizin ve sütunların geri kalanında kümelenmemiş beş dizin içeren bir tablo mytable1 oluşturun.

    CREATE TABLE mytable1(c1 INT,c2 CHAR(30),c3 CHAR(20),c4 CHAR(30),c5 CHAR(30))
GO
WITH cte
AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY c1.object_id) id FROM sys.columns CROSS JOIN sys.columns c1
)
INSERT mytable1
SELECT TOP 41000 id,REPLICATE('a',30),REPLICATE('a',20),REPLICATE('a',30),REPLICATE('a',30) 
FROM cte
GO

INSERT mytable1
SELECT TOP 250 50000,c2,c3,c4,c5 
FROM mytable1
GO

INSERT mytable1
SELECT TOP 251 50001,c2,c3,c4,c5 
FROM mytable1
GO

CREATE CLUSTERED INDEX ic1 ON mytable1(c1)
CREATE INDEX ic2 ON mytable1(c2)
CREATE INDEX ic3 ON mytable1(c3)
CREATE INDEX ic4 ON mytable1(c4)
CREATE INDEX ic5 ON mytable1(c5)

  2. Aşağıdaki üç T-SQL UPDATE deyimini çalıştırın ve sorgu planlarını karşılaştırın:

    • UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) - bir satır güncelleştirildi
    • UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) - 250 satır güncelleştirildi.
    • UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) - 251 satır güncelleştirildi.

  3. İlk ölçüte göre sonuçları inceleyin (etkilenen satır sayısının eşiği 250'dir).

    Aşağıdaki ekran görüntüsünde ilk ölçüte göre sonuçlar gösterilmektedir:

    Dizinin boyutuna göre geniş ve dar planların ekran görüntüsü.

    Beklendiği gibi, etkilenen satır sayısı 250'den az olduğundan sorgu iyileştiricisi ilk iki sorgu için dar bir plan seçer. Etkilenen satır sayısı 251 olduğundan ve 250'den büyük olduğundan üçüncü sorgu için geniş bir plan kullanılır.

  4. İkinci ölçüte göre sonuçları inceleyin (yaprak dizin boyutunun belleği en fazla sunucu belleği ayarının en az 1/1000'idir).

    Aşağıdaki ekran görüntüsünde ikinci ölçüte göre sonuçlar gösterilmektedir:

    Boyutu nedeniyle dizin kullanmayan geniş planın ekran görüntüsü.

    Üçüncü UPDATE sorgu için geniş bir plan seçilir. Ancak dizin ic3 (sütunda c3) planda görülmez. Bu sorun, ikinci ölçüt karşılanmadığından oluşur. Yaprak sayfalar dizin boyutu, maksimum sunucu belleği ayarına kıyasla.

    ve sütununun c2c4c4 veri türü olurkenchar(30), sütunun c3 veri türü ise olur.char(20) Her dizin ic3 satırının boyutu diğerlerinden daha azdır, bu nedenle yaprak sayfaların sayısı diğerlerinden daha azdır.

    Dinamik yönetim işlevinin (DMF) sys.dm_db_database_page_allocationsyardımıyla, her dizin için sayfa sayısını hesaplayabilirsiniz. , ic4ve ic5dizinleri ic2için her dizinin 214 sayfası vardır ve bunların 209'unun yaprak sayfaları vardır (sonuçlar biraz değişebilir). Yaprak sayfalar tarafından kullanılan bellek 209 x 8 = 1.672 KB'tır. Bu nedenle oran 1672/(1500 x 1024) = 0,00108854101'dir ve 1/1000'den büyüktür. Ancak, ic3 yalnızca 161 sayfa vardır; bunların 159 tanesi yaprak sayfadır. Oran 159 x 8/(1500 x 1024) = 0,000828125'tir ve bu değer 1/1000'den (0,001) küçüktür.

    Ölçüte uyması için daha fazla satır ekler veya maksimum sunucu belleğini azaltırsanız plan değişir. Dizin yaprak düzeyi boyutunu 1/1000'den büyük yapmak için, aşağıdaki komutları çalıştırarak en yüksek sunucu belleği ayarını biraz 1.200'e düşürebilirsiniz:

    sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 1200;
GO
RECONFIGURE
GO
UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) --251 rows are updated.

    Bu durumda, 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. Bu değişiklik sonrasında ic3 , planda görüntülenir.

    hakkında show advanced optionsdaha fazla bilgi için bkz. Transact-SQL kullanma.

    Aşağıdaki ekran görüntüsünde, bellek eşiğine ulaşıldığında geniş planın tüm dizinleri kullandığı gösterilmektedir:

    Bellek eşiğine ulaşıldığında tüm dizinleri kullanan geniş planın ekran görüntüsü.

Geniş bir plan, dar bir plandan daha mı hızlıdır?

Yanıt, verilerin ve dizin sayfalarının arabellek havuzunda önbelleğe alınıp alınmadığına bağlıdır.

Veriler arabellek havuzunda önbelleğe alınır

Veriler zaten arabellek havuzundaysa, geniş plana sahip sorgu dar planlara kıyasla fazladan performans avantajları sunmayabilir çünkü geniş plan G/Ç performansını geliştirmek için tasarlanmıştır (mantıksal okumalar değil fiziksel okumalar).

Veriler bir arabellek havuzundayken geniş bir planın dar bir plandan daha hızlı olup olmadığını test etmek için aşağıdaki ortamda aşağıdaki adımları izleyin:

  • SQL Server 2019 CU11

  • En fazla sunucu belleği: 30.000 MB

  • Veri boyutu 64 MB, dizin boyutu ise 127 MB civarındadır.

  • Veritabanı dosyaları iki farklı fiziksel disktedir:

    • I:\sql19\dbWideplan.mdf
    • H:\sql19\dbWideplan.ldf

  1. Aşağıdaki komutları çalıştırarak başka bir tablo mytable2oluşturun:

    CREATE TABLE mytable2(C1 INT,C2 INT,C3 INT,C4 INT,C5 INT)
GO
CREATE CLUSTERED INDEX IC1 ON mytable2(C1)
CREATE INDEX IC2 ON mytable2(C2)
CREATE INDEX IC3 ON mytable2(C3)
CREATE INDEX IC4 ON mytable2(C4)
CREATE INDEX IC5 ON mytable2(C5)
GO
DECLARE @N INT=1
WHILE @N<1000000
BEGIN
  DECLARE @N1 INT=RAND()*4500
  DECLARE @N2 INT=RAND()*100000
  DECLARE @N3 INT=RAND()*100000
  DECLARE @N4 INT=RAND()*100000
  DECLARE @N5 INT=RAND()*100000
  INSERT mytable2 VALUES(@N1,@N2,@N3,@N4,@N5)
  SET @N+=1
END
GO
UPDATE STATISTICS mytable2 WITH FULLSCAN

  2. Sorgu planlarını karşılaştırmak için aşağıdaki iki sorguyu yürütür:

    update mytable2 set c1=c1 where c2<260 option(querytraceon 8790) --trace flag 8790 will force Wide plan
update mytable2 set c1=c1 where c2<260 option(querytraceon 2338) --trace flag 2338 will force Narrow plan

    Daha fazla bilgi için bkz. izleme bayrağı 8790 ve izleme bayrağı 2338.

    Geniş plana sahip sorgu 0,136 saniye sürerken, dar plana sahip sorgu yalnızca 0,112 saniye sürer. İki süre çok yakındır ve Per-Index güncelleştirmesi (geniş plan) daha az yararlı olur çünkü veriler deyimi yürütülmeden önce UPDATE arabellekte yer alır.

    Aşağıdaki ekran görüntüsünde, arabellek havuzunda veriler önbelleğe alınırken geniş ve dar planlar gösterilmektedir:

    Arabellek havuzunda veriler önbelleğe alınırken geniş ve dar planların ekran görüntüsü.

Veriler arabellek havuzunda önbelleğe alınmaz

Veriler arabellek havuzunda olmadığında geniş bir planın dar bir plandan daha hızlı olup olmadığını test etmek için aşağıdaki sorguları çalıştırın:

Not

Testi yaparken, SQL Server içindeki tek iş yükünün sizin olduğundan ve disklerin SQL Server ayrılmış olduğundan emin olun.

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
WAITFOR DELAY '00:00:02' --wait for 1~2 seconds
UPDATE mytable2 SET c1=c1 WHERE c2 < 260 OPTION (QUERYTRACEON 8790) --force Wide plan
CHECKPOINT 
GO
DBCC DROPCLEANBUFFERS
GO 
WAITFOR DELAY '00:00:02' --wait for 1~2 SECONDS
UPDATE mytable2 SET c1=c1 WHERE c2 < 260 OPTION (QUERYTRACEON 2338) --force Narrow plan

Geniş planlı sorgu 3,554 saniye, dar bir plana sahip sorgu ise 6,701 saniye sürer. Geniş plan sorgusu bu kez daha hızlı çalışır.

Aşağıdaki ekran görüntüsünde, arabellek havuzunda veriler önbelleğe alınmadığında geniş plan gösterilmektedir:

Arabellek havuzunda veriler önbelleğe alınmadığında geniş planın ekran görüntüsü.

Aşağıdaki ekran görüntüsünde, arabellek havuzunda veriler önbelleğe alınmadığında dar plan gösterilmektedir:

Arabellek havuzunda veriler önbelleğe alınmadığında dar planın ekran görüntüsü.

Veriler arabellekte olmadığında geniş plan sorgusu her zaman dar bir sorgu planından daha mı hızlıdır?

Cevap "her zaman değil"dir. Veriler arabellekte olmadığında geniş plan sorgusunun her zaman dar sorgu planından daha hızlı olup olmadığını test etmek için şu adımları izleyin:

  1. Aşağıdaki komutları çalıştırarak başka bir tablo mytable2oluşturun:

    SELECT c1,c1 AS c2,c1 AS C3,c1 AS c4,c1 AS C5 INTO mytable3 FROM mytable2
GO
CREATE CLUSTERED INDEX IC1 ON mytable3(C1)
CREATE INDEX IC2 ON mytable3(C2)
CREATE INDEX IC3 ON mytable3(C3)
CREATE INDEX IC4 ON mytable3(C4)
CREATE INDEX IC5 ON mytable3(C5)
GO

    mytable3, veriler dışında ile aynıdırmytable2. mytable3 aynı değere sahip beş sütunun tümüne sahiptir, bu da kümelenmemiş dizinlerin sırasını kümelenmiş dizinin sırasını izler. Verilerin bu şekilde sıralanması, geniş planın avantajını en aza indirir.

  2. Sorgu planlarını karşılaştırmak için aşağıdaki komutları yürütebilirsiniz:

    CHECKPOINT 
GO
DBCC DROPCLEANBUFFERS
go
UPDATE mytable3 SET c1=c1 WHERE c2<12 OPTION(QUERYTRACEON 8790) --tf 8790 will force Wide plan

CHECKPOINT 
GO
DBCC DROPCLEANBUFFERS
GO
UPDATE mytable3 SET c1=c1 WHERE c2<12 OPTION(QUERYTRACEON 2338) --tf 2338 will force Narrow plan

    Her iki sorgunun süresi önemli ölçüde azaltıldı! Geniş plan 0,304 saniye sürer ve bu süre bu kez dar plandan biraz daha yavaştır.

    Aşağıdaki ekran görüntüsünde geniş ve dar kullanıldığında performansın karşılaştırılması gösterilmektedir:

    Geniş ve dar kullanıldığında performansın karşılaştırılmasını gösteren ekran görüntüsü.

Geniş planların uygulandığı senaryolar

Geniş planların da uygulandığı diğer senaryolar şunlardır:

Kümelenmiş dizin sütununun benzersiz veya birincil anahtarı vardır ve birden çok satır güncelleştirilir

Senaryoyu yeniden oluşturmaya yönelik bir örnek aşağıda verilmişti:

CREATE TABLE mytable4(c1 INT primary key,c2 INT,c3 INT,c4 INT)
GO
CREATE INDEX ic2 ON mytable4(c2)
CREATE INDEX ic3 ON mytable4(c3)
CREATE INDEX ic4 ON mytable4(c4)
GO
INSERT mytable4 VALUES(0,0,0,0)
INSERT mytable4 VALUES(1,1,1,1)

Aşağıdaki ekran görüntüsünde, küme dizininde benzersiz bir anahtar olduğunda geniş planın kullanıldığı gösterilmektedir:

Küme dizininde benzersiz bir anahtar olduğunda kullanılan geniş planın ekran görüntüsü.

Diğer ayrıntılar için Benzersiz Dizinleri Koruma bölümünü gözden geçirin.

Küme dizini sütunu bölüm düzeninde belirtilir

Senaryoyu yeniden oluşturmaya yönelik bir örnek aşağıda verilmişti:

CREATE TABLE mytable5(c1 INT,c2 INT,c3 INT,c4 INT)
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name='PS1')
    DROP PARTITION SCHEME PS1
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name='PF1')
    DROP PARTITION FUNCTION PF1
GO
CREATE PARTITION FUNCTION PF1(INT) AS 
  RANGE right FOR VALUES 
  (2000)   
GO
CREATE PARTITION SCHEME PS1 AS 
  PARTITION PF1 all TO 
  ([PRIMARY]) 
GO 
CREATE CLUSTERED INDEX c1 ON mytable5(c1) ON PS1(c1)
CREATE INDEX c2 ON mytable5(c2)
CREATE INDEX c3 ON mytable5(c3)
CREATE INDEX c4 ON mytable5(c4)
GO
UPDATE mytable5 SET c1=c1 WHERE c1=1

Aşağıdaki ekran görüntüsünde, bölüm düzeninde kümelenmiş bir sütun olduğunda geniş planın kullanıldığı gösterilmektedir:

Bölüm düzeninde kümelenmiş bir sütun olduğunda geniş planın kullanıldığını gösteren ekran görüntüsü.

Kümelenmiş dizin sütunu bölüm düzeninin bir parçası değildir ve bölüm düzeni sütunu güncelleştirilir

Senaryoyu yeniden oluşturmaya yönelik bir örnek aşağıda verilmişti:

CREATE TABLE mytable6(c1 INT,c2 INT,c3 INT,c4 INT)
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name='PS2')
    DROP PARTITION SCHEME PS2
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name='PF2')
    DROP PARTITION FUNCTION PF2
GO
CREATE PARTITION FUNCTION PF2(int) AS 
  RANGE right FOR VALUES 
  (2000)   
GO
CREATE PARTITION SCHEME PS2 AS 
  PARTITION PF2 all TO 
  ([PRIMARY]) 
GO 
CREATE CLUSTERED INDEX c1 ON mytable6(c1) ON PS2(c2) --on c2 column
CREATE INDEX c3 ON mytable6(c3)
CREATE INDEX c4 ON mytable6(c4)

Aşağıdaki ekran görüntüsünde, bölüm düzeni sütunu güncelleştirildiğinde geniş planın kullanıldığı gösterilmektedir:

Bölüm düzeni sütunu güncelleştirildiğinde kullanılan geniş planın ekran görüntüsü.

Sonuç

  • SQL Server, aşağıdaki ölçütler aynı anda karşılandığında geniş bir plan güncelleştirmesi seçer:

    • Etkilenen satır sayısı 250'den büyüktür.
    • Yaprak dizinin belleği, en fazla sunucu belleği ayarının en az 1/1000'idir.

  • Geniş planlar, fazladan bellek kullanma pahasına performansı artırır.

  • Beklenen sorgu planı kullanılmıyorsa, bunun nedeni eski istatistikler (doğru veri boyutunu bildirmeme), maksimum sunucu belleği ayarı veya parametreye duyarlı planlar gibi diğer ilgisiz sorunlardan kaynaklanıyor olabilir.

  • Geniş bir plan kullanan deyimlerin UPDATE süresi çeşitli faktörlere bağlıdır ve bazı durumlarda dar planlardan daha uzun sürebilir.

  • İzleme bayrağı 8790 geniş bir planı zorlar; izleme bayrağı 2338 , dar bir planı zorlar.