Makale Özeti

Merhaba Sevgili Arkadaşlar, Sizlerle bu gün Missing Index yani eksik index operasyonu nasıl gerçekleştirilir ve bizim için önemi olanları nasıl analiz ederiz onları göreceğiz.

Makale

Konu başlıklarımız aşağıda ki şemamız da yer almaktadır.

 1.1

 

Yukarıda da gördüğünüz üzere YSFKHVC Serverımızda barındırdığımız DATABASE imiz üzerinde Missing Index operasyonunu adım adım gerçekleştireceğiz. İlk önce aşağıda hazırladım query ile Missing indexlere ulaşacağız.

--Test Database imizi oluşturuyoruz...
CREATE DATABASE SQLTURKIYE_

--Test tablolarımızı oluşturuyoruz...
USE SQLTURKIYE_
CREATE TABLE SQLTURKIYE_TEST1
(
	Id INT IDENTITY(1,1),
	Name			VARCHAR(50),
	Surname		VARCHAR(60),
	Identity_Number CHAR(11)
)

CREATE TABLE SQLTURKIYE_TEST2
(
	Id				INT IDENTITY(1,1),
	Adress			VARCHAR(50),
	Phone		CHAR(10),
	E_Mail VARCHAR(50),
	JOB VARCHAR(50),
	Identity_Number CHAR(11)
)

insert INTO SQLTURKIYE_TEST1 VALUES
('Özgür','Özeren','11111111111')
 ,('Önder','Özeren','22222222222')
 ,('Yusuf','Kahveci','33333333333')

insert INTO SQLTURKIYE_TEST2 VALUES ('ISTANBUL','9999999990','info@sqlturkiye.com','Advisor',11111111111)
									   ,('SAMSUN','8888888880','info@sqlturkiye.com','Student',22222222222)
									   ,('RİZE','7777777770','yusuf@ysfkhvc.com','Database Administrator',33333333333)

-- Tablomuza veri basıyoruz.
Declare @i int=1
While @i < 100000
Begin
insert INTO SQLTURKIYE_TEST1 VALUES ('Yusuf '+cast(@i as char(11)),'Kahveci',cast(@i as char(11)))
set @i=@i+1
print @i
End

Bu aşamadan sonra tablomuza bir kaç Select işlemi uyguluyoruz.

 

use SQLTURKIYE_
select * from SQLTURKIYE_TEST1 Where id=3

select * from SQLTURKIYE_TEST1 Where name='Yusuf'

select * from SQLTURKIYE_TEST2 Where phone='7777777770'

select * from SQLTURKIYE_TEST1 t1 inner join SQLTURKIYE_TEST2 t2 on t1.identity_Number=t2.identity_Number Where t2.phone='7777777770'

Bu aşamadan sonra Missing Index lere ulaşmak için elimizde bulunan 2 farklı scripten ilkini çalıştırıyoruz.

 

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Yukarıdaki scriptimizin sonucunda ki ekran görüntüsü aşağıdadır.   


* DatabaseID                               : Database imizin id bilgisine ulaşıyoruz bu kolon sayesinde.

Avg_estimated_impact  : Tahmini Ortalama Kazancımıza uılaşıyoruz.

Last_User_Seek                     : En son ne zaman seek edildiğine dair tarih bilgisi

TableName                                : Tablomuzun ismi.

*Create_Statament                : Oluşturacağımız yani önerilen index in create scriptini vermektedir. Aynı şekilde yine Missing Index i bulmamıza yardımımcı scriptimiz aşağıdadır.

 

select DB_NAME(id.database_id) as databaseName,
	id.statement as TableName,
	id.equality_columns,
	id.inequality_columns,
	id.included_columns,
	gs.last_user_seek,
	gs.user_seeks,
	gs.last_user_scan,
	gs.user_scans,
	gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) as ImprovementValue		
from sys.dm_db_missing_index_group_stats gs
INNER JOIN sys.dm_db_missing_index_groups ig on gs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details id on id.index_handle = ig.index_handle
order by avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) desc

Ekran görüntüsü aşağıdaki gibidir.   Yukarıda da görüldüğü gibi ,lk missing index scriptimizin aynı sonmuclarını vermekte fakat kolon olarak yöneten kişiye parça bilgileri daha fazla sunmaktadır. Yukarıda ki missing araştırmalarımıza göre index isteyen tablomuzu belirledik.Şimdi sıra geldi tablo analizimize.İlk önce tabloyu missing index e zorlayan queryler nelermiş? Aşağıda ki scriptimiz ile bu sonuçlara ulaşabiliriz.

 

--Missing Indexe sebep olan queryler...
select 
     st.[text],
     SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text,        
     qs.last_execution_time,
     qs.execution_count,
     qs.total_logical_reads as total_logical_read,
     qs.total_logical_reads/execution_count as avg_logical_read,
     qs.total_worker_time/1000000 as total_cpu_time_sn,
     qs.total_worker_time/qs.execution_count/1000 as avg_cpu_time_ms,
     qp.query_plan,
     qs.last_logical_reads,
     qs.plan_generation_num
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
where SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) like '%SQLTURKIYE_TEST1%'

Ekran görüntüsü ;   Şimdi sorgumuza da ulaştığımıza göre sorgumuzu ele alalım ve istatistik bilgilerine erişim not alalım.

 

set statistics io on
set statistics time on

select * from SQLTURKIYE_TEST1 t1 inner join SQLTURKIYE_TEST2 t2 on t1.identity_Number=t2.identity_Number Where t2.phone='7777777770'

set statistics io off
set statistics time off

Istatistik sonuçları aşağıdadır ;

(1 row(s) affected) Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘SQLTURKIYE_TEST1′. Scan count 1, logical reads 700, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘SQLTURKIYE_TEST2′. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 63 ms, elapsed time = 96 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

 

Bu bilgilere eriştikten sonra tablomuzu detaylı analize sokuyoruz.

--Tablomuzun üzerinde olan indexler
exec sp_helpindex 'SQLTURKIYE_TEST1'
--Tablo Analizi ve Index boyutları
exec sp_spaceused 'SQLTURKIYE_TEST1'

Bu işlemler sonucunda eğer index eklememiz eriştiğimiz sonuçlara göre mantıklı ve performanslı bir karar ise eklenecektir.

CREATE INDEX [IX_SQLTURKIYE_TEST1_Identity_Number] ON [SQLTURKIYE_].[dbo].[SQLTURKIYE_TEST1] ([Identity_Number]) INCLUDE ([Id], [Name], [Surname])

Index imizi oluşturduktan sonra aynı query mizi tekrar çekip istatistik değerlerimizi karşılaştırmalıyız.

 

set statistics io on
set statistics time on

select * from SQLTURKIYE_TEST1 t1 inner join SQLTURKIYE_TEST2 t2 on t1.identity_Number=t2.identity_Number Where t2.phone='7777777770'

set statistics io off
set statistics time off

Istatistik Değer ;

(1 row(s) affected) Table ‘SQLTURKIYE_TEST1′. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table ‘SQLTURKIYE_TEST2′. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

  Yukarıda da görüldüğü üzere incelemelerimiz sonucu gerekli index imizin sağlamasını yaparak en uygun yere oluşturduk ve istatistik sonuçlarımızda ki performans arttırımını görmüş olduk.    

Saygılarımla

Yusuf KAHVECİ

Database Administrator