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
<<  Ağustos 2017  >>
PaSaÇaPeCuCuPa
31123456
78910111213
14151617181920
21222324252627
28293031123
45678910
Keywords

SQL Server 2011 “Denali” ile gelen bir diğer özellik, EXEC komutu ile çağırdığımız Stored Procedure’den dönen result set’in kolon adlarını ve data type’larını yani metadatasını değiştirmek için kullanabileceğimiz WITH RESULT SETS seçeneği.

[more]

Hemen bir örnekle ne demek istediğimi açıklamaya çalışayım.

Önce bir SP create edelim.

create procedure dbo.getPersonContacts @Title varchar(5)
AS
  select Title,FirstName,MiddleName,LastName 
  from Person.contact
  where Title = @Title

 

Daha sonra klasik yöntemle SP’yi çağıralım.

1

Yapmak istediğim dönen result set’in kolon adlarını ve data type’larını değiştirmek. Bunun için aşağıdaki syntax’ı kullanabilirim.

exec dbo.getPersonContacts 'Mr.'
WITH RESULT SETS
(
	(
		[myTitle] varchar(5) not null,
		[myFirstName] varchar(50) not null,
		[myMiddleName] varchar(50) null,
		[myLastName] varchar(50) not null
	)
)

 

2

Gördüğünüz gibi kolon adları bizim yazdığımız şekilde değişti.

Birden fazla result set içeren SP’lerde, her bir result set için bu tanımın verilmesi gerektiği notunu düşüp yazımı noktalıyorum.

 

İ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 2011 “Denali” ile gelen yeni özelliklerden biride, SQL Server Configuration Manager’da service in startup parameter’larını ayarlayabileceğimiz ekran. Bu ekran vasıtasıyla yeni startup parameter’ları eklemek, silmek, değiştirmek eski versiyonlara göre daha kolay hale getirilmiş.

[more]

Denali’den önceki versiyonlarda startup parametre ayarları SQL Server Configuration >> SQL Server Services >> SQL Service >> Advanced tab’ından yapılmaktaydı.

1

SQL Server 2011 “Denali” de ise startup parameter için ayrı bir tab bulunmaktadır.

2

Örneğin service’i single user mode’da açmak için Add butonunun yanındaki alana –m yazıyoruz ve sonra Add’e basıyoruz. Daha sonra service’i restart ettiğimizde yeni service single user mode’da açılacaktır.

3

Az önce tanımladığımız parametreyi kaldırmak için existing parameters listesinde –m parametresini seçiyoruz ve remove’a basıyoruz. Tabiki değişikliğin geçerli olması için service’i tekrar restart etmemiz gerekmekte.

4

 

İ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 11 “Denali” ile gelen yeni özelliklerden biride Server Role create edebilmek. Bildiğiniz gibi SQL Server 2008 ve önceki sürümlerde olan server role’lerinde bir değişiklik yapamıyor yeni server role’leri create edemiyorduk. Denali ile beraber artık kendi server role’lerimizi create edebiliyoruz.

[more]

Konuyu daha iyi anlamak açısından Server Role’leri ile alakalı daha önce yazdığım aşağıdaki yazımı okumanızı tavsiye ederim.

http://www.turgaysahtiyan.com/post/SQL-Server-da-Kullanc4b1cc4b1-Yaratma-ve-Yetkilendirme.aspx

 

Giriş paragrafında da belirttiğim gibi Denali ile birlikte artık kendi server role’lerimizi create edebiliriz.

Örneğin TSQL ile bir server role create edip, bazı yetkilendirmeler yapalım. Son olarakta bir login’i bu server role’e dahil edelim.

USE [master]
GO
--Server Role'ü create et.
CREATE SERVER ROLE [myServerRole] AUTHORIZATION [sa]
GO
--DBUser login'ini bu role'e dahil et
ALTER SERVER ROLE [myServerRole] ADD MEMBER [DBUser1]
GO
--Server Side Permission
GRANT ALTER ANY DATABASE TO [myServerRole]
GO
--Server Side Permission
GRANT CREATE ANY DATABASE TO [myServerRole]
GO
--Server Side Permission
GRANT VIEW ANY DATABASE TO [myServerRole]
GO
--Server Side Permission
GRANT VIEW ANY DEFINITION TO [myServerRole]
GO

 

Aynı işlemi SSMS ile yapmak istersek;

1.Security >> Server Roles Sağ tık ile New Server Role yazısını tıklayalım.

1

2.İstediğimiz permission’ları verelim.

2

3.Login’leri Add butonu vasıtasıyla role’e ekleyelim.

3

4.Oluşturduğumuz bu role’ü istersek standart server role’lerinden birinin üyesi yapıp, bu server role’ün yetkilerinden faydalandırabiliriz.

4

5.Son olarak create işleminden sonra Server Role listesi aşağıdaki gibi gözükecektir.

5

Gördüğünüz gibi standart server role’leri ile user defined server role’lerini yanlarındaki iconlar vasıtasıyla ayırmak oldukça kolay.

 

İ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 2011 “Denali” kurulumunun SQL Server 2008 kurulumundan pek fazla farklı yanı olmadığını söyleyebilirim. Bugünkü yazımda full 2011 Denali kurulumunu anlatmaktansa 2008 den farklı yanlarını anlatacağım.

[more]

SQL Server 2011 “Denali” .Net Framework 4.0 kullanmakta. Bu kurulum setup ile beraber gerçekleştirilmekte. Ama sisteminizde .Net Framework 3.5 yok ise bunu setup tan önce kurmalısınız çünkü setup ta 3.5 bulunmamakta.

Aynı şekilde Powershell olarak 2.0 versiyonu gerekmekte, bu da setup ta bulunmamakta. Aşağıdaki adresten Powershell 2.0 kurulumunu gerçekleştirebilirsiniz.

http://support.microsoft.com/kb/968930/en-us

Kurulum dokümanlarında bir diğer dikkatimi çeken nokta ise 2011 Denali ile beraber artık Itanium desteği kaldırılmış durumda. Bunuda notlarımız arasına almakta fayda var.

Diğer hardware ve software gereksinimlerini aşağıdaki adresten detaylı bir şekilde inceleyebilirsiniz.

http://msdn.microsoft.com/en-us/library/ms143506%28v=SQL.110%29.aspx

 

2011 Denali kurulumunu 2008 kurulumu ile karşılaştırdığımda 2 farklı ekran dikkatimi çekti.

Setup Support Rules’un hemen arkasından gelen Setup Role ekranında kurulumun tipini seçebilmekteyiz.

1

Feature Selection ekranında ise artık üzerinde bulunduğumuz feature’ın açıklamalarını ve öngereksinimlerini ekranın sağındaki bölümlerde görebilmekteyiz.

2

 

Bunun haricinde kurulum SQL Server 2008 kurulumuna çok benzemekte. Dolayısıyla daha önce yayınladığım SQL Server 2008 kurulum videosu, 2011 Denali kurulumunuzda işinize yarayacaktır. Aşağıdaki adresten bu video’ya erişebilirsiniz.

http://www.turgaysahtiyan.com/post/SQL-Server-2008-Kurulumu-e28093-Video-Anlatc4b1m-%28Standalone-Installation%29.aspx

 

3

Bundan sonraki 2011 Denali yazılarımda yeni gelen özellikleri adım adım incelemeye çalışacağım.

 

İ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 – DBCC FREEPROCCACHE ile Plan Cache’i Temizlemek başlıklı yazımda bütün query plan’ların plan cache’den nasıl silinebileceğini incelemiştik. Bugünkü yazımda ise plan cache’den sadece ad hoc query’ler için hazırlanmış planların nasıl silineceğine bakıyor olacağız.

[more]

Ad hoc – Procedure farkına aşağıdaki yazımda değinmiştim. Okumadıysanız okumanızı tavsiye ederim.

http://www.turgaysahtiyan.com/post/SQL-Server-e2809coptimize-for-ad-hoc-workloadse2809d-Parametresi-ile-Memorye28099i-Daha-Randc4b1manlc4b1-Kullanmak.aspx

Şimdi gelelim bugünkü konumuza. Giriş paragrafında söylediğim gibi Plan cache’den sadece Ad Hoc Query’ler için oluşturulmuş query planları silmek, procedure gibi parameterize yapıya sahip query plan’ları ise silmek istemiyorum.

Peki ama neden böyle bir şey istiyorum da bütün plan cache’i DBCC FREEPROCCACHE ile temizlemiyorum. Çünkü procedure ler için oluşturulmuş query plan’lar çok büyük ihtimal birden fazla kere kullanılıyordur. Bizim asıl derdimiz Ad hoc query’ler. Çünkü bu query’ler için oluşturulmuş plan’ların bir çoğu maalesef sadece bir kez kullanılmakta ve memory de boşuna yer işgal etmekte.

Bizde periyodik olarak plan cache’i kontrol edip ad hoc query’ler için oluşturulmuş query plan’ları silmek isteyebiliriz.

Bu işlem için aşağıdaki komutu kullanacağız.

DBCC FREESYSTEMCACHE('SQL Plans')

 

Hemen bir örnekle komutu test edelim.

Örneğimizde AdventureWorks’teki bir sp’yi 3 kez, birde Ad Hoc Query’i farklı where statement ile 2 kez çalıştıracağım.

--Her satır ayrı ayrı çalıştıralacaktır.
use AdventureWorks
--SP yi 3 kez çalıştır
exec dbo.uspGetEmployeeManagers 11;
exec dbo.uspGetEmployeeManagers 11; 
exec dbo.uspGetEmployeeManagers 11; 
--Ad Hoc sorguyu farklı parametre ile 2 kez çalıştır.
select * from Person.Address where City='Bothell';
select * from Person.Address where City='Portland';

 

Şimdi cache’lenen query plan’ları sorgulayalım.

select st.text,* 
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%select * from Person.Address%'
or st.text like '%uspGetEmployeeManagers%')
and st.text not like '%select st.text%'

 

1(1)

Bizim amacımız sadece adhoc olanları silmek. Bunun için aşağıdaki komutu uygulayalım.

DBCC FREESYSTEMCACHE('SQL Plans')

 

Şimdi tekrar cache’lenen query plan’ları sorgulayalım.

1(2)

Bingo. Gördüğünüz gibi sadece AdHoc query plan’ları silindi. Ne güzel :)

 

İ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


Daha önceki bir çok yazımda da belirttiğim gibi, SQL Server çalıştırdığı query’ler için oluşturduğu query planları daha sonra kullanmak için Plan Cache’de yani memory de saklar. Daha sonraki kullanımlarda tekrar query plan oluşturmakla zaman kaybetmez, daha önce plan cache’e kaydettiği plan’ı kullanır ve buda performans artışı olarak bize geri döner.

Hem procedureler gibi parameterize edilebilen sorgular için hemde ad hoc gibi yani parameterize edilemeyen sorgular için query plan’lar oluşturulur ve plan cache’de saklanır. Procedure’ler için oluşturulmuş query plan’lar bize çok fazla problem çıkarmayacaktır. Çünkü bu planlar, sorguların parametrik olmalarından dolayı binlerce kez kullanılacaktır. Ama Ad Hoc sorgular içerdikleri parametreler ile cache’lenir ve aynı sorgu farklı bir parametre ile geldiğinde yeni bir plan cache oluşturulur. Dolayısıyla çok büyük ihtimalle ad hoc query’ler için oluşturulmuş query plan’lar sadece bir kez kullanılacaktır. Buda bize memory’de gereksiz yere yer işgali olarak geri dönecektir.

Bugünkü yazımda Ad Hoc için oluşturulan query plan’larının daha az yer kaplamasını nasıl sağlayacağımızı ve dolayısıyla memory’nin daha randımanlı nasıl kullanabileceğini görüyor olacağız.

[more]

Ufak bir örnekle Ad Hoc - Procedure kullanımının farkını açıklamaya çalışayım. Örneklerimde DBCC FREEPROCCACHE ve sys.dm_exec_cached_plans komutlarını kullanacağım için daha önce bu komutlar üzerine yazdığım makaleleri okumanızı tavsiye ederim.

Örneğimizde AdventureWorks’teki bir sp’yi 3 kez, birde Ad Hoc Query’i farklı where statement ile 2 kez çalıştıracağım.

--Her satır ayrı ayrı çalıştıralacaktır.
use AdventureWorks
--SP yi 3 kez çalıştır
exec dbo.uspGetEmployeeManagers 11;
exec dbo.uspGetEmployeeManagers 11; 
exec dbo.uspGetEmployeeManagers 11; 
--Ad Hoc sorguyu farklı parametre ile 2 kez çalıştır.
select * from Person.Address where City='Bothell';
select * from Person.Address where City='Portland';

 

Şimdi cache’len query plan’larını sorgulayalım.

select st.text,* 
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%uspGetEmployeeManagers%'
or st.text like '%select * from Person.Address%')
and st.text not like '%select st.text%'

 

1(1)

Gördüğünüz gibi SP için bir query plan oluşturuldu ve 3 kez aynı plan kullanıldı. Ad Hoc query’ler için ise, her bir query için bir plan oluşturuldu.

İşte bu şekilde Ad Hoc query’lerin her biri için plan oluşturulmakta ve bir çoğu 1 den fazla kullanılmamakta. Bizim amacımız bu 1 kez kullanılan plan’ların cache’de kapladığı boyutu düşürerek memory’nin diğer objeler için kullanılmasını sağlamak.

Bu amacımıza erişmek için kullanacağımız parametre “optimize for ad hoc workloads” server parametresi. Önce bir örnek yapıp daha sonra bu parametrenin ne işe yaradığını açıklayalım.

Örneğimizde Person.Address tablosuna 5 değişik where statement’ı ile select çekeceğiz.

--Her satır ayrı ayrı çalıştıralacaktır.
use AdventureWorks
--Once Plan Cache'i Temizleyelim.
DBCC FREEPROCCACHE 
--Ad Hoc sorguyu farklı parametre ile 5 kez çalıştır.
select * from Person.Address where City='Bothell';
select * from Person.Address where City='Portland';
select * from Person.Address where City='Orlando';
select * from Person.Address where City='Montreal';
select * from Person.Address where City='Calgary';

 

Cache’lenmiş planlara bakalım.

2(1)

Beklediğimiz gibi 5 ad hoc sorgusuda ayrı ayrı cache’lendi. Asıl acı olan belki bir daha hiç kullanılmayacak bu plan’ların her birinin memory’de 24 KB yer kaplaması. Bu boyut gözünüze küçük gözükebilir “24 KB mı kurtaracak benim memory’i” diyebilirsiniz. Production ortamında bu gereksiz planların 1-2 GB’a kadar yer kapladığına gözüyle şahit olan biri olarak şunu söyleyebilirim ki, 24 KB diyip geçmemek lazım :)

Bir kez kullanılan bu ad hoc query’lerin daha az yer kaplaması için “optimize for ad hoc workloads” parametresini 1 yapalım.

sp_configure 'optimize for ad hoc workloads',1
reconfigure

 

Şimdi select cümlelerinin olduğu sorgu bloğunu tekrar çalıştıralım.

--Her satır ayrı ayrı çalıştıralacaktır.
use AdventureWorks
--Once Plan Cache'i Temizleyelim.
DBCC FREEPROCCACHE 
--Ad Hoc sorguyu farklı parametre ile 5 kez çalıştır.
select * from Person.Address where City='Bothell';
select * from Person.Address where City='Portland';
select * from Person.Address where City='Orlando';
select * from Person.Address where City='Montreal';
select * from Person.Address where City='Calgary';

 

Cache’lenen planlara bakalım.

3

Vay ki vay. Az önce 24 KB yer kaplamakta olan planlar şimdi sadece 320 byte yer kaplamakta. Yani nerdeyse 100’de 1. Süper dimi :)

Peki ama nasıl oluyor bu.

Orlando ve Montreal sorgularını 1 kez daha çalıştıralım.

select * from Person.Address where City='Orlando';
select * from Person.Address where City='Montreal';

 

4

Gördüğünüz gibi 2 defa çalıştırdığım sorguların planları “Compiled Plan” halini dönüşüp 24KB yer kaplar hale geldiler. Diğer planlarım ise “Compiled Plan Stub” tipindeler.

İşin özü şu. Bu parametre aktif hale getirildikten sonra çalıştırılan sorgular için oluşturulan query plan’ların sadece belli bir kısmı cache’lenmekte. Bu kısımda, bir sonraki aynı sorgunun çalıştırılmasında eşleştirebilmek için gerekli olan kısım. Bu kadar basit :)

Detaylı ve açıklayıcı bir örnek olduğunu düşünüyorum. Umarım faydalı bir yazı olmuştur.

Benim için best practice olan bu parametreyi sizinde sunucularınızda kullanmanızı tavsiye ederek yazımı burada sonlandırıyorum.

 

İ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 execute edilen procedure, trigger ya da adhoc query’ler için oluşturduğu Query Plan’larını saklar ve daha sonraki kullanımlarda bu query plan’ları kullanarak işlemin daha hızlı sonuçlanmasını sağlar.

Bu Query Planlar Plan Cache’de saklanır. Troubleshoot ya da bazı gereksinimlerden dolayı bu cache’lenmiş planları silme ihtiyacı duyabiliriz. Bugünkü yazımda plan cache’i nasıl temizleyebileceğimize bakıyor olacağız.

[more]

Bu işlem için DBCC FREEPROCCACHE komutunu kullanacağız. Örnek kullanımı aşağıdaki gibidir.

DBCC FREEPROCCACHE

 

Yukarıdaki komut bütün cache’lenmiş planları silmektedir. İstersek sadece bir plan’ını da silebiliriz. Bunun için Plan’ın plan_handle’ını ya da sql_handle’ını bilmemiz gerekmektedir.

DBCC FREEPROCCACHE(0x060007002A192C22B8C03E0E000000000000000000000000) 

 

Şimdi gelin bir örnekle yazdıklarımızı pekiştirelim.

İlk olarak cache’lenmiş planlar neymiş bir bakalım. Bunun için daha önce yazdığım bir makalemdeki script’i kullanacağım. Bu makalemi okumadıysanız eğer okumanızı tavsiye ederim. SQL Server – Her Gün 1 DMV - Gün 21 – sys.dm_exec_cached_plans

select DB_NAME(st.dbid) as DBName,
	   OBJECT_SCHEMA_NAME(st.objectid,st.dbid) as SchemaName,
	   OBJECT_NAME(st.objectid,st.dbid) as ObjectName,
	   st.text,
	   cp.plan_handle,
	   qp.query_plan,
	   cp.usecounts,
	   cp.size_in_bytes
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where st.dbid<>32767 --Resource DB'yi exclude ediyoruz.
  and DB_NAME(st.dbid) <> 'ReportServer$S10ENT'

 

1

Örneğin sadece ilk Plan’ı Cache’den silmek için aşağıdaki komutu kullanabiliriz.

DBCC FREEPROCCACHE (0x0500070076146E6CB820B50A000000000000000000000000) 

 

Ya da bütün cache’lenmiş planları silmek için;

DBCC FREEPROCCACHE

 

Bu işlemlerden sonra sorguyu tekrar çalıştırdığınızda plan cache’in tamamen temizlendiğini göreceğiz.

2

 

İ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 da kullanılan Heap, Clustered ya da NonClustered Index’ler insert,update,delete yapıldıkça otomatik olarak güncellenirler ve yapılan bu işlemler sonucunda fragmante olurlar. Bu da Index’in performansını olumsuz etkiler. Belirli aralıklarla fragmante olan bu indexleri bulup drop-create, ReOrganize ya da Rebuild işlemleriyle fragmante oranlarının düşürülmesi gerekmektedir. Bugünkü makalemde bu defragmentation işlemlerini inceliyor olacağız.

[more]

Makalemizdeki ana başlıklarımız şu şekilde olacak;

  1. Fragmentation oranlarının belirlenmesi – Rebuild – ReOrganize Kararı
  2. Reorganize Index
  3. Rebuild Index
  4. Defragmentation Script’i
  5. Özet
  6. Kaynaklar

http://www.turgaysahtiyan.com/post/SQL-Server-e28093-Her-Gun-1-DMV-Gun-12-sysdm_db_index_physical_stats.aspx adresindeki makalemde hangi Index’in ne kadar fragmante olduğunu nasıl sorgulayacağımızı incelemiştik. Bugün yapacağımız defragmentation işlemlerinde bu DMV’yi kullanacağımız için öncelikli olarak bu makaleyi okumanızı tavsiye ederim.

1. Fragmentation Oranlarının Belirlenmesi – Rebuild – ReOrganize Kararı

Defrag işlemini başlamadan önce ilk yapılması gereken işlem hangi Index’in ne kadar fragmante olduğunu bulmaktadır. Bulunan oranlara göre Index’in Rebuild ya da Reorganize edilmesine karar verilir. Her firmaya göre bu oranlar farklılık gösterebileceği gibi piyasada kabul görmüş oranlar ve alınacak aksiyon aşağıdaki gibidir.

Fragmante Oranı

İşlem

> 5% and < = 30%

ALTER INDEX REORGANIZE

> 30%

ALTER INDEX REBUILD WITH (ONLINE = ON)*

(*) Rebuild index operasyonu online ya da offline yapılabilmektedir. Reorganize ise her daim online olarak işletilir.

Index’lerin fragmentation oranlarını bulmak için aşağıdaki script’i kullanabiliriz.

Use AdventureWorks
GO
SELECT
	ps.object_id,
	i.name as IndexName,
	OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
	OBJECT_NAME (ps.object_id) as ObjectName,
	ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ps
INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0
ORDER BY avg_fragmentation_in_percent desc

 

Bu script’i AdventureWorks DB’sinde çalıştırdığımızda aşağıdaki gibi bir sonuç ortaya çıkacaktır.

1

Bu sorgu sonucunda örneğin Sales.Individual table’ındaki XMLPROPERTY_Individual_Demographics Index’in %99 oranında fragmante olduğunu görüyoruz. Fragmante oranı %30 dan fazla olduğu için bu index’i ReBuild edeceğiz.

2.Reorganize Index

Bir index’i reorganize etmek, clustered veya nonclustered Index’te bulunan leaf level page’lerin tekrardan fiziksel sıralamaya sokulması demektir. Bu da index üzerinde yapılan sorgulamaların daha performanslı çalışmasını sağlar. Reorganize işlemi sırasında eski page’ler kullanılır ve yeni hiç bir page allocate edilmez. Dolayısıyla reorganize yapmak için ekstra bir disk alanına ihtiyaç duyulmaz.

Örnek kullanımı aşağıdaki gibidir.

Use AdventureWorks
GO
ALTER INDEX [PXML_Individual_Demographics] 
	ON [Sales].[Individual] REORGANIZE

 

Reorganize minimum sistem kaynağı tükettiği ve online olarak yapılıp blocking lere sebep olmadığı için %30 dan az fragmante olmuş index’lerde kullanımı tercih edilir.

Eğer index %30 dan fazla fragmante olduysa daha iyi bir sonuç almak için Reorganize yerine Rebuild kullanılmalıdır.

3.Rebuild Index

Rebuild işlemi aslında index’i drop edip tekrar create etmektir. Dolayısıyla fragmante tamamiyle kaldırılır ve index fill factor değeri göz önünde tutularak index page’leri tekrar allocate edilir. Index row’ları birbirini takip eden page’lerin içine sırasıyla kayıt edilir. Bu da bir index sorgulamasında gerekli kayıdı getirmek için daha az page okunacağından dolayı performans artışı sağlar.

Örnek kullanımı aşağıdaki gibidir.

ALTER INDEX [PXML_Individual_Demographics] 
	ON [Sales].[Individual]  REBUILD   WITH (ONLINE = ON)

 

Tekrar bir index create i söz konusu olduğu için ekstra disk alanına ihtiyaç duymaktadır.

4. Defragmentation Script’i

Ben instance’larımda aşağıda yazmış olduğum SP’yi kullanmaktayım. Bu SP parametre olarak aldığı DB’de bulunan index’lerin fragmante oranlarını sorguluyor, çıkan sonuca göre fragmante oranı %30 dan fazla olanları Rebuild, az olanları ReOrganize ediyor. Sonuç olarakta kaç Index’i rebuild, kaç index’i Reorganize ettiği bilgisini dönüyor.

CREATE PROC [INDEX_MAINTENANCE] @DBName VARCHAR(100)
AS BEGIN
		SET NOCOUNT ON;
		DECLARE
			@OBJECT_ID INT,
			@INDEX_NAME sysname,
			@SCHEMA_NAME sysname,
			@OBJECT_NAME sysname,
			@AVG_FRAG float,
			@command varchar(8000),
			@RebuildCount int,
			@ReOrganizeCount int

		CREATE TABLE #tempIM (
			[ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
			[INDEX_NAME] sysname NULL,
			[OBJECT_ID] INT NULL,
			[SCHEMA_NAME] sysname NULL,
			[OBJECT_NAME] sysname NULL,
			[AVG_FRAG] float
		)		
		SELECT @RebuildCount=0,@ReOrganizeCount=0
		
		--Get Fragentation values
		SELECT @command=
			'Use ' + @DBName + '; 
			INSERT INTO #tempIM (OBJECT_ID, INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG)
			SELECT
				ps.object_id,
				i.name as IndexName,
				OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
				OBJECT_NAME (ps.object_id) as ObjectName,
				ps.avg_fragmentation_in_percent
			FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, ''LIMITED'') ps
			INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
			WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0
				and ps.database_id=DB_ID('''+@DBName+''')
			ORDER BY avg_fragmentation_in_percent desc
			'
		
		exec(@command)
		DECLARE c CURSOR FAST_FORWARD FOR
			SELECT OBJECT_ID,INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG
			FROM #tempIM
		OPEN c
		FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG
		WHILE @@FETCH_STATUS = 0
		BEGIN
			--Reorganize or Rebuild
			IF @AVG_FRAG>30 BEGIN
				SELECT @command = 'Use ' + @DBName + '; ALTER INDEX [' + @INDEX_NAME +'] ON [' 
								  + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REBUILD   WITH (ONLINE = ON  )';
				SET @RebuildCount = @RebuildCount+1
			END ELSE BEGIN
				SELECT @command = 'Use ' + @DBName + '; ALTER INDEX [' + @INDEX_NAME +'] ON [' 
								  + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REORGANIZE ';
				SET @ReOrganizeCount = @ReOrganizeCount+1
			END
								  
			BEGIN TRY 
				EXEC (@command);	 
			END TRY 
			BEGIN CATCH 
			END CATCH 
			
			FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG
		END
		CLOSE c
		DEALLOCATE c
		
		DROP TABLE #tempIM
		
		SELECT cast(@RebuildCount as varchar(5))+' index Rebuild,'+cast(@ReOrganizeCount as varchar(5))+' index Reorganize edilmistir.' as Result
END		

 

Bu SP’yi master yada DBA scriptlerinizi allocate ettiğiniz bir DB’ye create edip daha sonra şu şekilde çağırabilirsiniz.

exec master.dbo.INDEX_MAINTENANCE 'AdventureWorks'

 

Hatta her gece çalışacak bir job vasıtasıyla istediğiniz DB’leri ekleyerek otomatik Index Maintenance yapılmasını sağlayabilirsiniz.

5. Özet

Bugünkü makalemizde Index fragmentation oranlarını nasıl sorgulayacağımızı, bu oranlara göre Rebuild, Reorganize kararını, Rebuild ve ReOrganize ın nasıl işlediklerini gördük. Daha performanslı bir sistem için sizde periyodik olarak index fragmentation larınıza bakmalı ve ihtiyacı olan index’leri defrag etmelisiniz.

6.Kaynaklar

http://technet.microsoft.com/en-us/library/ms189858.aspx

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

 

İ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


Bugün size taze taze aldığım bir hata mesajından bahsetmek istiyorum.

Error 8623: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

[more]

Instance larımdan birinde yapılan aşağıdaki update sorgusu yukarıdaki hatayı vermekteydi.

update table1 
set column1 = 'H'
where column2 in (1,
2,
4,
5,
6,
7,
8,
9,
10,
....
....
....
)

 

In bloğu içinde 39 bin kayıt bulunmaktaydı. Yaptığım araştırmalar sonucunda IN içinde binlerce kayıt kullanıldığında aşağıdaki gibi hataların alınabileceğini gördüm.

Error 8623:The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

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

Bu durumda yapılması gereken en mantıklı işlem, IN içinde bulunan kayıtları bir temp tabloya almak ve daha sonra temp tabloyu ana tabloya join’lemektedir. Aşağıdaki gibi;

create table #temp1(Col1 int)

insert #temp1 values(1)
insert #temp1 values(2)
insert #temp1 values(3)
insert #temp1 values(4)

update table1
set column1 = '3'
from table1
join #temp1 t1 on t1.Col1=table1.column2

drop table #temp1

 

İ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 2011 – Code Named “Denali” ‘nin CTP1’i çıktı. Aşağıdaki adresten download edebilirsiniz.

http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx

Denali için books online >> http://msdn.microsoft.com/en-us/library/ms130214%28SQL.110%29.aspx

En kısa zamanda ilk incelemelerimi yapıp sizinle paylaşacağım.

Key Capabilities that SQL Server code-named "Denali" will deliver:

Mission Critical Platform

  • SQL Server AlwaysOn
  • Column-based query accelerator

IT and Developer Productivity

  • IT administration enhancements
  • Beyond relational enhancements
  • Unified developer experience, SQL Server Tools code-named “Juneau”

Pervasive Insight

  • Expand the use of BI to business users
  • Experience breakthrough performance with in-memory analytics, and drive alignment across the organization
  • Enterprise data integration management
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan