Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Haziran 2017  >>
PaSaÇaPeCuCuPa
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
Keywords

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


Index’lerin içinde bulundukları filegroup’ları değiştirmek istediğimizde drop edip tekrar create etmemiz gerekir. Bu işlemi yapabilmek için de elimizde 2 farklı seçenek var. “Create With Drop_Existing = On” ve “Drop&Create”. Bu yazımda bu 2 seçenek arasındaki farka bakıyor olacağız.

[more]

Aslında ilk bakışta 2 seçenek te aynı gibi duruyor.

  • Drop&Create seçeneğinde index’i önce drop edip daha sonra tekrar create ediyoruz.
    DROP INDEX CI1 on tbl1
    GO
    CREATE INDEX CI1 on tbl1 (Col1)
    on [NewFileGroup]
    GO
  • Drop_existing de kelime anlamına baktığımızda olan index’i önce drop edip sonra create et demek.
    CREATE INDEX CI1 on tbl1 (Col1)
    with (DROP_EXISTING=ON)
    on [NewFileGroup]
    GO

 

Ama aralarında çok önemli bir fark var

  • Eğer bir Clustered Index’i “Create with Drop_Existing” metodu yerine “drop&create” metodu ile tekrar create edersek tüm nonclustered index’ler 2 defa rebuild olur. Bunun sebebi ilk drop işleminde tüm nonclustered index’lerin leaf level page’lerındaki clustered index key’lerin kaldırılıp Heap Row Pointer bilgilerinin konulması, tekrar create edildiğinde de yeni clustered index’in key bilgilerinin tekrar tüm nonclustered index’lerin leaf level page’lerine yazılmasıdır. (Clustered Index key’lerinin nonclustered index’lerin leaf level’inde olması lookup’ların yapılabilmesi içindir. Daha detaylı bilgi için şu makaleyi inceleyebilirsiniz.)

    Bu davranış aşağıdaki msdn article’da şu şekilde açıklanıyor;

    Sometimes indexes are dropped and re-created to reorganize the index, for example to apply a new fillfactor or to reorganize data after a bulk load. It is more efficient to use CREATE INDEX and the WITH DROP_EXISTING clause for this, especially for clustered indexes. Dropping a clustered index causes all the nonclustered indexes to be rebuilt. If the clustered index is then re-created, the nonclustered indexes are rebuilt once again to replace the row pointers with clustered index keys. The WITH DROP_EXISTING clause of CREATE INDEX has optimizations to prevent this overhead of rebuilding the nonclustered indexes twice. DBCC DBREINDEX can also be used and has the advantage that it does not require that the structure of the index be known.
    http://msdn.microsoft.com/en-us/library/aa258837(SQL.80).aspx
  • Olaya nonclustered index açısından bakarsak; bir nonclustered index’in drop_existing metodu yerine drop&create metodu ile tekrar create edilmesi daha fazla IO yapılmasına ve dolayısıyla daha uzun sürmesine sebebiyet veriyor. Bunun nedeni; işlem drop&create metodu ile yapıldığında index tekrar create edilirken index page’ler tüm tablo okunarak oluşturuluyor. Drop_Existing metodu ile yapıldığında ise hali hazırda olan page’ler kullanılıp sadece index’in filegroup’u değiştiriliyor. Tüm tablo okunmadığı için drop_existing metodu bize daha performanslı bir sonuç vermiş oluyor. Tabi tanımlama olarak tamamen aynı index’i tekrar oluşturduğumuzu varsayarsak.

    Bu konuda www.sqlservercentral.com daki bir makalede detaylı bir analiz yapılmış. Drop_existing, drop&create ve rebuild işlemlerinin yaptıkları IO ve operasyon süreleri tablo halinde karşılaştırılmış. Detaylı bilgiye şu makaleden erişebilirsiniz.

 

Sonuç olarak özellikle Index filegroup değişimlerinde drop_existing metodunu tercih etmenizi tavsiye ederim. aynı işi boşu boşuna daha fazla kaynak tüketerek yapmanın bir anlamı yok değil mi? :)

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


SQL Server’da birden fazla kolon üzerine tanımlanan index’lere composite index denilmektedir. Örneğin aşağıdaki index 2 kolon üzerine tanımlanmış nonclustered bir composite index örneğidir.

create nonclustered index IX_1 on tbl1
	(Column1, Column2)

 

Klasik Index’ler (ColumnStore Index’de durum farklıdır) B-Tree yapısında organize olduklarından dolayı index’in hangi sıralama ile oluşturulduğu oldukça önem taşımaktadır. Çünkü yukarıdaki index örneğini ele alırsak kayıtlar önce column1’e göre dizilecek, daha sonra column2’e göre dizelecektir. (Index’lerdeki B-Tree yapısı hakkında daha detaylı bilgi almak için şu makaleye göz atabilirsiniz)

[more]

Composite index oluştururken uyguladığımız ilk best practice where koşulundaki eşitlik (equality) kolonlarının eşitlik olmayan (inequality) kolonlardan daha önce yazılması gerektiğidir.

2. best practice’imiz ise her grubun içinde (eşitlik ve eşitlik değil) selectivity’si yani seçiciliği daha fazla olan kolonun daha önde tanımlanmasıdır. Örneğin kişi bilgilerini tuttuğumuz bir tabloda cinsiyet ve il alanlarına index tanımlayacağımızı ve sorgumuzda cinsiyet ve il alanlarını eşitlik şeklinde kullandığımızı düşünelim. Cinsiyet alanı sadece 2 farklı değer alabilirken il alanı 81 farklı değer alabilmektedir. Dolayısıyla il alanının seçiciliği cinsiyet alanine göre çok daha fazladır. Bu yüzden bu 2 kolon için composite index tanımlanacaksa sıralamanın il,cinsiyet şeklinde olması gerekir.

Best practice’leri toplayacak olursak;

  • «Eşittir» kolonları «eşit değil» kolonlarından önce kullanılmalıdır.
  • En seçici «eşittir» kolonu ilk sıraya konulmalıdır.
  • En seçici «eşit değildir» kolonu son «eşittir» kolonundan sonraya konulmalıdır.

Örneğin aşağıdaki gibi bir sorgu için index create ediyorsak ve col2, col4 kolonları diğer kolonlara göre daha seçiciyse index’in şu şekilde olması best practice’dir

Select * from tbl1_
where Col1<>'a' and Col2='b' and Col3='c' and Col4<>'d'

create nonclustered index IX_1 on tbl1
	(Col2, Col3, Col4, Col1)

 

Şimdi bir örnek yaparak konuyu pekiştirelim.

--Bir calisma tablosu olusturuyoruz
Create table tbl_CompSample(Gender char(1), City int, Col1 Char(50), Col2 Char(50))
go

--Calisma tablosuna 1000 kayit basiyoruz
declare @i int = 1
while @i<1000 begin
	insert tbl_CompSample 
		select (case @i%2 when 1 then 'M' else 'F' end), (@i%81)+1, 'value1', 'value2'
	set @i=@i+1
end

--Kayit sayisini arttiriyoruz
declare @i int
set @i = 0
while @i < 8
begin
	insert into tbl_CompSample select * from tbl_CompSample
	set @i = @i + 1
end

--255.744 kayittan olusan bir calisma tablosu hazirladik
select count(*) from tbl_CompSample

--Sorgumuz su sekilde
--Sorguda Gender kolonu esitlik seklinde iken City kolonu esitlik degil seklinde kullanilmis
--	Bu yuzden Gender kolonunun selectivity'si daha az olmasına ragmen index'de bu kolonu basta kullanmak best practice'dir
select Col1, Col2 from tbl_CompSample
where Gender='M' and City between 34 and 50


--ilk olarak Gender, City seklinde bir nonclustered 
--index olusturup IO degerlerine bakiyoruz
create nonclustered index IX_1 on tbl_CompSample (Gender, City) include (Col1, Col2)

set statistics io on

select Col1, Col2 from tbl_CompSample
where Gender='M' and City between 34 and 50

--Bu index ile 389 logical read yapildi
--logical read degerini result kisminda gorebilirsiniz

--Simdi ayni index'i bu sefer City, Gender seklinde tanimlayalim
create nonclustered index IX_1 on tbl_CompSample (City, Gender) include (Col1, Col2)
with (drop_existing=ON)

select * from tbl_CompSample
where Gender='M' and City between 34 and 50

--Bu sekilde ise 754 IO yaptı.
--Cunku City daha secici olmasina ragmen esitlik degil seklinde
--	kullanildigi icin Gender, City daha iyi bir index'tir

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


During DML (insert, update, delete) operations, every related indexes are updated. If the table has too many indexes, SQL Server will spend a lot of resource and time for these operations and it might be a performance problem.

[more]

You can use the below script to identify which tables have more indexes than column count.

create table #tmp1 (DBName sysName, SchemaName sysName, ObjectName sysName, IndexCount int, ColumnCount int)

exec sp_msforeachdb 'use ?;
insert #tmp1
select ''?'' as DBName, SchemaName, ObjectName, IndexCount, count(*) as ColumnCount
from (
      select object_schema_name(object_id) as SchemaName
            ,object_name(object_id) as ObjectName
            ,count(*) as IndexCount       
            ,object_id
      from sys.indexes i
      where ObjectProperty(object_id,''IsUserTable'')=1 and index_id>0 
      group by object_id
)xx   
join sys.columns c on c.object_id=xx.object_id
group by SchemaName, ObjectName, IndexCount
having IndexCount>count(*)
'

select * from #tmp1

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


If a table does not have clustered index then we call this table as “Heap Table”

For OLTP system, most of time, it is better to use Clustered Index instead of Heap Table for performance. If a table does not have clustered index, when a new row inserting, SQL engine try to find a page to locate this row by using PFS pages. And this behavior might be a performance issue. On the other hand, if the table has clustered index, then SQL engine already knows where the new record might have been located. Because clustered index define the physical order of the rows.

[more]

The second reason might be “forwarded record” issue. Forwarded record is a Heap Table issue. To avoid this issue, we should create clustered index.

And also, heap tables are out of the index maintenance operation. Index fragmentation is really important to get better performance from queries. So third reason might be that.

Finally, if you decided to create clustered index you need a script to identify heap tables. You can use the below script for this purpose.

create table #HeapTables(			
	DBName sysname, 		
	Objectid int, 		
	SchemaName sysname, 		
	TableName sysname,		
	TableRowCount int,		
	NonClusterIndexCount int,		
	TableHasIdentity smallint,		
	IdentityColumnName sysName null		
	)		
			
exec sp_MSforeachdb 'use ?;			
	insert #HeapTables		
		select DB_NAME() as DBName,t.object_id,schema_name(t.schema_id) as SchemaName,t.name as TableName	
			,(select sum(row_count) from sys.dm_db_partition_stats where object_id=t.object_id and index_id in (0,1)) as TableRowCount 
			,(Select count(1) from sys.indexes i where i.object_id=t.object_id and index_id>1) as NonClusterIndexCount
			,objectproperty(t.object_id,''TableHasIdentity'')
			,(Select name from sys.columns c where c.object_id=t.object_id and is_identity=1) as IdentityColumn
		from sys.tables t	
		where t.type=''U'' and objectproperty(t.object_id,''TableHasClustIndex'')=0	
'			
			
select * from #HeapTables			
where DBNAME not in ('master','model','msdb','tempdb')			
order by DBName, SchemaName, TableName			
			
drop table #HeapTables			

Sample output might be like that;

image

You can take the last 2 columns as advice. Identity columns are good candidate to be clustered index key. So if you don’t have better option you can use this identity column as clustered index key.

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


Insert,Update,Delete işlemlerinde tablonun üzerinde bulunan her index’in update görme ihtimali vardır. Dolayısıyla tablo üzerinde ne kadar çok index var ise DML işlemlerinin performansı o kadar düşecektir.

Bugün sizler ile paylaşacağım script ile kolon sayısından daha fazla index’e sahip tabloları listeleyeceğiz.

[more]

Script’imiz şu şekilde

create table #tmp1 (DBName sysName, SchemaName sysName, ObjectName sysName, IndexCount int, ColumnCount int)

exec sp_msforeachdb 'use ?;
insert #tmp1
select ''?'' as DBName, SchemaName, ObjectName, IndexCount, count(*) as ColumnCount
from (
	select object_schema_name(object_id) as SchemaName
		,object_name(object_id) as ObjectName
		,count(*) as IndexCount		
		,object_id
	from sys.indexes i
	where ObjectProperty(object_id,''IsUserTable'')=1 and index_id>0 
	group by object_id
)xx	
join sys.columns c on c.object_id=xx.object_id
group by SchemaName, ObjectName, IndexCount
having IndexCount>count(*)
'

select * from #tmp1

drop table #tmp1

 

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

image

Örneğin ilk tabloda 2 kolon varken bu tablo 3 adet index’e sahip.

Yukarıdaki script’in sizin ortamlarınızda kaç sonuç döndürdüğünü merak ediyorum. Bu script’i kendi ortamınızda çalıştırıp çıkan sonucu yorum olarak paylaşır mısınız?

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


SQL Server’da 900 byte’ı geçen varchar kolonlar üzerine Index tanımlaması yapılabilmektedir. Fakat böyle bir index’e 900 byte’dan daha büyük bir kayıt insert’i ya da update’i yapıldığında hata alınacaktır. Bu yüzden Index Key toplamı 900 byte’dan büyük olan Index create etmemeye özen göstermekte fayda vardır.

[more]

Normal şartlarda char gibi sabit boyutlu bir key ya da key’ler üzerine tanımlanan index 900 byte’ı geçiyorsa tanımlama esnasında hata alır.

create table tbl_Char900Byte(a char(400), b char(400), c char(400))
GO
create clustered index IX_1 on tbl_Char900Byte(a,b,c)
GO

 

Msg 1944, Level 16, State 1, Line 1
Index 'IX_1' was not created. This index has a key length of at least 1200 bytes. The maximum permissible key length is 900 bytes.

Aynı index varchar gibi değişken boyutlu key’ler üzerine tanımlanırsa sadece uyarı verilerek index create edilecektir.

create table tbl_VarChar900Byte(a varchar(400), b varchar(400), c varchar(400))
GO
create clustered index IX_1 on tbl_VarChar900Byte(a,b,c)
GO

 

Warning! The maximum key length is 900 bytes. The index 'IX_1' has maximum length of 1200 bytes. For some combination of large values, the insert/update operation will fail.

 

Giriş bölümünde de belirttiğim üzere SQL Server böyle bir index’in oluşturulmasına izin vermesine rağmen bu şekilde index’ler create etmemeye özen göstermeliyiz. Çünkü bu index’ler üzerine yapılacak 900 byte’dan büyük insert-update işlemleri hata alacaktır.

insert into tbl_VarChar900Byte(a,b,c)
	values (REPLICATE('A',400),REPLICATE('B',400),REPLICATE('C',400))

 

Msg 1946, Level 16, State 3, Line 1
Operation failed. The index entry of length 1200 bytes for the index 'IX_1' exceeds the maximum length of 900 bytes.

 

İyi Çalışmalar

Turgay Sahtiyan

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


SQL Server 2012’den önceki versiyonlarda varchar(max), nvarchar(max) ya da varbinary(max) tipinde kolon içeren index’lerde create ya da rebuild gibi online Index operasyonları yapılamıyordu. SQL Server 2012 ile beraber artık bu işlemleri yapabileceğiz.

[more]

SQL Server 2012’den önceki versiyonlarda bu işlemi yapmak istediğimizde şu şekilde bir hata almaktaydık.

create table tblOnlineIndex(a int,b varchar(max))
go
insert tblOnlineIndex
	select 1,'abc'
go
create clustered index IX_1 on tblOnlineIndex (a)
	with (Online=ON)
GO

 

Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'IX_1' because the index contains column 'b' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

SQL Server 2012 ile beraber bu işlemi hatasız gerçekleştirebiliyoruz. Yani SQL Server 2012, içeriğinde varchar(max), nvarchar(max) ya da varbinary(max) tipinde kolon içeren Index’lerde online operasyonları desteklemekte.

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


İstatistiklerin Query Plan oluşturulması aşamasında üstlendikleri rolü SQL Server’da İstatistik Kavramı adlı makalemde detaylı olarak incelemiştik. Güncel olmayan istatistiklerin yanlış Query Plan’lar oluşturulmasına dolayısıyla performans sıkıntısına sebebiyet verdiğini belirtmiştik. Bu yüzden Auto_Update_Statistics gibi istatistiklerin otomatik olarak güncellenmesini sağlayan parametrelerin aktif yapılmasının çoğu ortam için oldukça önemli olduğunun özellikle üzerinde durmuştuk.

İstatistikler güncellendikten sonra sorgular Recompile edilir. (Sorguların ReCompile edilme nedenlerine şu makaleden erişebilirsiniz.) Fakat eğer tablo update görmesine rağmen istatistiğin dağılımında çok fazla değişiklik olmuyorsa bu durumda boşu boşuna istatistiği update etmeye ve sorgunun ReCompile edilmesini tetiklemeye gerek yoktur.

Bu makalemde Index create edilirken verilen STATISTICS_NORECOMPUTE parametresi ile create edilen Index’e bağlı istatistiğin otomatik olarak update edilmesinin nasıl engellendiğini görüyor olacağız.

[more]

STATISTICS_NORECOMPUTE parametresinin varsayılan değeri OFF’dur. Yani Auto_Update_Statistics parametresi ON durumdaysa ilgili istatistiğe bağlı tabloda %20+500 kayıt update olduğunda istatistik update olacaktır.

Eğer Index create edilirken STATISTICS_NORECOMPUTE özelliği OFF yapılırsa istatistik otomatik olarak update olmayacaktır.

Şimdi bu durumu gerçekleştirmek için bir çalışma tablosu oluşturup bu tablo üzerinde birbirinin aynısı olan 2 Index create edeceğiz. Index’lerin ilkini STATISTICS_NORECOMPUTE=OFF olarak create ederken, ikincisini ON olarak create edeceğiz. Daha sonra tabloda belirli bir sayıdaki kaydı update edip istatistiklerin otomatik olarak update olup olmadığını gözlemleyeceğiz.

Üzerinde çalışacağımız çalışma tablosunu hazırlayalım.

Create Database DBTest
GO
ALTER DATABASE [DBTest] SET AUTO_UPDATE_STATISTICS ON
GO
Use DBTest
GO
Create Table tbl_Test (Col1 int identity(1,1), Col2 varchar(10))
GO
Insert tbl_Test
	select 'a'
GO 10000

 

2 index create edelim. İlkini STATISTICS_NORECOMPUTE=OFF ikincisini ON olarak create ediyoruz.

--İlk index STATISTICS_NORECOMPUTE=OFF 
Create NonClustered Index IX_1 on tbl_Test(Col2)
	WITH (STATISTICS_NORECOMPUTE=OFF)
GO

--İkinci index STATISTICS_NORECOMPUTE=ON 
Create NonClustered Index IX_2 on tbl_Test(Col2)
	WITH (STATISTICS_NORECOMPUTE=ON)
GO

 

İstatistiklerin şu anki son güncellenme tarihlerine bakalım.

--İstatistiklerin şu anki güncellenme tarihlerine bakalım
select name, STATS_DATE(object_id,stats_id) as last_update_date
from sys.stats s
where s.object_id = object_id('dbo.tbl_Test')

 

image

Şimdi tablodaki kayıtların %20+500’ünü update ederek istatistiklerin otomatik güncellenmesini sağlayalım.

--Tablonun %20+500 kaydını update edelim
update top (2500) tbl_Test set Col2='b'

--İstatistikleri kullanarak otomatik güncellenmelerini tetikleyelim.
select * from tbl_Test With (INDEX=IX_1) where Col2='x' 
GO
select * from tbl_Test With (INDEX=IX_2) where Col2='x' 
GO

 

İstatistiklerin güncellenme tarihlerine tekrar bakalım.

--İstatistiklerin güncellenme tarihine tekrar bakalım
select name, STATS_DATE(object_id,stats_id) as last_update_date
from sys.stats s
where s.object_id = object_id('dbo.tbl_Test')

 

image

Gördüğünüz gibi NORECOMPUTE özelliği OFF olan istatistik güncellenmişken ON olan istatistik güncellenmedi.

 

Tablodaki kayıtlar güncellenmesine rağmen istatistiğin dağılımında çok fazla değişiklik olmuyorsa STATISTICS_NORECOMPUTE özelliğini ON yapmayı düşünebilirsiniz. Bu şekilde istatistik boşu boşuna güncellenmeyecek dolayısıyla da sorgulara ReCompile olmayacaktır.

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


23.11.2011 tarihinde gerçekleştirmiş olduğum SQL Server Performans İpuçları başlıklık webcast’imin videosuna ve diğer dosyalarına aşağıda erişebilirsiniz.

 

 

 

Scriptler

 

Not : Webcast videosu yakın bir zamanda www.cozumpark.com portalinde de yayınlanacaktır.

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


23 Kasım Çarşamba günü yapacağım webcast’in duyurusunu yapmak istiyorum.

SQL Server Performance Tuning İpuçları başlıklı webcast’im 23.11.2011 tarihinde 21:00-22:00 saatleri arasında gerçekleşecek.

Aşağıdaki url’i kullanarak vereceğim bu webcast’e katılabilirsiniz.

https://www.livemeeting.com/cc/mvp/join?id=6N4JP9&role=attend&pw=2NM%7E%60JK%7Bt

Webcast süresince şu performans ipuçları üzerinde duracağız.

  1. FILLFACTOR – PAD_INDEX
  2. Filtered Index
  3. Indexed View
  4. Filtered Index vs Indexed View
  5. NC Index’lerde Included Kolon Kullanımı
  6. Index Seek : PT Bitti mi?
  7. Where Bloğunda Case Kullanımı
  8. Where Bloğunda Collate Kullanımı
  9. Eksik Index (Missing Index) Analizi
  10. Index Maintenance
  11. İstatistiğin Güncel Olmasının Önemi
  12. Optimize For Ad Hoc Workloads
  13. Instant File Initialization
  14. Veritabanı Dosya Büyümeleri

 

Not:Katılım url’i webcast başlamadan yarım saat önce aktif hale gelecektir.

 

İyi Çalışmalar

Turgay Sahtiyan

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


Where Bloğunda Case Kullanmayın makalemden sonra ikinci “kullanmayın” makalem ile karşınızdayım :) Bu makalemde where bloğunda kullanılan “Collate” anahtar kelimesinin performansa olan etkisini örnekler ile inceleyeceğiz.

[more]

Aslında problem sadece “Collate” anahtar kelimesi değil. Index’in tanımlı olduğu kolondaki veriyi manupüle eden her anahtar kelimede aynı sıkıntı mevcut. Bunun da nedeni, en basitinden, Index’in tanımlı olduğu kolonun manupule edildiğinde sıralamanın(order) değişme ihtimali. Böyle bir durumda yani artık sıralı olmayan bir Index Key’de seek işleminin yani arama işleminin sağlıklı olarak yapılamamasından kaynaklı olarak Scan yapılma zorunluluğudur. Tekrar ayrı bir paragrafta belirterek altını çizmek gerekirse;

Collate Index Scan yapılmasına neden olur

Şimdi “Collate” özelinde bir örnek yapalım. Bu örneğimde aynı sorguyu “Collate” anahtar kelimesi varken ve yokken çalıştırıp Query Plan’larını karşılaştıracağım.

İlk olarak çalışma tablomuzu oluşturalım.

--Calisma DB'si olustuyorum.
--Collation olarak Latin1_General_CI_AS kullanıyorum.
Create Database DBCollateDeneme COLLATE Latin1_General_CI_AS
GO
Use DBCollateDeneme
GO
--Calisma tablosu olusturuyorum
create table tblDeneme1(a varchar(37), b char(400))
GO
--Tabloya 10000 kayıt basiyorum
declare @i int=0
declare @a char(37)
while @i<10000 begin
  select @a=CAST(NEWID() as CHAR(37))
  insert tblDeneme1
    select @a,REPLICATE('b',400)    
  set @i=@i+1
end
--a kolonu uzerine Clustered Index tanimliyorum
Create Clustered Index CX_1 on tblDeneme1(a)

 

Şimdi 2 farklı sorguyu aynı ekranda çekip Query Planlarını karşılaştıralım.

--Sorgu1
select * from tblDeneme1 
where a ='19D7D1DA-867A-4903-9B22-88171CF9D451' 
--Sorgu2
select * from tblDeneme1 
where a ='19D7D1DA-867A-4903-9B22-88171CF9D451' COLLATE TURKISH_CI_AS

 

image

Resimde de gördüğünüz üzere Collate kullanılmazken Index Seek yapılarak getirilen sorgu Collate kullanılınca Index Scan yaparak getirilmiş durumda.

Tekrar etmek gerekirse, Collate Index Scan yapılmasına neden olmaktadır. Bu yüzden gerçekten gerekli olan yerlerde Collate kullanmaya özen göstermek gerekmektedir.

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


SQL Server’da Index’lere erişim açısından 2 yöntem kullanılmaktadır. Bunlar; Index Seek ve Index Scan yöntemleridir. Index Scan, Index’in Leaf Level page’lerinin tamamının ya da belli bir range’inin okunması anlamına gelirken, Index Seek, B-Tree üzerinden arama gerçekleştirerek ilgili kayıt ya da kayıtlara erişilme yöntemidir. Bu yüzden performans çalışması yapan bizler Index Scan’den ziyade Index Seek görmek isteriz.

Peki Index Seek yapıldığı zaman performans çalışmalarımız bitmiş mi oluyor? Daha yapacak başka bir şeyimiz kalmadı mı?

[more]

Çoğu kişi açısından Index Seek performans açısından erişilebilecek son noktadır. Üzerinde çalışılan Query Plan’da Index Seek yapıldığı görülürse ekstra bir çalışma yapma gerekliliği olmadığı düşünülür.

Gerçekten öyle mi?

Bu soruya bir örnek ile cevap vereceğim. Örneğimde kullandığım sorgu Clustered Index Seek yapmakta. Ama ben bunun üzerine gene de performance tuning çalışmalarına devam edeceğim.

İlk olarak bir çalışma tablosu oluşturuyorum.

--Calisma DB'si olusturuyorum
create database db_Deneme
GO

Use db_Deneme
GO

--Calisma tablosu olusturuyorum
--Ozellikle buyuk kolonlar seciyorum
create table tbl_Deneme(ID int, Ad varchar(50), WideCol1 varchar(4000), WideCol2 varchar(4000),
				WideCol3 varchar(4000), WideCol4 varchar(4000))
GO

--Tabloya 1000 kayit basiyorum
declare @i int=1
while @i<1000 begin
  insert tbl_deneme
	select @i,'Turgay '+CAST(@i as varchar(5)),REPLICATE('1',4000),REPLICATE('2',4000),
		REPLICATE('3',4000),REPLICATE('4',4000) 
  set @i=@i+1 
end 
GO 

--ID uzerine clustered index olustuyorum 
Create Clustered Index CI_1 on tbl_Deneme(ID) 
GO

 

Şimdi performance tuning yapacağım sorguya bakalım. Sorgum şu şekilde;

select Ad from tbl_Deneme where ID=5

 

Yukarıdaki sorgunun Query Plan’ına baktığımda Clustered Index Seek yapıldığını görüyorum. Daha öncede belirttiğim gibi çoğu DBA bu aşamada daha fazla yapılacak bir şey olmadığını düşünür ve Performance Tuning işlemlerini sonlandırır. Ama biz böyle yapmayacağız.

1

Sorguyu incelediğimde sadece Ad kolonunu result set olarak döndürdüğümü görüyorum. Ama Clustered Index Seek yaptığım için Leaf Level’a erişip page’i okuduğum anda 4000 byte’dan oluşan diğer 4 kolonu da boşu boşuna okuyorum.

Ayrıca bu şekilde büyük kolonlara sahip olduğum için Clustered Index’in NonLeaf Page sayısı da artmış durumda. Bu durum da ekstradan IO yapılmasına sebebiyet veriyor.

Tekrar başa dönersek, sadece Ad kolonunu okuduğum ve ID üzerinden arama gerçekleştirdiğim için bu 2 kolonu cover eden bir Index benim işimi görecektir. Bu amaçla şu şekilde bir Included NonClustered Index oluşturuyorum.

Create NonClustered Index IX_1 on tbl_Deneme(ID) 
	INCLUDE (AD)

 

Şimdi her 2 durumu yaptıkları IO açısından birbiri ile karşılaştıracağım. İlk sorgumda Clustered Index’e force edip, ikinci sorgu da Included NonClustered Index’in kullanılması için herhangi bir force hint’i kullanmayacağım.

SET STATISTICS IO ON
--Clustered Index Seek
select Ad 
from tbl_Deneme WITH (Index=1)
where ID=5
--Included NonClustered Index Seek
select Ad 
from tbl_Deneme 
where ID=5

 

IO karşılaştırmasına bakarsak;

2

Clustered Index Seek yapılınca 6 logical read yapılırken yeni tanımladığım Index kullanılınca 2 logical read yapılırak sorgu sonucu getirilmektedir.

Performance Tuning çalışmaları sırasında Index Seek adımı son geliştirme adımı olarak düşünülmemelidir. Bazı durumlarda ek Index’ler kullanılarak ekstra performans sağlanabilir.

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


Keskin bir ifade oldu farkındayım :) O yüzden cümlenin başına “çok zorda kalmadıkça diye” ekleyelim. Kullanmayın deme sebebim ise where bloğunda case kullanımı, istatistikler üzerinden estimated rows hesabı yapılırken yanlış hesaplamalar yapılmasına dolayısıyla da estimate edilecek kayıt sayısının doğru tahminlenememesi anlamına gelmektedir. Bu durumda da Query Plan sağlıklı bir şekilde oluşturulamayacak ve gereksiz IO yapılmasından dolayı performans sıkıntısı ortaya çıkacaktır. Bugünkü yazımda bahsettiğim bu konuyu örnekler ile görüyor olacağız.

[more]

Örneklerimde AdventureWork.Person.Address tablosunu kullanıp City kolonu üzerinden sorgular çekeceğim. Bu yüzden ilk olarak City kolonu üzerine bir NonClustered Index oluşturuyorum.

IF  not exists (select * from sys.indexes where object_id = OBJECT_ID('Person.Address') AND name = 'IX_1')
  create nonclustered index IX_City on Person.Address
	(City)

 

Person.Address tablosuna çekeceğim sorgularda City bilgisi olarak Bothell. Normal şartlarda Bothell için çekilen sorguların Query Planı şu şekilde olmakta.

1

Şimdi where bloğunda case anahtar kelimesi içeren şöyle bir sorgu yazıyorum.

declare @val1 varchar(10)=NULL
declare @val2 varchar(10)='Bothell'
select * from Person.Address 
	where City = (case when @val1 is null then @val2
					else @val1 end)

 

Sorguda da gördüğünüz gibi eğer @val1 değişkeni null değil ise city bilgisi olarak bu değişken, eğer null ise city bilgisi olarak @val2 değişkeni kullanılacak. Bu durumda yukarıdaki sorguyu çalıştırdığımda “Bothell” için çalıştırmış olacağım.

2

Bothell için normal şartlarda Index Seek yaptığını daha önce görmüştük. Oysaki bu sorguda case kullandığım için artık Clustered Index Scan yapılmakta.

Bunun nedeni ise Estimated Rows kısmında gördüğümüz istatistikler üzerinden hesaplanan “tahmini dönecek kayıt sayısı” bilgisinin normalden çok olmasıdır. Aşağıdaki resimde 2 sorgu için tahminlenen kayıt sayılarını görebilirsiniz.

3

Bu yüzden ilk sorgu için Index Seek yapılmasına karar verilirken, ikinci sorguda dönecek kayıt sayısı olması gerekenden fazla yani yanlış hesaplandığı için Clustered Index Scan yapılmasına karar veriliyor.

İşte bu sebepten dolayı sorguların where bloğunda case kullanımından olabildiğince kaçınmakta fayda olduğunu söyleyebilirim.

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


Index oluşturulurken kullanılan FILLFACTOR ve PAD_INDEX seçenekleri ile daha sonra yapılacak DML işlemleri için Index sayfalarında (Page) ne kadar boş yer bırakılacağı belirlenebilir. Özellikle çok hızlı bir şekilde Fragmante olan Index’ler için bu değerler üzerinde oynama yapılmasında fayda olabilir. Fakat bu değerlerde yapılacak değişikliklerin sayfa sayısını arttıracağını ve dolayısıyla yapılacak IO miktarını arttıracağını unutmamak gerekir.

[more]

Index sayfaları B-Tree yapısında organize olurlar. Bu yapıda en alt bulunan sayfalar Leaf Level Page’ler en alt seviyenin üstünde bulunan sayfalara ise NonLeaf Level Page’ler olarak bilinir. (Daha detaylı bilgi için şu makaleyi okuyabilirsiniz.)

FILLFACTOR ve PAD_INDEX ile Index oluşturulurken ya da ReBuild edilirken NonLeaf ve Leaf Level sayfalarda ne kadarlık boş yer bırakılacağı ayarlanabilir.

FILLFACTOR : Leaf Level Page’lerde yüzde olarak ne kadarlık boş yer bırakılacağını belirlemek için kullanılır. 1 ile 100 arasında integer değer atanabilir. Default değeri 0’dır. Yani sayfalar oluşturulurken maksimum kapasitede doldurulmaya çalışılır. 100 atanması durumunda 0 ile aynı işi yapar.

Tekrar belirtmek gerekirse FILLFACTOR değerinde yapılacak değişiklik sayfalarda bulunan kayıt sayısını azaltacağı için toplam sayfa sayısını arttıracaktır. Örneğin FILLFACTOR değeri 0 iken toplam 100 Leaf Level Page varsa düz bir mantıkla FILLFACTOR değeri 50 olduğunda Leaf Level Page sayısı 200’e çıkacaktır. Bu da yapılacak IO miktarını arttıracaktır. Bu konuya ilerleyen bölümlerde örnek vererek daha detaylı değineceğim.

PAD_INDEX : Leaf Level Page’ler için belirlenen FILLFACTOR değerinin NonLeaf Level Page’ler için de uygulanıp uygulanmayacağını belirlemek için kullanılır. PAD_INDEX ON durumda ise FILLFACTOR değeri NonLeaf Level Page’lerde de uygulanır. Dolayısıyla PAD_INDEX’in çalışabilmesi için FILLFACTOR değerinin set edilmesi gerekmektedir.

Index oluştururken FILLFACTOR ve PAD_INDEX parametrelerini TSQL’de şu şekilde kullanabiliriz.

Create Clustered Index CX_1 on tblDeneme2(a)
	with (FILLFACTOR = 50, PAD_INDEX  = ON)

 

Bu script ile oluşturulan Index’in Leaf Level Page’leri %50 oranında doldurulacaktır. Aynı zamanda PAD_INDEX = ON denildiği için %50 oranı NonLeaf Level Page’ler içinde kullanılacaktır.

Aynı işlemi SSMS kullanarak ta yapabiliriz.

1

Şimdi 2 farklı bakış açısıyla FILLFACTOR ve PAD_INDEX değerlerini değiştirmenin bize getirdiği avantaj ve dezavantajlara bakalım.

FILLFACTOR ve PAD_INDEX Index Fragmantasyonunu Etkiler

Giriş paragrafında da söylediğim gibi çok hızlı bir şekilde fragmante olan Index’lerde FILLFACTOR ve PAD_INDEX değerleri değiştirilerek Page’lerde boş yer bırakılabilir. Bu şekilde, daha sonra oluşacak Insert ve Update’ler için yer bırakılmış olur. Bu da Index’te oluşacak fragmantasyonu geciktirir.

Şimdi bu konu üzerine bir örnek yapalım. Örneğimde birbirinin aynı 2 tablo kullanacağım. Bu tablolar üzerine Clustered Index oluşturacağım. Index’leri oluştururken ilk Index’te %0 FILLFACTOR değerini ikincisinde ise %50 FILLFACTOR değerini kullanacağım. Daha sonra tabloya Insert’ler yapıp 2 Index’in fragmantasyon değerlerini karşılaştıracağım.

--Calisma DB'si olusturuyorum
Create Database DBIndexDeneme
GO
--Calisma tablosu olusturuyorum
Use DBIndexDeneme
GO
--Karsilastirmada kullanacagim ayni ozellikle 2 tablo olusturuyorum
create table tblDeneme1(a char(37), b char(400))
GO
Create table tblDeneme2(a char(37), b char(400))
GO
--Tablolara 10000 kayýt insert ediyorum.
declare @i int=0
declare @a char(37)
while @i<10000 begin
  select @a=CAST(NEWID() as CHAR(37))
  insert tblDeneme1
    select @a,REPLICATE('b',400)    
  insert tblDeneme2
    select @a,REPLICATE('b',400)
  set @i=@i+1
end
--Clustered Index'leri olusturuyorum
--Ilk tablo icin FILLFACTOR degerini default yani 0 olarak birakiyorum
Create Clustered Index CX_1 on tblDeneme1(a)
--Ikinci tablo icin FILLFACTOR degerini 50 olarak set ediyorum
Create Clustered Index CX_1 on tblDeneme2(a)
	with (FILLFACTOR = 50, PAD_INDEX  = ON)

 

Çalışma tablolarım hazır. Şimdi bu tablolara 1000’er adet kayıt daha insert edeceğim. Kayıtlar aralara denk gelebileceği için fragmantasyon oluşacaktır. İşleme başlamadan önce şu anki fragmantasyon değerlerine bakıyorum.

select object_name(ps.object_id) as [tableName], 
	ps.index_id,
	i.name as indexName,
	ps.avg_fragmentation_in_percent, 
	ps.fragment_count,
	ps.avg_fragment_size_in_pages, 
	ps.page_count, 
	ps.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) ps
join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id
where object_name(ps.object_id) in ('tblDeneme1','tblDeneme2')

 

2

Şu an için her 2 index’te de fragmantasyon bulunmamakta. Şimdi 1000’er adet insert’i her 2 tabloya yapalım.

declare @i int=0
declare @a char(37)
while @i<1000 begin
  select @a=CAST(NEWID() as CHAR(37))
  insert tblDeneme1
    select @a,REPLICATE('b',400)    
  insert tblDeneme2
    select @a,REPLICATE('b',400)
  set @i=@i+1
end

 

Fragmantasyon değerlerine tekrar bakalım.

select object_name(ps.object_id) as [tableName], 
	ps.index_id,
	i.name as indexName,
	ps.avg_fragmentation_in_percent, 
	ps.fragment_count,
	ps.avg_fragment_size_in_pages, 
	ps.page_count, 
	ps.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) ps
join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id
where object_name(ps.object_id) in ('tblDeneme1','tblDeneme2')

 

3

Gördüğünüz gibi FILLFACTOR değeri %50 olarak ayarlanan Index’te hiç fragmantasyon oluşmamışken diğer Index’te %88 oranında fragmantasyon oluşmuş durumda.

Dolayısıyla tekrar etmek gerekirse; çok hızlı bir şekilde fragmante olan tablolarda FILLFACTOR değeri set edilerek fragmantasyon geciktirilebilir.

Yukarıdaki örnekte fragmante olan Index’i aşağıdaki script vasıtasıyla defragmante edebiliriz.

Alter Index CX_1 on tblDeneme1 Rebuild

 

FILLFACTOR ve PAD_INDEX Özelliklerinin IO’ya Etkisi

Daha öncede bahsettiğim gibi FILLFACTOR değerinin değiştirilmesi sayfada tutulacak kayıt sayısını etkilediği için bu durum da otomatikman toplam sayfa sayısını etkileyecektir. Dolayısıyla sorgular sonucunda okunacak sayfa sayısı da artabilir. Bu da daha fazla IO yapılması anlamına gelmektedir.

Şimdi bu aradaki farkı örneklendirmeye çalışalım. Çalışma tablosu olarak bir önceki başlıkta anlattığım tabloları kullanacağım.

İlk olarak 2 Index arasındaki sayfa sayısı farkına bakalım.

select schema_name(o.schema_id) as SchemaName,
         o.name as TableName,
         ps.index_id,
         i.name as indexName,
         fill_factor,
         is_padded,
         ps.used_page_count as LeafLevelPageCount,
         (ps.in_row_data_page_count*8.0) as LeafLevel_KB,
         ps.used_page_count-in_row_data_page_count as NonLeafLevelPageCount,
         ((ps.used_page_count-ps.in_row_data_page_count)*8.0) as NonLeafLevelSize_KB
from sys.dm_db_partition_stats ps
join sys.objects o on o.object_id = ps.object_id
join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id
where o.name in ('tblDeneme1','tblDeneme2')
order by o.name

 

4

Gördüğünüz gibi FILLFACTOR değerini %50 olarak set ettiğimiz Index, diğer Index’e oranla hem Leaf hem de NonLeaf Level Page sayısı olarak neredeyse 2 katı page’e sahip.

Şimdi aşağıdaki gibi bir sorguyu 2 tabloda birden çalıştırıp yaptıkları IO miktarına ve sorgu masrafına bakalım.

SET STATISTICS IO ON
Select * from tblDeneme1 
	where a between 'C' and 'G'
Select * from tblDeneme2 
	where a between 'C' and 'G'

 

5

Aynı sorgu tblDeneme1 tablosundan 158 IO yapılarak getirilebiliyorken, tblDeneme2 tablosundan 257 IO yapılarak getirilebildi. Bu da neredeyse 2 katı performans kaybı anlamına gelmekte.

Son olarak Query Planlarına bakalım.

6

2 Query Planda da Index Seek yapılmasına rağmen IO farklılığından dolayı ilk sorgu %38 maliyete sahipken 2.sorgu %62 maliyete sahip.

ÖZET

FILLFACTOR ve PAD_INDEX parametrelerini set etmek çok hızlı fragmante olan tablolar için bir avantaj gibi gözükebilir. Fakat sayfa sayısı artacağı için IO açısından dezavantaj doğurabilir. Sorgu ve tablo bazında inceleme yapılıp karar verilmesinde fayda vardır.

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


Son 2 makalemde Filtered Index ve Indexed View konularına değindim. Bazı senaryolarda kullanım noktaları kesişen bu özelliklerin aralarındaki benzerlikler ve farklar hangi çözümün kullanılması gerektiği noktasında belirleyici olabilir.

[more]

Filtered Index ve Indexed Views arasındaki farkları karşılaştırmalı olarak bir tabloya doldurmayı uygun gördüm. Bu şekilde hızlı bir şekilde 2 özellik arasındaki farklar karşılaştırabilir.

 

Filtered Index

Indexed View

Bir ya da birden fazla kolon için oluşturabilir.

Bir ya da birden fazla kolon için oluşturabilir.

Bir tablo üzerine oluşturulabilir.

Birden fazla tabloyu içerecek şekilde oluşturulabilir.

SQL Server’ın bütün sürümlerinde kullanılabilir.

SQL Server’ın bütün sürümlerinde oluşturulabilir ve sorgularda view kullanılarak index kullanılabilir. Sorguda view kullanılmadan index’in kullanılabilmesi için Enterprise sürümü gerekir.

Unique olmayan Filtered Index oluşturulabilir.

Indexed View’ler Unique olmak zorundadır.

Tüm sistemler için idealdir.

Genelde OLTP sistemlerinde kullanılması tavsiye edilmez. OLAP sistemler için daha uygundur.

Filtre olarak sadece çok basit (IS IS NOT = <> != > >= !> < <= !<)) operatörler kullanılabilir.

Filtre olarak herhangi bir sınırlama yoktur. Kompleks filtreleme yapılabilir.

Online olarak Rebuild yapılabilir

Online olarak Rebuild yapılamaz.

2 özellik arasındaki farkları biliyor olmak senaryoya göre hangi özelliğin kullanılması gerektiği sorusuna cevap bulma aşaması için önemlidir.

 

İyi çalışmalar

Turgay Sahtiyan

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


Bu makalemde, özellikle DWH sistemlerde bulunan, bol join ya da hesaplama içeren, çok fazla kayıt döndüren sorgularda ciddi performans artışı sağlayan Indexed View’leri inceliyor olacağız.

[more]

Indexed View Nedir?

Klasik View’leri hepimiz biliyoruz. Bu View’lerde dönecek kayıt seti herhangi bir şekilde veritabanında tutulmaz ve View her çalıştığında ilgili tablolardan getirilir. View’den eğer çok fazla kayıt dönüyorsa ve ayrıca bu kayıtlarda join ve hesaplamla işlemleri bolca yapılıyorsa çoğu durumda sonucun gelmesi oldukça fazla zaman alabilir.

Yukarıda ki örneğe benzer View’lerde performans artışı için View üzerine Unique Clustered Index tanımlanabilir. Index tanımlandığında artık View’den dönecek kayıt seti veritabanında saklanıyor olacaktır. Dolayısıyla karışık sorgularda her defasında hesaplama yapılmayacak, kayıtlar veritabanında saklandığı yerden hızlı bir şekilde getirilecektir.

View’lere Index tanımlamanın bir diğer artısı, sorguda ilgili view kullanılmasa bile eğer sorgunun tamamı ya da bir kısmı view tarafından karşılanıyorsa Index’in kullanılıyor olmasıdır. Yani view üzerine tanımlanan Index’in kullanılması için View’in sorguda kullanılması şart değildir.

Indexed view SQL Server’ın tüm versiyonlarında bulunmaktadır. Fakat bir önceki paragrafta bahsettiğim özellik, yani View kullanılmadan Index’in kullanılması özelliği sadece Enterprise versiyonunda bulunmaktadır.

Hangi Sistemler İçin Uygundur? OLTP - OLAP

View üzerine Index tanımlandığında dönecek kayıt seti veritabanında saklandığı için, çok fazla update ya da insert gören tablolardan oluşan View’lere Index tanımlanması pek mantıklı değildir. Çünkü tablolarda oluşacak DML işlemleri view’lerde de yapılmak durumunda kalınacaktır. Bu da DML işlemlerinin performansını etkileyecektir.

OLAP sistemleri Indexed View kullanımı için çok iyi bir alandır. Olap sistemlerinde tablolar belirli bir zamanda (her gece beslenen DWH sistemleri gibi) DML işlemi görür ve View’ler çok fazla sayıda sorgulanır. O yüzden bu tarz sistemlerde Indexed View kullanımı mantıklı olmaktadır.

Ayrıca veritabanları beslemesinden önce Indexed View’ler kapatılabilir ve DML işlemleri tamamlandıktan sonra Indexed View’ler tekrar oluşturabilir. Bu şekilde veri beslemesi aşamasında da performans sıkıntısı ortadan kaldırabilir.

Indexed View Oluşturmanın Ön Şartları

Bir View üzerine Clustered Index tanımlamadan önce bazı ön şartların sağlanmış olması gerekmektedir. Bu ön şartlardan bazıları aşağıdaki gibidir:

  • İlgili View’in içinde başka bir View kullanılamaz.
  • View’in içinde kullanılan tüm tablolar View ile aynı veritabanında ve aynı schema sahibine ait olmak zorundadır.
  • View SchemaBinding opsiyonu ile oluşturulmak zorundadır.
  • View içinde kullanılan UDF (User Defined Functions) ‘larda SchemaBinding opsiyonu ile oluşturulmuş olmalıdır.
  • View içinde kullanılan tablo ve UDF’ler SchemaName+ObjectName şeklinde kullanılmalıdır. Örneğin Person.Address gibi.
  • View’de hesaplama fonksiyonu kullanıldı ise select kısmında COUNT_BIG(*) kullanılması zorunludur.
  • Ayrıca select kısmı için aşağıdaki engeller mevcuttur.
    • Select * şeklinde bir kullanıma izin verilmez. Kolon adları belirtilmek zorundadır.
    • Aynı kolon adı 1 den fazla kullanılamaz.(“Select col1 as a, col1 as b from tbl1” gibi)
    • CTE (Common Table Expression) kullanımına izin verilmez.
    • TOP ve Order By kullanılamaz.
    • Count kullanılamaz. Count yerine Count_Big kullanılmalıdır.

Ön şartların tamamına aşağıdaki BOL dokümanından erişebilirsiniz.

http://msdn.microsoft.com/en-us/library/ms191432.aspx

Indexed View’in Performansa Etkisi

Indexed View’ın performansa olan etkisini analiz etmek aşağıdaki örnek sorguyu kullanacağım.

SELECT TOP 5 ProductID, 
	Sum(UnitPrice*OrderQty) as SumUnitPrice,
	Sum(UnitPrice*OrderQty*UnitPriceDiscount) AS SumDiscountPrice
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY SumDiscountPrice DESC

 

Bu sorgu için bir Indexed View oluşturacağız. Daha sonra Indexed View’den önceki performansı ile karşılaştıracağız.

İlk olarak Indexed view oluşturmadan yukarıdaki sorgunun Query Planını inceleyelim.

1

Where bloğu kullanmadığım için Clustered Index Scan yapıldı. Ayrıca hesaplama fonksiyonu (sum) kullandığım için Hash Match yapıldı ki bu işlemin bana maliyeti %35.

Şimdi bu sorgu için bir View oluşturup bu View içinde bir Unique Clustered Index oluşturuyorum.

IF OBJECT_ID ('Sales.vSales', 'view') IS NOT NULL
	DROP VIEW Sales.vSales ;
GO

CREATE VIEW Sales.vSales
	WITH SCHEMABINDING -- Indexed View tanımlamak için şart
AS	
	SELECT ProductID,
	    Sum(UnitPrice*OrderQty) as SumUnitPrice,
		Sum(UnitPrice*OrderQty*UnitPriceDiscount) AS SumDiscountPrice,
		COUNT_BIG(*) as TotalSales --Group By kullanıldığında mecburi
	FROM Sales.SalesOrderDetail 
	GROUP BY ProductID
GO
--View üzerine Index tanımlıyoruz.
CREATE UNIQUE CLUSTERED INDEX VI_1
	ON Sales.vSales (ProductId)
GO

 

Şimdi 2 sorguyu beraber çalıştıracağım. IO değerlerini görmek için SET STATISTICS IO ON ile IO istatistiğini de aktif hale getiriyorum. Ayrıca ilk sorgumda View’in Index’i değil de Clustered Index kullanılması için Clustered Index’i force ediyorum.

SET STATISTICS IO ON
GO

--Clustered Index kullanılacak.
SELECT TOP 5 ProductID, 
	Sum(UnitPrice*OrderQty) as SumUnitPrice,
	Sum(UnitPrice*OrderQty*UnitPriceDiscount) AS SumDiscountPrice
FROM Sales.SalesOrderDetail WITH(INDEX(0))
GROUP BY ProductID
ORDER BY SumDiscountPrice DESC

--Indexed View kullanılacak.
SELECT TOP 5 ProductID, 
	Sum(UnitPrice*OrderQty) as SumUnitPrice,
	Sum(UnitPrice*OrderQty*UnitPriceDiscount) AS SumDiscountPrice
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY SumDiscountPrice DESC

 

İlk olarak Query Planlara bakalım.

2

İlk dikkatinizi çekmek istediğim nokta yukarıdaki sorgularda View’in adını kullanmıyor olmama rağmen 2.sorguda View üzerine tanımladığım Index otomatik olarak kullanıldı. Giriş bölümünde bahsettiğim durum yani Enterprise versiyonun bir özelliği bu.

Şimdi sorguların masraflarına bakalım. Indexed View tanımlanmadan önceki durum tanımlandıktan sonraki duruma oranla 99 kat daha performansız. Bunun nedeni Indexed View kullanarak hem daha az IO yapmış olmam hem de Group By’dan kaynaklanan Hash Match’ten kurtulmuş olmam.

Peki IO değerlerimiz nasıl?

3

İlk sorgum 1240 IO yaparken 2. Sorgum sadece 4 IO yaparak işlemi tamamlamış oldu.

Indexed View’lerde normal View’lerden farklı olarak dönecek kayıt setleri veritabanında saklanır dedik. Normal bir Clustered Index’in page’lerine bakar gibi Indexed View’in Clustered Index Page’lerine bakabilirim.

--Index'in page'leri
DBCC IND('AdventureWorks','Sales.vSales',1)

 

4

NonLeaf Level Page örneği

DBCC Page('AdventureWorks',1,8207,3)

 

5

Leaf Level Page örneği

DBCC TRACEON(3604)
GO
DBCC Page('AdventureWorks',1,8204,3)

 

6

Sonuç

Indexed View’lerde klasik View’lerden farklı olarak dönecek kayıt setleri veritabanında saklanırlar. Bu özelliklerinden dolayı çok fazla hit alan ve bol join ya da hesaplama içeren View’lerde Index kullanmak performans açısından oldukça avantaj sağlamaktadır. Tabloda yapılacak DML işlemleri ilgili View’i de etkileyeceğinden dolayı OLTP sistemlerden ziyade OLAP sistemlerinde kullanılmaları daha mantıklıdır.

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


turgay sahtiyan , 27. Ekim 2011, 08:00

Filtered Index, SQL Server 2008 ile gelen, optimize edilmiş bir nonclustered index’tir. Oluşturulurken kullanılan where anahtar kelimesi sayesinde index key’in bütün verilerini değil sadece alt kümesini içerir. İyi tanımlanmış bir Filtered Index performansı arttırabilir, bakım maliyetini düşürür, aynı zamanda filtered olduğu için diskte daha az yer kaplar.

Filtered Index’in sağladığı avantajları detaylı olarak analiz etmek gerekirse;

  • Filtered Index Sorgu Performansını Arttırır : Filtered Index normal Index’e oranla daha az data içerdiği için bu Index üzerinden yapılacak seek/scan işlemleri normal Index üzerinden yapılacak seek/scan işlemlerine oranla daha performanslı çalışacaktır. Ayrıca Filtered Index için otomatik olarak oluşacak olan istatistik te veriyi daha iyi temsil edeceğinden estimated rows daha iyi tahmin edilecek, dolayısıyla da Index’e erişim metodu daha iyi belirlenebilecektir.
  • Index Bakım Maliyetlerini Düşürür : Index fragmante olduğu zaman bakım yapılarak bu fragmantasyonun giderilmesi gerekir. (Detaylı bilgi için şu makaleyi inceleyebilirsiniz.) Index fragmantasyonu tabloda DML işlemleri olduğu zaman oluşur. Filtered Index normal NonClustered Index’e oranla daha az veri içerdiği için fragmante olma ihtimali daha azdır. Ayrıca fragmante olsa dahi bakım işlemi sırasında daha az data ile uğraşılacağı için bakım maliyeti her halukarda normal index’e oranla daha az olacaktır.
  • Disk Maliyetini Düşürür : Daha önceki maddelerde de belirttiğim gibi Filtered Index datanın tamamını içermediği için normal Index’e oranla daha az yer kaplar.

Teorik kısmı burada kesip Filtered Index’i kafamızda daha iyi canlandırmak amacıyla canlı bir örnek verelim. Örneğin tbl1 isimli tablonuzda bulunan col1 adlı kolona index tanımlaması yapıyorsunuz. Tablo toplamda 1000 kayıttan oluşuyor. Fakat bu 1000 kaydın 700’ü için col1 kolonunda NULL bilgisi bulunuyor. Siz Col1 üzerinden yaptığınız bütün aramalarda null olmayan bir değer ile arama yapıyorsunuz. Böyle bir senaryoda col1’in tüm değerleri için Index tanımlamak yerine NULL olmayan satırlar için Index tanımlamanız daha anlamlı olacaktır. Bu şekilde 1000 değerden oluşan bir Index yerine 300 değerden oluşan bir Index olacak, bunun neticesinde B-Tree küçülecek ve Index üzerinden yapılan aramalar daha performanslı olacaktır. Ayrıca Index bütün kayıtlar için değil sadece 300 kayıt için oluştulduğu için diskte daha az yer kaplayacak ve Index maliyeti de otomatikman düşecektir.

Şimdi yukarıda bahsettiğim örneği SQL Server’da gerçekleştirelim ve 2 Index arasındaki farklara bakalım.

Bunun için ilk olarak 1.000.000 satırdan oluşan bir tablo oluşturacağım ve 700.000 kaydın col1 kolonuna null değer atacağım. Kalan 300.000 kaydın col1 kolonunu NewID() fonksiyonundan gelen GUID değeri ile dolduracağım.

--Bir çalışma DB'si oluşturuyoruz.
create database DBFilterIndex
GO
use DBFilterIndex
GO
--Bir çalışma tablosu oluşturuyoruz.
create table tbl_FilterIndex (ID int Identity(1,1), col1 varchar(37))
GO
--col1 bilgisi NULL olan 700.000 kayıt insert ediyoruz.
insert tbl_FilterIndex
  select null
go 700000  
--col1 bilgisi NULL olmayan 300.000 kayıt insert ediyoruz.
insert tbl_FilterIndex
  select cast(NEWID() as varchar(37))
go 300000  

 

1.000.000 kayıttan oluşan çalışma tablomuz hazır. Şimdi Index’leri oluşturalım.

--ID kolonu üzerine Clustered Index oluşturuyoruz
create clustered index CIX on tbl_FilterIndex (ID)
GO
--col1 için normal bir NonClustered Index oluşturuyoruz
create nonclustered index IX_RegularIndex on tbl_FilterIndex (col1)
GO
--col1 için Filtered NonClustered Index oluşturuyoruz
create nonclustered index IX_FilterIndex on tbl_FilterIndex (col1)
	where col1 is not null
GO

 

Şimdi 2 Index’i birbiri ile karşılaştırabiliriz.

İlk olarak 2 index’in kayıt sayılarını ve boyutlarını karşılaştıralım.

SELECT i.index_id, i.name, i.type_desc, ps.reserved_page_count, ps.used_page_count, 
	ps.reserved_page_count*8 as Size_KB,
	ps.row_count, i.filter_definition 
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.object_id = OBJECT_ID('tbl_FilterIndex')

 

1

Resimde de gördüğünüz üzere normal NonClustered Index 1.000.000 kayıttan oluşurken Filtered Index’te null olan kayıtlar bulundurulmadığı için 300.000 kayıttan oluşmakta. Bu yüzden normal index 22 MB iken Filtered Index 15 MB boyutunda. Disk açısından maliyetin nasıl düşürüldüğünü görmüş olduk.

Şimdi bir de performans karşılaştırması yapalım. Bu karşılaştırma için aynı sorguyu 2 farklı index’i force ederek yapacağım. Aynı zamanda farkı daha net görebilmnek adına Index’i scan edecek bir kriter gireceğim.

SET STATISTICS IO ON
--Normal NonClustered Index'i force ederek
select * from tbl_FilterIndex WITH (INDEX=IX_RegularIndex)
where col1=NEWID()
--Filtered Index'i force ederek
select * from tbl_FilterIndex WITH (INDEX=IX_FilterIndex)
where col1=NEWID()

 

IO karşılaştırmasına baktığımızda normal Index sorguyu tamamlamak için 2749 IO yaparken Filtered Index aynı sorguyu 1879 IO yaparak getirebilmekte.

2

Query Plan’ları karşılaştırarak maliyetlere bakacak olursak;

3

Resimde de görüldüğü üzere normal Index’in maliyeti 4 kat daha fazla. Bu örneklerle de Filtered Index’in performansa olan etkisini görmüş olduk.

Filtered Index’te desteklenen özelliklere bakacak olursak;

  • Filtered Index’in kriteri değiştirilebilir.
  • Missing Index DMV’leri Filtered Index önerisi toplamaz.
  • Database Engine Tuning Advisor “not null” Filtered Index önerisi sunabilir.
  • Filtered Index, online Index operasyonunu destekler. Yani Filtered Index’ler online olarak Rebuild edilebilirler.
  • Table Hint’ler Filtered Index tarafından desteklenir.

SQL Server 2008 ile beraber gelen Filtered Index özelliği Performance Tuning çalışmaları yapılırken göz önünde bulundurulması gereken güzel özelliklerden biri. Filtered Index sayesinde verinin alt kümesi için Index tanımlaması yapılarak performans artışı sağlanabilir.

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


Daha önce SQL Server’da Index Kavramı adlı makalemde ve web seminerimde üzerinde durduğum konuyu bu makalede biraz daha detaylandırmak istiyorum.

[more]

SQL Server’da oluşturulan Clustered Index’te eğer Index’in key’i unique değil ise key’i unique’leştirmek için 4 byte’lık UNIQUIFIER bilgisi konulur.

UNIQUIFIER konulup konulmaması Index oluşturulurken yazılan UNIQUE anahtar kelimesine bağlıdır. Yani sizin Clustered Index key’inizi Identity gibi Unique bir ifade olsa dahi eğer Index’i oluştururken UNIQUE anahtar kelimesi kullanılmaz ise SQL Server gene bu key için 4 byte’lık UNIQUFIER bilgisi kullanacaktır. Bu da gereksiz yere her index satırı için 4 byte’lık yer kaybı anlamına gelmektedir.

Şimdi bu durumu bir örnekle görelim. 2 table oluşturacağız. Bu tablolardan ilkine Integer ID kolonu için Unique olmayan bir Clustered Index, ikincisine ise Unique bir Clustered Index oluşturacağız. Daha sonra bu 2 tablonun boyutlarını karşılaştıracağız.

Örneğimiz aşağıdaki gibi;

--1.Tablo
--Unique Clustered Index
if object_id('dbo.tblIndexDeneme1') is not null drop table tblIndexDeneme1
GO
create table tblIndexDeneme1 (ID int, Ad varchar(20), SoyAd varchar(20), Telefon varchar(10))
GO
declare @i int=0
while @i<100000
begin
  insert tblIndexDeneme1
    select @i,'Turgay'+cast(@i as varchar(10)),'Sahtiyan' + cast(@i as varchar(10)),'555 55 55'
  set @i=@i+1
end
GO
--ID kolonu üzerine clustered index
create clustered index IX_1 on tblIndexDeneme1 ( ID )
GO

--2.Tablo
--NonUnique Clustered Index
if object_id('dbo.tblIndexDeneme2') is not null drop table tblIndexDeneme2
GO
create table tblIndexDeneme2 (ID int, Ad varchar(20), SoyAd varchar(20), Telefon varchar(10))
GO
declare @i int=0
while @i<100000
begin
  insert tblIndexDeneme2
    select @i,'Turgay'+cast(@i as varchar(10)),'Sahtiyan' + cast(@i as varchar(10)),'555 55 55'
  set @i=@i+1
end
GO
--ID kolonu üzerine clustered index
create unique clustered index IX_1 on tblIndexDeneme2 ( ID )
GO

--Tablo ve Index'lerin boyutlarý
sp_spaceused 'tblIndexDeneme1'
GO
sp_spaceused 'tblIndexDeneme2'
GO

 

Tabloların boyut karşılaştırması sonucu aşağıdaki gibi olacaktır.

1

Gördüğünüz gibi Unique olmayan Clustered Index’e sahip tablonun boyutu Unique Clustered Index içeren tabloya oranla daha fazla. Bunun da nedeni Unique olmayan Clustered Index’te kaydı Unique’leştirmek için 4 byte’lık UNIQUIFIER kolonunun kullanılıyor olması.

Boyut farkının hem data hem de index_size’da olduğu gözükmekte. Çünkü UNIQUIFIER kolonu hem Leaf hem de NonLeaf Page’lerde bulunmakta. Şimdi bu durumu görelim.

NonUnique Clustered Index Leaf ve NonLeaf Level Page’ler (UNIQUIFIER kolonu hem NonLeaf hem Leaf Level Page’lerde bulunmakta)

2

3

Unique Clustered Index Leaf ve NonLeaf Level Page’ler (UNIQUIFIER kolonu Page’lerde bulunmamakta.)

4

5

Son söz olarak şunu söyleyelim. Eğer Index’iniz Unique bir kolon için oluşturuluyorsa Index’i oluştururken kesinlikle Unique ifadesini kullanın. Bu şekilde 4 byte’lık UNIQUIFIER kolonundan kurtulmuş olursunuz.

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


29.09.2011 tarihinde gerçekleştirmiş olduğum SQL Server’da Index Kavramı - Performance Tuning ve Query Optimization başlıklı webcast’imin videosu aşağıdadır.

[more]

 

 

Webcast’te kullandığım sunum dosyasına şu linkten erişebilirsiniz.

Kullandığım scriptler ise aşağıdadır.

--table scan-index kullanımı arasındaki farkın 
--diskten yapılan okuma açısından değerlendirilmesi
drop table tblIndexDeneme1
GO
create table tblIndexDeneme1 (ID int, Ad varchar(20), SoyAd varchar(20), Telefon varchar(10))
GO
declare @i int=1
while @i<100000
begin
  insert tblIndexDeneme1
    select @i,'Turgay'+cast(@i as varchar(10)),'Sahtiyan' + cast(@i as varchar(10)),'555 55 55'
  set @i=@i+1
end
GO

--Table scan yaparak kayıt sorgulama-Query Plan
select * from tblIndexDeneme1 where ID=55000

--Table scan yaparak kayıt sorgulama-IO Consume
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
select * from tblIndexDeneme1 where ID=55000

--ID kolonu üzerine clustered index
create clustered index IX_1 on tblIndexDeneme1 ( ID )
GO

--clustered index kullanılarak kayıt sorgulama-IO Consume
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
select * from tblIndexDeneme1 where ID=55000
--230 kat daha az kayıt okundu.

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

-----------------------------------------------------------------
------------- Index Page'leri Görüntüleme -----------------------
-----------------------------------------------------------------
--
select * from tblIndexDeneme1 where ID=55000

--Index Page'leri
DBCC IND('AdventureWorks2008','tblIndexDeneme1',1)

--Data Page için Trace Flag
DBCC TRACEON (3604);

--Page içeriği
DBCC PAGE('AdventureWorks2008',1,39362,3)


-----------------------------------------------------------------
-------------Index Page'lere ilk Bakış---------------------------
-----------------------------------------------------------------
--Clustered Index NonLeaf Level Page Örneği
DBCC IND('AdventureWorks2008','tblIndexDeneme1',1)

DBCC TRACEON (3604);
DBCC PAGE('AdventureWorks2008',1,24080,3)

--NonClustered Index NonLeaf Level Page Örneği
--İlk olarak Ad kolonu üzerinde bir NonClustered Index tanımlaması yapalım.
--Soyad kolonunuda included olarak belirleyelim.
create nonclustered index IX_2 on tblIndexDeneme1 ( Ad ) INCLUDE(Soyad)
GO

DBCC IND('AdventureWorks2008','tblIndexDeneme1',3)

DBCC PAGE('AdventureWorks2008',1,40603,3)



------------------------------------------------------
-------Örneklerle Index Page’lerin İncelenmesi--------
------------------------------------------------------

----------------------------------------
--Clustered Index’te Page’lerin Yapısı--
----------------------------------------

--Unique Clustered Index
drop table tblIndexDeneme2
GO
create table tblIndexDeneme2 (ID int, Ad varchar(20), SoyAd varchar(20), Telefon varchar(10))
GO
declare @i int=1
while @i<10000
begin
  insert tblIndexDeneme2
    select @i,'Turgay'+cast(@i as varchar(10)),'Sahtiyan' + cast(@i as varchar(10)),'555 55 55'
  set @i=@i+1
end
GO
create unique clustered index IX_1 on tblIndexDeneme2 ( ID )
GO

DBCC IND('AdventureWorks2008','tblIndexDeneme2',1)

DBCC TRACEON (3604);
DBCC PAGE('AdventureWorks2008',1,40482,3)

--Unique Olmayan Clustered Index
drop table tblIndexDeneme2
GO
create table tblIndexDeneme2 (ID int, Ad varchar(20), SoyAd varchar(20), Telefon varchar(10))
GO
declare @i int=1
while @i<10000
begin
  insert tblIndexDeneme2
    select cast(@i/10 as int),'Turgay'+cast(@i as varchar(10)),'Sahtiyan' + cast(@i as varchar(10)),'555 55 55'
  set @i=@i+1
end
GO
create clustered index IX_1 on tblIndexDeneme2 ( ID )
GO

DBCC IND('AdventureWorks2008','tblIndexDeneme2',1)

DBCC TRACEON (3604);
DBCC PAGE('AdventureWorks2008',1,40625,3)


-------------------------------------------
--NonClustered Index’te Page’lerin Yapısı--
-------------------------------------------

--Heap Tablo + Unique NonClustered Index
drop table tblIndexDeneme2
GO
create table tblIndexDeneme2 (ID int, Ad varchar(20), SoyAd varchar(20), Telefon varchar(10))
GO
declare @i int=1
while @i<10000
begin
  insert tblIndexDeneme2
    select cast(@i/10 as int),'Turgay'+cast(@i as varchar(10)),'Sahtiyan' + cast(@i as varchar(10)),'555 55 55'
  set @i=@i+1
end
GO
create unique nonclustered index IX_2 on tblIndexDeneme2 ( Ad )
GO

DBCC IND('AdventureWorks2008','tblIndexDeneme2',2)

DBCC TRACEON (3604);
DBCC PAGE('AdventureWorks2008',1,40786,3)

--Unique Clustered Index + Unique NonClustered Index
drop table tblIndexDeneme2
GO
create table tblIndexDeneme2 (ID int, Ad varchar(20), SoyAd varchar(20), Telefon varchar(10))
GO
declare @i int=1
while @i<10000
begin
  insert tblIndexDeneme2
    select @i,'Turgay'+cast(@i as varchar(10)),'Sahtiyan' + cast(@i as varchar(10)),'555 55 55'
  set @i=@i+1
end
GO
create unique clustered index IX_1 on tblIndexDeneme2 ( ID )
GO
create unique nonclustered index IX_2 on tblIndexDeneme2 ( Ad )
GO

DBCC IND('AdventureWorks2008','tblIndexDeneme2',2)

DBCC TRACEON (3604);
DBCC PAGE('AdventureWorks2008',1,23099,3)

--Unique Olmayan Clustered Index + Unique NonClustered Index
drop table tblIndexDeneme2
GO
create table tblIndexDeneme2 (ID int, Ad varchar(20), SoyAd varchar(20), Telefon varchar(10))
GO
declare @i int=1
while @i<10000
begin
  insert tblIndexDeneme2
    select cast(@i/10 as int),'Turgay'+cast(@i as varchar(10)),'Sahtiyan' + cast(@i as varchar(10)),'555 55 55'
  set @i=@i+1
end
GO
create clustered index IX_1 on tblIndexDeneme2 ( ID )
GO
create unique nonclustered index IX_2 on tblIndexDeneme2 ( Ad )
GO

DBCC IND('AdventureWorks2008','tblIndexDeneme2',2)

DBCC TRACEON (3604);
DBCC PAGE('AdventureWorks2008',1,41236,3)

--Unique Olmayan Clustered Index + Unique Olmayan NonClustered Index
drop table tblIndexDeneme2
GO
create table tblIndexDeneme2 (ID int, Ad varchar(20), SoyAd varchar(20), Telefon varchar(10))
GO
declare @i int=1
while @i<10000
begin
  insert tblIndexDeneme2
    select cast(@i/10 as int),'Turgay'+cast(@i as varchar(10)),'Sahtiyan' + cast(@i as varchar(10)),'555 55 55'
  set @i=@i+1
end
GO
create clustered index IX_1 on tblIndexDeneme2 ( ID )
GO
create nonclustered index IX_2 on tblIndexDeneme2 ( Ad )
GO

DBCC IND('AdventureWorks2008','tblIndexDeneme2',2)

DBCC TRACEON (3604);
DBCC PAGE('AdventureWorks2008',1,41504,3)


------------------------------------------------------
----NonClustered Index’te Included Kolon Kullanımı----
------------------------------------------------------

--AdventureWorks Person.Address tablosunun kayıt sayısını arttırmak.
declare @i int=1
while @i<5 begin
  insert Person.Address (AddressLine1, AddressLine2,City,StateProvinceID,PostalCode)
    select AddressLine1 + CHAR(@i), isnull(AddressLine2,'') + CHAR(@i)
		,City,StateProvinceID,PostalCode
    from Person.Address
  set @i=@i+1
end

drop index IX_City on Person.Address
create nonclustered index IX_City on Person.Address
	(City)

--Çekmek istediğimiz sorgu. Query Plan'a bakalım.
--City üzerinde NonClustered Index tanımlı
select AddressLine1, PostalCode
from Person.Address
where City='Bothell'

--Hızlandırmak için 1.seçenek 
--City, AddressLine1, PostalCode üzerine composite index tanımlamak
drop index IX_1 on Person.Address

create nonclustered index IX_1 on Person.Address
	(City,AddressLine1,PostalCode) 

--Index tanımladıktan sonra Query Plan'a tekrar bakalım.
select AddressLine1, PostalCode
from Person.Address
where City='Bothell'

--Composite index'in boyutu
--25.210 MB
select b.name,b.index_id,SUM(a.reserved_page_count)*8/1024. as Size_MB
from sys.dm_db_partition_stats a inner join sys.indexes b
on a.object_id=b.object_id and a.index_id=b.index_id
where OBJECT_ID('Person.Address')=a.object_id
	and b.name = 'IX_1'
group by b.name,b.index_id	

--Şimdi composite index yerine included index oluşturalım.
drop index IX_1 on Person.Address
GO
create nonclustered index IX_1 on Person.Address
	(City) INCLUDE (AddressLine1,PostalCode)

--Query Plan
select AddressLine1, PostalCode
from Person.Address
where City='Bothell'

--Bu composite index'in boyutu
--25.02 MB
select b.name,b.index_id,SUM(a.reserved_page_count)*8/1024. as Size_MB
from sys.dm_db_partition_stats a inner join sys.indexes b
on a.object_id=b.object_id and a.index_id=b.index_id
where OBJECT_ID('Person.Address')=a.object_id
	and b.name = 'IX_1'
group by b.name,b.index_id	

--Aradaki boyut farkının sebebi included kolonların 
--non-leaf page'lerde bulunmamasıdır.

DBCC IND('AdventureWorks2008','person.address',7)

DBCC TRACEON (3604);
DBCC PAGE('AdventureWorks2008',1,45274,3)
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan