Database'de Indexler ve Lokasyonları Hakkında Bilgi Alma

Aşağıda bulunan DMV sayesinde database'de bulunan indexlerin tablo isimlerini, host edildikleri File Groupları ve indexin cluster, non-cluster veya heap-table olup olmadığını çok kolay bir şekilde görebiliriz.

WITH C AS
(
SELECT ps.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
JOIN sys.destination_data_spaces dds ON dds.data_space_id = f.data_space_id
JOIN sys.partition_schemes ps ON ps.data_space_id = dds.partition_scheme_id
UNION
 SELECT f.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
)
SELECT [ObjectName] = OBJECT_NAME(i.[object_id])
, [IndexID] = i.[index_id]
, [IndexName] = i.[name]
, [IndexType] = i.[type_desc]
, [Partitioned] = CASE WHEN ps.data_space_id IS NULL THEN 'No'
ELSE 'Yes'
END
, [StorageName] = ISNULL(ps.name, f.name)
, [FileGroupPaths] = CAST(( SELECT name AS "FileGroup"
, physical_name AS "DatabaseFile"
FROM C
WHERE i.data_space_id = c.data_space_id
FOR
XML PATH('')
) AS XML)
FROM [sys].[indexes] i
LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups f ON f.data_space_id = i.data_space_id
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
ORDER BY [ObjectName], [IndexName]

 

DMV-DMF Kullanımı-3(Kullanılmayan Indexlerin Tespit Edilmesi)

Bir önceki makalemde kullanılmayan  veya çok az kullanılan indexlerin query performansını  düşürdüğünü belirtmiştim. Sadece Select işlemleri için değil hatta özellikle insert ve update işlemlerinde kullanılmayan indexler performansı aşağılara çekmektedir.

Indexlerin bütün kullanım bilgileri sys.dm_db_index_usage_stats adlı  DMV'de tutulmaktadır. Bu DMV diğer sys.indexes, sys.objects, sys.schemas ve sys.partitions adlı DMVlerle birlikte sorgulandığında bize index kullanımları ile ilgili ayrıntılı bir bilgi verebilir.

Aşağıdaki script bize index kullanımı ile geniş bir kullanım bilgisi vermektedir. Bu tablo kullanılarak  kullanılmayan indexler silinebilir.  Ancak önceki makalelerimde belirttiğim gibi DMV ve DMFler her SQL Server restart olduğunda yeniden doldurulduğu için SQL Server başladıktan sonra belirli bir süre çalıştırılmış olması Best Practicedir.

SELECT TOP 250 db_name(dm_ius.database_id) As DbName
,o.name As ObjectName
,i.name As IndexName
,i.index_id As IndexID
,dm_ius.user_seeks As UserSeek
,dm_ius.user_scans As UserScans
,dm_ius.user_lookups As UserLookups
,dm_ius.user_updates As UserUpdates
,p.TableRows
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id=dm_ius.index_id and
dm_ius.object_id=i.object_id
INNER JOIN sys.objects o ON dm_ius.object_id=o.object_id
INNER JOIN sys.schemas s on o.schema_id=s.schema_id
INNER JOIN (SELECT SUM(P.ROWS) TableRows,p.index_id,p.object_id
FROM sys.partitions p GROUP BY p.index_id,p.object_id)p
on p.index_id=dm_ius.index_id and dm_ius.object_id=p.object_id
where OBJECTPROPERTY(dm_ius.object_id,'IsUserTable')=1
and i.type_desc='nonclustered'
and i.is_primary_key=0
and i.is_unique_constraint=0
order by (dm_ius.user_seeks+ dm_ius.user_scans+dm_ius.user_lookups) ASC

DMV-DMF Kullanımı-2(Eksik Indexlerin Tespit Edilmesi)

Bir databasede sorgu performanslarını artırmak için öncelikle index yapısını gözden geçirip eklememiz index ekleyebilmek içinse  eksik olan indexleri ekleyebilmemiz için öncelikle eksik indexleri bulmamız gerekmektedir.  SQL Servera gönderilen sorgular için SQL Server bir query plan oluşturur. Bu query plan en iyi index kullanarak sorgu yapmaya çalışmakta eğer sorgu için index bulamadığı takdirde aşağıdaki DMV ve DMFler üzerinde depolama yapmaktadır.

Aşağıda bulunan 4 DMV ve DMF kullanarak eksik indexlerimizi belirleyebiliyoruz.

•  sys.dm_db_missing_index_group_stats - DMV-Eksik index’ler hakkında özet bir bilgi sunar.

• sys.dm_db_missing_index_groups - DMV-sys.dm_db_missing_index_group_stats ile sys.dm_db_missing_index_details arasında ilişki kurmamızı sağlar.

• sys.dm_db_missing_index_details-DMV-Eksik index hakkında kolon bilgileri gibi detaylı bilgileri döndürür.

•  sys.dm_db_missing_index_columns - DMF-Eksik index kolonları hakkında bilgi döndüren fonksiyondur. Diğer 3 tanesi ise  viewdir.

Örnek Sorgu

Aşağıdaki sorgu mevcut databasede bulunan eksik indexlerin bir listesini döndürür.

SELECT so.name
    , (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact
    , mid.equality_columns
    , mid.inequality_columns
    , mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
INNER JOIN sys.objects so WITH (nolock) ON mid.object_id = so.object_id
WHERE migs.group_handle IN (
    SELECT     TOP (5000) group_handle
    FROM sys.dm_db_missing_index_group_stats WITH (nolock)
    ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)

DMVler her instance başlatıldığında yeniden doldurulduğu için bu DMVler SQL Server başlatıldıktan sonra ve belirli bir süre çalıştırıldıktan sonra kontrol edilmelidir.

SQL Server IQ oranı o kadar iyi olmadığı için bu sorguda önerilen indeksleri körü körüne uygulamak doğru değildir. Öncelikle bir test sunucusu üzerinde index oluşturulmalı ve iş yükü test edilmelidir. Eğer olumlu sonuçlar alınırsa teyit edilmeli ve kullanılmaya başlanmalıdır.

Eğer kullanılmayan bir index oluşturulursa boş yere disk alanı israfı veya dahada önemlisi tabloya yapılan diğer sorgularda performansı düşüreceği için index oluşturulma işi çok iyi analiz edilerek yapılmalıdır.

Not:Kullanılmayan indekslerin tespit edilmesi konusunu DMV-DMF Kullanımı-3 nolu makalede ayrıntılı olarak inceleyeceğiz.

DMV-DMF Kullanımı-1(Indexlerde Fragmentation Oranlarını Bulma)

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