SQL Server'da spinlock çekişmesini tanımlama ve çözümleme

Bu makalede, yüksek eşzamanlılık sistemlerinde SQL Server uygulamalarında spinlock çekişmesiyle ilgili sorunları tanımlama ve çözme hakkında ayrıntılı bilgi sağlanmaktadır.

Uyarı

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. İlk olarak Microsoft SQL Server Müşteri Danışmanlığı Ekibi (SQLCAT) ekibi tarafından yayımlandı.

Arka plan

Geçmişte, ticari Windows Server bilgisayarları yalnızca bir veya iki mikro işlemci/CPU yongasını kullanmıştır ve CPU'lar yalnızca tek bir işlemci veya "çekirdek" ile tasarlanmıştır. Bilgisayar işleme kapasitesindeki artışlar, daha hızlı CPU'lar kullanılarak elde edilmiştir ve transistör yoğunluğundaki ilerlemelerle büyük ölçüde mümkün hale getirilmiştir. "Moore Yasası"nın ardından, transistör yoğunluğu veya entegre bir devreye yerleştirilebilen transistörlerin sayısı, 1971'de ilk genel amaçlı tek yonga CPU'sunun geliştirilmesinden bu yana her iki yılda bir tutarlı olarak iki katına çıkar. Son yıllarda, daha hızlı CPU'larla bilgisayar işleme kapasitesini artırmaya yönelik geleneksel yaklaşım, birden çok CPU'ya sahip bilgisayarlar oluşturarak geliştirilmiştir. Bu yazıdan itibaren Intel Nehalem CPU mimarisi CPU başına sekiz adede kadar çekirdek barındırmaktadır. Bu mimari, sekiz yuvalı bir sistemde kullanıldığında eşzamanlı çoklu iş parçacığı (SMT) teknolojisi kullanılarak iki katına çıkarılarak 128 mantıksal işlemciye kadar çıkarılabilir. Intel CPU'larda SMT, Hyper-Threading Teknolojisi olarak adlandırılır. x86 uyumlu bilgisayarlardaki mantıksal işlemci sayısı arttıkça, mantıksal işlemciler kaynaklar için rekabet ettikçe eşzamanlılık ile ilgili sorunlar artar. Bu kılavuzda, SQL Server uygulamalarını bazı iş yükleriyle yüksek eşzamanlılık sistemlerinde çalıştırırken gözlemlenen belirli kaynak çekişmesi sorunlarının nasıl tanımlanıp çözüleceğini açıklar.

Bu bölümde, SQLCAT ekibinin spinlock çekişmesi sorunlarını tanılama ve çözme konusunda öğrendiği dersleri analiz edeceğiz. Spinlock çekişmesi, yüksek ölçekli sistemlerdeki gerçek müşteri iş yüklerinde gözlemlenen eşzamanlılık sorunlarından biridir.

Spinlock çekişmesi belirtileri ve nedenleri

Bu bölümde, SQL Server'da OLTP uygulamalarının performansına zarar veren spinlock çekişmesiyle ilgili sorunların nasıl tanılandığı açıklanır. Spinlock tanılama ve sorun giderme, hata ayıklama araçları ve Windows iç bilgileri gerektiren gelişmiş bir konu olarak kabul edilmelidir.

Spinlock'lar, veri yapılarına erişimi korumak için kullanılan basit eşitleme temelleridir. Spinlock'lar SQL Server'a özgü değildir. İşletim sistemi, belirli bir veri yapısına yalnızca kısa bir süre için erişim gerektiğinde bunları kullanır. Bir spinlock almaya çalışan bir iş parçacığı erişim alamadığında, kaynağın mevcut olup olmadığını kontrol etmek için döngü içinde düzenli aralıklarla kontrol ederek çalışır ve hemen teslim etmek yerine. Bir süre sonra, bir spinlock'ta bekleyen bir iş parçacığı kaynağı almadan önce verim alır. Verim, aynı CPU üzerinde çalışan diğer iş parçacıklarının yürütülmesine olanak tanır. Bu davranış geri alma olarak bilinir ve bu makalenin devamında daha ayrıntılı olarak ele alınmalıdır.

SQL Server, bazı iç veri yapılarına erişimi korumak için spinlock'ları kullanır. Spinlock'lar, belirli veri yapılarına erişimi mandallara benzer şekilde seri hale getirmek için altyapı içinde kullanılır. Bir mandal ile spinlock arasındaki temel fark, spinlock'ların bir veri yapısının kullanılabilirliğini kontrol etmek için belirli bir süre boyunca döngüde dönmesidir; mandal tarafından korunan bir yapıya erişim sağlamaya çalışan bir iş parçacığı ise kaynak mevcut değilse derhal vazgeçer. Verim, başka bir iş parçacığının yürütülebilmesi için bir iş parçacığının CPU'nun dışına bağlam geçişi gerektirir. Bu, nispeten pahalı bir işlemdir ve kısa bir süre boyunca tutulan kaynaklar için, bir iş parçacığının düzenli aralıklarla kaynağın kullanılabilirliğini denetleen bir döngüde yürütülmesine izin vermek genel olarak daha verimlidir.

SQL Server 2022'de (16.x) kullanıma sunulan Veritabanı Altyapısı'nda yapılan iç ayarlamalar, spinlock'ları daha verimli hale getirir.

Belirti -leri

Yoğun bir yüksek eşzamanlılık sisteminde, spinlock'lar tarafından korunan sık erişilen yapılarda etkin çekişmeler görmek normaldir. Bu kullanım yalnızca çekişme önemli CPU ek yüküne neden olduğunda sorunlu olarak kabul edilir. Spinlock istatistikleri SQL Server içindeki sys.dm_os_spinlock_stats Dinamik Yönetim Görünümü (DMV) tarafından kullanıma sunulur. Örneğin, bu sorgu aşağıdaki çıkışı verir:

Uyarı

Bu DMV tarafından döndürülen bilgilerin yorumlanmasıyla ilgili diğer ayrıntılar bu makalenin devamında ele alınmalıdır.

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

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

Köşe yazısı	Açıklama
Çarpışma	Bir iş parçacığı, bir spinlock ile korunan bir kaynağa erişmekten her engellendiğinde bu değer artırılır.
Spin	Bu değer, bir iş parçacığı bir döngüyü yürütürken spinlock'un kullanılabilir duruma gelmesini beklediği her seferinde artırılır. Bu, bir iş parçacığının kaynak edinmeye çalışırken yaptığı iş miktarının ölçüsüdür.
Çarpışma başına dönüş sayısı	Çarpışma başına dönüş oranı.
Uyku süresi	Geri alma olaylarıyla ilgili; ancak bu makalede açıklanan tekniklerle ilgili değildir.
Geri çekilmeler	Tutulan bir kaynağa erişmeye çalışan bir "dönen" iş parçacığının aynı CPU üzerindeki diğer iş parçacıklarının yürütülmesine izin vermek zorunda olduğunu belirlediğinde gerçekleşir.

Bu tartışmanın amaçları doğrultusunda, sistemin ağır yük altında olduğu belirli bir süre içinde gerçekleşen çarpışma, döndürme ve geri alma olaylarının sayısı, özellikle ilgi çekici istatistiklerdir. Bir iş parçacığı bir spinlock tarafından korunan bir kaynağa erişmeye çalıştığında, bir çakışma oluşur. Çakışma oluştuğunda, çarpışma sayısı artırılır ve iş parçacığı döngüde dönmeye başlar ve kaynağın kullanılabilir olup olmadığını düzenli aralıklarla denetler. İş parçacığı her dönüşte (döngüde) döndürme sayısı artırılır.

Çarpışma başına dönme sayısı, bir iş parçacığı tarafından spinlock kilidi tutulurken gerçekleşen döndürme miktarının bir ölçüsüdür ve iş parçacıkları spinlock'u tutarken kaç döndürme oluştuğunu gösterir. Örneğin, çarpışma başına küçük döndürmeler ve yüksek çarpışma sayısı, spinlock'un altında az miktarda döndürme olduğu ve bunun için çok sayıda iş parçacığı olduğu anlamına gelir. Fazla sayıda döndürme, spinlock kodunda nispeten uzun süreli (yani kod, karma demetteki çok sayıda girdinin üzerinden geçiyor) zaman harcandığı anlamına gelir. Çekişme arttıkça (böylece çarpışma sayısı arttıkça), devir sayısı da artar.

Geri almalar, döndürmelere benzer bir şekilde düşünülebilir. Tasarım gereği, aşırı CPU israfını önlemek için, spinlock’lar, tutulan bir kaynağa erişene kadar süresiz dönmezler. Bir spinlock'un aşırı CPU kaynağı kullanımını önlemek için, spinlock'lar geri çekilir veya dönmeyi durdurur ve "uyur". Spinlock'lar, hedef kaynağın sahipliğini alıp almadıklarına bakılmaksızın geri döner. Diğer iş parçacıklarının CPU'da zamanlanması, bunun daha üretken çalışmaların gerçekleşmesini sağlamasını ummak amacıyla yapılır. Motor için varsayılan davranış, geri çekilme işleminden önce sabit bir zaman aralığı boyunca dönmektir. Bir spinlock elde etmeye çalışmak için önbellek eşzamanlılığının durumunun korunması gerekir. Bu, döndürmenin CPU maliyetine göre yoğun CPU kullanan bir işlemdir. Bu nedenle, bir spinlock elde etmeye yönelik girişimler, bir iş parçacığı her döndürildiğinde tedbirli bir şekilde gerçekleştirilir ve gerçekleştirilmez. SQL Server'da bazı spinlock türleri (örneğin, LOCK_HASH), spinlock alma girişimleri arasında üstel olarak artan bir süre kullanılarak (belirli bir sınıra kadar) geliştirilmiştir, bu da genellikle CPU performansı üzerindeki etkisini azaltır.

Aşağıdaki diyagram, spinlock algoritmasının kavramsal bir görünümünü sağlar:

Tipik senaryolar

Spinlock çekişmesi, veritabanı tasarımı kararları ile ilgisiz olabilecek çeşitli nedenlerle oluşabilir. Spinlock'lar iç veri yapılarına erişim sağladığından, spinlock çekişmesi, şema tasarım seçimlerinden ve veri erişim desenlerinden doğrudan etkilenen arabellek mandal çekişmesiyle aynı şekilde ortaya çıkmaz.

Öncelikle spinlock çekişmesiyle ilişkili belirti, çok sayıda döngü ve aynı spinlock'u elde etmeye çalışan birçok iş parçacığının sonucu olarak yüksek CPU tüketimidir. Genel olarak bu, 24 ve daha fazla CPU çekirdeğine sahip sistemlerde ve en yaygın olarak 32'den fazla CPU çekirdeğine sahip sistemlerde gözlemlenmiştir. Daha önce de belirtildiği gibi, büyük yüke sahip yüksek eşzamanlı OLTP sistemleri için spinlock'larda bir miktar çekişme normaldir ve DMV'den sys.dm_os_spinlock_stats uzun süredir çalışan sistemlerde genellikle çok sayıda döndürme (milyar/trilyon) bildirilir. Yine, belirli bir spinlock türü için yüksek sayıda döndürmeyi gözlemlemek, iş yükü performansını olumsuz etkilediğini belirlemek için yeterli bilgi değildir.

Aşağıdaki belirtilerden birkaçının birleşimi, spinlock çekişmesi olduğunu gösterebilir. Bu koşulların tümü doğruysa, olası spinlock çekişmesi sorunlarıyla ilgili daha fazla araştırma yapın.

• Belirli bir spinlock türü için yüksek sayıda döndürme ve geri çekilme gözlemlenir.

• Sistemde yoğun CPU kullanımı veya CPU tüketiminde ani artışlar yaşanıyor. Yoğun CPU senaryolarında SOS_SCHEDULER_YIELD üzerinde yüksek sinyal beklemeleri görürsünüz (DMV sys.dm_os_wait_stats tarafından raporlanan).

• Sistem yüksek eşzamanlılık yaşıyor.

• CPU kullanımı ve döndürmeleri, aktarım hızına göre orantısız olarak artırılır.

Kolayca tanılanan yaygın olgulardan biri, aktarım hızı ve CPU kullanımında önemli bir farktır. Birçok OLTP iş yükünün (aktarım hızı / sistemdeki kullanıcı sayısı) ile CPU tüketimi arasında bir ilişkisi vardır. CPU tüketiminde ve aktarım hızında önemli bir farkla birlikte gözlemlenen yüksek döndürmeler, CPU ek yüküne neden olan spinlock çekişmesi göstergesi olabilir. Burada dikkat edilmesi gereken önemli noktalardan biri, belirli sorguların zaman içinde daha pahalı hale geldiği sistemlerde bu tür bir ayrılığı görmenin de yaygın olduğudur. Örneğin, zaman içinde daha fazla mantıksal okuma gerçekleştiren veri kümelerinde verilen sorgular benzer belirtilere neden olabilir.

Önemli

Bu tür sorunları giderirken yüksek CPU'nun diğer yaygın nedenlerini göz ardı etmek kritik önem taşır.

Yukarıdaki koşulların her biri doğru olsa bile, yüksek CPU tüketiminin kök nedeninin başka bir yerde olması mümkündür. Aslında, cpu artışının büyük çoğunluğunda spinlock çekişmesi dışındaki nedenlerden kaynaklanır.

Cpu tüketiminin artmasına neden olan yaygın nedenlerden bazıları şunlardır:

• Temel alınan verilerin büyümesi nedeniyle zaman içinde daha pahalı hale gelen sorgular, bellekte yerleşik verilerin ek mantıksal okumalarını gerçekleştirme gereksinimine neden olur.
• Sorgu planlarındaki değişiklikler en iyi olmayan yürütmeyle sonuçlanır.

Örnekler

Aşağıdaki örnekte, saniye başına işlemlerle ölçülen CPU tüketimi ile aktarım hızı arasında neredeyse doğrusal bir ilişki vardır. Herhangi bir iş yükü arttıkça ek yük oluştuğundan burada biraz farklılık görmek normaldir. Burada gösterildiği gibi, bu ayrışma önemli hale gelir. CPU tüketimi 100%ulaştığında aktarım hızında ani bir düşüş gözlemlenir.

Dönüş sayısını 3 dakikalık aralıklarla ölçerken spinlerde doğrusal artıştan ziyade üstel bir artış gördüğümüzde, bu da spinlock çekişmesinin sorunlu olduğunu belirtebilir.

Daha önce belirtildiği gibi, spinlock'lar en çok ağır yük altında olan yüksek eşzamanlılık sistemlerinde yaygındır.

Bu soruna eğilimli senaryolardan bazıları şunlardır:

• Nesnelerin adlarını tam olarak niteleme hatasının neden olduğu ad çözümleme sorunları. Daha fazla bilgi için bkz. Derleme kilitlerinden kaynaklanan SQL Server engellemesinin açıklaması. Bu özel sorun, bu makalenin içinde daha ayrıntılı olarak açıklanmıştır.

• Aynı kilide sık erişen iş yükleri için, (sık okunan satır üzerinde paylaşılan kilit gibi) kilit yöneticisindeki kilit karma demetlerinde çekişme yaşanır. Bu tür çekişme bir tür LOCK_HASH spinlock olarak ortaya çıkar. Belirli bir durumda, bu sorunun bir test ortamında yanlış modellenmiş erişim desenlerinin bir sonucu olarak ortaya çıkarıldığını bulduk. Bu ortamda, yanlış yapılandırılmış test parametreleri nedeniyle beklenen sayıdan fazla iş parçacığı sürekli olarak aynı satıra erişiyor.

• MSDTC işlem koordinatörleri arasında yüksek derecede gecikme olduğunda DTC işlemlerinin oranı yüksektir. Bu özel sorun, DTC ile İlgili Beklemeleri Çözümleme ve DTC'nin Ölçeklenebilirliğini Ayarlama SQLCAT blog girişinde ayrıntılı olarak belgelenmiştir.

Spinlock çekişmesi tanılama

Bu bölümde SQL Server spinlock çekişmesi tanılamaya yönelik bilgiler sağlanır. Spinlock çekişmelerini tanılamak için kullanılan birincil araçlar şunlardır:

Araç	Kullan
Performans İzleyicisi	Yüksek CPU koşullarını veya aktarım hızı ile CPU tüketimi arasındaki ayrılığı arayın.
Spinlock istatistikleri	Zaman aralıkları içinde çok sayıda döndürme ve geri alma olayı aramak için sys.dm_os_spinlock_stats DMV'yi sorgulayın.
Bekleme istatistikleri	SQL Server 2025 (17.x) Önizleme sürümünden başlayarak, bekleme türünü kullanarak sys.dm_os_wait_stats ve sys.dm_exec_session_wait_stats DMV'leri sorgular SPINLOCK_EXT . 8134 izleme bayrağı gerektirir. Daha fazla bilgi için bkz. SPINLOCK_EXT.
SQL Server genişletilmiş olayları	Çok sayıda döndürme yaşayan spinlock'lar için çağrı yığınlarını izlemek amacıyla kullanılır.
Bellek dökümleri	Bazı durumlarda SQL Server işleminin ve Windows Hata Ayıklama araçlarının bellek dökümleri. Genel olarak, bu analiz düzeyi Microsoft Destek ekipleri devreye alındığında gerçekleştirilir.

SQL Server Spinlock çekişmesi tanılamaya yönelik genel teknik süreç:

1. 1. Adım: Spinlock ile ilgili olabilecek bir çekişme olduğunu belirleyin.

2. 2. Adım: En fazla çekişme yaşayan spinlock türünü bulmak için 'den sys.dm_os_spinlock_stats istatistikleri yakalayın.

3. 3. Adım: sqlservr.exe (sqlservr.pdb) için hata ayıklama simgelerini alın ve simgeleri SQL Server örneğinin SQL Server hizmet .exe dosyası (sqlservr.exe) ile aynı dizine yerleştirin.\ Geri alma olaylarının çağrı yığınlarını görmek için, çalıştırdığınız belirli SQL Server sürümüne ait sembollere sahip olmanız gerekir. SQL Server simgeleri Microsoft Sembol Sunucusu'nda kullanılabilir. Microsoft Sembol Sunucusu'ndan sembol indirme hakkında daha fazla bilgi için bkz. Sembollerle hata ayıklama.

4. 4. Adım: SQL Server Genişletilmiş Olayları'nı kullanarak ilgili spinlock türleri için geri alma olaylarını izleyebilirsiniz. Yakalanacak olaylar spinlock_backoff ve spinlock_backoff_warning.

Genişletilmiş Olaylar, geri çekilme olaylarını izleme ve spinlock'u en çok elde etmeye çalışan o işlem(ler) için çağrı yığınını yakalama olanağı sağlar. Çağrı yığınını analiz ederek, belirli bir spinlock için çekişmeye katkıda bulunan işlem türünü belirlemek mümkündür.

Tanılama kılavuzu

Aşağıdaki kılavuzda, gerçek bir dünya senaryosunda bir spinlock çekişmesi sorununu tanılamak için araçların ve tekniklerin nasıl kullanılacağı gösterilmektedir. Bu kılavuz, 1 TB belleğe sahip 8 yuva, 64 fiziksel çekirdekli bir sunucuda yaklaşık 6.500 eşzamanlı kullanıcının benzetimini yapmak için kıyaslama testi çalıştıran bir müşteri etkileşimini temel alır.

Belirti -leri

CPU'da düzenli ani artışlar gözlemlendi ve bu da CPU kullanımını yaklaşık 100%itti. İşleme hızı ile CPU tüketimi arasında soruna yol açan bir ayrışma gözlemlendi. Büyük CPU ani artışı oluştuğunda, belirli aralıklarla yoğun CPU kullanımı sırasında gerçekleşen çok sayıda döndürme deseni oluşturulmuştur.

Bu, çekişmenin bir spinlock konvoyu durumu oluşturacak şekilde olduğu istisnai bir durumdu. İş parçacıkları artık iş yüküne hizmet vermek için ilerleme kaydedemeyip tüm işleme kaynaklarını kilit erişimi elde etmeye çalışmaya harcadığında bir konvoy meydana gelir. Performans izleyicisi günlüğü, işlem günlüğü aktarım hızı ile CPU tüketimi arasındaki bu ayrılığı ve sonuçta CPU kullanımındaki büyük artışı gösterir.

sys.dm_os_spinlock_stats'nın üzerinde SOS_CACHESTORE önemli bir çekişme olup olmadığını belirlemek için sorgulandıktan sonra, ilgilenilen spinlock türleri için geri çekilme olaylarının sayısını ölçmek amacıyla genişletilmiş bir olaylar betiği kullanıldı.

İsim	Çarpışma	Dönüşler	Çarpışma başına dönme sayısı	Geri çekilmeler
SOS_CACHESTORE	14,752,117	942,869,471,526	63,914	67,900,620
SOS_SUSPEND_QUEUE	69,267,367	473,760,338,765	6,840	2,167,281
LOCK_HASH	5,765,761	260,885,816,584	45,247	3,739,208
MUTEX	2,802,773	9,767,503,682	3,485	350,997
SOS_SCHEDULER	1,207,007	3,692,845,572	3,060	109,746

Dönüşlerin etkisini ölçmenin en basit yolu, en yüksek dönüş sayısına sahip spinlock türleri için, sys.dm_os_spinlock_stats etiketinin ortaya çıkardığı aynı 1 dakikalık aralıkta geri çekilme olaylarının sayısına bakmaktır. İş parçacıklarının spinlock'u almayı beklerken döndürme sınırını ne zaman tükettiğine işaret ettiğinden, bu yöntem önemli çekişmeyi algılamak için en iyisidir. Aşağıdaki betik, ilgili geri çekilme olaylarını ölçmek ve rekabetin bulunduğu belirli kod yollarını belirlemek için genişletilmiş olayları kullanan gelişmiş bir tekniği gösterir.

SQL Server'da Genişletilmiş Olaylar hakkında daha fazla bilgi için bkz. Genişletilmiş Olaylara genel bakış.

Senaryo

/*
This script is provided "AS IS" with no warranties, and confers no rights.

This script will monitor for backoff events over a given period of time and
capture the code paths (callstacks) for those.

--Find the spinlock types
select map_value, map_key, name from sys.dm_xe_map_values
where name = 'spinlock_types'
order by map_value asc

--Example: Get the type value for any given spinlock type
select map_value, map_key, name from sys.dm_xe_map_values
where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')

Examples:
61LOCK_HASH
144 SOS_CACHESTORE
08MUTEX

*/
--create the even session that will capture the callstacks to a bucketizer
--more information is available in this reference: http://msdn.microsoft.com/en-us/library/bb630354.aspx
CREATE EVENT SESSION spin_lock_backoff ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    OR TYPE = 144 --SOS_CACHESTORE
    OR TYPE = 8 --MUTEX
) ADD TARGET package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
);

--Ensure the session was created
SELECT * FROM sys.dm_xe_sessions
WHERE name = 'spin_lock_backoff';

--Run this section to measure the contention
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = START;

--wait to measure the number of backoffs over a 1 minute period
WAITFOR DELAY '00:01:00';

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1);

--Get the callstacks from the bucketizer target
SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spin_lock_backoff';

--clean up the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP;
DROP EVENT SESSION spin_lock_backoff ON SERVER;

Çıkışı analiz ederek, SOS_CACHESTORE döngüleri için en yaygın kod yollarının çağrı yığınlarını görebiliriz. Döndürülen çağrı yığınlarında tutarlılığı denetlemek için CPU kullanımının yüksek olduğu süre boyunca betik birkaç farklı kez çalıştırıldı. En yüksek yuva demet sayısına sahip çağrı yığınları iki çıkış arasında ortaktır (35.668 ve 8.506). Bu çağrı yığınlarının yuva sayısı, bir sonraki en yüksek girdiden iki büyüklük mertebesi daha fazladır. Bu koşul, ilgilendiğin bir kod yolunu gösterir.

Uyarı

Önceki betik tarafından döndürülen çağrı yığınlarını görmek sık karşılaşılan bir durum değildir. Betik 1 dakika çalıştırıldığında, yuva sayısı > 1.000 olan çağrı yığınlarının sorunlu olduğunu ancak yuva sayısı > 10.000 olduğu için, yuva sayısının daha yüksek olması nedeniyle daha sorunlu olma olasılığının daha yüksek olduğunu gözlemledik.

Uyarı

Okunabilirlik amacıyla aşağıdaki çıkışın biçimlendirmesi temizlendi.

Çıkış 1

<BucketizerTarget truncated="0" buckets="256">
<Slot count="35668" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid
      CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey
      CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
  </value>
</Slot>
<Slot count="752" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey             CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
  </value>
  </Slot>

Çıkış 2

<BucketizerTarget truncated="0" buckets="256">
<Slot count="8506" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep+c7 [ @ 0+0x0 SpinlockBase::Backoff Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
</value>
 </Slot>
<Slot count="190" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
       SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
   </value>
 </Slot>

Önceki örnekte, en ilginç yığınlar en yüksek yuva sayısına (35.668 ve 8.506) sahiptir ve aslında yuva sayısı 1.000'den fazladır.

Şimdi soru şu olabilir: "Bu bilgilerle ne yapmalıyım"? Genel olarak, çağrı yığını bilgilerini kullanmak için SQL Server altyapısı hakkında derin bilgi gereklidir ve bu nedenle bu noktada sorun giderme işlemi gri bir alana taşınır. Bu özel durumda, çağrı yığınlarına bakarak sorunun oluştuğu kod yolunun güvenlik ve meta veri aramalarıyla ilgili olduğunu görebiliriz (Aşağıdaki yığın çerçevelerinde CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID)gösterildiği gibi .

Tek başına, bu bilgileri kullanarak sorunu çözmek zordur, ancak sorunu daha da belirginleştirmek için ek sorun gidermeye odaklanmamız gereken bazı fikirler verir.

Bu sorun, güvenlikle ilgili denetimler gerçekleştiren kod yollarıyla ilgili olduğu için veritabanına bağlanan uygulama kullanıcısına ayrıcalıkların verildiği sysadmin bir test çalıştırmaya karar verdik. Bu teknik üretim ortamında asla önerilmiyor olsa da, test ortamımızda yararlı bir sorun giderme adımı olduğu kanıtlandı. Oturumlar yükseltilmiş ayrıcalıklar ()sysadmin kullanılarak çalıştırıldığında, çekişmeyle ilgili CPU ani artışları kayboldu.

Seçenekler ve geçici çözümler

Açıkçası, spinlock çekişmesi sorunlarını gidermek basit bir görev değildir. "Tek bir ortak en iyi yaklaşım" yoktur. Performans sorunlarını gidermenin ve çözmenin ilk adımı, kök nedeni belirlemektir. Bu makalede açıklanan teknikleri ve araçları kullanmak, spinlock ile ilgili çekişme noktalarını anlamak için gereken analizi gerçekleştirmenin ilk adımıdır.

SQL Server'ın yeni sürümleri geliştirildikçe altyapı, yüksek eşzamanlılık sistemleri için daha iyi iyileştirilmiş kod uygulayarak ölçeklenebilirliği geliştirmeye devam eder. SQL Server, en yaygın çekişme noktaları için üstel geri alma olan yüksek eşzamanlılık sistemleri için birçok iyileştirme getirmiştir. SQL Server 2012'den başlayarak altyapıdaki tüm spinlock'lar için üstel geri alma algoritmalarından yararlanarak bu alanı özel olarak geliştiren geliştirmeler vardır.

Aşırı performans ve ölçek gerektiren üst düzey uygulamalar tasarlarken, SQL Server'da gereken kod yolunu olabildiğince kısa tutmayı göz önünde bulundurun. Daha kısa bir kod yolu, veritabanı altyapısı tarafından daha az çalışma gerçekleştirileceği ve doğal olarak çekişme noktalarından kaçınacağı anlamına gelir. Birçok en iyi uygulama, motorun gerektirdiği çalışma miktarını azaltmanın bir yan etkisi olarak iş yükü performansının iyileşmesine de katkıda bulunur.

Bu makalenin önceki bölümlerinden örnek olarak birkaç en iyi yöntem alınıyor:

• Tam Adlar: Tüm nesnelerin tam olarak nitelenmesi, SQL Server'ın adları çözümlemek için gereken kod yollarını yürütme gereksinimini ortadan kaldırır. Saklı yordam çağrılarında tam nitelikli adlar kullanılmadığında karşılaşılan SOS_CACHESTORE spinlock türünde de çekişme noktaları gözlemledik. Bu adların tam olarak nitelenmemesi, SQL Server'ın kullanıcı için varsayılan şemayı aramasına neden olur ve bu da SQL'i yürütmek için daha uzun bir kod yolu gerektirir.

• Parametreli Sorgular: Başka bir örnek, yürütme planları oluşturmak için gereken işi azaltmak için parametreli sorgular ve saklı yordam çağrıları kullanmaktır. Bu da yürütme için daha kısa bir kod yolu elde eder.

• LOCK_HASH Çekişme: Bazı kilit yapısı veya karma demet çakışmaları üzerindeki çekişmeler bazı durumlarda kaçınılmazdır. SQL Server motoru kilit yapılarının çoğunu bölümlese de, yine de kilit alma işlemi aynı karma kovasına erişimle sonuçlandığı zamanlar vardır. Örneğin, bir uygulama aynı satıra eşzamanlı olarak birçok iş parçacığı tarafından erişir (yani başvuru verileri). Bu tür sorunlara, veritabanı şemasında bu başvuru verilerinin ölçeğini genişleten veya mümkün olduğunda iyimser eşzamanlılık denetimi ve iyileştirilmiş kilitleme kullanan teknikler yaklaşılabilir.

SQL Server iş yüklerini ayarlamanın ilk savunma hattı her zaman standart ayarlama uygulamalarıdır (örneğin, dizin oluşturma, sorgu iyileştirme, G/Ç iyileştirme vb.). Ancak, standart ayarlamaya ek olarak, işlemleri gerçekleştirmek için gereken kod miktarını azaltan uygulamaları izlemek önemli bir yaklaşımdır. En iyi yöntemler izlense bile, meşgul yüksek eşzamanlılık sistemlerinde spinlock çekişmesi oluşma olasılığı hala vardır. Bu makaledeki araç ve tekniklerin kullanılması, bu tür sorunları yalıtmaya veya emeye yardımcı olabilir ve yardımcı olmak için doğru Microsoft kaynaklarının ne zaman devreye alınması gerekeceğini belirleyebilir.

Ek: Bellek dökümü yakalamayı otomatikleştirme

Aşağıda verilen genişletilmiş olaylar betiğinin, spinlock anlaşmazlığı önemli hale geldiğinde bellek dökümlerinin toplanmasını otomatik hale getirmekte yararlı olduğu kanıtlanmıştır. Bazı durumlarda, sorunun tam tanılamasını gerçekleştirmek için bellek dökümleri gerekir veya Microsoft ekipleri tarafından ayrıntılı analiz gerçekleştirmesi istenir.

Aşağıdaki SQL betiği, spinlock çekişmesi analizine yardımcı olmak üzere bellek dökümlerini yakalama işlemini otomatikleştirmek için kullanılabilir:

/*
This script is provided "AS IS" with no warranties, and confers no rights.

Use:    This procedure will monitor for spinlocks with a high number of backoff events
        over a defined time period which would indicate that there is likely significant
        spin lock contention.

        Modify the variables noted below before running.

Requires:
        xp_cmdshell to be enabled
            sp_configure 'xp_cmd', 1
            go
            reconfigure
            go

*********************************************************************************************************/
USE tempdb;
GO

IF object_id('sp_xevent_dump_on_backoffs') IS NOT NULL
    DROP PROCEDURE sp_xevent_dump_on_backoffs;
GO

CREATE PROCEDURE sp_xevent_dump_on_backoffs (
    @sqldumper_path NVARCHAR(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"',
    @dump_threshold INT = 500, --capture mini dump when the slot count for the top bucket exceeds this
    @total_delay_time_seconds INT = 60, --poll for 60 seconds
    @PID INT = 0,
    @output_path NVARCHAR(MAX) = 'c:\',
    @dump_captured_flag INT = 0 OUTPUT
)
AS
/*
    --Find the spinlock types
    select map_value, map_key, name from sys.dm_xe_map_values
    where name = 'spinlock_types'
    order by map_value asc

    --Example: Get the type value for any given spinlock type
    select map_value, map_key, name from sys.dm_xe_map_values
    where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')
*/
IF EXISTS (
        SELECT *
        FROM sys.dm_xe_session_targets xst
        INNER JOIN sys.dm_xe_sessions xs
            ON (xst.event_session_address = xs.address)
        WHERE xs.name = 'spinlock_backoff_with_dump'
        )
    DROP EVENT SESSION spinlock_backoff_with_dump
        ON SERVER

CREATE EVENT SESSION spinlock_backoff_with_dump ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    --or type = 144           --SOS_CACHESTORE
    --or type = 8             --MUTEX
    --or type = 53            --LOGCACHE_ACCESS
    --or type = 41            --LOGFLUSHQ
    --or type = 25            --SQL_MGR
    --or type = 39            --XDESMGR
) ADD target package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
)

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = START;

DECLARE @instance_name NVARCHAR(MAX) = @@SERVICENAME;
DECLARE @loop_count INT = 1;
DECLARE @xml_result XML;
DECLARE @slot_count BIGINT;
DECLARE @xp_cmdshell NVARCHAR(MAX) = NULL;

--start polling for the backoffs
PRINT 'Polling for: ' + convert(VARCHAR(32), @total_delay_time_seconds) + ' seconds';

WHILE (@loop_count < CAST(@total_delay_time_seconds / 1 AS INT))
BEGIN
    WAITFOR DELAY '00:00:01'

    --get the xml from the bucketizer for the session
    SELECT @xml_result = CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets xst
    INNER JOIN sys.dm_xe_sessions xs
        ON (xst.event_session_address = xs.address)
    WHERE xs.name = 'spinlock_backoff_with_dump';

    --get the highest slot count from the bucketizer
    SELECT @slot_count = @xml_result.value(N'(//Slot/@count)[1]', 'int');

    --if the slot count is higher than the threshold in the one minute period
    --dump the process and clean up session
    IF (@slot_count > @dump_threshold)
    BEGIN
        PRINT 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 c:\ '''

        SELECT @xp_cmdshell = 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 ' + @output_path + ' '''

        EXEC sp_executesql @xp_cmdshell

        PRINT 'loop count: ' + convert(VARCHAR(128), @loop_count)
        PRINT 'slot count: ' + convert(VARCHAR(128), @slot_count)

        SET @dump_captured_flag = 1

        BREAK
    END

    --otherwise loop
    SET @loop_count = @loop_count + 1
END;

--see what was collected then clean up
DBCC TRACEON (3656, -1);

SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spinlock_backoff_with_dump';

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = STOP;
DROP EVENT SESSION spinlock_backoff_with_dump ON SERVER;
GO

/* CAPTURE THE DUMPS
******************************************************************/
--Example: This will run continuously until a dump is created.
DECLARE @sqldumper_path NVARCHAR(MAX) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"';
DECLARE @dump_threshold INT = 300; --capture mini dump when the slot count for the top bucket exceeds this
DECLARE @total_delay_time_seconds INT = 60; --poll for 60 seconds
DECLARE @PID INT = 0;
DECLARE @flag TINYINT = 0;
DECLARE @dump_count TINYINT = 0;
DECLARE @max_dumps TINYINT = 3; --stop after collecting this many dumps
DECLARE @output_path NVARCHAR(max) = 'c:\'; --no spaces in the path please :)
--Get the process id for sql server
DECLARE @error_log TABLE (
    LogDate DATETIME,
    ProcessInfo VARCHAR(255),
    TEXT VARCHAR(max)
);

INSERT INTO @error_log
EXEC ('xp_readerrorlog 0, 1, ''Server Process ID''');

SELECT @PID = convert(INT, (REPLACE(REPLACE(TEXT, 'Server Process ID is ', ''), '.', '')))
FROM @error_log
WHERE TEXT LIKE ('Server Process ID is%');

PRINT 'SQL Server PID: ' + convert(VARCHAR(6), @PID);

--Loop to monitor the spinlocks and capture dumps. while (@dump_count < @max_dumps)
BEGIN
    EXEC sp_xevent_dump_on_backoffs @sqldumper_path = @sqldumper_path,
        @dump_threshold = @dump_threshold,
        @total_delay_time_seconds = @total_delay_time_seconds,
        @PID = @PID,
        @output_path = @output_path,
        @dump_captured_flag = @flag OUTPUT

    IF (@flag > 0)
        SET @dump_count = @dump_count + 1

    PRINT 'Dump Count: ' + convert(VARCHAR(2), @dump_count)

    WAITFOR DELAY '00:00:02'
END;

Ek: Zaman içinde spinlock istatistiklerini toplama

Aşağıdaki komut dosyası, belirli bir zaman aralığındaki spinlock istatistiklerine bakmak için kullanılabilir. Her çalıştırıldığında program, geçerli değerler ile toplanan önceki değerler arasındaki farkı döner.

/* Snapshot the current spinlock 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 DATETIME;
DECLARE @previous_snap_time DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (
    SELECT name
    FROM tempdb.sys.sysobjects
    WHERE name LIKE '#_spin_waits%'
)
CREATE TABLE #_spin_waits (
    lock_name VARCHAR(128),
    collisions BIGINT,
    spins BIGINT,
    sleep_time BIGINT,
    backoffs BIGINT,
    snap_time DATETIME
);

--capture the current stats
INSERT INTO #_spin_waits (
    lock_name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    snap_time
    )
SELECT name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    @current_snap_time
FROM sys.dm_os_spinlock_stats;

SELECT TOP 1 @previous_snap_time = snap_time
FROM #_spin_waits
WHERE snap_time < (
    SELECT max(snap_time)
    FROM #_spin_waits
)
ORDER BY snap_time DESC;

--get delta in the spin locks stats
SELECT TOP 10 spins_current.lock_name,
    (spins_current.collisions - spins_previous.collisions) AS collisions,
    (spins_current.spins - spins_previous.spins) AS spins,
    (spins_current.sleep_time - spins_previous.sleep_time) AS sleep_time,
    (spins_current.backoffs - spins_previous.backoffs) AS backoffs,
    spins_previous.snap_time AS [start_time],
    spins_current.snap_time AS [end_time],
    DATEDIFF(ss, @previous_snap_time, @current_snap_time) AS [seconds_in_sample]
FROM #_spin_waits spins_current
INNER JOIN (
    SELECT *
    FROM #_spin_waits
    WHERE snap_time = @previous_snap_time
    ) spins_previous
    ON (spins_previous.lock_name = spins_current.lock_name)
WHERE spins_current.snap_time = @current_snap_time
    AND spins_previous.snap_time = @previous_snap_time
    AND spins_current.spins > 0
ORDER BY (spins_current.spins - spins_previous.spins) DESC;

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

İlgili içerik

• Performans izleme ve ayarlama araçları