SQL Server tempdb veritabanında ayırma çekişmesini azaltma önerileri

  • Makale

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'deki PFS ve SGAM sayfaları).

Not

Bir sayfa 8088'e kadar eşit olarak 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ı bir şekilde 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 bir şekilde 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 uzantıdan ayrılıp yeni nesneye atanmalıdır. Bir sayfa Dizin Ayırma Eşlemesi (IAM) ve ikincisi nesnenin ilk sayfası içindir. SQL Server Paylaşılan Genel Ayırma Haritası (SGAM) sayfasını kullanarak karma kapsamları izler. Her SGAM sayfası yaklaşık 4 gigabaytlık verileri izler.

Bir sayfayı karma ölçüden ayırmak için, SQL Server hangi karma sayfanın serbest ayrılacak 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 sayfayı ararsa, 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 tarafından kullanılan farklı ayırma mekanizmaları (SGAM, GAM, PFS, IAM) hakkında daha fazla bilgi edinmek için Başvurular bölümüne bakın.

Çözüm

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ırma ve günlük dosyasını olduğu gibi bırakma ö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.

En uygun tempdb veri dosyası sayısı tempdb'de görülen çekişme derecesine bağlıdır. Başlangıç noktası olarak tempdb'yi en az 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, başlangıç dosyası birbirini izleyen her karma sayfa ayırması için 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 bir kerede 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 FULL 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 izleme bayrağı -T1118 kullanmanı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 için 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ÇIKsa, neredeyse tüm yeni ayırmalar, izleme bayrağı olmadan bir nesnenin ilk sekiz (8) sayfası için bir ölçüden 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ı, -T1118AÇILDI olsa bile SGAM sayfasından tek sayfa ayırmalarını kullanmaya devam eder. Ancak, 8.00.0702 düzeltmesi ve artan tempdb veri dosyalarıyla birleştirildiğinde, net etkisi SGAM sayfasındaki çekişmede bir azalmadır. Alanla ilgili endişeler için sonraki bölüme bakın.

Dezavantaj -ları

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

  • Kullanıcı veritabanında yeni nesneler 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 kullanım ö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 daha fazla büyümeyen yeni nesneler için yedi (7) ek sayfa ayırabilir.

Başvurular