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
<<  Ekim 2017  >>
PaSaÇaPeCuCuPa
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345
Keywords

29.04.2013 Pazartesi günü yapacağım webcast’in duyurusunu yapmak istiyorum.

SQL Server 2012 Upgrade ve Migration metodları üzerine konuşacağımız webcast saat 14:00’da başlayacak.

Özellikle halihazırda kullandığı SQL Server veritabanlarını SQL Server 2012’ye taşımak isteyenlerin katılmasını önerdiğim bu webcast’e aşağıdaki linki kullanarak kayıt olabilirsiniz.

https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543843&Culture=TR-TR

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 Istatistik Kavramı adlı makalemde İstatistikler hakkında detaylı bir şekilde konuşmuştuk. Kısaca özet geçmek gerekirse; İstatistikler, Query Optimizier tarafından sorgu planı oluşturulurken daha performanslı bir plan oluşturmak için kullanılan, tablodaki verinin dağılımını gösteren istatistik bütünüdür. İstatistikler sayesinde, sorgu planı (Query Plan) oluşturulurken sorgudaki where bloğunda kullanılan kolon için dönecek tahmini kayıt sayısı bulunur. Bulunan bu değer index’e erişim şeklini belirler. Temel amaç, datayı en hızlı ve en az maliyetli şekilde kullanıcının karşısına getirmektir.

[more]

Gene aynı makalede Auto_Update_Statistics database özelliğinin ne şekilde çalıştığından ve basic threshold değerinin kayıt sayısının %20+500 olduğundan bahsetmiştik. Yani örneğin 10.000 kayıttan oluşan bir tablomuz var ise ve bu tablodaki Col1 kolonu üzerine istatistik tanımlandıysa, Col1 kolonunu etkileyecek 2.500 güncelleme işleminden sonra istatistik ilk kullanılmak istendiği anda (örneğin select işleminde) otomatik olarak güncellenecektir.

İstatistikler doğru query plan oluşturulması için çok önemli olduğu için istatistiklerin olması kadar güncel olması da çok önem taşımaktadır. Bu yüzden best practice olarak Auto_Update_Statistics database özelliğinin açık olması önerilir. (Sharepoint gibi bazı database’lerde kapalı olması önerilmektedir) Fakat %20’lik threshold değeri oldukça fazla olduğundan çoğu durumda bu threshold değeri yakalanamamakta ve tablo çok fazla update görmesine rağmen istatistik update olmamaktadır. Örneğin 100.000.000 kayıttan oluşan bir hareket tablosundaki istatistiğin bu özellik sayesinde otomatik olarak update görmesi için yaklaşık 20.000.000 kaydın update olması gerekiyor ki reel hayatta böyle bir şey neredeyse mümkün değil.

İşte bu noktada karşımıza SQL Server 2008 R2 SP1 gelen 2371 Trace Flag’i çıkıyor. Bu trace flag aktif edildiğinde tablodaki kayıt sayısı arttıkça %20’lik threshold değeri daha erişilebilir seviyelere iniyor.

image

Kaynak : http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

Yukarıdaki grafiği incelediğimizde tablodaki kayıt sayısı 25.000’ken %20’lik threshold geçerliyken, kayıt sayısı arttıkça %20’lik threshold aşağılara inmekte. Örneğin 100.000 kayıt sayısı için %10’lu değerlere, 1.000.000 kayıt için %3.2 gibi bir değere inmekte ki bu değerler artık yakalanabilir seviyelere gelmiş olmaktadır.

Özellikle çok büyük tablolarınız var ise (Örneğin SAP uygulaması, http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx) ve out of dated istatistiklerden dolayı performans sıkıntısı çektiğinizi düşünüyorsanız bu trace flag’i aktif hale getirmeyi düşünebilirsiniz.

Son olarak bir örnek yaparak trace flag’in davranışı görelim ve yazımıza son verelim.

--Bir calisma DB’si create ediyoruz
create database StatsDeneme
GO
use StatsDeneme
GO
--Auto_Create_Statistics ve Auto_Update_Statistics parametrelerini on yapiyoruz
alter database StatsDeneme set Auto_Create_Statistics ON
alter database StatsDeneme set Auto_Update_Statistics ON
--Bir calisma tablosu create ediyoruz
if OBJECT_ID('StatsDeneme','U') is not null
  drop table StatsDeneme
create table StatsDeneme(col1 int, col2 varchar(10), col3 varchar(5))
GO
--Calisma tablosuna rasgele 100.000 veri basiyoruz.
declare @i int=0
while @i<100000 begin
  insert StatsDeneme select @i,CAST(@i as varchar(10)),'a' + cast((@i % 10) as varchar(6))
  set @i=@i+1
end

--Istatistik olusturmak icin select sorgumuzu cekiyoruz
--Auto_Create_Statistics database ozelligi on oldugu icin
--	asagidaki sorgu sonucu col3 kolonu icin istatistik olusacaktir
select * from StatsDeneme where col3='a1'

--Istatistigin son guncellenme zamanina bakiyoruz
select object_name(object_id),name as StatsName,STATS_DATE(object_id,stats_id) 
from sys.stats
where object_name(object_id) = 'StatsDeneme'

--Trace Flag'i aktif etmeden 10.000 kaydi update ediyoruz (%10)
update top(10000) StatsDeneme set Col3 = 'xx' 

--Auto_Update_Statistics ozelligini tetiklemek icin select sorgusunu tekrar cekiyoruz
select * from StatsDeneme where col3='a1'

--Istatistigin son guncellenme zamanina tekrar bakiyoruz ve 
--	daha %20+500'luk threshold dolmadigi icin update olmadigini goruyoruz
select object_name(object_id),name as StatsName,STATS_DATE(object_id,stats_id) 
from sys.stats
where object_name(object_id) = 'StatsDeneme'

--10.500 kaydi daha update ediyoruz ve threshold'u yakaliyoruz (%20%500)
update top(10500) StatsDeneme set Col3 = 'xx' 

--Auto_Update_Statistics ozelligini tetiklemek icin select sorgusunu tekrar cekiyoruz
select * from StatsDeneme where col3='a1'

--Istatistigin son guncellenme zamanina tekrar bakiyoruz ve istatistigin update oldugunu goruyoruz
select object_name(object_id),name as StatsName,STATS_DATE(object_id,stats_id) 
from sys.stats
where object_name(object_id) = 'StatsDeneme'

--Dolayisiyla 2371 trace flag'ini kullanmadan threshold degerimiz %20+500
--Simdi ayni islemleri 2371 trace flag'ini aktif ederek tekrar yapalim

--Trace Flag'i aktif ediyoruz 
DBCC TraceOn (2371)

--Not:Trace flag'i bu sekilde aktif etmektense trace flag startup parametrelerine konularak 
--	SQL Server her acildiginda trace flag otomatik olarak aktif ettirilebilir)

--10.000 kaydi update ediyoruz (%10)
update top(10000) StatsDeneme set Col3 = 'yy' 

--Auto_Update_Statistics ozelligini tetiklemek icin select sorgusunu tekrar cekiyoruz
select * from StatsDeneme where col3='a1'

--Istatistigin update oldugunu goruyoruz. 
--%10'luk kisim update olmasına ragmen istatistigin update olma nedeni 2371 trace flag'ini aktif etmis olmamiz
select object_name(object_id),name as StatsName,STATS_DATE(object_id,stats_id) 
from sys.stats
where object_name(object_id) = 'StatsDeneme'

--calisma database'ini drop ediyoruz
use master
go
drop database StatsDeneme
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