Progress Report: Online Index Rebuild Süresince Detaylı İşlem Bilgisine Ulaşabilirsiniz

Bir önceki makalemde SQL Server 2014 ile online index rebuild operasyonlarındaki yeni geliştirmeleri paylaşmıştım (http://www.yigitaktan.com/archives/764). Bu makalemde ise bu sürecin en doğru şekilde nasıl izleneceğini anlatacağım. Bu makale içeriği bir önceki makaleden farklı olarak hem SQL Server 2012 hem de SQL Server 2014 için geçerlidir.

Büyük ölçekli tablolar üzerinde rebuild işlemleri çoğu DBA’in korkulu rüyasıdır. Nedeni ise bu tarz tablolardaki index’lerin rebuild süreçleri genelde uzun zaman alır. Bu tabikide SQL Server’ın kurulu olduğu ortama göre değişiklik göstersede tablonuz disk üzerinde tutulduğundan, memory optimized table yapısı olmadığından yani tablonuz memory’ye iğnelenmediğinden süreç yinede uzun zaman alacaktır. Ne olursa olsun rebuild operasyonları doğru zamanlarda yapılması gereken operasyonlardır.

Rebuild operasyonunun arkasında aslında Drop/Create mantığı çalışmaktadır. SQL Server engine’i tarafından create esnasında tablo üzerindeki her satır ayrı ayrı işleme alınır. Fakat bu sürecin normal bir monitoring ile hangi dakikada kaç satırlık veriyi işlediğinin cevabını vermek zordur. Bu sorunun cevabını SQL Server Profiler ve Extended Events ile nasıl bulacağımızı bu makalede anlatacağım. Extended Events ilk SQL Server 2008’de çıkartılan bir altyapıydı. Fakat SQL Server 2008’deki yapısında sadece Transact-SQL ifadeleri ile kullanılabiliyordu. Herhangi bir arayüz desteği bulunmamaktaydı.  SQL Server 2012’nin çıkmasıyla Profiler artık deprecated ilan edildi. Yani geliştirilmesi durduruldu. Buyüzden SQL Server 2012 ile birlikte Extended Events’e arayüz özelliği eklendi. Eğer Extended Events hakkında daha kapsamlı bilgi almak isterseniz 2012 senesinde gerçekleştirdiğimiz SQLSaturday’de ki demo ağırlıklı sunumumu izleyebilirsiniz.

 

Aşağıda yapacağımız demo’ları ben 10 milyon satırlık bir tabloda gerçekleştiriyorum. Bu tarz bir tabloyu T-SQL ile rahatlıkla oluşturabilirsiniz. Eğer anlamlı bir içerik ile bu şekilde büyük bir tablo oluşturmak isterseniz Red Gate’in SQL Data Generator ürününü önerebilirim. Yapacağımız demo için 10 milyon satırlık tabloyu bu tool ile çok kısa bir sürede oluşturabildim. Oluşturmuş olduğum tablonun schema’sının create script’i aşağıdaki gibidir. Sizde bu tabloyu kullanabilirsiniz.

Oluşturduğumuz tabloya bir non-cluster index ekliyorum. Rebuild sürecinde bu index’i kullanacağız.

Test ortamını hazırladıktan sonra index’i rebuild etmeden önce ilk olarak Extended Events için yeni bir session oluşturuyorum. Bu session için event’lerden progress_report_online_index_operation’ı seçiyorum. Aşağıdaki script ile oluşturabilirsiniz.

Oluşturmuş olduğumuz session’a sağ tıklayarak önce Start Session‘a ardından da Watch Live Data‘ya tıklayarak monitoring ekranını açıyoruz.

İlk açıldığı ekranda iki adet kolon gelmektedir. Bunlar name ve timestamp dir. name kolonunda event’in ismi yazılır. timestamp kolonunda ise belirlediğimiz koşullar doğrultusunda o an yakalanan trace’lerin tarih ve saat bilgileri yer almaktadır.

Tabi bu bahsettiğim süreç bir rebuild operasyonu gerçekleştirildiğinde oluşan bir durumdur. Mevcut hali ile gelen name kolonunu kullanmayacağız. Burada kullanmamız gereken timestamp kolonu ile birlikte rows_inserted kolonudur. Şuan için event tetiklenmediğinden event’in rows_inserted kolonu dahil diğer kolonlarını göremiyoruz. Bu yüzden yukarıda oluşturmuş olduğumuz non-clustered index’imizi online olarak rebuild edelim. Süreci izlerken dikkat etmeniz gereken nokta ilk olarak rebuild opersayonumuzu tek bir thread üzerinden gerçekleştiriyor olacağız. Yani MAXDOP parametresini 1 olarak rebuild ifademize ekliyoruz. MAXDOP’u hiç yazmaz veya “0” olarak set ederseniz SQL Server’ın mevcut bütün scheduler’ları kullanmasını sağlarsınız. MAXDOP, parallelism ile alakalı bir ifadedir. Daha önce karşılaşmadıysanız SQL Server’daki parallelism mantığını anlattığım makalemi okumanızı tavsiye ederim http://www.yigitaktan.com/archives/357 .

Yukarıda oluşturmuş olduğumuz non-clustered index’imizi aşağıdaki gibi tek bir thread’i kullanarak online olarak rebuild edelim.

Alter ifademizi çalıştırdıktan sonra Extended Events Live Data ekranına geçiyoruz.

Alter ifademiz devam ederken buraya yavaş yavaş verilerin dolduğunu göreceksiniz. Bu esnada operasyon süreci devam ederken bizim için gereksiz olan name kolonunu çıkartıp Details kısmında listelenen rows_inserted kolonunu ekliyoruz. Eklediğimiz anda karşımıza sıralı olarak artan satır sayıları gelmektedir. Bu da demek oluyor ki timestamp kolonunda belirtilen tarih/saat/dakika/saniye ‘de rows_inserted kolonundaki karşılığına denk gelen sayı kadar satırı o an SQL Server engine’i process etmiştir.

Bu bilgiyi Extended Events ile olduğu gibi SQL Server Profiler’la da almanız çok kolaydır. Aynı yapıyı profiler üzerinde monitor etmek için SQL Server Profiler’ı açıyoruz. New Trace ekranında herhangi bir template seçmiyoruz. O bölümü blank olarak seçelim. Events Selection tab’ına geçip event grup’ları içerisinden Progress Report event grubunu bulup altındaki Progress Report: Online Index Operation event’ini seçiyoruz. Şuan için burada bize gerekli olan StartTime ve BigintData1 kolonları. SPID hariç geri kalanlarını remove ederek trace’i başlatıyoruz.

Trace başladığında eğer rebuild operasyonunuz devam etmekte ise Extended Events’te aldığınız bilgilerin hepsini burada da alabilirsiniz. Eğer yukarıda linkini verdiğim Extended Events sunumumu izlemediyseniz production ortamlarda SQL Server Profiler’ın performansa olumsuz yönde etkisi olduğunu hatırlatmak isterim. Bu yüzden SQL Server Profiler yerine artık Extended Events kullanmak bu süreçte performans problemi yaşamanızı engelleyecektir.

Extended Events, SQL Server engine’i içerisinde işlem gören bir yapıya sahiptir. SQL Server Profiler ise ayrı bir executable dır. Yani SQL Server engine’i içerisinde sınıflandırılmaz. Bu yüzden SQL Server Profiler’ın performansı Extended Events’e göre daha düşüktür.

Örneğin yoğun transaction alan bir production sunucunuzda 5 ayrı session’dan 5 farklı trace’i SQL Server Profiler ile başlatın. Birkaç dakika geçmeden disk üzerindeki read I/O’nuzun arttığını ve CPU’larınızda da anlamsız dalgalanmalar olduğunu göreceksinizdir. Doğal olarak transaction’larınızda ciddi anlamda latency oluşacaktır. Fakat aynı senaryoyu Extended Events’de daha fazla session açarak gerçekleştirdiğinizde performansa hiçbir etki yapmadığını göreceksiniz.

Not: SQL Server Profiler’daki BigintData1 kolonu Extended Events’deki rows_inserted kolonuna, StartTime kolonu ise timestamp kolonuna denk gelmektedir.

Yukarıda da belirttiğim gibi bu testi yaparken bir non-clustered index’i online olarak tek bir thread (MAXDOP=1) olarak rebuild ettik. Bu sefer MAXDOP parametresini kaldırıp, SQL Server’a atanan mevcut bütün scheduler’ları kullanarak bu operasyonu gerçekleştirmesini sağlayalım.

Bu sefer Extended Events session’ına dönüp baktığımızda değerlerin sıralı olarak gitmediğini görüyoruz.

Aynı şekilde SQL Server Profiler trace’inize baktığınızda da bu değerlerin karışık olarak sıralı gitmediğini göreceksiniz. Bunun anlamı Query Optimizer, alter index ifademizi ilk yaptığımız gibi tek bir thread üzerinden tek bir scheduler’ı kullanarak işleme tabi tutmadı. Mevcut bütün scheduler’ları da işin içine katarak daha performanslı bir şekilde işlem görmesini sağladı.

Fakat burada görmediğimiz ve gözden kaçırdığımız mevcut blok’ların thread sayıları. Bunun için Extended Events session’ımızdaki kolonlardan parallel_process_thread_id ‘yi ekliyoruz. Böylelikle artık hangi bloğun kaç thread ile işleme alındığını görebiliyoruz. Bu yapının aynısını SQL Server Profiler üzerinde de görebilirsiniz. Bunun için SQL Server Profiler trace’inize BigintData2 kolonunu eklemeniz yeterli olacaktır. Extended Events’de olduğu gibi thread sayılarını görmeniz mümkündür. Fakat Extended Events’in size şu şekilde bir artısı olabilir. Extended Events üzerinde detaylı bir filtreleme bölümü mevcuttur. Eğer scheduler’larınızın tam anlamıyla thread yönetimini yapamadığını düşünüyorsanız (IO subsystem ve SQLOS tabanındaki hatalı ayarlar buna yol açar) filter bölümünden parallel_process_thread_id ‘yi hatalı olduğunu düşündüğünüz scheduler ID’niz ile filtreleyip işlem görme sürecini takip edebilirsiniz. Thread ID’nizi öğrenmek için sys.dm_os_schedulers DMV’sini status = ‘VISIBLE ONLINE’ olarak sorgulamanız yeterli olacaktır.

---