Derleme kilitlerinden kaynaklanan engelleme sorunlarını giderme

  • Makale

Bu makalede, derleme kilitlerinin neden olduğu engelleme sorunlarını giderme ve çözme adımları açıklanmaktadır.

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

Özet

Microsoft SQL Server'da, bir saklı yordam planının yalnızca bir kopyası genellikle bir kerede önbellektedir. Bunu zorunlu hale getirmek için derleme işleminin bazı bölümlerinin seri hale getirilmesi gerekir ve bu eşitleme kısmen derleme kilitleri kullanılarak gerçekleştirilir. Birçok bağlantı aynı anda aynı saklı yordamı çalıştırıyorsa ve her çalıştırıldığında bu saklı yordam için bir derleme kilidi alınması gerekiyorsa, her biri nesne üzerinde özel derleme kilidi elde etmeye çalışırken oturum kimlikleri (SPID) birbirini engellemeye başlayabilir.

Derleme engellemenin engelleme çıkışında gözlemlenebilir bazı tipik özellikleri aşağıda verilmiştir:

  • waittypeengellenen ve (genellikle) engelleyen oturum IÇIN SPID'ler LCK_M_X (özel) ve biçimindedirOBJECT: dbid: object_id [[COMPILE]]; waitresource burada object_id saklı yordamın nesne kimliğidir.

  • Engelleyiciler NULL, waittype durum çalıştırılabilir. Bloklar (özel kilit), durum uyku durumuna sahiptir waittypeLCK_M_X .

  • Engelleme olayının süresi uzun olsa da, diğer SPID'leri uzun süre engelleyen tek bir SPID yoktur. Sıralı engelleme var. Bir derleme tamamlanır tamamlanmaz, başka bir SPID birkaç saniye veya daha kısa bir süre boyunca baş engelleyici rolünü devralır ve bu şekilde devam edilir.

Aşağıdaki bilgiler, bu tür bir engelleme sırasında anlık sys.dm_exec_requests görüntüsünden alınıyor:

session_id   blocking_session_id   wait_type   wait_time   waitresource ---------- ------------------- --------- --------- ----------------------------
221           29                   LCK_M_X     2141        OBJECT: 6:834102
[[COMPILE]]
228           29                   LCK_M_X     2235        OBJECT: 6:834102
[[COMPILE]]
29            214                  LCK_M_X     3937        OBJECT: 6:834102
[[COMPILE]]
13            214                  LCK_M_X     1094        OBJECT: 6:834102
[[COMPILE]]
68            214                  LCK_M_X     1968        OBJECT: 6:834102
[[COMPILE]]
214           0                    LCK_M_X     0           OBJECT: 6:834102
[[COMPILE]]

Sütunda waitresource (6:834102), 6 veritabanı kimliği ve 834102 nesne kimliğidir. Bu nesne kimliği tabloya değil saklı yordama ait.

Daha fazla bilgi

Saklı yordam yeniden derlemesi, saklı yordam veya tetikleyicideki derleme kilitlerinin bir açıklamasıdır. Bu durumda çözüm, yeniden derlemeleri azaltmak veya ortadan kaldırmaktır.

Derleme kilitlerine yol açan ek Senaryolar

  1. Saklı Yordam Tam Ad olmadan yürütülür

    • Saklı yordamı çalıştıran kullanıcı, yordamın sahibi değildir.
    • Saklı yordam adı, nesne sahibinin adıyla tam olarak nitelenmez.

    Örneğin, dbo kullanıcısı nesneye dbo.mystoredproc ve başka bir kullanıcıya sahipse, Harrykomutunu kullanarak exec mystoredprocbu saklı yordamı çalıştırır, nesnenin sahip nitelikli olmaması nedeniyle nesne adına göre ilk önbellek araması başarısız olur. (Adlı Harry.mystoredproc başka bir saklı yordamın mevcut olup olmadığı henüz bilinmiyor. Bu nedenle, SQL Server için önbelleğe alınmış planın yürütülecek doğru plan dbo.mystoredproc olduğundan emin olamaz.) SQL Server ardından yordam üzerinde özel bir derleme kilidi alır ve yordamı derlemek için hazırlıklar yapar. Bu, nesne adını bir nesne kimliğine çözümlemeyi içerir. SQL Server planı derlemeden önce, SQL Server yordam önbelleğinde daha hassas bir arama yapmak için bu nesne kimliğini kullanır ve sahip niteliği olmadan bile önceden derlenmiş bir planı bulabilir.

    Mevcut bir plan bulunursa, SQL Server önbelleğe alınmış planı yeniden kullanabilir ve saklı yordamı derlemez. Ancak, sahip yeterliliği olmaması, program mevcut önbelleğe alınmış yürütme planının yeniden kullanılıp kullanılamayabileceğini saptamadan önce ikinci bir önbellek araması gerçekleştirmeye ve özel derleme kilidi elde etmeye SQL Server zorlar. Kilidi almak ve bu noktaya ulaşmak için gereken aramaları ve diğer işleri gerçekleştirmek, engellemeye yol açan derleme kilitleri için bir gecikmeye neden olabilir. Saklı yordamın sahibi olmayan birçok kullanıcı, sahibin adını vermeden yordamı eşzamanlı olarak çalıştırıyorsa bu durum özellikle geçerlidir. Derleme kilitlerini bekleyen SPID'ler görmeseniz bile sahip yeterliliği olmaması saklı yordam yürütmede gecikmelere neden olabilir ve yüksek CPU kullanımına neden olabilir.

    Bu sorun oluştuğunda aşağıdaki olay dizisi SQL Server Genişletilmiş Olay oturumuna kaydedilir.

    Olay Adı Metin
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss ada göre önbellek araması başarısız olduğunda gerçekleşir, ancak belirsiz nesne adı bir nesne kimliğine çözümlendikten sonra önbellekte eşleşen bir önbelleğe alınmış plan bulunur ve bir sp_cache_hit olay olur.

    Bu derleme kilitleme sorununun çözümü, saklı yordamlara yapılan başvuruların sahip nitelikli olduğundan emin olmaktır. (exec yerine exec mystoredprocdbo.mystoredprockullanın.) Sahip yeterliliği performans nedenleriyle önemli olsa da, ek önbellek aramasını önlemek için depolanan uzmanı veritabanı adıyla nitelemeniz gerekmez.

    Derleme kilitlerinden kaynaklanan engelleme, standart engelleme sorun giderme yöntemleri kullanılarak algılanabilir.

  2. Saklı yordama ön ek olarak sp_

    Saklı yordam adınız ön ek ile sp_ başlıyorsa ve ana veritabanında değilse, saklı yordamı sahip olarak niteleseniz bile her yürütme için önbellek isabet etmeden önce sp_cache_miss görürsünüz. Bunun nedeni, ön ekin sp_ SQL Server saklı yordamın bir sistem saklı yordamı olduğunu ve sistem saklı yordamlarının farklı ad çözümleme kurallarına sahip olduğunu söylemesidir. (Tercih edilen konum ana veritabanındadır.) Kullanıcı tarafından oluşturulan saklı yordamların adları ile sp_başlamamalıdır.

  3. Saklı yordam farklı bir durum kullanılarak çağrılır (büyük/küçük)

    Sahip tarafından nitelenmiş bir yordam, oluşturmak için kullanılan durumdan farklı bir durum (üst veya alt) kullanılarak yürütülürse, yordam bir CacheMiss olayını tetikleyebilir veya BIR COMPILE kilidi isteyebilir. Sonunda, yordam önbelleğe alınmış planı kullanır ve yeniden derlenmez. Ancak, derleme kilidi isteği bazen aynı yordamı oluşturmak için kullanılan durumdan farklı bir olay kullanarak yürütmeye çalışan çok sayıda SPID varsa engelleme zinciri durumuna neden olabilir. Bu, sunucuda veya veritabanında kullanılan sıralama düzeni veya harmanlamadan bağımsız olarak geçerlidir. Bu davranışın nedeni, önbellekteki yordamı bulmak için kullanılan algoritmanın karma değerlere (performans için) dayalı olması ve büyük/küçük harf farklıysa karma değerlerin değişebileceğidir.

    Geçici çözüm, uygulama yordamı yürütürken kullanılanla aynı durumu kullanarak yordamı bırakmak ve oluşturmaktır. Ayrıca, yordamın doğru büyük/küçük harf (üst veya alt) kullanılarak tüm uygulamalardan yürütülmesini de sağlayabilirsiniz.

  4. Saklı yordam Bir Dil olayı olarak çağrılır

    Bir saklı yordamı RPC yerine Dil Olayı olarak yürütmeye çalışırsanız, SQL Server dil olayı sorgusunu ayrıştırıp derlemeli, sorgunun belirli bir yordamı yürütmeye çalıştığını belirlemeli ve ardından bu yordam için önbellekte bir plan bulmayı denemelisiniz. SQL Server dil olayını ayrıştırıp derlemesi gereken bu durumdan kaçınmak için sorgunun SQL'e RPC olarak gönderildiğinden emin olun.

    Daha fazla bilgi için Çevrimiçi Kitaplar Saklı Yordam Oluşturma makalesinin Sistem Saklı Yordamları bölümüne bakın.

Başvurular

OPEN SYMMETRIC KEY komutu sorgu planı önbelleğe almayı engeller