SQL Server tempdb veritabanında ayırma çekişmesini azaltma önerileri
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
SQL Server 2016 ve sonraki sürümleri:
Gözden geçirin
SQL Server tempdb veritabanı performansını iyileştirme.
TEMPDB – Dosyalar ve İzleme Bayrakları ve Güncelleştirmeler, 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şme oranını daha da azaltan bir geliştirme yapılmıştır. Düzeltme, tüm tempdb veri dosyalarında hepsini bir kez deneme ayırmaya ek olarak, aynı veri dosyasındaki çeşitli PFS sayfalarında hepsini bir kez deneme ayırmaları gerçekleştirerek PFS sayfa ayırmasını geliştirir. Daha fazla bilgi için SQL Server 2014, 2016 ve 2017'de KB4099472 - PFS sayfası hepsini bir kez deneme algoritması geliştirme bölümüne bakın.
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'nin 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şmeleri 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 dosyalarının sayısını mantıksal işlemci sayısına kadar dört (4) katı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
Bu izleme bayrağı, SQL Server örneğindeki tüm veritabanlarını etkiler. Ayırma çekişmesi SGAM sayfalarında olup olmadığını belirleme hakkında bilgi için bkz. DML işlemlerinin neden olduğu izleme çekişmesi.
SQL Server 2014 ortamlarında, aşağıdaki KB makalesinde belgelenen düzeltmeden yararlanmak için Service Pack 3 uyguladığınızı doğrulayın. geliştirme, SQL Server 2014 ortamlarındaki çekişmeleri daha da azaltır. Düzeltme, tüm tempdb veri dosyalarında hepsini bir kez deneme ayırmaya ek olarak, aynı veri dosyasındaki çeşitli PFS sayfalarında hepsini bir kez deneme ayırmaları gerçekleştirerek PFS sayfa ayırmasını geliştirir.
MSSQL Tiger Team Blogu: SQL Server tempdb'de dosyalar ve izleme bayrakları ve güncelleştirmeleri
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.