SQL Server 2014’te Online Index Rebuild Geliştirmeleri

Index’lerin online olarak rebuild edilme şekli ilk SQL Server 2005 ile hayata geçirilmiştir. Aslında o dönemde tam anlamıyla online rebuild işlemi beklentilerimizi karşılamamaktaydı. Örneğin SQL Server 2005/2008/2008 R2 ‘de text, ntext, image, varchar(max), nvarchar(max), varbinary(max) ve xml kolon tiplerini içeren index’leri online olarak rebuild etmeye çalıştığımızda, bu veri tipindeki kolonları içeren index’lerin online rebuild edilemeyeceği hata mesajı olarak karşımıza çıkmaktaydı. SQL Server 2012 ile birlikte LOB verileri içeren kolon tiplerine sahip index’lerin de online olarak rebuild edilebilmesine olanak sağlanmıştır.

Peki ya locking problemi SQL Server 2012 ile çözüldü mü? Maalesef hayır. Bu konuyu biraz daha detaylı anlatmam faydalı olacaktır; SQL Server 2014 öncesinde, index’leri online olarak rebuild etmeye çalıştığımızda SQL Server index’i rebuild etmeden önce tabloya Shared Table Lock (S), rebuild ettikten hemen sonra ise Schema Modification Lock (Sch-M) koyardı. Yani her nekadar ismi ‘online’ olsada,  tablonuz lock’landığından ötürü eş zamanlı gelen sorgularınız locking’e takılır ve beklemeye düşerdi. Bu konuyu daha iyi anlamak için SQL Server 2012’de bu ortamı yaratalım. AdventureWorks2012 veritabanı üzerinden devam edelim.

Bu süreci en iyi üç ayrı session’ı eş zamanlı oluşturarak test edebiliriz. Bunun için ilk session’ımızda bir transaction açıp Person.Address tablosunu update edelim.

Update işleminin başarılı şekilde gerçekleştirildiği bilgisini göreceksiniz. Fakat session’ı daha commit etmedik. İkinci bir session’da online olarak Person.Address tablosu’nun clustered index’inin rebuild edilmesini sağlayalım.

Rebuild işlemini çalıştırdığınız anda sorgunuz ilk session’ı beklemeye geçecektir. Session’ın hangi sorguyu beklediğini aşağıdaki kod yardımı ile kontrol edebilirsiniz.

Aşağıdaki ekran görüntüsünde görüldüğü gibi spid’si 53 olan ALTER INDEX işlemi, spid’si 54 olan transaction açtığımız sorgu sonucunda locklanmıştır.

 

 

Şimdi ise üçüncü bir session açıp birinci session’da update ettiğimiz Person.Address tablosundaki AddressID’si 170 olan satırı herhangi bir hint kullanmadan ve transaction başlatmadan sorgulayalım.

Yukarıdaki sorguyu çalıştırdığınızda üçüncü session’ın da lock’landığını göreceksiniz. Daha detaylı incelemek ve blocklanmaları saptamak için yukarıda kullandığımız sorgumuzu tekrar çalıştırıyoruz.

 

 

 

Çıkan sonuca göre locklanma hiyerarşisi aşağıdaki şekilde oluşmuştur.

 

 

 

 

 

 

 

 

 

 

 

 

 

Son olarak Session 1’i commit edelim. Commit ettiğimizde Session 2’de ki online index rebuild’in yapıldığını ve Session 3’teki sorgununda sonuç döndüğünü göreceksiniz.
Peki ya SQL Server 2014? Evet SQL Server 2014’te artık lock yönetimi yapabiliyorsunuz. Bunun için ALTER INDEX statement’ındaki yeni keyword’ü ve aldığı değerleri inceleyelim.

SQL Server 2014’te index’leri online rebuild ederken artık lock öncelik yönetimini WAIT_AT_LOW_PRIORITY keyword’ü ile yapabiliyorsunuz. WAIT_AT_LOW_PRIORITY keyword’ünü kullandığınızda önceliklendirme kademelerine geçiş yapmışsınız demek oluyor. Yani o session’ın çalışma önceliği diğer session’lara göre düşürülüyor. MAX_DURATION değeri ile bu işlemin maximum olarak en fazla, belirtilen dakika kadar devam edeceğini belirliyorsunuz. Burada dikkat edilmesi gereken nokta, bu değer sadece dakika olarak set edilebilir. ABORT_AFTER_WAIT değeri ile ise MAX_DURATION süresine ulaşıldıktan sonra işlem sonucunda nasıl aksiyon alınması gerektiğini belirliyorsunuz. Yukarıdaki örnekte ben NONE olarak set ettim. Bununda anlamı MAX_DURATION süresine ulaşıldıktan sonra ALTER INDEX statement’ımız düşük önceliğini kaldırıp normal çalışma prensibinde çalışmaya devam etmesidir. ABORT_AFTER_WAIT’in aldığı diğer değerler SELF ve BLOCKERS dır. SELF değeri set edildiğinde, MAX_DURATION süresine ulaşıldığında ALTER INDEX işlemini sonlandırmış oluyorsunuz. BLOCKERS değeri set edildiğinde ise MAX_DURATION süresine ulaşıldığında ortamda blocking yapan session’ın sonlandırılmasını sağlamış oluyorsunuz.

Yukarıda SQL Server 2012 üzerinde yaptığımız demo’nun aynısını SQL Server 2014 üzerinde, ALTER INDEX keyword yeniliklerini kullanarak yapalım. Bu sefer AdventureWorks2014 veritabanı üzerinde işlemleri gerçekleştireceğim. İlk olarak transaction açarak Person.Address tablosunu sorguluyoruz.

İkinci bir session’da ise ALTER INDEX işlemimi gerçekleştiriyorum.

Cümlecik içerisinde bekleme süresini 1 dakika olarak ve 1 dakikayı geçtiğinde abort vermemesi için ABORT_AFTER_WAIT değerini de NONE olarak set ettim. İkinci session ile eş zamanlı olarak tabloyu üçüncü bir session’da sorguluyorum.

Yukarıdaki sorguyu çalıştırdığınızda cevap döndüğünü göreceksiniz. Aynı işlemi SQL Server 2012’de denediğimizde üçüncü session, ikinci session’ı beklemeye geçmişti. Burada önemli olan ALTER INDEX cümleciğinde belirttiğiniz 1 dakikalık MAX_DURATION değeridir. ALTER INDEX cümleciğini çalıştırdığınızdan itibaren bir dakika boyunca ücüncü session’dan istediğiniz kadar sorgu sonucu döndürebilirsiniz. MAX_DURATION değeri 1 dakikayı geçtiğinde ise ALTER INDEX cümleciğinin priority değeri low’dan regular’a yükseleceğinden artık üçüncü session’da çalıştıracağınız sorgu ikinci session’ı beklemeye geçecektir. Buradaki locking’i kaldırmak için ilk session’da ki açık olan transaction’ı sonlandırmanız gerekmektedir.

Aynı senaryoyu ABORT_AFTER_WAIT parametresine SELF değerini girerek tekrardan canlandıralım. İlk session adımını aynı şekilde çalıştıralım. Ardından aşağıdaki ALTER INDEX cümleciğini çalıştırıyoruz. Bir üstteki cümlecikten tek farkı SELF değerini alması.

Bu esnada üçüncü session’daki sorgumuzu tekrardan çalıştırıyoruz ve cevap döndüğünü görüyoruz. Bu arada ikinci session’da ki ALTER INDEX cümleciği ilk session’da ki transaction’ın sonlanmasını 1 dakika kadar bekliyor. Eğer 1 dakika içerisinde ilk session commit edilmezse, ALTER INDEX ifadesi 1 dakikayı geçtiğinde aşağıdaki hata mesajını üretecektir.

Bu da demek oluyorki, ilk session 1 dakika içerisinde commit edilmediğinden dolayı ikinci session 1 dakikalık MAX_DURATION değerine ulaşınca kendini abort ediyor! Son olarak ABORT_AFTER_WAIT için BLOCKERS parametresi girerek bir test yapalım. Aynı şekilde ilk session’ı çalıştırıyoruz. Ardından aşağıdaki ALTER INDEX ifadesini çalıştırıyoruz.

Farklı bir adım olarak bu sefer blocking script’imizi çalıştırıp takıldığımız lock türüne bakıyoruz.

 

 

Gördüğünüz gibi session, Shared Lock yedi. Fakat bu lock’ın priority’sini düşük tutarak WAIT_AT_LOW_PRIORITY keyword’ü içerisinde belirtilen koşulları baz alarak lock yönetimi gerçekleştirildi.

Eş zamanlı olarak denediğimizde, üçüncü session’daki sorgunuzdan 1 dakika içerisinde veya sonrasında cevap alabiliyorsunuz. 1 dakika tamamlandığında ALTER INDEX ifadenizden success alırsınız. Yani online olarak index’iniz rebuild edilmiş olacaktır. Aslında BLOCKERS parametresini set ettiğimizde senaryo şu şekilde gelişmiş oldu: 1 dakika yine session 1’in commit olmasını beklediniz, 1 dakika sonrasinda ABORT_AFTER_WAIT’e atadığınız BLOCKERS parametresi ile blocking’e takıldığınız session’ı yani ilk session’ın connection’ını direkt drop ettiniz. Session 1’e gelip COMMIT’i seçip execute etmek istediğinizde aşağıdaki hata mesajı karşınıza çıkacaktır.

Bu işlemleri yaparken sys.dm_tran_locks DMV’sini veya yukarıda kullandığımız blocking script’ini kullanmanız sürecin ne şekilde işlediği, hangi lock modellerinde beklendiği konusunda size detaylı bilgi verecektir. SQL Server 2014 ile WAIT_AT_LOW_PRIORITY ile 21 adet yeni wait type gelmiştir. Bunlara aşağıdaki şekilde ulaşabilirsiniz.

Eğer geçmişe yönelik bu sürece ulaşmak isterseniz, SQL Error Log’larından da ulaşmanız mümkündür.

 

---