Bu yazı dizimde
DBAlerin hayatlarında çok önemli yer kaplayan kavramlar olan DMV (Database
Management View) ve DMF(Database Management Function) kavramlarından bahsetmek
istiyorum.
Bu kavramlar
hayatımıza SQL Server 2005 ile beraber girmiş ve DBAlerin işlerini
kolaylaştırmıştır. Sql Server üzerindeki
aktiviteler depolanmakta ve DMVler sayesinde bir view gibi
kullanılabilmektedir. Çalıştırılan
sorguların hangi indexleri kullandığı, ne kadar I/O yaptığı veya ne kadar CPU
tükettiği gibi bir çok veriye ulaşmamız mümkün.
DMVler bir view gibi
kullanılabilir demiştik ancak veriler
databasede değilde bellekte tutulduğu için veritabanı her yeniden başladığında
veriler yeniden toplanmaya başlamaktadır.
Ayrıca DMV ile DMF
birbirine çok benzesede DMVler view gibi sorgulanabildiği ancak DMFler dışarıdan aldığı parametreye göre veri
döndüren fonksiyonlardır desek doğru olur.
DMV ve DMF Sql
Serverımız üzerinde bir çok işlem için kullanılabilir. Bu konu çok detaylı ve ayrıntılı bir konu.
Bir veya birkaç makale ile anlatmak imkansız. Ben sizlere bugün indexler
üzerinde fragmentation oranlarının bulunması için nasıl kullandığımızı
anlatacağım.
Tablolarımız
üzerinde bulunan indexler zamanla çalışan sorgular neticesinde bozulmalara
uğramaktadır. Bu indexlerin belirli periyotlarla rebuild veya reorganize
edilmesi gerekir. %5 ile %30 arası bozulmalarda reorganize %30 üzeri
bozulmalarda ise rebuild işlemi yapılması Microsoft'un meşhur deyimi ile Best
Practicedir. Bu yazımda indexlerin nasıl Rebuild veya reorganize edildiğinden
bahsetmeyeceğim. Hangi indexin fragmentationa uğradığını ve bu oranın nekadar
olduğunun nasıl tespit edileceğinden bahsetmek istiyorum.
Yukarıdada
bahsettiğim gibi oluşturduğumuz
indexlerde veri eklenmesi silinmesi gibi işlemler oldukça indexlerimiz pageleri
arasında boşluklar oluşacak ve btree veri yapımızın dengesi bozulacaktır. Bu
nedenle düzenli olarak bozulan indexlerimizi bulup bozulma oranlarına göre
rebuild veya reorganize etmemiz gerekir.
Bozulma
oranlarınıda aşağıdaki sorguyu kullanarak görebiliriz. Bu sorguda indexler üzerinde bozulma
oranlarını görebildiğimiz sys.dm_db_index_physical_stats fonksiyonu ile sys.indexes adlı view birlikte sorgulanmakta ve bizlere index fragmentation yani dağılma oranlarını
verecektir.
sys.dm_db_index_physical_stats sistem fonksiyonu bize istediğimiz bir
indexte, bir databasede tüm indexler veya tüm databaselerdeki tüm indexlerin
fragmentation oranlarını verebilir.
sys.dm_db_index_physical_stats sistem fonksiyonu 5 tane parametre almaktadır.
Bunlar
database_id,object_id,index_id,partition_id ve modedur.
database_id: Bu parametre ile database
seçebiliyoruz. NULL olarak verilirse bütün dbler seçilmiş olur
object_id:Bu parametre ile tablo seçebiliyoruz.
NULL olarak verilirse bütün tablolar seçilmiş olur.
index_id:Bu parametre ile index seçebiliryoruz.
NULL olarak verilirse bütün indexler seçilmiş olur.
partition_id:Bu parametre ile partition
seçebiliryoruz. NULL olarak verilirse bütün indexler seçilmiş olur.
mode:İşlem sonucunun detayını belirleyebiliriz.
DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED değerlerini alabilir. NULL olarak
geçilirse LIMITED mode kullanılır.
SELECT TOP 200 DB_NAME() AS databaseName ,OBJECT_SCHEMA_NAME(s.object_id) AS SchemaName , OBJECT_NAME(s.[object_id]) As TableName ,i.name As IndexName ,ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null) s INNER JOIN sys.indexes i on s.[object_id] =i.[object_id] and s.index_id=i.index_id INNER JOIN sys.indexes o on i.object_id=o.object_id WHERE s.database_id=DB_ID() and i.name is not null and OBJECTPROPERTY(s.[object_id],'IsMsShipped')=0 order by [Fragmentation %] desc
