Periyodik olarak yapılan DBA işlemlerinden biri de sys.dm_db_index_usage_stats ile index’lerin kullanım istatistiklerini analiz etmektir. Bu DMV sayesinde az kullanılan ya da kullanılmayan index’ler bulunup drop edilmesine karar verilebilir.

Yalnız bu noktada kontrol edilmesi gereken çok önemli bir nokta silinmek istenen index’in stored procedure, function ya da view gibi SQL Server objelerinin içinde force edilip edilmediğidir. Çünkü eğer silinmek istenen index örneğin bir SP’nin içinde force edildiyse, index silindikten sonra ilgili SP çağrıldığında hata alacaktır.

[more]

Aşağıdaki script ile index force kullanılmış SQL Server objelerini bulabilirsiniz.

--Find SQL Server Objects which use index force
create table #tmp1 (DBName sysname,object_id int, name sysname, type varchar(3), type_desc varchar(50), OriginalText varchar(max), text varchar(max))
GO

insert #tmp1
	select db_name(),sm.object_id,o.name,o.type,o.type_desc
		  ,definition as OriginalText 
		  ,replace(
			replace(
				replace(
					replace(
						replace(
							replace(replace(upper(definition),' INDEX ','(INDEX')
						,'INDEX ','(INDEX'),'	','')
					,'CHARINDEX','')
				,'PATINDEX','')
			,'CHARINDEX','')
		,'PATINDEX','') as text
	from sys.sql_modules sm
	left join sys.objects o on o.object_id=sm.object_id
	where o.type in ('AF','FN','P','IF','FS','FT','PC','RF','TA','TF','TR','V','X')

select * 
from #tmp1 t
where t.text like '%(Index(%' or t.text like '%(Index=%' 
order by t.name

 

Şimdi yukarıdaki script’i denemek için bir test yapalım. Bunun için önce bir çalışma tablosu oluşturup daha sonra bu tabloyu kullanan 2 SP oluşturalım. SP’lerin ilkinde index force kullanırken ikincisinde kullanmayacağız.

--create a work database
Create database DBindexForce
GO
use DBindexForce
GO
--create a work table
Create table tblindexForce(col1 int, col2 char(10))
GO
create clustered index IX_1 on tblindexForce (col1)
GO

--Create 2 work SPs
--First one uses forced index
create proc mySP1
as
	select * from tblindexForce with(Index = IX_1)
GO

create proc mySP2
as
	select * from tblindexForce
GO

--Find SQL Server Objects which use index force
create table #tmp1 (DBName sysname,object_id int, name sysname, type varchar(3), type_desc varchar(50), OriginalText varchar(max), text varchar(max))
GO

insert #tmp1
	select db_name(),sm.object_id,o.name,o.type,o.type_desc
		  ,definition as OriginalText 
		  ,replace(
			replace(
				replace(
					replace(
						replace(
							replace(replace(upper(definition),' INDEX ','(INDEX')
						,'INDEX ','(INDEX'),'	','')
					,'CHARINDEX','')
				,'PATINDEX','')
			,'CHARINDEX','')
		,'PATINDEX','') as text
	from sys.sql_modules sm
	left join sys.objects o on o.object_id=sm.object_id
	where o.type in ('AF','FN','P','IF','FS','FT','PC','RF','TA','TF','TR','V','X')

select * 
from #tmp1 t
where t.text like '%(Index(%' or t.text like '%(Index=%' 
order by t.name

--CleanUp
Use master
GO
drop table #tmp1
GO
drop database DBindexForce
GO

 

Örnek çıktı aşağıdaki gibi olacaktır.

image

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan