Aracılığıyla paylaş

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

Bu makale, sunucu ağır bir yükle karşılaştığında ciddi engelleme fark ettiğiniz sorunu çözmenize yardımcı olur.

Özgün ürün sürümü: SQL Server
Özgün KB numarası: 2154845

Belirtiler

Microsoft SQL Server çalıştıran bir sunucuda, sunucu ağır bir yükle karşılaştığında ciddi engellemeler fark edeceksiniz. Dinamik Yönetim Görünümleri [sys.dm_exec_request veya sys.dm_os_waiting_tasks] bu isteklerin veya görevlerin tempdb kaynaklarını beklediğini gösterir. Ayrıca, bekleme türü şeklindedir PAGELATCH_UPve bekleme kaynağı tempdb'deki sayfaları gösterir. Bu sayfalar 2:1:1, 2:1:3 vb. biçiminde olabilir (tempdb'de PFS ve SGAM sayfaları).

Not

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

Aşağıdaki işlemler tempdb'yi kapsamlı olarak kullanır:

  • Geçici tabloların (yerel veya genel) yinelenen oluşturma ve bırakma işlemi.
  • Depolama için tempdb kullanan tablo değişkenleri.
  • CURSORS ile ilişkili iş tabloları.
  • ORDER BY yan tümcesiyle ilişkili iş tabloları.
  • GROUP BY yan tümcesiyle ilişkili iş tabloları.
  • KARMA PLANLARI ile ilişkilendirilmiş iş dosyaları.

Bu etkinlikler çekişme sorunlarına neden olabilir.

Neden

Tempdb veritabanı yoğun olarak kullanıldığında, SQL Server sayfaları ayırmaya çalıştığında çekişmeyle karşılaşabilir. Çekişme derecesine bağlı olarak, bu tempdb içeren sorguların ve isteklerin kısa bir süre yanıt vermesine neden olabilir.

Nesne oluşturma sırasında, iki (2) sayfa karma bir ölçüden ayrılıp yeni nesneye atanmalıdır. Bir sayfa Dizin Ayırma Eşlemesi (IAM) içindir ve ikincisi 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 gigabayt veri izler.

Karışık ölçüden bir sayfa ayırmak için, SQL Server'ın hangi karma sayfanın ayrılmaya uygun olduğunu belirlemek için Sayfa Boş Alanı (PFS) sayfasını taraması gerekir. PFS sayfası her sayfada kullanılabilir boş alanı izler ve her PFS sayfası yaklaşık 8000 sayfayı izler. PFS ve SGAM sayfalarında değişiklik yapmak için uygun eşitleme korunur; ve bu, 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, çeşitli karma sayfa ayırmaları devam ederken SGAM sayfasında yoğun çekişmelere neden olur. Bu, Belirtiler bölümünde belgelenen sorunlara neden olabilir.

Not

Ayırma etkinliklerini kaldırma, sayfaları da değiştirmelidir. Bu, artan çekişmeye katkıda bulunabilir.

SQL Server (SGAM, GAM, PFS, IAM) tarafından kullanılan farklı ayırma mekanizmaları hakkında daha fazla bilgi edinmek için Başvurular bölümüne bakın.

Çözüm

  • SQL Server 2016 ve sonraki sürümleri:

    İnceleyin

    • SQL Server'da tempdb veritabanı performansını iyileştirme.

    • TEMPDB – Dosyalar ve İzleme Bayrakları ve Güncelleştirmeleri, Oh My!

    • Aşağıdaki güncelleştirmeden yararlanmak için SQL Server 2016 ve 2017 için ilgili CU'yi uygulayın. SQL Server 2016 ve SQL Server 2017'de çekişmesini daha da azaltan bir geliştirme yapılmıştır. Düzeltme, tüm tempdb veri dosyaları genelinde hepsini bir kez deneme ayırmaya ek olarak, aynı veri dosyasındaki birkaç PFS sayfasında hepsini bir kez deneme ayırmaları gerçekleştirerek PFS sayfa ayırmasını geliştirir. Daha fazla bilgi için bkz . SQL Server 2014, 2016 ve 2017'de KB4099472 - PFS sayfası hepsini bir kez deneme algoritması geliştirme.

    Bu öneriler ve SQL 2016'da sunulan diğer değişiklikler hakkında daha fazla bilgi için gözden geçirin

  • SQL Server 2014 ve önceki sürümleri:

    tempdb eşzamanlılığını geliş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 tempdb'deki veri dosyalarının sayısını artırın. Kural olarak, mantıksal işlemci sayısı sekizden (8) küçük veya buna eşitse, mantıksal işlemcilerle aynı sayıda veri dosyası kullanın. Mantıksal işlemci sayısı sekizden (8) büyükse, sekiz veri dosyası kullanın. Çekişme devam ederse, çekişme kabul edilebilir düzeylere düşürülene kadar veri dosyası sayısını dört (4) kat artırarak mantıksal işlemci sayısına kadar artırın. Alternatif olarak, iş yükünde veya kodda değişiklik yapabilirsiniz.

    • SQL Server 2005'te tempdb ile çalışma konusunda en iyi yöntem önerilerini uygulamayı göz önünde bulundurun.

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

      Not

Eşit boyutlandırmaya sahip tempdb veri dosyalarının sayısını artırın

Örneğin, tempdb'nin tek veri dosyası boyutu 8 GB ve Günlük dosyası boyutu 2 GB ise, veri dosyalarının sayısını sekize (8) (eşit boyutlandırmayı korumak için 1 GB'ın her biri) artırmanız ve günlük dosyasını olduğu gibi bırakmanız önerisinde bulunabilirsiniz. Farklı veri dosyalarının ayrı disklerde olması ek performans avantajı sağlayabilir. Ancak, bu gerekli değildir. Dosyalar aynı disk biriminde birlikte bulunabilir.

Tempdb veri dosyalarının en iyi sayısı tempdb'de görülen çekişme derecesine bağlıdır. Başlangıç noktası olarak tempdb'yi en azından SQL Server için atanan mantıksal işlemci sayısına eşit olacak şekilde yapılandırabilirsiniz. Üst uç sistemler için başlangıç sayısı sekiz (8) olabilir. Çekişme azaltılmazsa veri dosyalarının sayısını artırmanız gerekebilir.

Veri dosyalarının eşit boyutlandırmasını kullanmanızı öneririz. SQL Server 2000 Service Pack 4 (SP4), karma sayfa ayırmaları için hepsini bir kez deneme algoritması kullanan bir düzeltme kullanıma sunulmuştur. Bu geliştirme nedeniyle, her ardışık karma sayfa ayırması için başlangıç dosyası farklıdır (birden fazla dosya varsa). SGAM için yeni ayırma algoritması tamamen hepsini bir kez denemedir ve hızı korumak için orantılı dolguya uygun değildir. Tüm tempdb veri dosyalarını aynı boyutta oluşturmanızı öneririz.

Tempdb veri dosyalarının sayısının artırılması çekişmeyi nasıl azaltır?

Aşağıdaki listede eşit boyutlandırmaya sahip tempdb veri dosyalarının sayısının artırılmasının çekişmeyi nasıl azalttığı açıklanmaktadır:

  • Tempdb için bir veri dosyanız varsa, her 4 GB alan için yalnızca bir GAM sayfanız ve bir SGAM sayfanız vardır.

  • Tempdb için aynı boyutlara sahip veri dosyalarının sayısını artırmak, her veri dosyası için etkili bir şekilde bir veya daha fazla GAM ve SGAM sayfası oluşturur.

  • GAM için ayırma algoritması, oransal dolguyu yerine getirirken hepsini bir kez deneme biçimindeki dosya sayısından tek seferde bir kapsam (sekiz bitişik sayfa) ayırır. Bu nedenle, eşit boyutta 10 dosyanız varsa, ilk ayırma Dosya1'den, ikincisi Dosya2'den, üçüncüsü Dosya3'ten vb. olur.

  • BIR kerede sekiz sayfa TAM olarak işaretlendiğinden, GAM sayfaları ayırdığı için PFS sayfasının kaynak çekişmesi azaltılır.

İzleme bayrağının uygulanması -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.

Aşağıdaki listede- T1118 izleme bayrağının kullanılmasının çekişmeyi nasıl azalttığı açıklanmaktadır:

  • -T1118 , sunucu genelinde bir ayardır.
  • SQL Server geri dönüştürüldikten sonra bile izleme bayrağının etkin kalması için SQL Server'ın Başlangıç parametrelerine -T1118 izleme bayrağını ekleyin.
  • -T1118 , sunucudaki neredeyse tüm tek sayfa ayırmalarını kaldırır.
  • Tek sayfa ayırmalarının çoğunu devre dışı bırakarak SGAM sayfasındaki çekişme azaltılır.
  • -T1118 AÇILDIysa, neredeyse tüm yeni ayırmalar, izleme bayrağı olmadan bir nesnenin ilk sekiz (8) sayfası için tek bir sayfadan farklı olarak bir nesneye bir kerede sekiz (8) sayfa (bir kapsam) ayıran bir GAM sayfasından (örneğin, 2:1:2) yapılır.
  • IAM sayfaları, -T1118 AÇıK olsa bile SGAM sayfasından tek sayfa ayırmalarını kullanmaya devam eder. Ancak, düzeltme 8.00.0702 ve artan tempdb veri dosyalarıyla birleştirildiğinde, net etki SGAM sayfasındaki çekişmede bir azalmadır. Alanla ilgili endişeler için sonraki bölüme bakın.

Dezavantajlar

-T1118 kullanmanın dezavantajı, aşağıdaki koşullar doğruysa veritabanı boyutunda artışlar görebilmenizdir:

  • Yeni nesneler bir kullanıcı veritabanında oluşturulur.
  • Yeni nesnelerin her biri 64 KB'tan az depolama alanı kaplar.

Bu koşullar doğruysa, yalnızca 8 KB alan gerektiren bir nesne için 64 KB (sekiz sayfa * 8 KB = 64 KB) ayırabilir ve bu nedenle 56 KB depolama alanı boşa harcanabilir. Ancak, yeni nesne ömrü boyunca 64 KB'tan (sekiz sayfa) fazla kullanıyorsa izleme bayrağının dezavantajı yoktur. Bu nedenle, en kötü durumda, SQL Server ilk ayırma sırasında yalnızca bir (1) sayfadan fazla büyümemiş yeni nesneler için yedi (7) ek sayfa ayırabilir.

Başvurular