SQL Server tempdb veritabanında ayırma çekişmesini azaltmak için öneriler

Orijinal ürün sürümü: SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022
Özgün KB numarası: 2154845

Summary

Bu makalede, SQL Server tempdb veritabanında ayırma çekişmesini tanılama ve azaltma açıklanmaktadır. PAGELATCH_UP Sayfa Boş Alanı (PFS), Genel Ayırma Haritası (GAM) ve Paylaşılan Genel Ayırma Haritası (SGAM) sayfalarındaki beklemeleri kapsar. Ağır eş zamanlı iş yükleri altında SQL Server performansını geliştirmek için birden çok eşit boyutlu tempdb veri dosyasının ve eski sürümlerde izleme bayraklarının 1118 ve 1117'nin nasıl kullanılacağını açıklar.

Belirtiler

Microsoft SQL Server çalıştıran bir sunucuda, sunucu ağır yük altındayken ciddi engellemeler fark edeceksiniz. Dinamik yönetim görünümleri sys.dm_exec_requests ve sys.dm_os_waiting_tasks, isteklerin veya görevlerin tempdb kaynaklarını beklediğini gösterir. Bekleme türü PAGELATCH_UP’dır ve bekleme kaynağı, tempdb içindeki sayfaları işaret eder. Bu sayfalar 2:1:1, 2:1:3 vb. biçiminde olabilir (içindeki PFS ve SGAM sayfaları tempdb).

Not

Sayfa numarası 8088'e kadar eşit olarak bölünebiliyorsa, bu bir PFS sayfasıdır. Örneğin, 2:3:905856 sayfası, file_id=3 içindeki tempdb içinde bir PFS sayfasıdır.

Aşağıdaki işlemler tempdb yoğun olarak kullanır:

  • Geçici tablolarda (yerel veya genel) yinelenen oluşturma ve bırakma işlemleri.
  • Depolama için kullanılan tempdb tablo değişkenleri.
  • İmleçlerle ilişkilendirilmiş iş tabloları.
  • Bir ORDER BY yan tümcesiyle ilişkili iş tabloları.
  • GROUP BY yan tümcesiyle ilişkili iş tabloları.
  • Hash planlarıyla ilişkili çalışma dosyaları.

Bu etkinlikler çekişmelere neden olabilir.

Neden

tempdb yoğun şekilde kullanıldığında, SQL Server sayfa ayırma sırasında çekişme sorunu yaşayabilir. Çekişme düzeyine bağlı olarak, tempdb kullanan sorgular ve istekler kısa süreliğine yanıt vermeyebilir.

SQL Server'ın eski sürümlerinde nesne oluşturma sırasında, sistem karma bir ölçüden iki sayfa ayırır ve bunları yeni nesneye atar. Bir sayfa Dizin Ayırma Eşlemesi (IAM) sayfası, ikinci sayfa ise nesnenin ilk sayfasıdır. SQL Server, Paylaşılan Genel Ayırma Eşlemesi (SGAM) sayfasını kullanarak karma kapsamları izler. Her SGAM sayfası yaklaşık 4 GB veri izler.

Karma bir extent'ten sayfa ayırmak için SQL Server, boş bir karma sayfa bulmak üzere Page Free Space (PFS) sayfasını tarar. PFS sayfası her sayfada boş alanı, her PFS sayfası ise yaklaşık 8.000 sayfayı izler. SQL Server PFS ve SGAM sayfalarını değiştirdiğinde eşitleme gereklidir ve bu eşitleme diğer değiştiricileri kısa süreler için geciktirebilir.

SQL Server ayrılacak karma bir sayfayı ararken, taramayı her zaman aynı dosyada ve SGAM sayfasında başlatır. Bu davranış, çok sayıda karma sayfa ayırma işlemi aynı anda yürütüldüğünde SGAM sayfasında şiddetli çekişmeye neden olur ve bu da daha önce açıklanan belirtilere yol açar.

Not

Serbest bırakma işlemleri de bu sayfaları değiştirir ve çekişmeyi artırabilir.

SQL Server kullanan ayırma mekanizmaları (SGAM, GAM, PFS ve IAM) hakkında daha fazla bilgi edinmek için İlgili içerik bölümüne bakın.

Çözüm

SQL Server 2014, 9 Temmuz 2024'te genişletilmiş desteğin sonuna ulaşmıştır, bu nedenle çoğu okuyucunun SQL Server 2016 ve sonraki sürümler yönergelerini izlemesi gerekir.

SQL Server 2016 ve sonraki sürümleri

  1. Geçerli performans kılavuzu için tempdb veritabanını gözden geçirin.
  2. tempdb öğesinin birden çok eşit boyutlu veri dosyası içerdiğinden emin olun. Başlangıç noktası olarak, mantıksal işlemci başına en fazla sekiz dosya için bir veri dosyası kullanın. Çekişme devam ederse, mantıksal işlemci sayısına kadar dört grup halinde dosya ekleyin.
  3. Daha fazla ayırma iyileştirmesi elde etmek için en son toplu güncelleştirmeyle SQL Server örneğini güncel tutun. Örneğin, KB 4099472: SQL Server 2014, 2016 ve 2017'de PFS sayfası döngüsel (round-robin) algoritmasında iyileştirme bölümüne bakın.
  4. SQL Server 2019 veya sonraki bir sürümde çekişme devam ederse eşzamanlı PFS güncelleştirmeleri ve bellek için iyileştirilmiş meta veriler hakkındaki tempdb kılavuzunu gözden geçirin.

SQL Server 2014 ve önceki sürümleri

eşzamanlılığını tempdbgeliştirmek için aşağıdaki yöntemleri deneyin:

  • Disk bant genişliğini en üst düzeye çıkarmak ve ayırma yapılarındaki çekişmesini azaltmak için içindeki tempdb veri dosyalarının sayısını artırın. Kural olarak, mantıksal işlemci sayısı sekiz veya daha azsa, mantıksal işlemcilerle aynı sayıda veri dosyası kullanın. Mantıksal işlemci sayısı sekizden büyükse, sekiz veri dosyasıyla başlayın. Çekişme devam ederse, mantıksal işlemci sayısına kadar dört grup halinde veri dosyaları ekleyin ya da iş yükünü veya kodu değiştirin.

  • SQL Server 2005'te tempdb ile çalışma konusunda en iyi uygulama kılavuzunu gözden geçirin.

  • Önceki adımlar ayırma çekişmesini önemli ölçüde azaltmıyorsa ve çekişme SGAM sayfalarındaysa izleme bayrağını -T1118etkinleştirin. Bu izleme bayrağı altında, SQL Server her veritabanı nesnesine tam kapsamlar ayırarak SGAM sayfalarında çekişmesini önemli ölçüde azaltır.

    Not

    • Bu izleme bayrağı, SQL Server örnekteki tüm veritabanlarını etkiler. Ayırma çekişmesinin SGAM sayfalarında olup olmadığını denetlemek için bkz. DML işlemlerinin neden olduğu çekişmenin izlenmesi.
    • SQL Server 2014 için, KB 4099472'deki düzeltmeyi almak üzere Service Pack 3'ü yükleyin; bu düzeltme, aynı veri dosyası içindeki birden çok PFS sayfasına dönüşümlü PFS sayfası ayırmaları yaparak çekişmeyi daha da azaltır.

Tempdb ayırma hakkında daha fazla bilgi

SQL Server 2016 ve sonraki sürümlerde varsayılan tempdb davranışı

SQL Server 2016'dan itibaren, çeşitli iyileştirmeler varsayılan olarak tempdb ayırma çekişmesini azaltır; bu nedenle genellikle 1118 veya 1117 izleme bayraklarını etkinleştirmeniz gerekmez:

  • tempdb için karma genişletme ayırmaları varsayılan olarak devre dışıdır (önceden izleme bayrağı 1118 ile etkinleştirilen davranış). Tüm yeni ayırmalar tekdüzen kapsamlardan gelir.
  • Tüm tempdb veri dosyaları birlikte otomatik olarak büyütülmektedir (daha önce izleme bayrağı 1117 tarafından etkinleştirilen davranış).
  • SQL Server Kurulumu, en fazla sekiz mantıksal işlemci sayısına göre eşit boyutta tempdb birden çok veri dosyası önerir.
  • Eşzamanlı PFS güncelleştirmeleri, SQL Server 2019 ve sonraki sürümlerde PFS sayfa mandal çekişmesini daha da azaltır.

SQL Server 2016 veya sonraki bir sürümü çalıştırıyorsanız, dosya boyutuna ve sayısına odaklanıntempdb. İzleme bayraklarını yalnızca SQL Server 2014 veya önceki sürümlerde kullanın.

tempdb veri dosyalarının eşit boyutlandırılmasına örnek

Örneğin, tek tempdb veri dosyası 8 GB ve günlük dosyası 2 GB ise, veri dosyası sayısını sekize (eşit boyutlandırmayı korumak için her 1 GB) artırın ve günlük dosyasını değiştirmeden bırakın. Veri dosyalarını ayrı disklere yerleştirmek ek bir performans avantajı sağlayabilir, ancak gerekli değildir. Dosyalar aynı disk biriminde birlikte bulunabilir.

tempdb veri dosyalarının en uygun sayısı, karşılaştığınız çekişme düzeyine bağlıdır. Başlangıç noktası olarak, dosya sayısını SQL Server atanan mantıksal işlemci sayısına eşit olarak ayarlayın. Üst düzey sistemler için sekiz dosyayla başlayın. Çekişme azaltılmıyorsa daha fazla veri dosyası ekleyin.

Tüm veri dosyaları için eşit boyutlandırma kullanın. SQL Server 2000 Service Pack 4 (SP4), karma sayfa ayırmaları için döngüsel (round-robin) bir algoritma kullanan bir düzeltme sundu. Bu iyileştirmeyle, birden fazla dosya varsa ardışık her karma sayfa tahsisinde başlangıç dosyası değişir. SGAM ayırma algoritması tamamen döngüseldir ve orantılı doldurmayı dikkate almaz; bu nedenle tüm tempdb veri dosyalarını aynı boyutlarda oluşturun.

Daha fazla tempdb veri dosyası çekişmeyi nasıl azaltır?

Eşit boyutlandırılmış tempdb veri dosyaları eklemek, çekişmesini aşağıdaki yollarla azaltır:

  • için tempdbbir veri dosyanız varsa, her 4 GB alan için bir GAM sayfanız ve bir SGAM sayfanız vardır.
  • Aynı boyuttaki daha fazla veri dosyası, her veri dosyası için etkili bir şekilde bir veya daha fazla GAM ve SGAM sayfası oluşturur.
  • GAM ayırma algoritması, orantılı doldurma ilkesine uyarak dosyalardan döngüsel olarak her seferinde bir extent (sekiz bitişik sayfa) ayırır. Örneğin, eşit boyutta 10 dosyanız varsa, ilk ayırma Dosya1'den, ikincisi Dosya2'den, üçüncüsü Dosya3'ten vb. olur.
  • GAM sayfaları ayırırken aynı anda sekiz sayfa FULL olarak işaretlendiğinden PFS sayfası çekişmesi azaltılır.

İzleme bayrağı -T1118 çekişmeyi nasıl azaltır?

Not

Bu bölüm yalnızca SQL Server 2014 ve önceki sürümler için geçerlidir. SQL Server 2016 ve sonraki sürümlerinde, bu davranış için tempdbvarsayılandır.

-T1118 izleme bayrağı, çekişmeyi aşağıdaki şekillerde azaltır:

  • -T1118 sunucu genelinde bir ayardır.
  • SQL Server yeniden başlatıldıktan sonra izleme bayrağının etkin kalması için SQL Server başlangıç parametrelerine ekleyin-T1118.
  • -T1118 sunucudaki neredeyse tüm tek sayfalı ayırmaları kaldırır.
  • Tek sayfalı ayırmaların çoğunun devre dışı bırakılması SGAM sayfa çekişmesini azaltır.
  • -T1118 açık olduğunda, neredeyse tüm yeni ayırmalar, bir nesneye her seferinde sekiz sayfa (bir extent) ayıran bir GAM sayfasından (örneğin, 2:1:2) gelir; yani bir nesnenin ilk sekiz sayfası için bir extent içinden tek tek sayfa ayırmak yerine.
  • -T1118 açık olsa bile, IAM sayfaları SGAM sayfasından tek sayfalık ayırmalar kullanmaya devam eder. Ancak hotfix 8.00.0702 ve daha fazla tempdb veri dosyasıyla birleştirildiğinde, nihai sonuç SGAM sayfası çekişmesinin azalmasıdır. Alanla ilgili endişeler için sonraki bölüme bakın.

İzleme bayrağı 1118'in modern SQL Server'daki varsayılan davranışla karşılaştırması

Davranış SQL Server 2014 ve önceki sürümler (-T1118 olmadan) SQL Server 2014 ve önceki sürümler (-T1118 ile) SQL Server 2016 ve üzeri (tempdbvarsayılan)
Yeni bir nesnenin ilk sekiz sayfası Karma kapsamlardan (SGAM) tek sayfalı ayırmalar Tam eşit kapsam (GAM) Tam eşit kapsam (GAM)
SGAM sayfa çekişmesi riski Yoğun kullanımda yüksek tempdb Low Low
Scope Uygulanamaz Sunucu genelinde tempdb sadece
Eylem gerekli None Başlangıç parametresi ekleme -T1118 None

İzleme bayrağı -T1118'in dezavantajları

Kullanmanın -T1118 dezavantajı, aşağıdaki koşulların her ikisi de doğruysa veritabanı boyutunun büyüyebileceğidir:

  • Kullanıcı veritabanında yeni nesneler oluşturursunuz.
  • Her yeni nesne 64 KB'tan az depolama alanı kaplar.

Bu koşullar altında, SQL Server yalnızca 8 KB'a ihtiyaç duyan ve 56 KB depolama alanı boşa harcayan bir nesne için 64 KB (sekiz sayfa * 8 KB) ayırabilir. Yeni nesne yaşam süresi boyunca 64 KB'tan (sekiz sayfa) fazla kullanıyorsa izleme bayrağının dezavantajı yoktur. En kötü durumda SQL Server, ilk ayırma sırasında yedi ek sayfa ayırır, ancak bu yalnızca hiçbir zaman bir sayfanın ötesine büyümeyen yeni nesneler için geçerlidir.

İlgili içerik

  • Last updated on