Bu makalede mssql server’da Index komutlarını görmüş olacağız. İndex tanımlı olan kolona insert update ve delete işlemleri sonucu zamanla indexs’lerimizde fragmentation oranı artar. Bozulan index yapılarımızın tekrar düzenli hala getirmek için index rebuil ve reorganize komutları kullanılmaktadır.
Index reorganize işlemi index yapımızın düzeltme işlemi olarak karşımıza çıkmaktadır.
İndex rebuild işlemi ise indexlerimizin baştan sona kadar tekrardan düzenlenmesi işlemi olarak karşımıza çıkmaktadır. Reorganize yapısına göre uzun sürmektedir.
Şimdi gerçek sistem üzerinde veya https://ola.hallengren.com sayfasından alınmış olan index komutlarıyla detaylı bir şekilde index joblarını inceleyelim. Bu kod bloklarının çalışabilmesi için ilgili sayfadan IndexOptimize.sql stored procedure’ün indirilip index bakımı yapacağımız instance altında çalıştırılması gerekmektedir.
Yukarıdaki belirtilen link’e girildikten sonra Downloads sekmesinde IndexOptimize.sql procedure scriptinin indirilmesi gerekmektedir. İndirdikten sonra ilgili sunucumuza taşıyıp çalıştırılması gerekmektedir. Aşağıdaki resimde görüldüğü gibi script çalıştırılır.
Master veritabanımızın altında stored procedure oluşmuş oldu. Artık ola.hallengren sayfasındaki kod bloklarını çalıştırarak Index işlemi yapabiliriz.
Aşağıdaki paylaşılan komutlar SSMS arayüzünde new query denilerek veya job oluşturularak çalıştırılabilir. New query ile manuel bir şekilde çalıştırılması sağlanırken job ile periyodik olarak çalıştırılması sağlanır. Aşağıdaki Index komutunu çalıştırdığımızda farklı stored procedure’leride yüklememiz gerektiğini söylüyor. Bunun için ola.hallengren sayfasından tüm procedure’leri kapsayacak bir şekilde MaintenanceSolution.sql scriptini indirerek hata mesajlarının önüne geçmiş oluyoruz.
Yukarıdaki resimde dikkat edersek MaintenanceSolution.sql içinde tüm stored procedure’ün olduğunu görmüş oluyoruz. Bu strored procedure’leri çalıştırdıktan sql agent altında bunlarla ilgili job’lar oluşmaktadır.
Bu yapımızı ayarladıktan sonra belirtilen linkten SQL Server Index and Statistics Maintenance index ve istatistik job komutlarını görmekteyiz. Şimdi ilgili sayfada bulunan joblar ve gerçek sistem üzerinde kullanılan komutlara değinelim.
Aşağıdaki komut Alwayson yapılı sistemlerimizde kullanılan bir yapı olarak karşımıza çıkmaktadır.
Not: Ola scriptleri kullanılıyorsa AG kontrol şartı eklenmesine gerek yoktur. Çünkü birden fazla AG olursa kontrol işlemi zorlaşmaktadır. Sisteminiz üzerinde birden fazla AG yapımız varsa çalışmamasına sebebiyet verecektir.
DECLARE @tsql nvarchar(max)=''
DECLARE @AGName nvarchar(50)
SELECT @AGName=grp.ag_name
FROM sys.dm_hadr_availability_replica_states AS a JOIN
sys.availability_replicas AS b ON b.replica_id = a.replica_id left outer join
sys.dm_hadr_name_id_map as grp on grp.ag_id = a.group_id
WHERE a.role=1
IF @AGName = 'AG_NAME'
BEGIN
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES, -DB1, -DB2, -DB3',
@Indexes = 'ALL_INDEXES, -DBNAME.SCHEMENAME.TABLONAME'
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = 'Y',
@PageCountLevel=1000,
@SortInTempdb='Y',
@OnlyModifiedStatistics='Y',
@UpdateStatistics='ALL',
@StatisticsSample=10,
@MaxDop= 16
END
Not: @StatisticsSample = 100 bu işlem yukarıdaki komutlarda kullanılırsa FULLSCAN yapmaktadır. 100 yerine 10 yazılırsa %10 örneklem ile istatistik günceller. DEFAULT yazılırsa SQL Server’ın kendi örnekleme kararına bırakır. Full şeklinde tarama olsa bile @OnlyModifiedStatistics sadece değişen istatistikler toplanır. Fullscan yapısı bozulmaz.
Şimdi bu kavramlardan bazılarına değinelim.
@SortInTempdb: Dizinleri yeniden oluştururken sıralama işlemleri için tempdb kullanır. Bu ifade aktif edildiği zaman index işlemlerin yapılan işlemler tempdb veritabanından tutulur. Herhangi bir sorun anında index yapımızın tekrardan eski haline geçmesi sağlanır. Dikkat edilmesi lazım tempdb veritabanından doluluk anlamında sıkıntıya sebebiyet verebilir.
@OnlyModifiedStatistics: Sadece değiştirilmiş satırların istatistiklerini günceller. Bu komut kullanılmaya bilir. Çünkü yukarıda Rebuild veya Reorganize işlemlerinde istatistikler otomatik olarak güncellenir.
@UpdateStatistics: Tüm istatistiklerin güncellenmesi gerekmektedir. Ayrı bir statistics job’ı tanımlamak istemiyorsanız veya herhangi bir database veya tablo bazlı işlemde kullanılması istendiğinde bu ifade kullanılabilir.
Update istatistik komutunun diğer parametreleri:
- @UpdateStatistics = ‘ALL’: Index ve kolon istatistiklerini güncellemektedir.
- @UpdateStatistics = ‘INDEX’: Sadece Index istatistiklerini günceller.
- @UpdateStatistics = ‘COLUMNS’: Kolon seviyesinde otomatik istatistikleri güncellemektedir._WA_ statistic
- @UpdateStatistics = ‘NULL’: Herhangi bir istatistik güncelleme işlemi yapmaz.
@PageCountLevel: Bu şu anlama gelir. Eğer bu değer default olarak bırakılırsa sql server index bakımlarında 1000 üzeri olan index page_count değerine bakarak bakım jobını çalıştırır. 1000 altı olan page_count değerleri için index bakım çalışması yapmaz. Örnek vermek gerekirse @PageCountLevel=50 olunca 50 ve üzeri pagecount sayısına sahip olan index’ler için bakım reorganize ve rebuild işlemi yapılmaktadır. @PageCountLevel=0 yapılarak tüm indexleri kaplayan bir bakım çalışması yapılmaktadır.
Güncel bir index optimize stored procedure create edildiğinde PageCountLevel parametresini desteklemez. Aşağıda görülen iki parametrenin set edilmesi gerekmektedir. Burada bulunan MinnumberOfPages değerinin istediğiniz değere set edebilirsiniz.
@StatisticsSample: Tablonun ne kadarını baz alarak istatistiklerini güncelleyeceğimizi belirtiriz. Yüzde 10’unu alarak istatistikleri günceller. Bu ifadenin kullanılmaması sistem tarafından bu değerin kullanacağını göstermektedir. Yukarıda belirttiğim gibi Rebuild veya Reorganize işlemlerinde istatistikler güncelleneceği için bu komut kullanılmaması gerekmektedir. Örnek vermek gerekirse rebuild işlemlerinde fullscan yapıldığı taktirde yukarıda belirli yüzdelik olarak StatisticsSample çalışırsa bu ifade fullscan yaptığımız istatistikleri ezmektedir. Dikkatli olunulması gerekmektedir. OnlyModifiedStatistics komutu sadece değişen istatistik değerini güncellemektedir.
@LogToTable: İlgili commandlog tablosuna kayıt işlemi yapmaktadır. Master veritabanı altında oluşmaktadır. Olanhallengren index optimize sorgusu sonucu gelen bir ifadedir. İndex veya istatistik komutlarının ne kadar sürede çalıştığını hata mesajlarını tüm ifadeleri görebiliriz.
@MaxDop: Belirtilen maksimum worker tread sayısı kadar çalışmaktadır. Numa Node sayısını geçmemesi sistem performansı için önemlidir.
FragmentationLevel: Bu bölümde 5-30 arasındaki değerleri Reorganize, 30 ve üzeri değerleri Rebuild işlemine yapmaktadır.
Yukarıda gerçek sistem üzerinde çalışan Index optimize job’ını görmüş olduk
Buradaki tire(-)işareti ilgili veritabanları alma anlamında dikkat edersek istatistik job’ıda aşağıdaki kodun kısaltılması gibi bir şey.
@FragmentationMedium: ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
Bu değerle üzerinde teker teker döner hangisini şartlar destekliyorsa onu yapar. 5-30 arasında olan değerler bu seviyeye yönlendirilmektedir. Sürekli Reorganize işlemlerine tabi olması diye 5 değeri 10 veya 15 yapılabilir.
@FragmentationHigh : ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’, Bu değerle üzerinde teker teker döner hangisini şartlar destekliyorsa onu yapar. 30 ve üzerindeki değerler bu seviyeye yönlendirilmektedir. INDEX_REBUILD_ONLINE bu ifade rebuild işleminin online olarak gerçekleşeceğini belirtmektedir.
Not: INDEX_REBUILD_OFFLINE ifadesi blob nesneleri içeren ortamlarda kullanılır. Blob indexslerin olduğu ortamlarda kullanılmaktadır.
Not: Oluşturulan herhangi bir indexsin çalışıp çalışmadığını kontrol etmek için ilgili scriptin sonuna @execute=’N’ ifadesinin yazılması gerekmektedir. Sadece kontrol amaçlıdır. Herhangi bir indexs rebuild veya reorganize işlemi olmaz.
İlgili Index yapımıza bir zamanlayıcı tanımlamak istersek @TimeLimit = 36000 değişkeni kullanılmaktadır. Bu değişken saniye cinsindendir. İlgili indexsimiz sadece bu süre boyunca çalışmasını belirtiyorum. Böylece sistemin aktif olduğu zaman dilimlerinde çalışmasının önüne geçmiş oluruz. Örnek vermek gerekirse 10 saatlik bir limit koyduk. 10 saat doldu devam eden indexs bakımı kill edilmez. Kısacası 10 saat dolsa bile son indexsin bitmesini bekler ondan sonra jobımızı çalışmaz.
@Indexes parametresi belirtilen veritabanı veya veritabanlarında tüm indexsleri @Fragmentation parametresindeki değerlere göre güncellemektedir. @Indexes= parametresinden sonra kullanılan parametreler.
| Değer | Açıklama |
|---|---|
| ALL_INDEXES | Tüm indeksler |
| Db1.Schema1.Tbl1.Idx1 | Db1 veritabanındaki Schema1.Tbl1 nesnesindeki Idx1 dizini |
| Db1.Schema1.Tbl1.Idx1, Db2.Schema2.Tbl2.Idx2 | Db1 veritabanındaki Schema1.Tbl1 nesnesindeki Idx1 dizini ve Db2 veritabanındaki Schema2.Tbl2 nesnesindeki Idx2 dizini |
| Db1.Schema1.Tbl1 | Db1 veritabanındaki Schema1.Tbl1 nesnesi üzerindeki tüm dizinler |
| Db1.Schema1.Tbl1, Db2.Schema2.Tbl2 | Db1 veritabanındaki Schema1.Tbl1 nesnesi üzerindeki tüm dizinler ve Db2 veritabanındaki Schema2.Tbl2 nesnesi üzerindeki tüm dizinler |
| Db1.Schema1.% | Db1 Veritabanındaki Şema1 şemasındaki tüm dizinler |
| %.Schema1.% | Tüm veritabanlarındaki şema Şema1’deki tüm dizinler |
| ALL_INDEXES, -Db1.Schema1.Tbl1.Idx1 | Db1 veritabanındaki Schema1.Tbl1 nesnesindeki Idx1 dizini dışındaki tüm dizinler |
| ALL_INDEXES, -db1.schema1.tbl1 | Db1 veritabanındaki Schema1.Tbl1 nesnesindeki dizinler dışındaki tüm dizinler |
@AvailabilityGroups parametresi AG bazlı yapılan bir işlem olarak karşımıza çıkmaktadır. Aşağıdaki parametreleri desteklemektedir.
Not: AG kontrol şartımız varsa birden fazla AG eklenmesi ilgili procedure yapısının çalışmamasına sebep verecektir. Ola kendi içerisinde AG kontrolü yapmaktadır.
| Değer | Açıklama |
|---|---|
| ALL_AVAILABILITY_GROUPS | Tüm kullanılabilirlik grupları |
| AG1 | Kullanılabilirlik grubu AG1 |
| AG1, AG2 | AG1 ve AG1 kullanılabilirlik grupları |
| ALL_AVAILABILITY_GROUPS, -AG1 | AG1 hariç tüm kullanılabilirlik grupları |
| %AG% | Adında “AG” bulunan tüm kullanılabilirlik grupları |
| %AG%, -AG1 | AG1 dışında adında “AG” olan tüm kullanılabilirlik grupları |
| ALL_AVAILABILITY_GROUPS, -%AG% | Adında “AG” bulunmayan tüm kullanılabilirlik grupları |
Jobımızı tekrardan düzenlersek Aşağıdaki job bizim için en ideal olmuş olacaktır.
DECLARE @tsql nvarchar(max)=''
DECLARE @AGName nvarchar(50)
SELECT @AGName=grp.ag_name
FROM sys.dm_hadr_availability_replica_states AS a JOIN
sys.availability_replicas AS b ON b.replica_id = a.replica_id left outer join
sys.dm_hadr_name_id_map as grp on grp.ag_id = a.group_id
WHERE a.role=1
IF @AGName = 'AG_NAME'
BEGIN
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES, -DB1, -DB2, -DB3',
@Indexes = 'ALL_INDEXES, -DBNAME.SCHEMENAME.TABLONAME'
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 30,
@LogToTable = 'Y',
@MaxDop= 16,
@TimeLimit = 36000;
ENDYukarıda gerçek sistem üzerinde çalışan job’ın açıklamasını yaptıktan sonra ola.hallengren sayfasında bulunan diğer komutlarımıza değinelim. Sizde belirtilen sayfada istediğiniz script’i çalıştırabilirsiniz.
- Aşağıda belirtilen komut belirli bir veritabanında belirtilen tablodaki index’leri güncellemektedir. Aşağıdaki komutta sadece AdventureWorks.Production.Product tablosundaki indexs yapılarını güncellemektedir. Birden fazla tablo olursa aralarını virgül koyulur.
EXECUTE dbo.IndexOptimize
@Databases = 'AdventureWorks',
@Indexes = 'AdventureWorks.Production.Product',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = 'Y'- Aşağıdaki komut AdventureWorks veritabanındaki Production.Product tablosundaki indeksler hariç tüm indeksleri rebuild ve reorganize yapmaktadır.
EXECUTE dbo.IndexOptimize
@Databases = 'AdventureWorks',
@Indexes = 'ALL_INDEXES, -AdventureWorks.Production.Product',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@TimeLimit = 36000- Aşağıdaki komut index rebuild ve reorganize işlem sonuçlarını master veritabanı altında bulunan commandlog tablosuna kaydetmektedir.
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = 'Y'Eğer yukarıdaki sorgularımıza istatistikle ilgili bir ifade eklemek istersek aşağıdaki komut kullanılmaktadır.
- Tüm kullanıcı veritabanındaki istatistikleri güncellemek istersek aşağıdaki komut kullanılabilir.
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'
- Tüm kullanıcı veritabanındaki değişen istatistikleri güncellemektedir. Ayrıca Fullscan işlemi yapılmaktadır.
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@StatisticsSample = 100Not: Şunu belirtmek gerekir ki yukarıdaki komutları sqcmd aracılığıylada çalıştırılabilir. sqlcmd windows bazında herhangi bir yetkilendirme sorununa takılmaz. Sadece dikkat ederseniz ilgili komutu sqlcmd komut satırının arasına yerleştirmiş oldum. Aynı işlemleri backup ve istatistikler içinde yapılabilir.
Aşağıdaki komutta dikkat edilmesi gereken sqlcmd komutunun tek satır üzerinde yapılması gerekmektedir. Komutların alt alta yazılması hataya sebebiyet verecektir.
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@Indexes = 'ALL_INDEXES, -AdventureWorks.Production.Product',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@TimeLimit = 36000" -bNot: Ola hallengrenn scriptlerinde ag kontrola gerek yoktur. Bu sebepten backup index diğer bakım joblarına ag kontrol koymamıza gerek yoktur. AG kontrol komutu en son eklenen AG ismini almaktadır.!!
Not: Yukarıdaki index bakım joblarımızda @Fillfactor parametresi girilerek her index bakım joblarında rebuild işlemlerinde index yapımızın yeni fillfactor değerine göre ayarlanması sağlanacaktır. Reorganize işlemlerinde fillfactor oranı bir etki etmez. Fillfactor değerinin instance bazında gelmemesinin sebebi son kullanıcının index yapısını manuel oluştururken bu ayarı index üzerinden belirtmemesinden kaynaklanmaktadır.
Bu makalede sql server üzerinde kullanılan index job komutlarını görmüş olduk. Başka bir makalede görüşmek dileğiyle..
“Yürüyüşünde ölçülü ol, sesini yükseltme; çünkü seslerin en çirkini eşeğin anırmasıdır.”Lokman-19
