SQL Server'da mandal çekişmesi tanılama ve çözme

Bu kılavuzda, SQL Server uygulamalarını belirli iş yükleriyle yüksek eşzamanlılık sistemlerinde çalıştırırken gözlemlenen mandal çekişmesi sorunlarının nasıl belirlendiği ve çözüldiği açıklanmaktadır.

Sunuculardaki CPU çekirdeği sayısı artmaya devam ettikçe, eşzamanlılıktaki ilişkili artış veritabanı altyapısında seri bir şekilde erişilmesi gereken veri yapılarında çekişme noktalarına neden olabilir. Bu, özellikle yüksek aktarım hızı/yüksek eşzamanlılık işlemi işleme (OLTP) iş yükleri için geçerlidir. Bu zorluklara yaklaşmanın çeşitli araçları, teknikleri ve yollarının yanı sıra bunları tamamen önlemeye yardımcı olabilecek uygulamalar tasarlarken izleyebileceğiniz uygulamalar vardır. Bu makalede, bu veri yapılarına erişimi seri hale getirmek için spinlock kullanan veri yapıları üzerinde belirli bir çekişme türü ele alınmaktadır.

Uyarı

Bu içerik Microsoft SQL Server Müşteri Danışma Ekibi (SQLCAT) ekibi tarafından, yüksek eşzamanlılık sistemlerindeKI SQL Server uygulamalarında sayfa mandal çekişmesiyle ilgili sorunları tanımlama ve çözme sürecine göre yazılmıştır. Burada belgelenen öneriler ve en iyi yöntemler, gerçek dünya OLTP sistemlerinin geliştirilmesi ve dağıtımı sırasında gerçek dünya deneyimine dayanır.

SQL Server mandal çekişmesi nedir?

Tutmalar, aşağıdakiler dahil olmak üzere bellek içi yapıların tutarlılığını garanti etmek için SQL Server altyapısı tarafından kullanılan hafif eşitleme temel öğeleridir; B-Ağacındaki yaprak olmayan sayfalar gibi dizin, veri sayfaları ve dahili yapılar. SQL Server, arabellek havuzundaki sayfaları korumak için arabellek tutmaları ve henüz yüklenmemiş sayfaları korumak için G/Ç tutmaları kullanır. SQL Server arabellek havuzunda bir sayfaya veri yazıldığında veya sayfadan veri okunduğunda, bir iş parçacığının önce sayfa için bir arabellek mandalı alması gerekir. Arabellek havuzundaki sayfalara erişim için, özel kilit (PAGELATCH_EX) ve paylaşılan kilit (PAGELATCH_SH) dahil olmak üzere çeşitli arabellek kilit türleri mevcuttur. SQL Server, arabellek havuzunda henüz mevcut olmayan bir sayfaya erişmeye çalıştığında, sayfanın arabellek havuzuna yüklenmesi için zaman uyumsuz bir G/Ç işlemi başlatılır. SQL Server'ın G/Ç alt sisteminin yanıt vermesini beklemesi gerekiyorsa, isteğin türüne bağlı olarak özel (PAGEIOLATCH_EX) veya paylaşılan (PAGEIOLATCH_SH) G/Ç mandalı üzerinde bekler; bu, başka bir çalışan iş parçacığının aynı sayfayı uyumlu olmayan bir mandala sahip arabellek havuzuna yüklemesini önlemek için yapılır. Mandallar, arabellek havuzu sayfaları dışındaki iç bellek yapılarına erişimi korumak için de kullanılır; Bunlar Arabellek Dışı mandallar olarak bilinir.

Sayfa mandallarında çekişme, çok CPUlu sistemlerde karşılaşılan en yaygın senaryodur ve bu nedenle bu makalenin çoğu bunlara odaklanır.

Birden çok iş parçacığı, eşzamanlı olarak aynı bellek içi yapıya uyumsuz kilitler almaya çalıştığında mandal çekişmesi oluşur. Mandal bir iç kontrol mekanizması olduğundan; SQL motoru, bunların ne zaman kullanılacağını otomatik olarak belirler. Mandalların davranışı deterministik olduğundan, şema tasarımı dahil olmak üzere uygulama kararları bu davranışı etkileyebilir. Bu makale aşağıdaki bilgileri sağlamayı amaçlar:

• Latch'lerin SQL Server tarafından nasıl kullanıldığına ilişkin arka plan bilgisi.
• Kilit (tutum) çekişmelerini incelemek için kullanılan araçlar.
• Gözlemlenen çekişme miktarının sorunlu olup olmadığını belirleme.

Bazı yaygın senaryoları ve çekişmeyi hafifletmek için bunları en iyi şekilde nasıl işleyebileceğimizi ele alıyoruz.

SQL Server mandalları nasıl kullanır?

SQL Server'daki bir sayfa 8 KB'tır ve birden çok satır depolayabilir. Eşzamanlılık ve performansı artırmak için, arabellek tutamaçları, sayfadaki fiziksel işlemin süresi boyunca tutulur; oysa kilitler, mantıksal işlem süreci bitene kadar elde tutulur.

Mandallar SQL altyapısının içinde yer alır ve bellek tutarlılığı sağlamak için kullanılırken, kilitler SQL Server tarafından mantıksal işlem tutarlılığı sağlamak için kullanılır. Aşağıdaki tablo, mandalları kilitlerle karşılaştırır:

Yapı	Amaç	Tarafından kontrol edilen	Performans maliyeti	Tarafından ifşa edilen
Mandal	Bellek içi yapıların tutarlılığını garanti eder.	Yalnızca SQL Server motoru.	Performans maliyeti düşüktür. Maksimum eşzamanlılığa izin vermek ve maksimum performans sağlamak için mandallar, mantıksal işlem süresi boyunca tutulan kilitlerden farklı olarak yalnızca bellek içi yapıdaki fiziksel işlem süresi boyunca tutulur.	sys.dm_os_wait_stats - PAGELATCH, PAGEIOLATCH ve LATCH bekleme türleri hakkında bilgi sağlar (LATCH_EX, LATCH_SH arabellek mandalı olmayan tüm beklemeleri gruplandırmak için kullanılır). sys.dm_os_latch_stats : Arabellek olmayan mandal beklemeleri hakkında ayrıntılı bilgi sağlar. sys.dm_db_index_operational_stats - Bu DMV, her dizin için toplu beklemeler sağlar ve bu da mandal ile ilgili performans sorunlarını gidermek için kullanışlıdır.
Kilitle	İşlemlerin tutarlılığını garanti eder.	Kullanıcı tarafından denetlenebilir.	İşlem süresi boyunca kilitlerin tutulması gerektiğinden performans maliyeti mandallara göre yüksektir.	sys.dm_tran_locks. sys.dm_exec_sessions.

SQL Server latch modları ve uyumluluk

SQL Server altyapısının çalışmasının normal bir parçası olarak bazı mandal çekişmesi beklenir. Yüksek eşzamanlılık sisteminde farklı uyumluluklara sahip birden çok eşzamanlı mandal isteğinin gerçekleşmesi kaçınılmazdır. SQL Server, uyumsuz kontrol kilidi isteklerinin, mevcut kontrol kilidi istekleri tamamlanana kadar bir kuyrukta beklemesini isteyerek kontrol kilidi uyumluluğunu sağlar.

Kilitler, erişim düzeyiyle ilgili beş farklı modundan birinde elde edilir. SQL Server kilit modları aşağıdaki gibi özetlenebilir:

• KP: Mandalı tutun. Başvuruda bulunılan yapının yok edilememesini sağlar. bir iş parçacığı arabellek yapısına bakmak istediğinde kullanılır. KP mandalı yok etme (DT) mandalları dışında tüm mandallarla uyumlu olduğundan, KP mandalı hafif olarak kabul edilir, yani kullanıldığında performans üzerindeki etkisi en düşük düzeydedir. KP mandalı DT mandalı ile uyumsuz olduğundan, diğer iş parçacığının başvurulan yapıyı yok etmesini önler. Örneğin, KP mandalı başvuruda bulunan yapının gecikmeli yazıcı işlemi tarafından yok edilmesini engeller. SQL Server bellek sayfası yönetiminde Lazy Writer sürecinin nasıl kullanıldığı hakkında daha fazla bilgi için bkz. Veritabanı Altyapısı'nda sayfaları yazma.

• SH: Paylaşılan kilit. Başvurulan yapıyı okumak için gereklidir (örneğin, bir veri sayfasını okumak). Birden fazla iş parçacığı, paylaşılan bir mandal altında bir kaynağa aynı anda okuma için erişebilir.

• UP: Mandal güncelleniyor. (Paylaşılan mandal) ve KP ile SH uyumludur, ancak başkasıyla uyumlu değildir ve bu nedenle bir EX mandalın başvurulan yapıya yazmasına izin vermez.

• EX: Münhasır mandal. Diğer iş parçacıklarının başvuruda bulunan yapıya yazmasını veya okumasını engeller. Kullanım örneklerinden biri, bozuk sayfa koruması için bir sayfanın içeriğini değiştirmek olabilir.

• DT: Mandalı yok et. Başvuruda bulunılan yapının içeriği yok edilmeden önce alınmalıdır. Örneğin, bir DT mandalı, diğer iş parçacıkları tarafından kullanılabilen boş arabellekler listesine eklemeden önce temiz bir sayfayı boşaltmak için gecikmeli yazıcı işlemi tarafından alınmalıdır.

Mandal modları farklı uyumluluk düzeylerine sahiptir; örneğin, paylaşılan mandal (SH) bir güncelleştirme (UP) veya tutma (KP) mandalıyla uyumludur ancak yok etme mandalı (DT) ile uyumsuzdur. Kilitlemeler uyumlu olduğu sürece aynı yapı üzerinde birden çok kilit eşzamanlı edinilebilir. İş parçacığı uyumlu olmayan bir modda tutulan bir mandalı almaya çalıştığında, kaynağın kullanılabilir olduğunu belirten bir sinyal beklemek için kuyruğa yerleştirilir. SOS_Task türündeki bir spinlock, kuyruğa serileştirilmiş erişim zorunlu kılma yoluyla bekleme kuyruğunu korumak için kullanılır. Kuyruğa öğe eklemek için bu spinlock alınmalıdır. SOS_Task döner kilidi, uyumsuz mandallar serbest bırakıldığında kuyruktaki iş parçacıklarını da sinyalleyerek, bekleyen iş parçacıklarının uyumlu bir mandal edinip çalışmalarına devam etmelerini sağlar. Bekleme kuyruğu, kilitleme istekleri serbest bırakıldığında ilk giren ilk çıkar (FIFO) temelinde işlenir. Mandallar eşitliği sağlamak ve iş parçacığı açlığını önlemek için bu FIFO sistemini izler.

Mandal modu uyumluluğu aşağıdaki tabloda listelenmiştir (Evet uyumluluğu ve Hayır uyumsuzluğu gösterir):

Kilitleme modu	KP	SH	YUKARI	EX	DT
KP	Evet	Evet	Evet	Evet	Hayı
SH	Evet	Evet	Evet	Hayı	Hayı
UP	Evet	Evet	Hayı	Hayı	Hayı
EX	Evet	Hayı	Hayı	Hayı	Hayı
DT	Hayı	Hayı	Hayı	Hayı	Hayı

SQL Server Üst Dizileri ve alt kafesleri

NUMA TABANLI birden çok soketli / çok çekirdekli sistemlerin giderek artmasıyla birlikte, SQL Server 2005, yalnızca 32 veya daha fazla mantıksal işlemciye sahip sistemlerde etkili olan Superlatches, diğer adıyla sublatches, kullanıma sunulmuştur. Superlatches, yüksek düzeyde eşzamanlı OLTP iş yüklerinde belirli kullanım desenleri için SQL motorunun verimliliğini artırır; örneğin, belirli sayfaların ağır salt okunur paylaşımlı (SH) erişim deseni olduğunda, ancak nadiren yazıldığında. Böyle bir erişim deseni olan bir sayfaya örnek olarak B ağacı (dizin) kök sayfası gösteriliyor; SQL altyapısı, B ağacında herhangi bir düzeyde sayfa bölme gerçekleştiğinde kök sayfada paylaşılan bir mandalın tutulmasını gerektirir. Yoğun eklemeli ve yüksek eşzamanlılıklı OLTP iş yükünde sayfa bölme sayısı aktarım hızına göre geniş ölçüde artar ve bu da performansı düşürebilir. Üst anahtarlar, birden çok eşzamanlı çalışan çalışan iş parçacığının mandal gerektirdiği SH paylaşılan sayfalara erişim için daha yüksek performans sağlayabilir. Bunu başarmak için, SQL Server Motoru böyle bir sayfadaki bir kilidi dinamik olarak Superlatch'e yükseltir. Superlatch, tek bir mandalı CPU çekirdeği başına bölüm başına bir alt mandal yapıları dizisine böler; böylece ana mandal bir proxy yeniden yönlendiriciye dönüşür ve salt okunur mandallar için genel durum eşitlemesi gerekmez. Bunu yaparken, her zaman belirli bir CPU'ya atanan çalışanın yalnızca yerel zamanlayıcıya atanan paylaşılan (SH) alt eki alması gerekir.

Uyarı

Belgelerde genellikle dizinlere başvuruda B ağacı terimi kullanılır. Rowstore dizinlerinde Veritabanı Altyapısı bir B+ ağacı uygular. Bu, sütun deposu dizinleri veya bellek için iyileştirilmiş tablolardaki dizinler için geçerli değildir. Daha fazla bilgi için SQL Server ve Azure SQL dizin mimarisi ve tasarım kılavuzuna bakın.

Uyumlu mandalların, örneğin paylaşılan bir Superlatch'in edinilmesi, daha az kaynak kullanır ve sık erişilen sayfalara erişimi, bölünmemiş paylaşılan mandaldan daha iyi ölçeklendirir; çünkü yalnızca yerel NUMA belleğine erişerek performansı önemli ölçüde artıran global durum senkronizasyon gereksinimi ortadan kaldırılır. Buna karşılık, SQL'in tüm alt kafeslerde sinyal vermesi gerektiğinden, özel (EX) Bir Superlatch almak normal bir EX mandal almaktan daha pahalıdır. Bir Superlatch'in yoğun EX erişim deseni kullandığı gözlemlendiğinde, sayfa arabellek havuzundan atıldıktan sonra SQL Altyapısı bunu daha düşük bir seviyeye indirebilir. Aşağıdaki diyagramda normal bir mandal ve bölümlenmiş Superlatch gösterilmiştir:

Super mandalların sayısı, saniye başına süper mandal terfileri ve saniye başına süper mandal indirgemeleri dahil olmak üzere süper mandallar hakkında bilgi toplamak için Performans İzleyici'de SQL Server:Latches nesnesini ve ilişkili sayaçları kullanın. SQL Server:Latches nesnesi ve ilişkili sayaçlar hakkında daha fazla bilgi için bkz. SQL Server, Latches nesnesi.

Mandal bekleme türleri

Toplu bekleme bilgileri SQL Server tarafından izlenir ve Dinamik Yönetim Görünümü (DMW) sys.dm_os_wait_statskullanılarak erişilebilir. SQL Server, DMV'de wait_type ile ilgili olarak sys.dm_os_wait_stats tarafından tanımlanan üç kilit bekleme türünü kullanır:

• Arabellek (BUF) mandalı: Kullanıcı nesneleri için dizin ve veri sayfalarının tutarlılığını garanti etmek için kullanılır. Ayrıca, SQL Server'ın sistem nesneleri için kullandığı veri sayfalarına erişimi korumak için de kullanılır. Örneğin, ayırmaları yöneten sayfalar arabellek tutmaları tarafından korunur. Bunlar Sayfa Boş Alanı (PFS), Genel Ayırma Haritası (GAM), Paylaşılan Genel Ayırma Eşlemesi (SGAM) ve Dizin Ayırma Eşlemesi (IAM) sayfalarını içerir. Arabellek mandalları sys.dm_os_wait_stats içinde wait_type'nin PAGELATCH_* olarak rapor edilir.

• Arabellek olmayan (BUF olmayan) mandal: Arabellek havuzu sayfaları dışındaki bellek içi yapıların tutarlılığını garanti etmek için kullanılır. Bekleme durumları, arabellek dışı mandallar için wait_type olarak LATCH_* bildirilir.

• GÇ mandalı: Bu yapılar G/Ç işlemiyle arabellek havuzuna yüklenmesi gerektiğinde arabellek mandalları tarafından korunan aynı yapıların tutarlılığını garanti eden arabellek mandallarının bir alt kümesi. IO mandalları, başka bir iş parçacığının arabellek havuzuna uyumsuz bir mandalla aynı sayfayı yüklemesini engeller. bir wait_type ile ilişkilendirildi PAGEIOLATCH_*.

  Uyarı

  Önemli PAGEIOLATCH beklemeler görüyorsanız, bu SQL Server'ın G/Ç alt sisteminde beklediği anlamına gelir. Belirli miktarda bekleme ve normal davranış beklense PAGEIOLATCH de, ortalama PAGEIOLATCH bekleme süreleri tutarlı olarak 10 milisaniyenin (ms) üzerindeyse G/Ç alt sisteminin neden baskı altında olduğunu araştırmanız gerekir.

DMV'yi sys.dm_os_wait_stats incelerken arabellek olmayan mandallarla karşılaşırsanız, sys.dm_os_latch_stats arabellek olmayan mandallar için kümülatif bekleme bilgilerinin ayrıntılı dökümünü almak için incelenmelidir. Tüm arabellek mandal beklemeleri mandal sınıfı altında BUFFER sınıflandırılır ve kalanlar arabellek olmayan mandalları sınıflandırmak için kullanılır.

SQL Server mandal çekişmesi belirtileri ve nedenleri

Yoğun bir yüksek eşzamanlılık sisteminde, sık erişilen ve SQL Server'daki diğer denetim mekanizmalarıyla korunan yapılarda etkin çekişmeler görmek normaldir. Bir sayfa için kilitleme mekanizmasına erişme ile ilgili çekişme ve bekleme süresi, kaynak (CPU) kullanımını azaltacak kadar yüksek olduğunda ve bu da verimliliği engellediğinde sorunlu olarak kabul edilir.

Mandal çekişmesi örneği

Aşağıdaki diyagramda mavi çizgi, saniyedeki İşlemler tarafından ölçülen SQL Server'daki aktarım hızını temsil eder; siyah çizgi ortalama sayfa mandal bekleme süresini temsil eder. Bu durumda, her işlem, sıralı olarak artan bir baştaki değerle, örneğin bigint veri türündeki bir sütunu doldururken olduğu gibi, bir kümelenmiş dizin üzerinde bir INSERT işlemi yaparIDENTITY. CPU sayısı 32'ye yükseldikçe, genel aktarım hızının azaldığı ve sayfa mandal bekleme süresinin siyah çizgi tarafından gösterildiği gibi yaklaşık 48 milisaniyeye yükseldiği açıktır. Aktarım hızı ile sayfa mandal bekleme süresi arasındaki bu ters ilişki, kolayca tanılanan yaygın bir senaryodur.

Mandal çekişmesi çözümlendiğinde performans düzeyi

Aşağıdaki diyagramda gösterildiği gibi, SQL Server artık sayfa mandal beklemelerinde şişe boyunlu değildir ve saniyedeki işlemler tarafından ölçüldükçe aktarım hızı 300% artırılır. Bu, bu makalenin devamında açıklanan Hesaplanan Sütun ile Karma Bölümleme Kullanma tekniğiyle gerçekleştirilir. Bu performans artışı, yüksek sayıda çekirdek ve yüksek eşzamanlılık düzeyine sahip sistemlere yöneliktir.

Mandal çekişmesini etkileyen faktörler

OLTP ortamlarında performansı engelleyen mandal çekişmesi genellikle aşağıdaki faktörlerden biriyle veya daha fazlası ile ilgili yüksek eşzamanlılık nedeniyle meydana gelir:

Faktör	Ayrıntılar
SQL Server tarafından kullanılan çok sayıda mantıksal CPU	Herhangi bir çok çekirdekli sistemde mandal çekişmesi oluşabilir. SQLCAT deneyiminde, uygulama performansını kabul edilebilir düzeylerin ötesinde etkileyen aşırı mandal çekişmesi, en yaygın olarak 16'nın üzerinde CPU çekirdeğine sahip sistemlerde gözlemlenmiştir ve daha fazla çekirdek kullanıma sunuldukçe artabilir.
Şema tasarımı ve erişim desenleri	B ağacı derinliği, kümelenmiş ve kümelenmemiş dizin tasarımı, sayfa başına satır boyutu ve yoğunluğu ve erişim desenleri (okuma/yazma/silme etkinliği) aşırı sayfa mandal çekişmesi oluşturabilecek faktörlerdir.
Uygulama düzeyinde yüksek eşzamanlılık derecesi	Aşırı sayfa mandalı çekişmesi genellikle uygulama katmanından gelen yüksek düzeyde eşzamanlı isteklerle birlikte gerçekleşir. Belirli bir sayfa için çok sayıda istek de sunabilen bazı programlama uygulamaları vardır.
SQL Server veritabanları tarafından kullanılan mantıksal dosyaların düzeni	Mantıksal dosya düzeni, Sayfa Boş Alanı (PFS), Genel Ayırma Eşlemesi (GAM), Paylaşılan Genel Ayırma Eşlemesi (SGAM) ve Dizin Ayırma Eşlemesi (IAM) sayfaları gibi ayırma yapılarından kaynaklanan sayfa mandal çekişmesi düzeyini etkileyebilir. Daha fazla bilgi için bkz TempDB İzleme ve Sorun Giderme: Tahsis Darboğazı.
G/Ç alt sistemi performansı	Önemli PAGEIOLATCH beklemeler SQL Server'ın I/O alt sistemi üzerinde beklediğini gösterir.

SQL Server mandal çekişmesini teşhis et

Bu bölüm, ortamınızda sorun olup olmadığını belirlemek için SQL Server mandal çekişmesi tanılamaya yönelik bilgiler sağlar.

Mandal çekişmesi tanılamak için araçlar ve yöntemler

Mandal çekişmelerini tanılamak için kullanılan birincil araçlar şunlardır:

• SQL Server'da CPU kullanımını ve bekleme sürelerini izlemek ve CPU kullanımı ile mandal bekleme süreleri arasında bir ilişki olup olmadığını belirlemeye ilişkin Performans İzleyicisi.

• Soruna ve etkilenen kaynağa neden olan belirli türdeki mandalları belirlemek için kullanılabilecek SQL Server DMV'leri.

• Bazı durumlarda SQL Server işleminin bellek dökümleri Windows hata ayıklama araçlarıyla alınıp analiz edilmelidir.

Uyarı

Bu gelişmiş sorun giderme düzeyi genellikle yalnızca arabellek mandal çekişmesi dışındaki sorunları gidermek için gerekir. Bu tür gelişmiş sorun giderme için Microsoft Ürün Destek Hizmetleri ile etkileşime geçmek isteyebilirsiniz.

Mandal çekişmesi tanılamak için teknik süreç aşağıdaki adımlarda özetlenebilir:

1. Mandal kaynaklı bir ihtilaf olduğunu belirleyin.

2. Etkilenen mandal ve kaynakların türünü belirlemek için Ek: SQL Server Mandal Çekişmesi Betikleri'nde sağlanan DMV görünümlerini kullanın.

3. Farklı Tablo Desenleri için Kilit Çekişmesi İşleme bölümünde açıklanan tekniklerden birini kullanarak çekişmeyi azaltın.

Kilitleme çakışması göstergeleri

Daha önce belirtildiği gibi, mandal çekişmesi yalnızca sayfa mandallarını alma ile ilişkili çekişme ve bekleme süresi CPU kaynakları kullanılabilir olduğunda aktarım hızının artmasını önlediğinde sorunludur. Kabul edilebilir bir çekişme miktarını belirlemek için kullanılabilir G/Ç ve CPU kaynaklarıyla birlikte performans ve aktarım hızı gereksinimlerini göz önünde bulunduran bütünsel bir yaklaşım gerekir. Bu bölümde, mandal çekişmelerinin iş yükü üzerindeki etkisini belirlemek için aşağıdaki adımları izleyin:

1. Temsili test sırasında genel bekleme sürelerini ölçme.
2. Bunları sıralı olarak sırala.
3. Mandallar ile ilgili bekleme sürelerinin oranını belirleyin.

Toplu bekleme bilgileri DMV'den sys.dm_os_wait_stats edinilebilir. En yaygın mandal çekişmesi türü arabellek mandal çekişmesidir ve bu durum, wait_type ve PAGELATCH_* özelliklerine sahip mandallar için bekleme sürelerinde artış olarak gözlemlenir. Arabellek dışı kilitler, LATCH* bekleme tipi altında gruplandırılır. Aşağıdaki diyagramda gösterildiği gibi, arabellek veya arabellek dışı kilitlerin neden olduğu genel bekleme süresinin yüzdesini tespit etmek amacıyla önce sys.dm_os_wait_stats DMV'yi kullanarak sistem beklemelerine topluca bakmalısınız. Arabellek dışı kilitlerle karşılaşırsanız, sys.dm_os_latch_stats DMV'nin de incelenmesi gerekir.

Aşağıdaki diyagram, sys.dm_os_wait_stats ve sys.dm_os_latch_stats DMV'ler tarafından döndürülen bilgiler arasındaki ilişkiyi açıklamaktadır.

DMV hakkında sys.dm_os_wait_stats daha fazla bilgi için SQL Server yardımındaki sys.dm_os_wait_stats bakın.

DMV hakkında daha fazla bilgi için SQL Server yardımındaki sys.dm_os_latch_stats bakabilirsiniz.

Aşağıdaki mandal bekleme süresi ölçüleri, aşırı mandal çekişmesinin uygulama performansını etkilediğinin göstergeleridir:

• Ortalama sayfa mandal bekleme süresi aktarım hızıyla tutarlı bir şekilde artar: Ortalama sayfa mandal bekleme süreleri aktarım hızıyla tutarlı bir şekilde artarsa ve ortalama arabellek mandal bekleme süreleri de beklenen disk yanıt sürelerinin üzerinde artarsa, DMV kullanarak sys.dm_os_waiting_tasks geçerli bekleme görevlerini incelemeniz gerekir. Yalıtılmış olarak analiz edilirse ortalamalar yanıltıcı olabilir, bu nedenle iş yükü özelliklerini anlamak mümkün olduğunda sisteme canlı bakmak önemlidir. Özellikle, herhangi bir sayfada PAGELATCH_EX ve/veya PAGELATCH_SH isteklerinde yüksek bekleme olup olmadığını denetleyin. Ortalama sayfa mandal bekleme sürelerinin verimlilikle nasıl arttığını teşhis etmek için şu adımları izleyin:

  • Geçerli bekleme görevlerine bakmak ve ortalama mandal bekleme süresini ölçmek için sys.dm_os_waiting_tasks sorgusunu Oturum Kimliğine Göre Sıralı veya Belirli Bir Süredeki Beklemeleri Hesapla örnek betiklerini kullanın.

  • Çatışmanın gerçekleştiği dizini ve ilgili tabloyu belirlemek için Tutum Kilitlemesi Çatışmasına Neden Olan Nesneleri Belirlemek İçin Sorgu Arabelleği Tanımlayıcıları örnek betiğini kullanın.

  • Performans İzleyicisi sayacı MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Wait Time ile veya DMV'yi çalıştırarak ortalama sayfa mandal bekleme süresini ölçün sys.dm_os_wait_stats .

  Uyarı

  Belirli bir bekleme türünün ortalama bekleme süresini hesaplamak için, toplam bekleme süresini (sys.dm_os_wait_stats olarak döndürülür) bekleyen görevler sayısına (wt_:type olarak döndürülür) bölün.

• Yoğun yükleme sırasında mandal bekleme türlerinde harcanan toplam bekleme süresi yüzdesi: Uygulama yüküne göre genel bekleme süresinin yüzdesi olarak ortalama mandal bekleme süresi artarsa, mandal çekişmesi performansı etkiliyor olabilir ve araştırılmalıdır.

  SQL Server, Bekleme İstatistikleri nesnesi performans sayaçlarıyla sayfa tutamaç beklemelerini ve sayfa dışı tutamaç beklemelerini ölçün. Ardından bu performans sayaçlarının değerlerini CPU, G/Ç, bellek ve ağ aktarım hızıyla ilişkili performans sayaçlarıyla karşılaştırın. Örneğin, işlemler/sn ve toplu iş istekleri/sn, kaynak kullanımının iki iyi ölçüsü olabilir.

  Uyarı

  Bu DMW, SQL Server örneğinin son başlatılmasından veya toplu bekleme istatistiklerinin kullanılarak sys.dm_os_wait_statssıfırlanmasından bu yana geçen bekleme sürelerini ölçtiğinden, her bekleme türü için göreli bekleme süresi DMV'ye dahil DBCC SQLPERF değildir. Her bir bekleme türünün göreli bekleme süresini hesaplamak için yoğun yüklemeden önce, yoğun yüklemeden sonra anlık görüntüsünü sys.dm_os_wait_stats alın ve ardından farkı hesaplayın. Bu amaç için bir Zaman Aralığındaki Beklemeleri Hesapla örnek betiği kullanılabilir.

  Yalnızca üretim dışı bir ortam için DMV'yi şu komutla temizleyin sys.dm_os_wait_stats :

  DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  

  DMV'yi temizlemek sys.dm_os_latch_stats için benzer bir komut çalıştırılabilir:

  DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');
  

• Uygulama yükü arttıkça ve SQL Server için kullanılabilir CPU sayısı arttıkça aktarım hızı artmaz ve bazı durumlarda azalır: Bu, Mandal Çakışması Örneği'nde gösterilmiştir.

• Uygulama iş yükü arttıkça CPU Kullanımı artmıyor: Uygulama aktarım hızına bağlı eşzamanlılık arttıkça sistemdeki CPU kullanımı artmıyorsa, bu SQL Server'ın bir şey beklediğine ve mandal çekişmesi belirtilerine neden olduğunu gösteren bir göstergedir.

Kök nedeni analiz edin. Yukarıdaki koşulların her biri doğru olsa bile performans sorunlarının kök nedeninin başka bir yerde olması mümkündür. Aslında, çoğu durumda yetersiz CPU kullanımı, kilitlenmelerde bekleme, Giriş/Çıkış ile ilgili beklemeler veya ağ ile ilgili sorunlar gibi diğer bekleme türlerinden kaynaklanır. Temel bir kural olarak, daha ayrıntılı analize geçmeden önce genel bekleme süresinin en büyük oranını temsil eden kaynak beklemesini çözmek her zaman en iyisidir.

Geçerli bekleme arabelleği kitleyicilerini analiz edin

Arabellek mandalı çekişmesi, DMV'de görüntülenen wait_type mandalların PAGELATCH_* veya PAGEIOLATCH_* olanlar için bekleme sürelerinde sys.dm_os_wait_stats bir artış olarak kendini gösterir. Sisteme gerçek zamanlı olarak bakmak için, sys.dm_os_wait_stats, sys.dm_exec_sessions ve sys.dm_exec_requests DMV'lerini birleştirmek üzere bir sistemde aşağıdaki sorguyu çalıştırın. Sonuçlar, sunucuda yürütülen oturumlar için geçerli bekleme türünü belirlemek için kullanılabilir.

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Bu sorgu tarafından sunulan istatistikler aşağıdaki gibi açıklanmıştır:

Istatistik	Açıklama
session_id	Görevle ilişkili oturumun kimliği.
wait_type	SQL Server'ın altyapıya kaydettiği bekleme türü, geçerli bir isteğin yürütülmesini engelliyor.
last_wait_type	Bu istek daha önce engellenmişse, bu sütun son beklemenin türünü döndürür. null atanamaz.
wait_duration_ms	SQL Server örneği başlatıldığından veya toplu bekleme istatistikleri sıfırlandığından bu yana bu bekleme türünde beklemeye harcanan milisaniye cinsinden toplam bekleme süresi.
blocking_session_id	İsteği engelleyen oturumun kimliği.
blocking_exec_context_id	Görevle ilişkili yürütme bağlamının ID'si.
resource_description	Sütun, beklenen sayfayı tam olarak şu formatta listeler: resource_description<database_id>:<file_id>:<page_id>

Aşağıdaki sorgu, arabellek olmayan tüm mandallar için bilgi döndürür:

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

Bu sorgu tarafından sunulan istatistikler aşağıdaki gibi açıklanmıştır:

Istatistik	Açıklama
latch_class	SQL Server'ın altyapıya kaydettiği mandal türü, geçerli bir isteğin yürütülmesini engelliyor.
waiting_requests_count	SQL Server yeniden başladıktan sonra bu sınıftaki kilitlerde bekleme sayısı. Bu sayaç, mandal beklemesi süreci başladığında artırılır.
wait_time_ms	Bu mandal türü üzerinde beklenen ve milisaniye cinsinden ölçülen toplam bekleme süresi.
max_wait_time_ms	Herhangi bir isteğin bu mandal türünde beklerken harcadığı maksimum süre milisaniye cinsindendir.

Bu DMV tarafından döndürülen değerler, veritabanı altyapısının son yeniden başlatılmasından veya DMV'nin sıfırlanmasından bu yana kümülatiftir. sqlserver_start_time Son veritabanı altyapısı başlangıç zamanını bulmak için sys.dm_os_sys_info sütununu kullanın. Uzun süredir çalışan bir sistemde bu, gibi max_wait_time_ms bazı istatistiklerin nadiren yararlı olduğu anlamına gelir. Bu DMV'nin bekleme istatistiklerini sıfırlamak için aşağıdaki komut kullanılabilir:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

SQL Server mandal çekişme senaryoları

Aşağıdaki senaryoların aşırı kilit çekişmesine neden olduğu gözlemlenmiştir.

Son sayfa/arka sayfa ekleme çatışması

Yaygın bir OLTP uygulaması, bir kimlik veya tarih sütununda kümelenmiş dizin oluşturmaktır. Bu, dizinin iyi fiziksel bir şekilde düzenlenmesine yardımcı olur ve bu da hem okumaların hem de dizine yazmanın performansından büyük ölçüde yararlanabilir. Ancak bu şema tasarımı istemeden mandal çekişmesine yol açabilir. Bu sorun, genellikle küçük satırlardan oluşan büyük tablolarda oluşur ve artan tamsayı veya tarih saat anahtarı gibi sıralı artan bir ön anahtar sütunu içeren bir dizinle satır eklerken görülür. Bu senaryoda, uygulama nadiren güncelleştirme veya silme işlemleri gerçekleştirir, arşivleme operasyonları bunun istisnasıdır.

Aşağıdaki örnekte, iş parçacığı bir ve iş parçacığı iki, 299. sayfada depolanacak bir kaydın eklenmesini istiyor. Mantıksal kilitleme perspektifinden bakıldığında, satır düzeyi kilitler kullanıldığından ve aynı sayfadaki her iki kayıtta da özel kilitler aynı anda tutulaabildiği için sorun yoktur. Ancak fiziksel belleğin bütünlüğünü sağlamak için bir kerede yalnızca bir iş parçacığı özel bir mandal alabilir, bu nedenle bellekte kayıp güncelleştirmeleri önlemek için sayfaya erişim serileştirilir. Bu durumda, iş parçacığı 1 özel mandalı alır, ve iş parçacığı 2 bekler, bu kaynak için bekleme olarak bekleme istatistiklerine kaydedilir. Bu, wait_type DMV'sindeki sys.dm_os_waiting_tasks değeri aracılığıyla görüntülenir.

Bu çekişme genellikle "Son Sayfa Ekleme" çekişmesi olarak adlandırılır çünkü B ağacının en sağ kenarında aşağıdaki diyagramda gösterildiği gibi gerçekleşir:

Bu tür mandal çekişmesi aşağıdaki gibi açıklanabilir. Dizine yeni bir satır eklendiğinde, SQL Server değişikliği yürütmek için aşağıdaki algoritmayı kullanır:

1. Yeni kaydı barındıracak doğru sayfayı bulmak amacıyla B ağacını dolaşın.

2. Sayfayı PAGELATCH_EX ile kilitleyerek başkalarının değiştirmesini engelleyin ve yaprak olmayan tüm sayfalarda paylaşılan kilitlemeleri (PAGELATCH_SH) elde edin.

   Uyarı

   Bazı durumlarda SQL Motoru, yaprak olmayan B-ağacı sayfalarında da EX mandalların edinilmesine ihtiyaç duyar. Örneğin, bir sayfa bölme gerçekleştiğinde, doğrudan etkilenen tüm sayfaların münhasır şekilde kilitlenmesi (PAGELATCH_EX) gerekir.

3. Satırın değiştirildiğine dair bir günlük girdisi kaydedin.

4. Satırı sayfaya ekleyin ve sayfayı kirli olarak işaretleyin.

5. Tüm sayfaların mandallarını açın.

Tablo dizini sıralı olarak artan bir anahtarı temel alırsa, her yeni ekleme, sayfa dolana kadar B ağacının sonunda aynı sayfaya gider. Yüksek eşzamanlılık senaryolarında bu, B ağacının en sağ kenarında çekişmelere neden olabilir ve kümelenmiş ve kümelenmemiş dizinlerde oluşabilir. Bu tür çekişmelerden etkilenen tablolar öncelikli olarak sorguları kabul INSERT eder ve sorunlu dizinler için sayfalar normalde nispeten yoğundur (örneğin, satır boyutu ~165 bayt (satır yükü dahil) sayfa başına ~49 satıra eşittir. Bu yoğun ekleme yapılan örnekte, PAGELATCH_EX/PAGELATCH_SH beklemelerinin gerçekleşmesini bekliyoruz ve bu yaygın bir gözlemdir. Sayfa Latch beklemeleri ile Ağaç Sayfa Latch beklemelerini incelemek için sys.dm_db_index_operational_stats DMV'yi kullanın.

Aşağıdaki tabloda, bu tür bir mandal çekişmesiyle gözlemlenen başlıca faktörler özetlenmiştir:

Faktör	Tipik gözlemler
SQL Server tarafından kullanılan mantıksal CPU'lar	Bu tür bir mandal çekişmesi çoğunlukla 16'nın üzerinde CPU çekirdek sisteminde ve en yaygın olarak 32'den fazla CPU çekirdek sisteminde gerçekleşir.
Şema tasarımı ve erişim desenleri	İşlem verileri için tablodaki bir dizinde öncü sütun olarak sıralı olarak artan bir kimlik değeri kullanır. Dizin, yüksek oranda ekleme ile artan bir birincil anahtara sahiptir. Dizinde en az bir sıralı olarak artan sütun değeri vardır. Genellikle bir sayfada birçok satır içeren küçük satır boyutu.
Gözlemlenen bekleme türü	Birçok iş parçacığı, EX tarafından döndürülen DMV'deki SH ile aynı resource_description'a sahip özel (sys.dm_os_waiting_tasks) veya paylaşılan () tutum beklemeleriyle ilintili aynı kaynak için çekişmektedir.
Dikkate alınması gereken tasarım faktörleri	Dizin sütunlarının sırasını sıralı olmayan dizin azaltma stratejisinde açıklandığı gibi değiştirmeyi göz önünde bulundurun. Eklemelerin B ağacı boyunca her zaman aynı şekilde dağıtıldığını garanti edebilirsiniz. Hash bölümleme azaltma stratejisi kullanılırsa, bölümlemenin, kayan pencere arşivleme gibi diğer amaçlarla kullanılmasını engeller. Hash bölümleme azaltma stratejisinin kullanılması, uygulama tarafından kullanılan sorgularda bölümün ortadan kalkmasına neden olabilecek sorunlara SELECT yol açabilir.

Kümelenmemiş dizine ve rastgele eklemelere (kuyruk tablosu) sahip küçük tablolarda kilit çekişmesi

Bu senaryo genellikle bir SQL tablosu geçici kuyruk olarak kullanıldığında (örneğin, zaman uyumsuz bir mesajlaşma sisteminde) görülür.

Bu senaryoda, aşağıdaki koşullarda özel (EX) ve paylaşılan (SH) mandal çekişmesi oluşabilir:

• Ekleme, seçme, güncelleştirme veya silme işlemleri yüksek eşzamanlılık altında gerçekleşir.
• Satır boyutu görece küçüktür (yoğun sayfalara yol açar).
• Tablodaki satır sayısı nispeten küçüktür; bu da, iki veya üç katmanlı bir dizin derinliği ile tanımlanan yüzeysel bir B ağacı oluşmasına neden olur.

Uyarı

Veri işleme dilinin (DML) sıklığı ve sistemin eşzamanlılığı yeterince yüksekse, bundan daha derin bir derinliğe sahip B ağaçları bile bu tür erişim deseniyle çekişme yaşayabilir. Sistemde 16 veya daha fazla CPU çekirdeği kullanılabilir olduğunda eşzamanlılık arttıkça mandal çekişmesi düzeyi belirgin hale gelebilir.

Kümelenmiş dizinde sıralı olmayan bir sütunun önde gelen anahtar olması gibi, erişim B ağacı genelinde rastgele olsa bile mandal çekişmesi oluşabilir. Aşağıdaki ekran görüntüsü, bu tür bir mandal çekişmesi yaşayan bir sistemden alınmaktadır. Bu örnekte çekişme, küçük satır boyutu ve görece sığ bir B ağacının neden olduğu sayfaların yoğunluğundan kaynaklanır. Eşzamanlılık arttıkça, dizinde önde gelen sütun GUID olduğundan, eklemeler B ağacı boyunca rastgele olsa da sayfalarda mandal çekişmesi oluşur.

Aşağıdaki ekran görüntüsünde, beklemeler hem arabellek veri sayfalarında hem de boş alan (PFS) sayfalarında gerçekleşir. Veri dosyalarının sayısı artırıldığında bile arabellek veri sayfalarında mandal çekişmesi yaygındı.

Aşağıdaki tabloda, bu tür bir mandal çekişmesiyle gözlemlenen başlıca faktörler özetlenmiştir:

Faktör	Tipik gözlemler
SQL Server tarafından kullanılan mantıksal CPU'lar	Geçit çekişmesi çoğunlukla 16 veya daha fazla CPU çekirdeğine sahip bilgisayarlarda gerçekleşir.
Şema Tasarımı ve Erişim Desenleri	Küçük tablolarda ekleme/seçme/güncelleştirme/silme gibi işlemler için yüksek oranlı erişim desenleri. Sığ B ağacı (dizin derinliği iki veya üç olan). Küçük satır boyutu (sayfa başına çok sayıda kayıt).
Eşzamanlılık düzeyi	Uygulama katmanından gelen yüksek düzeydeki eşzamanlı istekler sırasında yalnızca mandal kilitlenmesi meydana gelir.
Gözlemlenen bekleme türü	Kök bölmeler nedeniyle arabellek (PAGELATCH_EX ve PAGELATCH_SH) ve arabellek dışı kilitte ACCESS_METHODS_HOBT_VIRTUAL_ROOT beklemeleri gözlemleyin. Ayrıca PAGELATCH_UP PFS sayfalarında bekler. Arabelleksiz mandal beklemeleri hakkında daha fazla bilgi için SQL Server yardımında sys.dm_os_latch_stats'a bakın.

Sığ bir B Ağacı ile dizin genelinde rastgele eklemelerin birleşimi, B ağacında sayfa bölmelerine neden olabilir. Sayfa bölme işlemi gerçekleştirmek için SQL Server'ın tüm düzeylerde paylaşılan (SH) mandallar alması ve ardından B ağacında sayfa bölmelerine dahil olan sayfalarda özel (EX) mandallar alması gerekir. Eşzamanlılık yüksek olduğunda ve veriler sürekli eklenip silindiğinde, B ağacı kök bölmeleri oluşabilir. Bu durumda, diğer eklemelerin B ağacında alınan arabellek dışı mandalları beklemesi gerekebilir. Bu, ACCESS_METHODS_HOBT_VIRTUAL_ROOT DMV'sinde gözlemlenen sys.dm_os_latch_stats mandal türünde çok sayıda bekleme olarak kendini gösterir.

Aşağıdaki betik, etkilenen tablodaki dizinler için B ağacının derinliğini belirlemek amacıyla değiştirilebilir.

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

Sayfa boş alanı (PFS) sayfalarında mandal çekişmesi

PFS, Sayfa Boş Alanı anlamına gelir, SQL Server her veritabanı dosyasındaki her 8088 sayfa (ile PageID = 1başlayarak) için bir PFS sayfası ayırır. PFS sayfasındaki her bayt, sayfada ne kadar boş alan olduğu, ayrılıp ayrılmadığı ve sayfanın hayalet kayıtları depolayıp depolamadığı gibi bilgileri kaydeder. PFS sayfası, ekleme veya güncelleştirme işlemleri sırasında yeni bir sayfa gerektiğinde ayırma için kullanılabilir sayfalar hakkında bilgi içerir. PFS sayfası, ayırmaların veya serbest bırakmaların gerçekleştiği durumlar da dahil olmak üzere çeşitli senaryolarda güncelleştirilmelidir. PFS sayfasını korumak için güncelleştirme (UP) mandalı kullanılması gerektiğinden, bir dosya grubunda nispeten az veri dosyanız ve çok sayıda CPU çekirdeğiniz varsa PFS sayfalarında mandal çekişmesi oluşabilir. Bunu çözmenin basit bir yolu, dosya grubu başına dosya sayısını artırmaktır.

Uyarı

Dosya grubu başına dosya sayısının artırılması, diske bellek taşan birçok büyük sıralama işlemine sahip yükler gibi belirli yüklerin performansını olumsuz etkileyebilir.

Eğer PAGELATCH_UP içinde PFS veya SGAM sayfaları için çok fazla tempdb bekleme gözlemleniyorsa, bu darboğazı ortadan kaldırmak için şu adımları tamamlayın:

1. tempdb veri dosyalarının sayısının sunucunuzdaki işlemci çekirdeği sayısına eşit olması için veri dosyalarını tempdb'ye ekleyin.

2. SQL Server izleme bayrağı 1118'i etkinleştirin.

Sistem sayfalarında çekişme nedeniyle oluşan ayırma performans sorunları hakkında daha fazla bilgi için ayırma performans sorunu nedir? blog gönderisine bakın.

Tempdb'de tablo değerli işlevler ve kilitlenme çekişmesi

Ayırma çekişmesi dışında, sorgular içinde ağır TVF kullanımı gibi tempdb üzerinde mandal çekişmesine neden olabilecek başka faktörler de vardır.

Farklı tablo desenleri için mandal çekişmesi işleme

Aşağıdaki bölümlerde, aşırı mandal çekişmesiyle ilgili performans sorunlarını gidermek veya geçici olarak çözmek için kullanılabilecek teknikler açıklanmaktadır.

Sıralı olmayan bir baştaki dizin anahtarı kullanma

Mandal çekişmesi işlemenin bir yöntemi, sıralı dizin anahtarını sıralı olmayan bir anahtarla değiştirerek eklemeleri bir dizin aralığına eşit olarak dağıtmaktır.

Bu genellikle, dizinde iş yükünü orantılı olarak dağıtan bir önde gelen sütuna sahip olarak yapılır. Burada birkaç seçenek vardır:

Seçenek: Değerleri dizin anahtarı aralığı arasında dağıtmak için tablonun içindeki bir sütunu kullanın

İş yükünüzü, eklemeleri anahtar aralığına dağıtmak için kullanılabilecek doğal bir değer olarak değerlendirin. Örneğin, bir müşteri aynı anda yalnızca bir ATM kullanabildiğinden, ATM_ID işlem tablosuna eklemeleri, para çekme işlemleri için dağıtmakta iyi bir aday olabilecek bir ATM bankacılığı senaryosunu düşünün. Benzer şekilde, bir satış noktası sisteminde, belki Checkout_ID veya mağaza kimliği, eklemeleri bir anahtar aralığına dağıtmak için kullanılabilecek doğal bir değer olabilir. Bu teknik, önceki anahtar sütununun tanımlanan sütunun değeri veya bu değerin bir ya da daha fazla ekstra sütunla birleştirilmiş bir hash değeri olacak şekilde bileşik bir dizin anahtarı oluşturulmasını gerektirir. Bu, benzersizlik sağlar. Çoğu durumda değerin karması en iyi sonucu verir çünkü çok fazla farklı değer kötü fiziksel kuruluşa neden olur. Örneğin, bir satış noktasında, Mağaza Kimliği'nden cpu çekirdeği sayısıyla uyumlu bir modül olan bir karma oluşturulabilir. Bu teknik, tablo içinde görece az sayıda aralıkla sonuçlanır, ancak takma çekişmelerini önlemek için eklemeleri bu şekilde dağıtmak yeterli olur. Aşağıdaki görüntüde bu teknik gösterilmektedir.

Önemli

Bu düzen, geleneksel dizin oluşturma en iyi yöntemleriyle çelişiyor. Bu teknik, B ağacı genelinde eklemelerin tekdüzen dağıtılmasını sağlamaya yardımcı olsa da, uygulama düzeyinde bir şema değişikliği de gerektirebilir. Buna ek olarak, bu desen kümelenmiş dizini kullanan aralık taramaları gerektiren sorguların performansını olumsuz etkileyebilir. Bu tasarım yaklaşımının düzgün çalışıp çalışmadığını belirlemek için iş yükü desenlerinin bir analizi gerekir. Bu desen, ekleme verimi ve ölçeklendirme kazanmak için sıralı tarama performansından biraz fedakârlık yapabiliyorsanız uygulanmalıdır.

Bu düzen bir performans laboratuvarı etkileşimi sırasında uygulandı ve 32 fiziksel CPU çekirdeğine sahip bir sistemde mandal çekişmesi çözüldü. Tablo, kapanış bakiyesini bir işlemin sonunda depolamak için kullanılmıştır; her iş işlemi tabloya tek bir ekleme gerçekleştirdi.

Özgün tablo tanımı

Özgün tablo tanımı kullanılırken, kümelenmiş indeks pk_table1 üzerinde aşırı kilitlenme anlaşmazlığı yaşandığı gözlendi.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

Uyarı

Tablo tanımındaki nesne adları özgün değerlerinden değiştirildi.

Yeniden sıralanmış dizin tanımı

Dizinin anahtar sütunlarını, birincil anahtarın başında UserID olacak şekilde yeniden sıralamak, eklemelerin sayfalar arasında neredeyse rastgele dağılımını sağladı. Tüm kullanıcılar aynı anda çevrimiçi olmadığından sonuçta elde edilen dağıtım 100% rastgele değildi, ancak dağıtım aşırı mandal çekişmelerini hafifletecek kadar rastgeleydi. Dizin tanımını yeniden sıralamanın bir sakıncası, bu tablodaki tüm seçme sorgularının UserID ve TransactionID eşitlik koşullarını kullanacak şekilde değiştirilmesi gerektiğidir.

Önemli

Üretim ortamında çalıştırmadan önce test ortamındaki tüm değişiklikleri kapsamlı bir şekilde test ettiğinizden emin olun.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

Birincil anahtarın başlangıç sütunu olarak karma değeri kullanma

Aşağıdaki tablo tanımı, CPU sayısına uygun bir modülü oluşturmak için kullanılabilir; bu modül, B Ağacı genelinde eşit bir dağılım sağlamak amacıyla sıralı olarak artan değer HashValue kullanılarak TransactionID olarak üretilir.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

Seçenek: Dizinin önde gelen anahtar sütunu olarak GUID kullanın

Doğal ayırıcı yoksa guid sütunu, eklemelerin tekdüzen dağıtılmasını sağlamak için dizinin önde gelen anahtar sütunu olarak kullanılabilir. Dizin anahtarı yaklaşımında öndeki sütun olarak GUID kullanılması diğer özellikler için bölümlemenin kullanılmasını sağlarken, bu teknik daha fazla sayfa bölme, zayıf fiziksel kuruluş ve düşük sayfa yoğunluklarının olası olumsuz yönlerini de ortaya çıkarabilir.

Uyarı

GUID'lerin dizinlerin önde gelen temel sütunları olarak kullanılması, son derece tartışmalı bir konudur. Bu yöntemin olumlu ve dezavantajlarıyla ilgili ayrıntılı bir tartışma bu makalenin kapsamı dışındadır.

Hesaplanan sütunla karma bölümleme kullanma

SQL Server içindeki tablo bölümleme, aşırı mandal çekişmesi azaltmak için kullanılabilir. Bölümlenmiş tabloda hesaplanan sütun ile karma bölümleme şeması oluşturmak, şu adımlarla gerçekleştirilebilecek yaygın bir yaklaşımdır:

1. Bölümleri tutmak için yeni bir dosya grubu oluşturun veya mevcut bir dosya grubunu kullanın.

2. Yeni bir dosya grubu kullanıyorsanız, en iyi düzeni kullanmaya dikkat edin ve tek tek dosyaları LUN üzerinden eşit şekilde dengeleyin. Erişim düzeni yüksek oranda ekleme içeriyorsa, SQL Server bilgisayarında fiziksel CPU çekirdekleriyle aynı sayıda dosya oluşturduğunuzdan emin olun.

3. CREATE PARTITION FUNCTION Tabloları X bölümlerine bölmek için komutunu kullanın; burada X, SQL Server bilgisayarındaki fiziksel CPU çekirdeklerinin sayısıdır. (en az 32 bölüm)

   Uyarı

   Bölüm sayısının CPU çekirdeği sayısına 1:1 hizalaması her zaman gerekli değildir. Çoğu durumda bu, CPU çekirdeği sayısından daha az bir değer olabilir. Daha fazla bölüm olması, tüm bölümlerde arama yapmak zorunda olan sorgular için daha fazla ek yüke neden olabilir ve bu durumlarda daha az bölüm yardımcı olabilir. Gerçek müşteri iş yüklerine sahip 64 ve 128 mantıksal CPU sistemleri üzerindeki SQLCAT testinde 32 bölüm aşırı mandal çekişmelerini çözmek ve ölçek hedeflerine ulaşmak için yeterli olmuştur. Sonuç olarak, test yoluyla ideal bölüm sayısı belirlenmelidir.

4. CREATE PARTITION SCHEME komutunu kullanın:

   • partition işlevini dosya gruplarına bağlayın.
   • Tabloya tinyint veya smallint türünde bir karma sütun ekleyin.
   • İyi bir karma dağılımı hesaplayın. Örneğin, HASHBYTES modulo veya BINARY_CHECKSUM ile kullanın.

Aşağıdaki örnek betik, uygulamanızın amaçları doğrultusunda özelleştirilebilir:

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Bu betik, Son sayfa/sondaki sayfa üzerine ekleme çekişmesi nedeniyle sorun yaşayan bir tabloyu karma bölümlendirme yapmak için kullanılabilir. Bu teknik, tabloyu bölümleyerek ve eklemeleri karma değer modulus işlemiyle tablo bölümleri arasında dağıtarak çekişmeyi son sayfadan taşır.

Hesaplanan sütunla karma bölümleme ne yapar?

Aşağıdaki diyagramda gösterildiği gibi, bu teknik karma işlevindeki dizini yeniden oluşturarak ve SQL Server bilgisayarında fiziksel CPU çekirdekleriyle aynı sayıda bölüm oluşturarak çekişmeyi son sayfadan taşır. Eklemeler mantıksal aralığın sonuna (sıralı olarak artan bir değer) girmeye devam eder ancak karma değer modulus işlemi, eklemelerin farklı B ağaçları arasında bölünmesini sağlar ve bu da performans sorununu ortadan kaldırır. Bu, aşağıdaki diyagramlarda gösterilmiştir:

Karma bölümleme kullanılırken ödünler

Karma bölümleme, eklemelerdeki çekişmeleri ortadan kaldırsa da, bu tekniği kullanıp kullanmamaya karar verirken göz önünde bulundurmanız gereken çeşitli dengeler vardır:

• SELECT sorgularının, koşula hash bölümünün eklenmesi için değiştirilmesi ve bu sorgular yapıldığında bölüm eleme sağlamayan bir sorgu planına yol açması çoğu durumda gereklidir. Aşağıdaki ekran görüntüsünde karma bölümleme uygulandıktan sonra bölüm eleme içermeyen hatalı bir plan gösterilmektedir.

  

• Aralık tabanlı raporlar gibi diğer bazı sorgularda bölüm eleme olasılığını ortadan kaldırır.

• Karma bölümlenmiş tabloyu başka bir tabloya eklerken, bölüm eleme elde etmek için ikinci tablonun aynı anahtarda karma bölümlenmesi ve karma anahtarın birleştirme ölçütlerinin bir parçası olması gerekir.

• Karma bölümleme, kayan pencere arşivleme ve bölüm değiştirme işlevi gibi diğer yönetim özellikleri için bölümleme kullanımını engeller.

Aşırı mandal çekişmesini azaltarak eklemelerdeki çekişmeyi hafifleten karma bölümleme, genel sistem aktarım hızını artırmak için etkili bir stratejidir. Bazı dengeler söz konusu olduğundan, bazı erişim desenleri için en uygun çözüm olmayabilir.

Kilit tutuşmasını ele almak için kullanılan tekniklerin özeti

Aşağıdaki iki bölümde, aşırı mandal çekişmesi için kullanılabilecek tekniklerin özeti sağlanır:

Sıralı olmayan anahtar/dizin

Avantajlar:

• Kayan pencere düzeni ve bölüm anahtarı işlevselliği kullanarak verileri arşivleme gibi diğer bölümleme özelliklerinin kullanılmasına izin verir.

Dezavantajlar:

• Eklemelerin her zaman 'yeterince yakın' tekdüzen bir dağılımını sağlamak için bir anahtar/dizin seçerken karşılaşılabilecek olası zorluklar.
• Öndeki sütun olarak GUID, aşırı sayfa bölme işlemlerine neden olabileceği uyarısıyla tekdüzen dağıtımı garanti etmek için kullanılabilir.
• B-Tree genelinde rastgele eklemeler çok fazla sayfa bölme işlemine neden olabilir ve yaprak olmayan sayfalarda mandal çekişmesine yol açabilir.

Hash bölümleme ile hesaplanan sütun

Avantajlar:

• Eklemeler için saydam.

Dezavantajlar:

• Bölümleme, bölüm anahtarı seçeneklerini kullanarak verileri arşivleme gibi hedeflenen yönetim özellikleri için kullanılamaz.
• Bireysel ve aralık tabanlı seçim/güncelleme sorguları ile birleşim gerçekleştiren sorgular dahil, sorgularda bölüm eleme sorunlarına neden olabilir.
• Kalıcı hesaplanan sütun eklemek çevrimdışı bir işlemdir.

Tip

Diğer teknikler için PAGELATCH_EX beklemeleri ve ağır eklemeler blog gönderisine bakın.

Adım Adım Kılavuz: Kilitleme Anlaşmazlığını Tanılama

Aşağıdaki kılavuzda, gerçek bir dünya senaryosundaki bir sorunu çözmek için SQL Server Mandal Çekişmesi tanılama ve Farklı Tablo Desenleri için Mandal Çekişmesi İşleme başlığında açıklanan araçlar ve teknikler gösterilmektedir. Bu senaryoda, 256 GB belleğe sahip 8 yuva, 32 fiziksel çekirdek sistemi üzerinde çalışan bir SQL Server uygulamasına göre işlem gerçekleştiren yaklaşık 8.000 mağazanın simülasyonunu yapan bir satış noktası sisteminin yük testini gerçekleştirmek için müşteri etkileşimi açıklanır.

Aşağıdaki diyagramda, satış noktası sistemini test etmek için kullanılan donanım ayrıntıları yer alır:

Belirti: Sık kullanılan mandallar

Bu durumda, genellikle ortalama 1 ms'den fazla yüksek olarak tanımladığımız yüksek beklemeler PAGELATCH_EX gözlemledik. Bu durumda sürekli olarak 20 ms'yi aşan beklemeler gözlemledik.

Mandal çekişmesinin problemli olduğunu belirledikten sonra, mandal çekişmesine neyin sebep olduğunu belirlemek için harekete geçtik.

Mandal çekişmesi neden olan nesneyi yalıtma

Aşağıdaki betik, resource_description sütununu kullanarak PAGELATCH_EX çekişmeye hangi indeksin neden olduğunu belirler.

Uyarı

Bu betik tarafından döndürülen resource_description sütunu, <DatabaseID,FileID,PageID> biçiminde kaynak açıklamasını sağlar. DatabaseID ile ilişkilendirilmiş veritabanının adı, DatabaseID değerinin DB_NAME() fonksiyonuna geçirilmesiyle belirlenebilir.

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       s.name AS schema_name,
       o.name AS object_name,
       i.name AS index_name
FROM sys.dm_os_buffer_descriptors AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS wt
      WHERE wait_type LIKE 'PAGELATCH%') AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
        AND bd.file_id = SUBSTRING(wt.rd, wt.file_index + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

Burada gösterildiği gibi, ihtilaf LATCHTEST tablosu ve CIX_LATCHTEST dizin adı üzerindedir. Not adları iş yükünü anonimleştirecek şekilde değiştirildi.

Daha gelişmiş, tekrarlı sorgulama yapan ve yapılandırılabilir bir süre boyunca toplam bekleme süresini belirlemek için geçici bir tablo kullanan bir betik için, ekteki Mandal Çakışmasına Neden Olan Nesneleri Belirlemek için Sorgu Tampon Tanımlayıcıları başlıklı bölüme bakın.

Latç çekişmesine neden olan nesneyi izole etmek için alternatif teknik

Bazen sorgulamak sys.dm_os_buffer_descriptorspratik olmayabilir. Sistemdeki bellek ve arabellek havuzu için kullanılabilir olan bellek arttıkça bu DMV'yi çalıştırmak için gereken süre de artar. 256 GB'lık bir sistemde bu DMV'nin çalışması 10 dakika veya daha uzun sürebilir. Alternatif bir teknik mevcuttur ve aşağıda özetlenmiştir ve laboratuvarda çalıştırdığımız farklı bir iş yüküyle gösterilmiştir:

1. Ek betiği Sorgu sys.dm_os_waiting_tasks Bekleme Süresine Göre Sıralı komutunu kullanarak geçerli bekleme görevlerini sorgula.

2. Bir konvoyun gözlemlendiği kritik sayfayı belirleyin; bu durum, birden fazla iş parçacığının aynı sayfada çekişme yaşadığı时 gerçekleşir. Bu örnekte, ekleme işlemi gerçekleştiren iş parçacıkları B ağacının sonundaki sayfada rekabet eder ve bir EX kilidi elde edene kadar bekler. Bu, bizim örneğimizde 8:1:111305ilk sorgudaki resource_description tarafından gösterilir.

3. Aşağıdaki söz dizimi ile sayfa DBCC PAGE hakkında daha fazla bilgi sunan izleme bayrağı 3604'i etkinleştirin, resource_description aracılığıyla elde ettiğiniz değeri parantez içindeki değerle değiştirin:

   Konsol çıkışını etkinleştirmek için izleme bayrağı 3604'i etkinleştirin:

   DBCC TRACEON (3604);
   

   Sayfanın ayrıntılarını inceleyin:

   DBCC PAGE (8, 1, 111305, -1);
   

4. DBCC çıkışını inceleyin. bizim örneğimizde 78623323ilişkili bir Metadata ObjectID olmalıdır.

   

5. Artık aşağıdaki komutu çalıştırarak çekişmeye neden olan nesnenin adını (beklendiği gibi ) LATCHTESTbelirleyebiliriz.

   Uyarı

   Doğru veritabanı bağlamında olduğunuzdan emin olun, aksi takdirde sorgu döndürür NULL.

   --get object name
   SELECT OBJECT_NAME(78623323);
   

   

Özet ve sonuçlar

Yukarıdaki tekniği kullanarak, ihtilafın sıralı olarak artan anahtar değerine sahip ve en fazla ekleme yapılan tabloda bulunan kümelenmiş bir dizinde gerçekleştiğini doğrulayabildik. Bu tür bir çekişme, datetime, identity veya uygulama tarafından oluşturulan TransactionID gibi sıralı olarak artan anahtar değerine sahip dizinler için yaygındır.

Bu sorunu çözmek için hesaplanan sütunla karma bölümleme kullandık ve performansta 690 birimlik bir% iyileşme gözlemledik. Aşağıdaki tabloda, hesaplanan sütunla karma bölümleme uygulamadan önce ve sonra uygulamanın performansı özetleniyor. Mandal çekişmesi performans sorunu kaldırıldıktan sonra CPU kullanımı beklendiği gibi aktarım hızına göre geniş ölçüde artar:

Ölçüm	Karma bölümlemeden önce	Hash bölümlemeden sonra
İş İşlemleri/Sn	36	249
Ortalama Sayfa Mandalı Bekleme Süresi	36 milisaniye	0,6 milisaniye
Kilidi Bekleme Süresi/Saniye	9,562	2,873
SQL İşlemci Zamanı	%24	78%
SQL Batch İstekleri/sn	12,368	47,045

Önceki tabloda görüldüğü gibi, aşırı sayfa mandal çekişmesi nedeniyle performans sorunlarının doğru şekilde tanımlanması ve çözülmesi, genel uygulama performansı üzerinde olumlu bir etkiye sahip olabilir.

Ek: Alternatif teknik

Aşırı sayfa mandalı çekişmesini önlemeye yönelik olası stratejilerden biri, her satırın tam sayfa kullanmasını sağlamak için satırları karakter sütunuyla doldurmaktır. Bu strateji, genel veri boyutu küçük olduğunda ve aşağıdaki faktörlerin birleşiminden kaynaklanan EX sayfa mandalı uyuşmazlığını gidermek istediğinizde bir seçenektir.

• Küçük satır boyutu
• Sığ B ağacı
• Yüksek oranda rastgele ekleme, seçme, güncelleştirme ve silme işlemlerine sahip erişim deseni
• Geçici kuyruk tabloları gibi küçük tablolar

Satırları doldurarak tam sayfayı kaplayarak SQL'in daha fazla sayfa ayırmasını, eklemeler için daha fazla sayfa ayırmasını ve sayfa mandal çekişmesini azaltmasını EX sağlarsınız.

Satırları her bir satırın tam bir sayfayı kaplamasını sağlamak için doldurun.

Aşağıdakine benzer bir betik, tüm sayfayı kaplarken satırları bölmek için kullanılabilir:

ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');

Uyarı

Sayfa başına bir satır yerleştirilmesini sağlayarak doldurma değeri için ek CPU gereksinimlerini ve satırı günlüğe kaydetmek için gereken fazladan alanı azaltmak amacıyla, mümkün olan en küçük karakter boyutunu kullanın. Her bayt, yüksek performanslı bir sistemde sayılır.

Bu teknik tamlık açısından açıklanmıştır; uygulamada SQLCAT bunu yalnızca tek bir performans katılımında 10.000 satır içeren küçük bir tabloda kullanmıştır. Bu tekniğin sınırlı bir uygulaması vardır çünkü büyük tablolar için SQL Server'da bellek baskısını artırır ve yaprak olmayan sayfalarda arabellek dışı mandal çekişmesiyle sonuçlanabilir. Ek bellek baskısı, bu tekniğin uygulanması için önemli bir sınırlayıcı faktör olabilir. Modern bir sunucuda kullanılabilir bellek miktarıyla, OLTP iş yükleri için çalışma kümesinin büyük bir kısmı genellikle bellekte tutulur. Veri kümesi belleğe sığmayan bir boyuta çıktığında performansta önemli bir bırakma oluşur. Bu nedenle, bu teknik yalnızca küçük tablolar için geçerli olan bir şeydir. Bu teknik SQLCAT tarafından büyük tablolar için son sayfa/sondaki sayfa ekleme çekişmesi gibi senaryolarda kullanılmaz.

Önemli

Bu stratejinin kullanılması, B ağacının yaprak olmayan düzeylerinde çok sayıda sayfa bölünmesine yol açabileceği için ACCESS_METHODS_HOBT_VIRTUAL_ROOT mandal türünde çok sayıda beklemeye neden olabilir. Bu durumda, SQL Server'ın tüm düzeylerde paylaşılan (SH) mandalları ve ardından sayfa bölmenin mümkün olduğu B ağacındaki sayfalarda özel (EX) mandallar alması gerekir. sys.dm_os_latch_stats DMV'de satır doldurma işlemi sonrasında ACCESS_METHODS_HOBT_VIRTUAL_ROOT kilit tipi için yüksek sayıda bekleme olup olmadığını kontrol edin.

Ek: SQL Server kilit çekişmesi betikleri

Bu bölüm, mandal çekişmesi sorunlarını tanılamaya ve gidermeye yardımcı olmak için kullanılabilecek betikler içerir.

Oturum kimliğine göre sıralanmış sorgu sys.dm_os_waiting_tasks

Aşağıdaki örnek betik, sys.dm_os_waiting_tasks sorgular ve oturum kimliğine göre sıralanmış mandal bekleme sürelerini döndürür.

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

Sorgu sys.dm_os_waiting_tasks bekleme süresine göre sıralanmış

Aşağıdaki örnek betik sorgular sys.dm_os_waiting_tasksve bekleme süresine göre sıralanmış mandal beklemelerini döndürür:

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Bir zaman aralığındaki beklemeleri hesaplama

Aşağıdaki betik, belirli bir zaman dilimindeki eşlem beklemelerini hesaplar ve döndürür.

/* Snapshot the current wait stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb.  if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

--clean up table
DELETE FROM #_wait_stats
WHERE snap_time = @previous_snap_time;

Mandal çekişmesine neden olan nesneleri belirlemek için arabellek tanımlayıcılarını sorgulayın.

Aşağıdaki betik, hangi nesnelerin en uzun kilitlenme bekleme süreleriyle ilişkili olduğunu belirlemek için arabellek tanımlayıcılarını sorgular.

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

Hash bölümleme betiği

Bu betiğin kullanımı, Hesaplanan Sütun ile Karma Bölümleme kullanma bölümünde açıklanmıştır ve uygulamanıza yönelik olarak özelleştirilmelidir.

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

İlgili içerik

• Performans izleme ve ayarlama araçları