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

Bu yazımda Plan Cache’de bulunan Plan’ları ve Memory’de bulunan clean data page’leri hangi komutlar ile temizleyebileceğimize bakıyor olacağız.

[more]

DBCC FREEPROCCACHE

Plan Cache’in tamamını ya da belirli bir plan’ı silmek için kullanılır. Kullanım şekilleri aşağıdaki gibidir.

Belirli bir Plan’ı Plan Cache’den silmek için;

DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);
GO

 

Tüm Plan Cache’i temizlemek için;

DBCC FREEPROCCACHE
GO

 

Daha önce bu komut ile alakalı yazdığım şu detaylı makaleyi okuyabilirsiniz.

DBCC FREESYSTEMCACHE

Belirli bir grup cache’i silmek için kullanılabilir. Örneğin “SQL Plans” ilişkili cache’lerin silmek için;

DBCC FREESYSTEMCACHE(‘SQL Plans’)
GO

 

Daha önce bu komut ile alakalı yazdığım şu detaylı makaleyi okuyabilirsiniz.

DBCC DROPCLEANBUFFERS

Clean yani checkpoint konularak disk’e yazılmış bütün page’leri cache’den temizlemek için kullanılır.

DBCC DROPCLEANBUFFERS
GO

 

DBCC FLUSHPROCINDB

Sadece bir veritabanı ile alakalı clean data page’lerin silinmesi için kullanılabilir. Örneğin aşağıdaki kod ile memory’den AdventureWorks ile alakalı clean data page’ler silinebilir.

declare @dbid int=1
select @dbid=DB_ID('AdventureWorks')
DBCC FLUSHPROCINDB(@dbid)
GO

 

İ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 bulunan Auto Growth (Otomatik Büyüme) özelliği sayesinde veritabanı dosyaları dolduğunda sistem tarafından otomatik olarak büyütülmektedir. Bu büyüme oransal ya da boyutsal olarak daha önceden belirlenebilir. Aynı zamanda dosyaların en fazla hangi boyuta kadar büyüyebileceği de ayarlanabilir.

Auto Growth işlemi diskte yeni bir alan allocate edilmesinden dolayı kaynak tüketimi fazla olan bir operasyondur. Büyümenin boyutlarına göre bazı durumlarda bu büyüme işlemi 1-5 saniye arasında sürebilir. Büyüme tamamlanana kadar da ilgili dosyaya gelen okuma ve yazma istekleri bekletilecek bu da performans sıkıntısı olarak dönecektir.

[more]

Best practice olarak dosyaların büyümesinin Auto Growth ile değil de DBA’lerin kontrolü altında yapılması önerilir. Sistemin en az yoğun olduğu saatlerde dosyalar yeteri kadar büyütülürse sistemin yoğun olduğu zamanlarda büyüme ihtiyacı ortaya çıkmayacak dolayısıyla performans sıkıntısı oluşmayacaktır.

Her ne kadar büyümeler DBA’ler tarafından yapılsa da plansız büyümelere karşı yine de Auto Growth özelliğinin açık olması önerilir. Bu durumda siz uygun bir saatte büyüme yapsanız da veritabanında gerçekleşecek plansız bir data girişi sonucu dosya dolarsa auto growth özelliği bu durumu sıkıntısız aşmanızı sağlayacaktır.

Auto Growth konusunda SQLRAP’ın önerisi aşağıdaki gibidir.

“Always maintain sufficient free disk space on the SQL Server data and transaction log drives. Enable Autogrow for managing the unexpected growths only. Do not use the Autogrow option for the day-to-day growth operations. Expand the database files during the non-peak times and expand to sufficiently larger chunks.”

Ben aşağıdaki gibi bir job ile otomatik büyümeleri kontrol altında tutmaktayım. Job’ın çalışma mantığını şu şekilde özetleyebiliriz;

  • @FreeSpaceThresholdRate parametresi ile boş alan için bir eşik değeri belirlenir. Örneğin bu değer 0.7 ise; dosyadaki boş olan %70 in altında ise ilgili dosyada büyüme gerçekleştirilir.
  • Sunucuda bulunan bütün veritabanlarının bütün dosyaları bazı özellikler ile beraber bir temp tabloya yazılır. Bu özellikler aşağıdaki gibidir.
    • DBReadOnly : Veritabanının readonly olup olmadığıdır. Readonly olan veritabanlarının dosyaları threshold değerinin içinde kalsalar dahi büyütülmeyecektir. Çünkü bu veritabanlarına veri girişi olmadığı için büyüme ihtimalleri yoktur.
    • FileReadOnly : Dosyanın readonly olup olmadığıdır. Readonly olan dosyalar threshold değerinin içinde kalsalar dahi büyütülmeyecektir. Çünkü bu dosyalara veri girişi olmadığı için büyüme ihtimalleri yoktur.
    • GrowthType : Auto Growth şeklinin oransal mı yoksa boyutsal mı olduğunu gösterir.
    • Growth : Auto Growth’un boyutunu gösterir. Örneğin %10 büyüme olsun şeklinde set edilen parametre için bu kolonda 10 yazacaktır.
    • MaxSize_MB : Dosya boyutu için bir sınır verildiyse bu kolonda gözükür. -1 değeri herhangi bir sınır verilmediğini belirtir.
    • TotalSize_MB : Dosyanın şu andaki boyutudur.
    • SpaceUsed_MB : Dosyanın ne kadarının kullanıldığını gösterir.
    • FreeSpace_MB : Dosyanın boş alanını gösterir.
    • FreeSpaceThreshold_MB : @FreeSpaceThresholdRate parametresine göre dosyada bulunması gereken minimum boş alanı gösterir. Bu değerin FreeSpace_MB değerinden büyük olduğu dosyalarda büyüme gerçekleştirilecektir.
    • NextGrowthSize_MB : Bir sonraki büyümenin ne kadar olduğunu gösterir. File alter edilirken TotalSize_MB+ NextGrowthSize_MB değeri ile alter edilecektir.
  • Temp tablo doldurulduktan sonra bütün satırlar cursor ile dönülür ve FreeSpaceThreshold_MB değeri FreeSpace_MB’dan büyük olan dosyalar alter edilerek büyütülür.
  • Eğer dosya için maksimum büyüme değeri verildi ise(MaxSize_MB), bu değer TotalSize_MB+ NextGrowthSize_MB ile karşılaştırılır. Eğer maksimum değeri aşma durumu var ise büyüme yapılmaz.
Job’ın scripti ise şu şekilde;
declare @FreeSpaceThresholdRate float = 0.7

declare @DBName sysName, 
		@DBIsReadOnly int,
		@FileIsReadOnly int,
		@LogicalFileName sysName, 
		@TotalSize_MB int, 
		@NextGrowthSize_MB int,
		@sql varchar(max)=''

CREATE TABLE #tmptbl1(
	[DBName] [varchar](128),
	[DBIsReadOnly] [int] ,
	[fileid] [smallint] ,
	[LogicalFileName] [sysname] ,
	[FileNameWithURL] [varchar](260) ,
	[FileIsReadOnly] [int] ,
	[IsPrimaryFile] [int] ,
	[IsLogFile] [int] ,
	[GrowthType] [varchar](7) ,
	[Growth] [float] ,
	[MaxSize_MB] [float] ,
	[TotalSize_MB] [float] ,
	[SpaceUsed_MB] [float] ,
	[FreeSpace_MB] [float] ,
	[FreeSpaceThreshold_MB] [float] ,
	[NextGrowthSize_MB] [float] 
) ON [PRIMARY]

select @sql='use ?;
	insert #tmptbl1
	select DB_NAME() as DBName
		, (case DATABASEPROPERTYEX(DB_NAME(),''Updateability'') when ''READ_ONLY'' then 1 else 0 end) as DBIsReadOnly
		, f.fileid, f.name as LogicalFileName
		, f.filename as FileNameWithURL
		, FILEPROPERTY(f.name, ''IsReadOnly'') as FileIsReadOnly
		, FILEPROPERTY(f.name, ''IsPrimaryFile'') as IsPrimaryFile
		, FILEPROPERTY(f.name, ''IsLogFile'') as IsLogFile
		, (Case when f.growth<128 then ''Percent'' else ''MB'' end) as GrowthType
		, (Case when f.growth<128 then growth else Round(cast(f.growth*8 as float)/1024,2) end) as Growth
		, (Case f.maxsize when -1 then -1 else Round(cast(f.maxsize/1024 as float)*8,2) end) as MaxSize_MB
		, Round(cast(f.size*8 as float)/1024,2) as TotalSize_MB
		, Round(cast(FILEPROPERTY(f.name, ''SpaceUsed'')*8 as float)/1024,2) as SpaceUsed_MB
		, Round(cast((f.size - FILEPROPERTY(f.name, ''SpaceUsed''))*8 as float)/1024,2) as FreeSpace_MB
		, Round('+cast(@FreeSpaceThresholdRate as varchar(5))+'*(Case 
			when f.growth<128 then (growth/100.)*cast(f.size*8 as float)/1024
			else Round(cast(f.growth*8 as float)/1024,2) 
			end),2) as FreeSpaceThreshold_MB
		, Round((Case 
			when f.growth<128 then (growth/100.)*cast(f.size*8 as float)/1024
			else Round(cast(f.growth*8 as float)/1024,2) 
			end),2) as NextGrowthSize_MB
	from sys.sysfiles f
	order by f.fileid
'
exec sp_msforeachdb @sql

declare CursorX cursor for
select DBName, DBIsReadOnly, FileIsReadOnly, LogicalFileName, TotalSize_MB, NextGrowthSize_MB from #tmptbl1 
	where (DBIsReadOnly = 0) and (FileIsReadOnly = 0)
		and (FreeSpaceThreshold_MB>FreeSpace_MB)
		and (MaxSize_MB=-1 or (TotalSize_MB+NextGrowthSize_MB)<=MaxSize_MB)
open Cursorx
fetch from Cursorx into @DBName, @DBIsReadOnly, @FileIsReadOnly, @LogicalFileName, @TotalSize_MB, @NextGrowthSize_MB
while @@FETCH_STATUS=0
begin
    set @sql = 'USE [master];
		ALTER DATABASE ['+@DBName+'] MODIFY FILE ( NAME = N'''+@LogicalFileName+''', 
	         SIZE = '+cast((@TotalSize_MB+@NextGrowthSize_MB)*1024 as varchar(10))+'KB )'
    exec(@sql)
    fetch next from Cursorx into @DBName, @DBIsReadOnly, @FileIsReadOnly, @LogicalFileName, @TotalSize_MB, @NextGrowthSize_MB
end
close Cursorx
deallocate Cursorx

drop table #tmptbl1

 

Veritabanı dosyalarının otomatik büyüme işlemlerinin gün içinde yapılmaması performans amaçlı best practice’lerden biridir. Bu yüzden otomatik büyümeler kontrol altında tutulmamalı ve gün içinde büyüme ihtiyacı olmayacak şekilde az yoğun olan saatlerde bakımı yapılarak büyütülmelidir. Her ne kadar büyümeler bu şekilde kontrol altına alınsa da plansız büyümelerin problem yaratmaması için Auto Growth özelliği kullanılmaya devam edilmelidir.

 

İ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ünkü makalemde, heap tablolarda karşımıza çıkan Forwarded Record konusu üzerine konuşuyor olacağız. Alt başlıklarımız şu şekilde;

  • Forwarded Record Nedir?
  • Forwarded Record Nasıl Oluşur?
  • Forwarded Record Neden Oluşur? SQL Server’ın Bu Davranışının Nedeni Nedir?
    • NonClustered Index İçeren Heap Tablolarda Forwarded Record
    • NonClustered Index İçermeyen Heap Tablolarda Forwarded Record
  • Forwarded Record’un Performansa Etkisi Nedir?
  • Clustered Index İçeren Tablolarda Neden Forwarded Record Oluşmaz?
  • Hangi Tablolarımda Forwarded Record Var?
  • Tablolarımda Bulunan Forwarded Record’lardan Dolayı Performans Sıkıntısı Yaşıyor muyum?
  • Forwarded Record Nasıl Düzeltilir?
  • Sonuç

[more]

Forwarded Record Nedir?

Forwarded Record heap yani Clustered Index’e sahip olmayan tablolarda karşımıza çıkan bir problemdir. Problemdir diyorum çünkü bir tabloda Forwarded Record olması demek bu tablodaki NonClustered Index’ler üzerinden çekilen sorgularda fazladan IO yapılması bu da performans sıkıntısı anlamına gelmektedir.

Heap tabloda bulunan bir kolon update edildiğinde, kaydın hali hazırda bulunduğu page eğer bu update’i karşılayamayacak kadar doluysa, ilgili kayıt yeni bir page’e taşınır ve eskiden olduğu page’e bir pointer konulur.

Örneğin varchar(8000) büyüklüğünde kolona sahip bir row’unuz var şu anda bu kolonun içinde ‘turgay’ yazmakta. Bu row’un Page1 adlı page’in içinde olduğunu düşünelim. Biz bu kolonu 8000 byte’lık bir veri ile update ettiğimizde eğer Page1’de bu 8000 byte’lık veriyi allocate edecek kadar yer yok ise bu kayıt yeni bir page’e yazılacak ve update’den önce Page1’de bulunduğu yere de yeni page’i işaret edecek bir pointer konulacaktır.

Bu durumda yeni page’e taşınan kayıda Forwarded Record, eski bulunduğu yere forwarding-stub denilmektedir ve tekrar etmek gerekirse forwarding-stub orjinal kayda erişmeye yarayan pointer bilgisini tutmaktadır.

Forwarded Record Nasıl Oluşur?

Şimdi gelin bir Forwarded Record oluşturma örneği yapalım.

Bunun için ilk olarak örnek bir tablo create edip içine 2 adet kayıt basıyorum.

if object_id('ForwardedRecord', 'U') is not null
	drop table ForwardedRecord
GO

create table ForwardedRecord(ID int, IntCol1 int, vcCol1 varchar(8000), vcCol2 varchar(8000))
GO

insert ForwardedRecord select 1,1,'row1col1','row1col2'
insert ForwardedRecord select 2,2,replicate('row2col1',100),replicate('row2col2',100)

 

Şu anda elimde 1 adet data page var. Bu page’in içeriğine bakalım.

Bunun için ilk olarak tablomda hangi page’lerin olduğuna bakmam gerekiyor.

DBCC IND('AdventureWorks2008','ForwardedRecord',0)

 

1

Data page’in ID’si 21216. Şimdi bu page’in içeriğine bakalım.

DBCC TRACEON (3604);
GO
DBCC page('AdventureWorks2008',1,21216,3)

 

Insert ettiğim 2 kayıtta bu page’in içerisinde.

Şimdi Forwarded Record oluşturacağım. Bunun için aşağıdaki update komutu ile daha önce 3 byte veri içeren kolonu 8000 byte’lık veri ile update ederek 21216 nolu page’in içerisine sığmamasını ve yeni bir page’e atılmasını sağlıyorum.

update ForwardedRecord
set vcCol1=REPLICATE('row1col1',1250)
where ID=1

 

Tekrar tablonun page’lerine bakıyorum.

DBCC IND('AdventureWorks2008','ForwardedRecord',0)

 

2

Gördüğünüz gibi 21218 nolu yeni bir page oluştu. Beklediğim şey ID’si 1 olan kaydın 21218 nolu page’in atılması ve 21216 nolu yani kaydın eski page’inde ilgili yere forwarding bilgilerinin yazılması. Bakalım öyle mi olmuş?

21218 nolu page’in içeriğine bakıyorum.

DBCC page('AdventureWorks2008',1,21218,3)

 

3

Beklediğimiz gibi update ettiğimiz kayıt eski olduğu page’de yeteri kadar yer olmadığı için yeni page’in yani 21218 nolu page’in içine taşındı.

Şimdide eski page’in yani 21216 nolu page’in içeriğine bakalım.

DBCC page('AdventureWorks2008',1,21216,3)

 

4

Gördüğünüz gibi ID’si 1 olan kaydın eski olduğu yere bir pointer konulmuş ve datanın orjinal yeri işaret edilmiş durumda.

Dolayısıyla ben bu kaydı okumak istediğimde ilk olarak 21216 nolu page’ geleceğim, kaydın burada olmadığını ama orjinal yerini gösteren bir pointer (file 1 page 21218 slot 0) göreceğim. Bu pointer vasıtasıyla kaydın olduğu page’e gidip okumayı tamamlayacağım.

Forwarded Record Neden Oluşur? – SQL Server’ın Bu Davranışının Nedeni Nedir?

Forwarded Record’un nasıl oluştuğunu bir önceki bölümde inceledik. Peki ama SQL Server neden böyle bir davranış içine girer.

Yani aslında demek istediğim şu. Kaydı yeni page’e taşıdıktan sonra eski yerine bir pointer koymaktansa “datanın asıl yeri artık burasıdır” diye neden denilmez?

Bu soruya NonClustered Index içeren ve içermeyen heap tablolar açısından cevap bulmaya çalışacağız.

NonClustered Index İçeren Heap Tablolarda Forwarded Record

Bildiğiniz gibi NonClustered Index’in Leaf Level’ında eğer tablo heap ise datanın geri kalanına ulaşabilmek için HEAP RID pointer bilgisi bulunur. Bu pointer vasıtası ile NonClustered Index üzerinde arama tamamlandığında kayıdın geri kalanına erişilir. (Index yapısı hakkında detaylı makalem için lütfen tıklayınız.)

İşte bu pointer yapısı sebebiyle eğer kaydın bulunduğu page değişirse ilgili tablo üzerinde bulunan bütün NonClustered Index’lerin Leaf Level’ında bulunan, update edilen kayda ait pointer bilgisinin yeni pointer bilgisi ile değiştirilmesi gerekmektedir. Bu da update işleminin daha uzun sürmesine sebep olacaktır. SQL Server linkleme işlemini bu şekilde yapmaktansa NonClustered Index’in işaret ettiği yeri değiştirmez, ama bu işaret edilen yere de bir pointer konularak kaydın gerçek yeri işaret edilir.

Şimdi yukarıda bahsettiğimiz işlemi deneyelim ve HEAP RID pointer bilgilerinin update olup olmadığını gözlemleyelim.

Bir önceki örneği NonClustered Index create ederek tekrar yapalım. Bu sefer kullanacağım çalışma tablosunun adı “ForwardedRecordNCIndex”.

if object_id('ForwardedRecordNCIndex', 'U') is not null
	drop table ForwardedRecordNCIndex
GO

create table ForwardedRecordNCIndex(ID int, IntCol1 int, vcCol1 varchar(8000), vcCol2 varchar(8000))
GO

insert ForwardedRecordNCIndex select 1,1,'row1col1','row1col2'
insert ForwardedRecordNCIndex select 2,2,replicate('row2col1',100),replicate('row2col2',100)

create nonClustered Index IX_1 on ForwardedRecordNCIndex (IntCol1)

 

Şimdi NonClusteredIndex’in Leaf Level page’ine bakalım. Bunun için ilk olarak Leaf Level Page’in PageID’sini öğrenmem gerekiyor.

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

 

5

NonClustered Leaf Level Page’in ID’si 21513. Şimdi bu page’in içeriğine bakalım.

DBCC page('AdventureWorks2008',1,21513,3)

 

6

Heap RID yani datanın geri kalanına erişmek için gerekli olan pointer bilgileri resimdeki gibi.

Şimdi bir Forwarded Record oluşturacağız ve Heap RID pointer bilgisinin değişip değişmediğine bakacağız.

update ForwardedRecordNCIndex
set vcCol1=REPLICATE('row1col1',1250)
where ID=1

 

Tekrar NonClustered Index Leaf Level Page’in içeriğine bakıyoruz.

7

Gördüğünüz gibi kaydın yeri değişmesine rağmen Heap RID pointer bilgisi değişmedi. İşte burada Heap RID hala eski yeri göstersede, eski page’e, kaydın gerçek yerini gösteren pointer bilgisi eklenmekte, bu sayede kayda erişilebilmektedir.

NonClustered Index İçermeyen Heap Tablolarda Forwarded Record

NonClustered Index içeren tablolarda neden Forwarded Record oluşturulmasının mantıklı olduğunu bir önceki bölümde konuşmuştuk. Kısaca üstünden geçmek gerekirse; eğer Forwarded Record oluşmaz ise NonClustered Index’in Leaf level page’inde bulunan Heap RID pointer’ının değişmesi gerekmekte bu da update işleminin daha uzun sürmesi anlamına gelmektedir.

Peki üzerinde NonClustered Index olmayan heap tablolarda Forwarded Record oluşmasının mantığı nedir?

Index olmayan yani Heap tablolara yapılan select işleminde table scan yapılmaktadır. Forwarded Record olsa da olmasa da tablonun tamamı okunacağı için Forwarded Record olup olmaması herhangi bir fark oluşturmaktadır. Bu yüzden NonClustered Index içeren tablolardaki davranışın aynısı burada da sürdürülmektedir.

Forwarded Record’un Performansa Etkisi Nedir?

Update performansının daha iyi olması için Forwarded Record davranışı sergilenir dedik. Peki bir tabloda Forwarded Record bulunmasının performansa ne gibi bir eksi etkisi vardır.

ForwardedRecordNCIndex çalışma tablosu kullanarak yaptığımız örnekte ID’si 1 olan kayıt 21515 nolu page’in içinde bulunmakta. Ama bu page’e erişmek için ilk olarak bu page’i işaret eden pointer bilgisine sahip olan page’in okunması gerekmektedir. Yani Forwarded Record’a sahip bir tabloda okuma yaparken gereksiz yere page okuma yani fazladan IO yapmak durumunda kalınmakta, bu da performans sıkıntısı oluşturmaktadır. Bu durumun örneğini daha sonraki bölümlerde yapacağım.

Clustered Index İçeren Tablolarda Neden Forwarded Record Oluşmaz?

Clustered Index içeren tablolar mantıksal olarak Clustered Index Key’lere göre dizilidir. Page’e sığmayan bir update işlemi gerçekleştiğinde kayıtlar yer değiştirmeli ve mantıksal olarak tekrar dizilmelidir. İşte bu yüzden Clustered Index’lerde Forwarded Record oluşmaz.

Ayrıca Clustered Index içeren tablolarda bulunan NonClustered Index’lerin Leaf Level’ında Heap RID yerine Clustered Index Key’leri bulunur. (Detaylı bilgi için bakınız.) Bu sebepten dolayı Clustered Index içeren bir tabloda page’e sığmayacak bir update işlemi yapıldığında ve update edilen kaydın ya da page’de bulunan diğer kayıtların yeri değiştiği zaman NonClustered Index’te herhangi bir değişiklik yapılmasına gerek yoktur. Dolayısıyla bu tarz bir update işleminde herhangi bir performans sıkıntısı oluşmaz.

Hangi Tablolarımda Forwarded Record Var?

Forwarded Record içeren tablolarda yapılan select işlemleri gereksiz yere fazladan IO yapılmasına neden olacağından dolayı hangi tablolarda Forwarded Record olduğu belirlenmeli ve gerekiyorsa düzeltilmelidir.

Bir tabloda Forwarded Record olup olmadığına sys.dm_db_index_physical_stats DMF’si ile bakılabilir. Örneğin ForwardedRecordNCIndex tablosu için bu DMF’i sorgularsak;

select object_name(object_id) as ObjectName
        ,index_id
        ,page_count
        ,avg_page_space_used_in_percent
        ,record_count
        ,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('ForwardedRecordNCIndex'),null,null,'DETAILED')
where OBJECT_NAME(object_id)='ForwardedRecordNCIndex'

 

8

Heap için 1 tane Forwarded Record olduğunu görüyoruz.

Bir DB’de bulunan bütün tabloları aşağıdaki script ile sorgulayıp forwarded record count’ları öğrenebiliriz. (Kaynak: http://sqlserverpedia.com/blog/sql-server-2005/find-tables-with-forwarded-records)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

IF OBJECT_ID('tempdb..#HeapList') IS NOT NULL
    DROP TABLE #HeapList

CREATE TABLE #HeapList
    (
    object_name sysname
    ,page_count int
    ,avg_page_space_used_in_percent float
    ,record_count int
    ,forwarded_record_count int
    )

DECLARE HEAP_CURS CURSOR FOR
    SELECT object_id
    FROM sys.indexes i
    WHERE index_id = 0

DECLARE @IndexID int

OPEN HEAP_CURS
FETCH NEXT FROM HEAP_CURS INTO @IndexID

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #HeapList
    SELECT object_name(object_id) as ObjectName
        ,page_count
        ,avg_page_space_used_in_percent
        ,record_count
        ,forwarded_record_count
    FROM sys.dm_db_index_physical_stats (db_id(), @IndexID,  0, null,  'DETAILED'); 

    FETCH NEXT FROM HEAP_CURS INTO @IndexID
END

CLOSE HEAP_CURS
DEALLOCATE HEAP_CURS

SELECT *
FROM #HeapList
WHERE forwarded_record_count > 0
ORDER BY 1

 

Sonuç şuna benzer olacaktır.

9

Tablolarımda Bulunan Forwarded Record’lardan Dolayı Performans Sıkıntısı Yaşıyor muyum?

Bir önceki bölümde hangi tablolarda Forwarded Record olduğunu nasıl sorgulayacağımızı gördük. Peki ama bu Forwarded Record’lar şu an bir sıkıntı çıkarıyor mu? Yani çektiğim sorguların kaçı bu Forwarded Record’lardan etkileniyor?

Bu kontrolü SQLServer:Access Methods altında bulunan Forwarded Records/Sec performance counter’ı ile yapabiliriz. Bu performance counter saniyede gerçekleşen Forwarded işlemini yani, forward edilmiş bir kaydın okunma işlemini göstermektedir.

SQL Rap’te bu performance counter için verilen threshold değeri her 100 Batch Requests/Sec için 10 Forwarded Records/Sec. Dolayısıyla sunucu üzerinde Batch Requests/Sec değeri ile Forwarded Records/Sec counter’larını toplayıp herhangi bir Forwarded Record problemi olup olmadığını anlayabiliriz.

Forwarded Record Nasıl Düzeltilir?

Diyelim ki performance counter’ları izledik ve Forwarded Records/Sec oranının bizim beklediğimiz threshold değerinden fazla olduğunu gördük. Bu durumda Forwarded Record’ları düzeltmek istiyorum. Peki ama nasıl?

Forwarded Record’ları düzeltmenin en kısa yolu Forwarded Record içeren Heap tablo üzerinde Clustered Index oluşturup daha sonrada drop etmektir. Bu şekilde page’ler tekrar düzenlenecek ve Forwarded Record’lar ortadan kalkacaktır.

Şimdi bu işlemin etkisini görmek için ForwardedRecordNCIndex tablosu üzerinde bir örnek yapalım.

Şu anda ForwardedRecordNCIndex tablosunda 1 nolu kayıt Forwarded durumda. Bu kaydı okuduğumda ne kadar IO yapıldığına bakalım.

SET STATISTICS IO ON
select * from ForwardedRecordNCIndex where IntCol1=1

 

10

3 logical read yapıldı. Şimdi bu tablo üzerinde bir Clustered Index create ederek Forwarded Record’ları düzelteceğim. İşlem bittikten sonra Clustered Index’i drop edeceğim.

create clustered index CI_1 on ForwardedRecordNCIndex (ID) 
go
drop index CI_1 on ForwardedRecordNCIndex
go

 

Bu işlemden sonra Forwarded Record’ların düzelmiş olması gerekiyor. sys.dm_db_index_physical_stats DMF’sini sorgulayarak tabloda Forwarded Record olup olmadığına bakalım.

select object_name(object_id) as ObjectName
        ,index_id
        ,page_count
        ,avg_page_space_used_in_percent
        ,record_count
        ,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('ForwardedRecordNCIndex'),null,null,'DETAILED')
where OBJECT_NAME(object_id)='ForwardedRecordNCIndex'

 

11

Gördüğünüz gibi daha önce 1 olan forwarded_record_count şu an 0. Yani Forwarded Record’lar düzeltildi.

Şimdi select sorgusunu tekrar çekelim ve Forwarded Record’lar düzeltildikten sonraki IO değerlerine bakalım.

12

Gördüğünüz gibi daha önce 3 logical read yapan sorgu Forwarded Record düzeltildikten sonra 2 logical read yaparak sonucu getirdi. Bunun sebebi daha önce Forwarded Record yüzünden fazladan okunan page’in artık okunmuyor olması.

Sonuç

Heap tablolarda oluşan Forwarded Record problemi bu tablolar üzerinde yapılan select sorgularında fazladan IO yapılmasına dolayısıyla performans sıkıntısına sebep olmaktadır. Bu yüzden Forwarded Record içeren tablolar periyodik olarak analiz edilmeli, eğer bir sıkıntı oluştuğu belirlenirse bu kayıtlar Clustered Index Create-Drop işlemi ile düzenlenmelidir.

 

İ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 sistemin nasıl çalıştığını kontrol etmek için bakacağımız belkide ilk yer SQL Server Error Log’larıdır. SQL Server ve Agent Error Log’ları sayesinde sistemde herhangi bir ciddi problem olup olmadığını kontrol etmemiz mümkündür.

Bugünkü yazımda SQL Server Error Log’ların sayısının nasıl arttırılacağını, yeni bir error log dosyasının nasıl create edilebileceğini, kısacası SQL Server’da Error Log’lar ile çalışırken hangi best practice’leri uygulamamız gerektiğinden bahsedeceğim.

[more]

Bu makalemde şu ana başlıklar üzerinde duracağız.

  • SQL Server’da Error Log’lar ile Çalışmak
  • Database Engine Error Log’ları
    • Database Engine Error Log’ları Hakkında Genel Bilgilendirme
    • Database Engine Error Log Recycle’ı Elle Tetiklemek (master.sys.sp_cycle_errorlog)
    • Database Engine Error Log’larının Sayısını Arttırmak
    • Database Engine Error Log’ları Okumak İçin Özelleştirilmiş Log Arama SP’si
    • Başarılı Backup İşlemi Log’larının Error Log’a Yazılmasını Engellemek
  • SQL Server Agent Error Log’ları
    • SQL Server Agent Error Log’ları Hakkında Genel Bilgilendirme
    • SQL Server Egent Error Log Recycle’ı Elle Tetiklemek (msdb.sys.sp_cycle_agent_errorlog)
  • Error Log Recycle İşlemini SQL Job ile Tetiklemek

SQL Server ve Agent Error Log’lar eğer değişiklik yapılmadıysa şu tarz bir folder’da bulunur.

C:\Program Files\Microsoft SQL Server\MSSQL10.S10ENT\MSSQL\Log

Bu folder’da bulunan ERRORLOG isimli dosyalar Database Engine’in Error Log’ları, SQLAGENT şeklinde başlayan dosyalar ise SQL Server Agent’ın Error Log’larıdır.

Database Engine Error Log’ları

SQL Server Database Engine için default olarak 1 adet aktif (ERRORLOG) ve 6 adet arşiv (ERRORLOG.1’den ERRORLOG.6’ya kadar olan dosyalar) olmak üzere 7 adet error log dosyası bulunmaktadır. Her SQL Server servisi restart olduğunda aktif olan log ERRORLOG.1 haline dönüşür ve ondan sonraki error log’lar da bir sıra kayar. Ve sondaki error log’da silinir. (Bu işleme recycle denilmektedir.)

Recycle işlemini şu şekilde gösterebiliriz.

el1

Recycle işleminin servis restart’ı ile olduğunu söylemiştik. Bu işlemi elle tetiklemekte mümkündür. Bir sonraki bölümde bu işlemin nasıl yapılabileceğine değiniyor olacağım.

Database Engine Error Log Recycle’ı Elle Tetiklemek (master.sys.sp_cycle_errorlog)

SQL Server Database Engine servisi restart olduğunda yeni bir error log dosyası oluşur ve en sondaki error log dosyası silinir demiştik. Bu işleme de Recycle dendiğini belirtmiştik. Detayları bir önceki bölümde inceleyebilirsiniz.

SQL Server servisinin uzun süre ayakta olduğu ya da bir hatadan dolayı çok fazla error basıldığı durumda aktif ErrorLog dosyası şişmeye başlar ve büyük bir boyuta erişir. Böyle bir durumda bu errorlog dosyasının içeriğine bakmak oldukça güç hale gelir.

İşte böyle bir durumda 0’dan yeni bir error log dosyası oluşturmak için servisi restart etmektense recycle işlemini elle tetiklememiz mümkündür.

Aşağıdaki SP execute edildiğinde aktif olan errorlog dosyası ERRORLOG.1 haline dönüşecek ve yeni bir error log dosyası 0’dan oluşturulacaktır.

EXEC master.sys.sp_cycle_errorlog;

 

Database Engine Error Log’larının Sayısını Arttırmak

Bir önceki bölümde anlattığım gibi servis restart olduğunda ya da recycle işlemi elle tetiklendiği zaman yeni bir error log dosyası oluşturulur ve en sondaki error log dosyasıda silinir. Yani default olarak 1 aktif 6 da arşiv olmak üzere 7 error log dosyasına izin verilir.

Error Log’lar sistemi analiz etmek için etkin yöntemlerden biri olduğu için eski error log dosyalarının hemen silinmesini istemeyebiliriz. Bu durumda saklanacak error log dosyası parametresini değiştirmemiz gerekir.

Bu değişikliği Management Studio üzerinden şu şekilde yapabiliriz.

  1. Management >> SQL Server Logs yazısına sağ tıklayıp configure ekranını açıyoruz. 

    el2
  2. Gelen ekranda kaç adet error log’un arşiv olarak saklanmasını istediğimizi belirtip OK’e basıyoruz. 

    el3

Database Engine Error Log’ları Okumak İçin Özelleştirilmiş Log Arama SP’si

SQL Server Log’larını okurken filter özelliği kullanabilmekteyiz. Ama bu filter özelliği sadece “like” yani “içinde geçen” şeklinde çalışmaktadır. Ve ne yazık ki “not like” yani “içinde geçmeyen” şeklinde bir filter verememekteyiz. Peki ne zaman ihtiyacımız olur not like aramasına. Örneğin başarılı backup işlemleri loglandığında ve çok sık aralıklarla TLog backup’ı aldığımızda log dosyası çok fazla sayıda backup bilgisi ile dolmaktadır. Ve biz bu backup loglarını exclude ederek raporu görüntülemek isteyebiliriz.

İşte bu amaçla yazmış olduğum SP’yi anlattığım makaleme buradan erişebilirsiniz.

Başarılı Backup İşlemi Log’larının Error Log’a Yazılmasını Engellemek

SQL Server da başarılı olan her backup işlemi bildiğiniz gibi SQL Server Log’a yazılmaktadır. TLog backup kullanımınız var ise ve çok sıklıkla backup alıyorsanız SQL Server Log a çok fazla sayıda kayıt yazılacaktır ve bu da log’un okunmasını zorlaştıracaktır. Şu linkten erişebileceğiniz makalemde başarılı backup işlemlerinin SQL Server Log’a yazılmasını nasıl engelleyeceğimizi görebilirsiniz.

SQL Server Agent Error Log’ları

SQL Server Agent Error Log’ları da Database Engine Error Log’ların da olduğu gibi servis restart olduğunda recycle olur. Tabi burada bahsettiğimiz servis database engine servisi değil SQL Server Agent servisidir.

Bir diğer farklılık ise Database Engine log’ları default olarak 1+6 log file’dan oluşmakta ve bu rakamda değişiklik yapılabilmekteyken SQL Agent Error Log’ları 1+9 log file’dan oluşmakta ve bu rakamlarda değişiklik yapılamamaktadır. Aktif olan SQL Agent Error Log’un dosya adı SQLAGENT.OUT, arşiv olan log file’ların adları ise SQLAGENT.x şeklindedir.

SQL Server Agent Error Log Recycle’ı Elle Tetiklemek (master.sys.sp_cycle_agent_errorlog)

SQL Server Agent Error Log recycle işlemini hem SSMS’ten hem de TSQL ile yapmamız mümkündür.

Management Studio’dan recycle işlemini yapmak için SQL Server Agent >> Error Logs yazısına sağ tıklayıp açılan ekrandan Recycle yazısını tıklamamız gerekir.

el4

Aynı işlemi TSQL ile şu şekilde yapabiliriz.

EXEC msdb.dbo.sp_cycle_agent_errorlog;

 

Error Log Recycle İşlemini SQL Job ile Tetiklemek

Error Log’ları servisler restart olduğunda recycle olur aynı zamanda elle tetiklememizde mümkündür diye belirtmiştik.

Şimdi bu işlemi otomatize etmek için bir SQL Server Job hazırlayacağız ve her ayın başında çalışan bu job vasıtasıyla hem database engine hem SQL Server Agent log’larını otomatik olarak recycle edeceğiz.

Bu şekilde otomatik olarak error log’ların boyutlarını kontrol altında tutmuş olacağız.

Bahsi geçen Job’ın script’ine aşağıda erişebilirsiniz.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Error Log Recycle', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Database Engine Log Recycle', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC master.sys.sp_cycle_errorlog;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SQL Server Agent Log Recycle', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC msdb.dbo.sp_cycle_agent_errorlog;', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every Month', 
		@enabled=1, 
		@freq_type=16, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20110419, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'f631ead7-612f-4536-bd65-15aa1f75179a'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

 

SQL Server’da sistemin nasıl çalıştığını kontrol etmek için Error Log’ların analizi oldukça fazla kullanılan yöntemlerden biridir. Bu yüzden best practice’lere göre ayarlanmış SQL Server ve Agent Error Log kullanımı ileride oluşabilecek sıkıntıların önüne geçmek için çok ö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 makale, Index kullanımının amacını ve faydalarını sorguladıktan sonra, Index tiplerinin arasındaki farkları derinlemesine inceleyip, bakım işlemlerinin nasıl yapılacağı üzerinde duracaktır.

[more]

Makaledeki ana başlıklar şu şekildedir.

  • Neden Index ?
  • Index Nasıl Çalışır ? (B-Tree – Balanced Tree Yapısı)
  • Clustered Index
  • Non-Clustered Index
  • Index’lere Göre Page’lerin Yapısı
    • Clustered Index’te Page’lerin Yapısı
    • NonClustered Index’te Page’lerin Yapısı
  • NonClustered Index’te Included Kolon Kullanımı
  • Index Maintenance (Index Defragmentation)
    • Fragmentation Oranlarının Belirlenmesi – Rebuild – ReOrganize Kararı
    • Reorganize Index
    • Rebuild Index
    • Defragmentation Script’i
    • Özet
  • Index’lerin Kullanım İstatistikleri
  • DMV’ler ile Eksik Index’leri Sorgulama (Missing Index)
  • Sonuç

 

Makale çok uzun olduğu için word formatında yayınlayacağım. Dosyayı buradan indirebilirsiniz. (doc uzantılı halini ise buradan indirebilirsiniz.)

Bu arada makalenin teknik incelemesini yapan Batuhan Yıldız’a tekrar teşekkür etmek isterim.

 

İ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


Data bütünlüğü açısından DBCC CheckDB’nin nasıl kullanılacağını şu yazımda görmüştük. Bugünkü yazımda DBCC CheckDB yapıldığında Error Log’a kayıt olarak düşen rolled back ve rolled forward mesajlarının neler olduklarına ve bu konuyla alakalı herhangi bir aksiyon alınmasının gerekip gerekmediğini tartışıyor olacağız.

[more]

Konuyu bir örnekle açıklamakta fayda var.

Yeni bir DB ve table create edelim.

create database DBCCDeneme
GO

Use DBCCDeneme
GO

Create table tblDeneme(a int, b varchar(10))
GO

 

Bir transaction başlatıp sonlandırmayalım.

begin tran
insert tblDeneme select 1,'a'

 

Şimdi başka bir query window açıp bu DB için DBCC CheckDB başlatalım.

DBCC CheckDB('DBCCDeneme')

 

Şimdi error log’a baktığımızda ;

1

1 transactions rolled back in database 'DBCCDeneme' (24). This is an informational message only. No user action is required.

Gördüğünüz gibi bir transaction’ın rolled back yapıldığı bilgisi gözükmekte. Bunun nedeni ise şu;

DBCC CheckDB işlemi bir internal snapshot üzerinden gerçekleştirilir ve data consistency açısından bu internal snapshot oluşturulurken active olan transaction’lar rolled forward yada rolled back yapılır. Ana DB üzerinde herhangi bir işlem yapılmaz. Dolayısıyla bu mesaj için bir aksiyon alınmasına gerek yoktur.

Dediğimizi doğrulamak için transaction’ı başlattığımız yerde commit edip select çekelim.

commit tran

select * from tblDeneme

 

2

Görüldüğü gibi DB üzerinde bulunan transaction’a herhangi bir şey olmamış :)

 

İ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 2008 R2 Best Practices Analyzer, instance inizi best practice değerlerine göre kontrol edip size önerilerde bulunan bir tooldur. Bugünkü makelemizde bu tool un kullanımını inceliyor olacağız.

Best practise kavramını biraz açalım. Best practise bahsi geçen teknolojik ürün üzerinde kabul görmüş genel konfigurasyonlardır. Örneğin SQL Server ı ele alacak olursak; Database Data ve Log dosyalarını performans amaçlı farklı fiziksel disklerde bulundurmak best practice dir. Ya da TempDB Data file larını core cpu sayısı kadar yapmak gene bir SQL Server Best Practice dir.

Microsoft SQL Server 2005 Best Practise tool unu release ettikten sonra 2008 Best Practice tool u için çok uzun süre bekledi ve ancak 18.06.2010 tarihinde release edebildi. Çok uzun bir süre 2008 ortamlarımızda hala 2005 best practice tool unu kullanmak zorunda kalırken artık şimdi elimizde 2008 best practice tool u var. Ve bu yazımızda bu tool un detaylarına iniyor olacağız.

[more]

Makalemi 4 başlık altında toplayacağım

  • Kurulum
  • MBCA’ya İlk Bakış
  • MBCA ile Network’teki Bir DB Server’a Bağlanma
  • Tarama Sonucu Bulguları

Kurulum


Öncelikle tool u bilgisayarımıza kuralım. Best Practice Analyzer 2008 R2, Microsoft Baseline Configuration Analyzer (MBCA) 2.0 çatısı altında çalışmaktadır. Dolayısıyla ilk olarak bu tool u bilgisayarımıza kuralım. Download için aşağıdaki MSDN linkini kullanabilirsiniz. Bu linkten sisteminiz için uygun olan tool u indirip kurunuz.

http://www.microsoft.com/downloads/details.aspx?FamilyID=1B6E9026-F505-403E-84C3-A5DEA704EC67&amp;displaylang=en&displaylang=en

Eğer kurulumda “Microsoft Baseline Configuration Analyzer 2.0 is not supported on this SKU.” gibi bir hata alıyorsanız işletim sistemi uyumluluğunu kontrol etmenizde fayda var. Çünkü MBCA sadece aşağıdaki OS larda support edilmektedir.

Supported Operating Systems: Windows 7 Enterprise; Windows 7 Professional; Windows 7 Ultimate; Windows Server 2003; Windows Server 2003 R2 (32-Bit x86); Windows Server 2008; Windows Server 2008 R2; Windows Vista Business; Windows Vista Enterprise; Windows Vista Ultimate

MBCA kurulumunu bitirdikten sonra şimdi Best Practice Analyzer 2008 R2 kurulumuna geçebiliriz. Download için aşağıdaki Microsoft linkini kullanabilirsiniz. Bu linkten sisteminiz için uygun olan tool u indirip kurunuz.

http://www.microsoft.com/downloads/details.aspx?FamilyID=0FD439D7-4BFF-4DF7-A52F-9A1BE8725591&amp;displaylang=en&displaylang=en

Gene Supported Operating Systems kısmını kontrol ederek operating sisteminizin desteklenenler listesinde olduğunu teyit etmenizde fayda var.

Supported Operating Systems: Windows 7; Windows Server 2003; Windows Server 2008; Windows Server 2008 R2; Windows Vista

 

MBCA’ya İlk Bakış


MBCA’yı çalıştıralım. Home ekranında SQL Server 2008 R2 BPA’yı seçelim.

1

Enter Parameters ekranında server ve instance bilgilerini dolduralım. İlk örneğimde kendi makinam üzerinde bulunan DB Engine’i analiz edeceğim.

Daha sonra analiz edilmesini istediğimiz service’lerin yanındaki enable seçeneklerini işaretleyelim.

2

Start Scan yazısına tıkladığımızda MBCA seçtiğimiz DB Server’ı analiz etmeye başlayacak ve ekran görüntüsü bu analiz süresi boyunca aşağıdaki gibi olacaktır.

3

Analiz işlemi tamamlandığında aşağıdaki gibi bir sonuç ortaya çıkacaktır.

4

Ekran detayına inersek;

1 – Select Report Type

Collected Data seçeneği seçilirse tarama sonuçlarını ağaç yapısında görebiliriz.

5

Biz bu makalemizde genelde Results seçeneğini kullanacağız.

2 – Noncompliant – All

All seçeneğinde analiz sonucu best practise değerlerimize uygun olan kayıtlarda gösterilir. Noncompliant seçeneğinde gösterilmez.

6

3 – Export Report

Tarama sonucunu XML file olarak export edebilirsiniz.

4 – Filter

Tarama sonucu çıkan ifadelerde belirli bir ifadeyi aratabilirsiniz. Örneğin aşağıdaki örnekte tempdb ifadesi aratılmıştır.

7

5 – Arrange by

Tarama sonucu çıkan bulguları neye göre dizeceğimizi belirler. Severity, önem derecesini göstermektedir. Genelde bu kritere göre dizim yapmakta fayda vardır. İstersek Category’e görede dizme yapabiliriz.

8

6 – Tarama Sonucu

Tarama sonucu çıkan bulguları bu ekranda görebilir, bu bulguların detayına erişerek Etki(Impact) ve Resolution(Çözüm) bilgilerine ulaşabiliriz.

9

MBCA ile Network’teki Bir DB Server’a Bağlanma


MBCA ile Network üzerindeki bir DB Server’a ayar yapmadan bağlanmaya çalışırsak aşağıdaki gibi bir hata mesajı ile karşılaşmamız mümkün.

10

Hata mesajının detayında da görebileceğiniz gibi MBCA yı kullanan kullanıcın remote makinada local admin olması gerekmektedir. Ayrıca remote makinada powershell’in enable edilmesi gerekmektedir.

Bunun için hem local hemde remote makinalarda powershell’de şu komutların çalıştırılıp powershell’in remote kullanıma açılması gerekmektedir.

1. Enable-PSRemoting –f

2. winrm set winrm/config/winrs `@`{MaxShellsPerUser=`"10`"`}

11

Bu ayarlamalar yapıldıktan sonra analiz işlemi problemsiz gerçekleştirilecektir.

12

Tarama Sonucu Bulguları


Makalemin bu bölümünde tarama sonucu çıkan bazı önemli bulguların detayına inip, bu bulgunun neden geldiğine, neye sebep olabileceğine ve nasıl düzeltilebileceğine bakıyor olacağız.

Engine - Database files and backup files exist on the same volume

13

Best practice olarak disklerde bir sıkıntı olma ihtimalinden dolayı data ve log dosyalarının farklı fiziksel disklerde bulundurulması önerilir. Aynı şekilde alınan backup’larında farklı bir fiziksel diskte bulundurulması best practice’dir. Bu error sonucu bize data ve backup dosyalarının aynı diskte olduğunu göstermekte.

Engine – SQL Server tempdb database not configured optimally

14

Best practice olarak tempdb datafile larının core CPU sayısı kadar yapılması önerilir. Yani 4 CPU lu 8 core lu bir sunucuda tempdb datalarını birbirlerine eşit boyutta 8 adet dosya yapmanızı öneririm.

Engine – Authentication Mode

15

Authentication Mode’un Windows Authentication olması best practice’dir. Lakin hemen hemen her firmada SQL Login’ler ile login olunmaya çalışıldığı için bu bulgu üzerinde çok durmayabiliriz.

Engine – Database consistenct check not current

16

Issue kısmında belirtilen DB’ler için hiç DBCC CHECKDB yapıldığını göstermektedir. DBCC CheckDB DB consistency ve integrity’sini kontrol eden bir sistem komutudur. Best practice olarak periyodik olarak bu kontrolün DB’ler üzerinde yapılması önerilir.

Engine – Backups outdated for databases

17

Bu bulguda uzun zamandır backup alınmayan DB’ler hakkında bilgi verilmektedir. Diskte ve DB’de oluşabilecek problemlerden dolayı her daim iyi bir backup policy üzerinde çalışma yapılması gerekmektedir.

Engine – Databases using simple recovery model

18

Point in time recovery ve disaster anında minimum data kaybı ile sıyrılmak için production DB’lerinde full recovery model kullanılması önerilmektedir. Bu bulguda simple recovery model kullanan DB’ler raporlanmaktadır.

 

ÖZET


Best practise bahsi geçen teknolojik ürün üzerinde kabul görmüş genel konfigurasyonlardır. SQL Server 2008 R2 Best Practices Analyzer ile Database Server’larınızı periyodik olarak kontrol edip, best practice’lere uymayan durumları sorgulayabilirsiniz.

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 yada NonClustered Index’ler kullanıldıkça fragmante olurlar. Bu da Index’in performansını olumsuz etkiler. Belirli aralıklarla fragmante olan bu indexleri bulup drop-create, ReOrganize yada Rebuild işlemleriyle fragmante oranlarının düşürülmesi gerekmektedir. Bugünkü yazımızda Index’lerin fragmante oranlarını nasıl sorgulayacağımızı işliyor olacağız.

[more]

Index’lerin fragmante oranlarına sys.dm_db_index_physical_stats DMF’si ile bakılabilir.

select * from sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),null,null,null,null)

 

Bu DMF 5 parametre almaktadır.

  • database_id : İstenilen bir database üzerindeki index’leri sorgulamak için bu parametre kullanılabilir. NULL verilirse kontrol bütün database lerde yapılır.
  • object_id : İstenilen bir table üzerindeki index’leri sorgulamak için bu parametre kullanılabilir. NULL verilirse kontrol bütün table larda yapılır.
  • index_id : İstenilen bir index’i sorgulamak için bu parametre kullanılabilir. NULL verilirse kontrol bütün index lerde yapılır.
  • partition_id : İstenilen index’in istenilen bir partition’ı sorgulanmak istendiğinden bu parametre kullanılabilir. NULL verilirse kontrol bütün partition’larda yapılır.
  • mode : İşlem sonucunun detayı için kullanılabilir. DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED değerlerini alabilir. NULL olarak geçilirse LIMITED mode kullanılır.

Sorgu sonucunda gelen result set te bizi ilgilendiren kolonlar ve açıklamaları şu şekildedir.

  • database_id : Index’in bulunduğu table yada view in bulunduğu database in ID si.
  • object_id : Index’in bulunduğu table yada view in ID si. OBJECT_NAME() function’ı ile bu değeri kullanarak obje’nin adını alacağız.
  • index_id : Index’in Id’si.Eğer heap ise bu değer 0’dır.
  • partition_number : Partition number’ı verir. 1 ise non-partitioned index’tir.
  • index_type_desc : Index’in tipi. Heap, Clustered Index vs.
  • avg_fragmentation_in_percent : Index’teki fragmante oranını verir. Best practise olarak bu değerin %10 dan fazla olduğu index’lerde operasyon yapılması önerilir.
  • page_count : Index’in toplam kaç page’ten oluştuğunu verir.
  • record_count : Index’teki toplam kayıt sayısını verir.

Bu teorik bilgilerden sonra ilk sorgumuzu çekelim.


AdventureWorks DB sinde bulunan index’lerin fragmante oranları

select * from sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),null,null,null,null)

 

Şimdi biraz daha detaylı bir rapor çekelim. Best practise olarak %10 dan fazla fragmante olmuş index’ler de operasyon yapılmasının önerildiğini söylemiştik. Şimdi bu sorguyu nasıl çekeceğinize bakalım.

AdventureWorks DB sinde bulunan %10 dan fazla fragmante olmuş index ler

select object_name(ps.object_id) as [name], 
	ps.index_id,
	ps.avg_fragmentation_in_percent, 
	ps.fragment_count,
	ps.avg_fragment_size_in_pages, 
	ps.page_count, 
from sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) ps
where ps.avg_fragmentation_in_percent > 10
order by ps.avg_fragmentation_in_percent desc

 

Sorgu sonucu şu şekilde bir sonuç olacaktır.

1


İlk paragrafta belirttiğim gibi bu index ler üzerinde fragmante yi azaltabilmek için drop-create,reorganize ve rebuild işlemleri yapılabilir. Bu konu için daha sonra çok detaylı bir yazı hazırlayacağım. Şu anda sadece belirli komutlar ile fragmante oranlarının düşürüldüğünü bilmemiz yeterli.

Sizde sistemlerinizde bulunan index’leri belirli maintenance job lar ile kontrol ettirip Fragmante oranları %10 dan fazla olanlar üzerinde operasyonlar yapabilirsiniz. Bu sorgulama için yukarıdaki script i kullanabilirsiniz.

 

İ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 TempDB veritabanı yanlış yapılan join sorgularının order by clause ile beraber kullanımı ile plan dışı büyüyebilir. Bu büyüme disk size ı dolduracak kadar olursa servis kullanılmaz duruma gelebilir. Bugünkü yazımda TempDB boyutunu küçültme amaçlı yapılabilecek shrink operasyonlarını inceliyor olacağız. Bu kapsamda 3 farklı shrink metodu anlatıyor olacağım.

[more]

Anlatacağım 3 farklı shrink operasyonundan ilkinde “size” tamamen kontrolünüz altında olmakla beraber service restart ı gerektirmektedir. Diğer 2 yöntemde ise shrink komutları kullanılacak ve bu komutlar kullanılırken tempdb üzerinde bir action olmaması gerekmektedir.

Yöntemleri anlatırken örnekler üstünden gidiyor olacağız. Bu örnekler de aşağıdaki 2 script i kullanacağız.

TempDB File Boyutları Sorgusu (1.Script)

Bu sorgu ile TempDB veritabanı dosyalarının aktif boyutlarını sorgulayabiliriz. Değerler MB cinsindendir.

use tempdb
select (size*8)/1024 as FileSizeMB from sys.database_files

 

Örnek sorgu sonucu aşağıdaki gibidir.

res1

TempDB Boyutunu Arttırmak İçin Kullanılacak Script (2.Script)

Bu script ile AdventureWorks2008 de bulunan 6 tablo join clause kullanmadan birbirine bağlanacak ve cross join olması sağlanacaktır. Ayrıca order by clause kullanılacak ve order işlemi tempdb de yapılacağından dolayı tempdb boyutunun büyümesi sağlanacaktır. File larının boyutlarının yeteri kadar büyüdüğünü düşündüğünüzde sorgunun tamamlanmasını beklemeden kapatabilirsiniz.

use AdventureWorks2008
select * 
from Production.TransactionHistory
	,Production.TransactionHistoryArchive	
	,Sales.SalesOrderDetail
	,Production.WorkOrder
	,Production.WorkOrderRouting
	,Person.BusinessEntity
order by 1,2,3

Yöntem 1

  1. TempDB file larının şu anki boyutuna bakmak için 1.Script i çalıştıralım. 

    res1
  2. 2.Script i kullanarak tempdb file larının boyutlarını arttıralım.
  3. 1 nolu script i kullanarak tempdb file larının aldığı boyutu görelim. 

    res2
  4. SQL Server service ini Configuration Manager den stop edelim. 

    res3
  5. Command Prompt u kullanarak SQL Server service ini –c –f parametresi ile tekrar start edelim. Bu parametreler SQL Server ın minimum configuration lar la açılmasını sağlayacaktır. TempDB data file ı 1MB , log file ı 0.5MB olarak açılacaktır. 

    Res4
  6. SSMS üzerinden aşağıdaki sorguyu kullanarak TempDB dosyalarının boyutunu set edelim. Ben örneğimde data file ını 100 MB a, log file ını 10 MB a set edeceğim. 

    Res5
  7. Command Prompt ekranında CTRL+C ye basarak ekranı kapatalım ve Configuration Manager dan SQL Server Service ini start edelim.
  8. 1.Script i tekrar çektiğimizde Data file ın 100 Mb log file ın 10 MB olduğunu göreceğiz. 

    Res6

 

Yöntem 2  - DBCC SHRINKDATABASE

Bu yöntemde DBCC SHRINKDATABASE komutunu kullanarak tempdb veritabanını shrink edeceğiz. Bu komut “target_percent” adında bir parametre almaktadır. Bu parametre de shrink işlemi bitirildikten sonra file larda kalması planlanan boş yer miktarıdır. Örneğin parametreyi %10 olarak verirsek shrink işlemi bittikten sonra data ve log file larının içinde %10 arlık boş alanların bırakılmasını istediğimizi belirtmiş oluruz.

  1. 2 nolu script i kullanarak file boyutlarını tekrar arttıralım.
  2. 1 nolu script i kullanarak tempdb file larının aldığı boyutu görelim. 

    Res7
  3. SHRINKDATABASE komutu ile veritabanını shrink edelim.
    	
        DBCC SHRINKDATABASE (tempdb, 10)	
        
    Res8
  4. 1 nolu script i tekrar kullanarak file boyutlarını kontrol ettiğimizde boyutların düştüğünü göreceğiz. 

    Res9

 

Yöntem 3 - DBCC SHRINKFILE

DBCC SHRINKFILE ın DBCC SHRINKDATABASE den en büyük farkı DB bazında değil file bazında shrink yapabiliyor olmaktır. Bu şekilde bütün DB üzerinde shrink işlemi yapmaktansa sadece shrink yapılmak istenen file üzerinde çalışılabilir. SHRINKFILE da SHRINKDATABASE gibi parametre almaktadır. Yalnız bu sefer oran bazında değil MB bazında parametre kullanılır ve shrink yapılmak istenen file ın shrink işleminden sonra olması planlanan boyutu parametre olarak verilir.

  1. 2 nolu script i kullanarak file boyutlarını tekrar arttıralım.
  2. 1 nolu script i kullanarak tempdb file larının aldığı boyutu görelim. 

    Res10
  3. SHRINKFILE komutu ile primary file ı shrink edelim ve boyutunu 50 MB a getirelim.
    DBCC SHRINKFILE (tempdev, 50)

    Res11

  4. 1 nolu script i tekrar kullanarak file boyutlarını kontrol ettiğimizde primary file ın düştüğünü göreceğiz. 

    Res12

 

ÖZET

Bugünkü yazımda production ortamlarında sıklıkla başımıza gelebilecek olan TempDB database boyutunun artması sonucu bu boyutu nasıl düşüreceğimizi inceledik. 3 yöntemden size en uygun geleni kullanarak TempDB veritabanını shrink edebilirsiniz.

 

İ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 DBA olarak ana görevlerimizden biride veritabanlarımızın periyodik olarak mantıksal ve fiziksel bütünlüklerini kontrol etmektir. Bugünkü yazımda CHECKDB, CHECKALLOC ve CHECKTABLE DBCC komutlarını kullanarak bu kontrollerin nasıl yapılacağını işliyor olacağız.

CHECKDB en kapsamlı check işlemidir ve CHECKALLOC,CHECKTABLE komutlarının yaptığı kontrolleri de içermektedir. Dolayısıyla sisteminizde CHECKDB çalıştırdıysanız ayriyeten CHECKALLOC ve CHECKTABLE çalıştırmanıza gerek yoktur.

[more]

DBCC CHECKDB


DBCC CHECKDB ile parametre olarak verilen database in bütünlük kontrolü yapılır. Bu kontrolde her index in içeriği validate edilir, FILESTREAM kullanılıyorsa metadata ve dosya sistemi arasındaki uyumluluk kontrol edilir ve service broker dataları validate edilir.

Şimdi gelin DBCC CHECKDB komutu çalıştırıp sonucuna göz atalım, daha sonra bu komut için kullanılabilecek parametreleri inceleyelim.

DBCC CHECKDB
GO


İlk satırlarda service broker kontrollerini görüyorsunuz. Bunun akabinde database de bulunan her table için bütünlük kontrollerinin sonuçları bulunmakta.

There are 20777 rows in 96 pages for object "Person.BusinessEntity".
DBCC results for 'Production.ProductReview'.

Her table için içerdiği kayıt bilgisine bu kayıtların kaç page e dağıldığı bilgisine erişebiliyoruz. Eğer table da bulunan datalarda ya da index lerde bulunan kayıtlarda herhangi bir problem var ise çıktıda bunu görüyor oluyoruz.

Table bütünlükleri tamamlandıktan sonra çıktının en altında şöyle bir mesaj görüyoruz.

CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2008'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Bu mesajdan veritabanımızda herhangi bir hata olmadığı bilgisine erişiyoruz. Dediğim gibi eğer sizin veritabanınızda bu tarz bir bütünlük hatası var ise en alttaki mesaj da toplamda kaç hata oluştuğunu izliyor olacaksınız.

Şimdi DBCC CHECKDB komutunun parametrelerine bakalım.

database_name - database_id

CHECKDB komutu database_name yada database_id parametresi verilerek çalıştırılabilir. Eğer bu parametre verilmezse ya da 0 verilirse o anda aktif olan DB için bu komut çalışır.

--AdventureWorks2008 te çalıştırmak için
DBCC CHECKDB('AdventureWorks2008')
GO

 

NOINDEX

Kontrol esnasında Non-Clustered index lerin kontrol edilmemesi için bu parametreyi kullanabilirsiniz. Bu şekilde kontrol daha hızlı bir şekilde bitmektedir. Yalnız şunu ekleyelim bu parametre system table larını etkilememekte, system table larında her daim index kontrolü yapılmaktadır.

DBCC CHECKDB('AdventureWorks2008', NOINDEX)
GO

 

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

Index datalarında ya da table datalarında kayıt problemi var ise bunun raporlanacağını belirtmiştik. REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD parametreleri ile bu bulunan hataların düzeltilmesi sağlanabilir. Bu komutların kullanılabilmesi için database in Single User Mode da olması gerekmektedir.

  • REPAIR_ALLOW_DATA_LOSS: Bütün raporlanan hataları düzeltmeye çalışır. Bu düzeltme işleminde data kaybı ihtimali vardır. Bu yüzden bu parametrenin kullanılmasındansa en son alınmış bir full backup ın dönülmesi önerilir. Genede kullanmaya karar verirseniz başlamadan önce sisteminizin full backup ını almanızı öneririm.
  • REPAIR_REBUILD : Data kaybı olmadan hata düzeltmeye olanak sağlar. Bu hata düzeltmeye non-clustered index de ki eksik kayıdın eklenmesi ya da bir index in rebuild edilmesi örnek olarak verilebilir.
ALTER DATABASE AdventureWorks2008 SET SINGLE_USER
GO
DBCC CHECKDB('AdventureWorks2008', REPAIR_REBUILD) 
GO
ALTER DATABASE AdventureWorks2008 SET MULTI_USER
GO

 

TABLOCK

CHECKDB internal bir database snapshot kullanılarak işletilir. TABLOCK parametresi ile snapshot kullanmak yerine database exclusive lock kullanılır. TABLOCK parametresi işlemin daha kısa sürmesini sağlarken, exclusive lock sebebiyle database e erişimi bir süreliğine durdurur.

DBCC CHECKDB('AdventureWorks2008') WITH TABLOCK
GO

 

ESTIMATEONLY

CHECKDB komutu için TempDB de kullanılacak alanın tahmini değerini görmek için kullanılabilir.

DBCC CHECKDB('AdventureWorks2008') WITH ESTIMATEONLY
GO

 

PHYSICAL_ONLY

CHECKDB nin limitli versiyonudur. Full versiyona göre daha kısa sürdüğü için production ortamlarında kullanılması tavsiye edilir. Tabi genede arada bir full scan yapmak gerekir.

DBCC CHECKDB('AdventureWorks2008') WITH PHYSICAL_ONLY
GO

 

Yazının geri kalan kısmında CHECKALLOC ve CHECKTABLE DBCC komutlarını işliyor olacağız. Bu komutların parametreleri CHECKDB ye çok benzediği için parametreleri geçerek direk komut sonuçlarını inceleyeceğiz.

 

DBCC CHECKDB


Database in dosya sistemi yapısı hakkında bütünlülük kontrolü yapar.Örnek kullanımı aşağıdaki gibidir

DBCC CHECKALLOC('AdventureWorks2008')
GO

 

Daha öncede söylediğim gibi CHECKALLOC CHECKDB nin yaptığı kontrollerin tamamını yapmaktadır. Fakat CHECKDB dosya sistemi yapısı açısından daha detaylı bilgi vermektedir. Örneğin aşağıdaki rapor sonucunda sysobjects tablosunun data ve index page lerinin yapılarını detaylı olarak kontrol edebilmekteyiz.

Table sysobjects                Object ID 1.
Index ID 1         FirstIAM (1:11)   Root (1:12)    Dpages 22.
    Index ID 1. 24 pages used in 5 dedicated extents.
Index ID 2         FirstIAM (1:1368)   Root (1:1362)    Dpages 10.
    Index ID 2. 12 pages used in 2 dedicated extents.
Index ID 3         FirstIAM (1:1392)   Root (1:1408)    Dpages 4.
    Index ID 3. 6 pages used in 0 dedicated extents.
Total number of extents is 7.

 

 

DBCC CHECKTABLE


Parametre olarak verilen table veya indexed view in tüm page ve yapısı hakkında bütünlülük kontrolü yapar. Kullanımı aşağıdaki gibidir.

DBCC CHECKTABLE ('HumanResources.Employee');
GO

 

Örnek rapor sonucu ise aşağıdaki gibidir.

DBCC results for 'HumanResources.Employee'.
There are 290 rows in 7 pages for object "HumanResources.Employee".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

ÖZET

Bugünkü makalemde periyodik olarak database lerimizin bütünlülük kontrolünün DBCC komutlarıyla nasıl yapılabileceğini gördük. Database lerin sağlıklı olarak çalışabilmesi için önemli olan bu kontrolleri maintenance planımıza eklemenin faydalı olacağını söyleyerek 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 üzerindeki database lerimize daha sağlıklı çalışabilmesi için periyoduk olarak bakım yapmamız gerekmektedir. Aynı zamanda disaster durumları için periyodik olarak yedekleme işlemleride yaparız. Genelde yapılan işlemleri şu şekilde sıralayabiliriz;

  • Shrink (Boş Alanları Sıkıştırma) İşlemleri
  • Index Bakım İşlemleri (reIndex ve ReBuild)
  • BackUp Alma İşlemleri

Bu işlemleri manuel olarak yapmamız mümkün. Ama eğer birden fazla database varsa ve her gece belli bir saatte bu işlemleri yapmak istiyorsak bu durumda job ları devreye sokabiliriz.

SQL Server Maintenance Plan ise bize bu işlemleri belli bir plan içinde yapma şansı tanıyor. Aynı zamanda yapılan tanımlamayı tek bir yerden kontrol edebilme, aynı şekilde işlem sonucunuda tek bir yerden raporlama hakkı veriyor. Bu şekilde karmaşık yapılı veritabanı yönetiminde oldukça büyük rahatlık sağlamakta.

Şimdi isterseniz SQL Server 2008 de Maintenance Plan nasıl tanımlanır ve işletilir görelim.

Maintenance Plan menüsüne SQL Server Management Studio >> Management altından erişiyoruz.

mn1_thumb

Maintenance Plans a sağ tıklayıp yeni bir Maintenance Plan tanımlamak için “Maintenance Plan Wizard” a tıklıyoruz.

mn2_thumb

Gelen karşılama ekranında Next e basıp bir sonraki ekrana geçiyoruz.

mn3_thumb

Hazırladığımız maintenance plan a bir ad ve açıklama veriyoruz. Eğer birden fazla maintenance plan tasarlamayı düşünüyorsanız ad ve açıklama kısmına dikkat etmenizi tavsiye ederim.

Yapılan Maintenance ve BackUp işlemleri için schedule tanımlayabilir ve bu şekilde bu işlemleri periyodik olarak gerçekleşmesini sağlayabilirsiniz.

Bu işlemlerin her biri için ayrı ayrı schedule tanımlayabileceğiniz gibi hepsi için tek bir schedule da tanımlayabilirsiniz. Biz bu örneğimizde bütün işlemler için tek bir schedule tanımlayacağız. Ekrandaki radio butonu 2.seçenek olarak seçip schedule tanımlamak üzere Change butonuna basıyoruz.

mn4_thumb

Daha önceki yazılarımda SQL Server 2008 üzerinde Schedule (Job) tanımlamasını ayrıntılı anlattığım için burada tekrar anlatmıyorum. Schedule ile alakalı yazılarıma buradan erişebilirsiniz.

Biz bu ekranda işimizi 1 dakikada bir tekrarlatmak için 1 minute seçip OK e basıyoruz. Arka ekranda da Next e basıp bir sonraki ekrana geçiyoruz.

mn5_thumb

Bu ekranda Maintenance Plan dahilinde yapılması istenen işlemleri seçiyoruz. İşlemleri kendi bölümlerinde açıklayacağım. Ekranda ki bütün işlemleri seçip Next e basarak bir sonraki ekrana geçiyoruz.

mn6_thumb

Bir önceki ekranda seçtiğimiz işlemleri bu ekranda sıralayabiliriz. İşlemler yukarıdan aşağıya şekilde gerçekleştirilecektir. İstediğiniz sıralama değişikliklerini Move Up ve Move Down butonları vasıtasıyla yapabilirsiniz. Next e basıp bir sonraki ekrana geçiyoruz.

mn7_thumb

Bu aşamadan itibaren yapılacak işlemler için ayarlama tanımlamaları başlıyor.

Check Database Integrity : İstenilen database lerin bütünlüğünü ve sağlamlığını kontrol edip rapor verir. Database seçimi yapmak için Databases yazısının yanındaki combobox a tıklayıp database seçim ekranını açıyoruz.

mn8_thumb

Gelen ekranda istediğimiz databaseleri seçerek Ok e basıyoruz. Arka ekranda da Next e basıp bir sonraki ekrana geçiyoruz.

mn9_thumb

Shrink Database : Database lerdeki boş kullanılmayan alanları sıkıştırmaya yarar. Gerekli shrink ayarlarını yapıp database seçimini de yaptıktan sonra Next e basıyoruz. (Daha sonra bir yazımda Shrink i ayrıntılı ele almayı düşünüyorum. O yüzden burada çok detaylı bilgi vermeyeceğim.)

mn10_thumb

ReOrganize Index : Table ve view lerde kullanılan Index leri ReOrganize edip daha hızlı çalışmasını sağlayabiliriz. Database seçimini yapıp Index işleminin hangi Object lerde yapılmasını istediğimiz belirtip Next e basarak bir sonraki ekrana geçiyoruz.

mn11_thumb

ReBuild Index : Gene Index leri ReBuild ederek daha hızlı çalışmasını sağlayabiliriz. (Daha sonraki yazılarımda ReBuild ile ReOrganize arasındaki farklara değineceğim.) Database ve Object seçimini istediğimiz gibi yapıyoruz. Bu ekranda ayrıca “Keep Index online while reindexing” seçeneği bizim için çok önemli. Bu seçenek vasıtasıyla reindex işlemi yapılırken dahi index e erişimi kesmemiş oluyoruz. Next e basarak bir sonraki ekrana geçiyoruz.

mn12_thumb

Update Statistics : Veritabanı ile alakalı istatistik verilerinin update edilmesini yarayan bu ekranda table ve object seçimlerini yapıp Next e basıyoruz.

mn13_thumb

CleanUp History : Hazırlanan Maintenance Plan lar gerçekleştiğinde hdd de history dosyaları oluşturmaktadır. Bu ekranda bu oluşan history dosyalarının ne kadar süreyle hdd de saklanacağını seçebiliyorsunuz. İstenilen ayarlamaları yapıp Next e basıyoruz.

mn14_thumb

Execute SQL Server Agent Job : Daha önce başka işlemler için hazırladığınız SQL Server Job ları Maintenance Plan ın içine dahil edebilirsiniz. Bu ekranda eklemek istediğiniz Job ları seçip Next e basıyoruz.

mn15_thumb

BackUp Database (Full) : Bu ekranda FULL BackUp ının alınmasını istediğimiz Database leri belirliyoruz. Database seçimini yaptıktan sonra “Create a sub-directory for each database” yazısını işaretleyerek her database için bir klasör oluşmasını sağlıyoruz. Folder kısmına da BackUp ların alınacağı folder ı belirtiyoruz. Son olarak Compression Type ı seçerek Next e basıyoruz.

mn16_thumb

BackUp Database (Differential) : Differential BackUp alınmasını istediğimiz veritabanlarını bu ekranda belirliyoruz. Diğer ayarlamalar bir önceki ekrandaki gibidir. Next e basıp bir sonraki ekrana geçiyoruz.

mn17_thumb1

BackUp Database (Transaction Log) : Transaction Log BackUp alınmasını istediğimiz veritabanlarını bu ekranda belirliyoruz. Diğer ayarlamalar bir önceki ekrandaki gibidir. Next e basıp bir sonraki ekrana geçiyoruz.

mn18_thumb

Maintenance CleanUp Task : Maintenance Plan gerçekleşmeleri sonucunda oluşan BackUp ve Maintenance Plan Report dosyalarının hdd de ne kadar süre ile saklanacağını bu ekran vasıtasıyla ayarlayabilirsiniz. Gerekli ayarlamaları yapıp Next e basıyoruz.

mn19_thumb

Son olarak Maintenance Plan gerçekleştiğinde report dosyasının nereye oluşturulacağını ve bu sonucun mail olarak kime gönderileceğini seçerek Next e basıyoruz. Eğer SQL Server için mail ayarlarının anlatıldığı şu yazımı okumadıysanız okumanızı tavsiye ederim.

mn20_thumb

Yaptığımız Maintenance Plan ın bir summary si olan bu ekranı kontrol ettikten sonra Next e basıyoruz.

mn21_thumb

Ve son olarak close a basıp Maintenance Plan ı tanımlama işlemimizi bitiriyoruz.

Artık Maintenance Plan ımız hazır. Bu işlemin tamamlanmasından sonra Maintenance Plans kısmında ve Jobs kısmında 1 er yeni item oluşmuş olması lazım.

mn22_thumb mn23_thumb

Maintenance Plan a bir schedule atadığımız için burada atadığımız schedule Jobs kısmında görüntülenmekte.

Schedule 1 dakikada 1 olarak ayarlamıştık. Dolayısıyla her 1 dakikada 1 ayarladığımız bütün işlemler gerçekleşecektir. Biz genede bu işlemi elle çalıştırmak isteyebiliriz. Bunun için Maintenance Plans altından hazırladığımız Plan ı bulup sağ tıkla açılan ekranda Evecute e basabiliriz.

mn24_thumb

Bu işlem sonucunda Plan çalışmaya başlayacak ve aşağıdaki ekran açılacaktır.

mn25_thumb

Hata verirse bu ekranda message kısmında verdiği hatayı görebilirsiniz.

Son olarak oluşan backup dosyalarına HDD de bakalım ve yazımızı noktalayalım.

mn26_thumb

SQL Server 2008 de Maintenance Plan oluşturma ve yönetme işlemleri bu şekilde. Umarım açıklayıcı bir yazı olmuştur ve işinize yarar.

 

Kolay gelsin

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


Merhaba arkadaşlar

Bu yazımda SQL Server 2008 de ki Policy Management kavramından bahsedeceğim.

Policy Management Sql Server 2008 ile beraber gelen yeni bir özellik. Hazırlanan policy ler sayesinde örneğin bir veritabanı create edilirken auto shrink özelliğinin kontrol edilmesi ve bizim belirlediğimiz değilse hata verilmesi sağlanabilir. Yada table create edilirken girilen table adı policy e uygun mu, yada table create edilirken index e sahip olsun mu olmasın mı tarzında policy ler tanımlayabiliriz.

Policy Management Menüsüne SQL Server Management Studio (SSMS) da Management ın altından erişebiliriz.

 

 

 

İlk incelememiz gereken yer facets. Facets, sql server kurulumuyla beraber gelen yönetilebilen özelliklerdir. Mesela bu bölümdeki database facet ına çift tıklarsak açılan pencerede database için verebileceğimiz özellikleri görmekteyiz.



 

Şimdi sırasıyla bir policy oluşturmanın adımlarına bakalım.

  • Condition oluştur
  • Policy oluştur
  • Policy i kontrol et.

 

Condition Oluşturmak

Örneğin table adı için bir policy yapalım. Bu policy table adı “tbl_” ile mi başlıyor diye kontrol etsin. İlk olarak bir condition oluşturmalıyız. Bunun için menüden condition a sağ tıklayıp new condition diyoruz.




Name kısmına condition için bir ad giriyoruz. Daha sonra facet kısmından table ı seçiyoruz. Çünkü table adını kontrol edeceğiz. Expression kısmında ki field kısmından @name seçeneğini seçip operator ü like yapıyoruz. Çünkü belirli bir ifade ile başlayıp başlamadığını kontrol edeceğiz. Son olarak ta value kısmına kontrolümüz olan ‘tbl_%’ yi yazıp ok e basıyoruz.

 

Policy Oluşturmak

Management Studio da policy e sağ tıklayıp new policy diyoruz. Açılan pencerede policy a bir isim veriyoruz.  Check condition kısmından az önce yaptığımız condition ı seçiyoruz. Şu an için bu condition bütün table lar ve bütün database için geçerli olacaktır. Eğer bunu değiştirmek istiyorsak every yazılarının yanındaki ok tuşlarına basarak onlar içinde condition lar tanımlayabiliriz.




Evaluation Mode kısmında 4 seçenek göreceksiniz. Bunlar;

  • On Demand (Default Seçenek) = Bu seçimi yaparsanız eğer policy in kontrolünü management studio da policy e sağ tıklayıp evaluate seçeneğini seçtiğinizde gelen ekranda yapabilirsiniz.
  • On Schedule : Policy için bir iş emri tanımlayabilirsiniz. Bunun için hazır tanımlanmış iş emri seçebileceğiniz gibi kendiniz istediğiniz bir süreyi de seçebilirsiniz.
  • On Change – Log Only : Bu seçenek vasıtasıyla koşul sağlanmadığı anda otomatikman Windows Event Log a bununla alakalı bilgi düşmekte.
  • On Change – Prevent : Bu seçenek vasıtasıyla örneğin daha table ı create ederken hata mesajı almanız mümkün. SQL ile table Create komutu uygulandığında sistem size bir uyarı mesajı verecektir.


On Demand seçeneğini seçip Ok e basarak policy e oluşturuyoruz. Bu arada on demand hariç diğer seçeneklerde enable yazısını tiklemeyi unutmayın yoksa policy çalışmaz.

Şimdi oluşturduğumuz bu policy i evaluate edip sistemimizdeki tableların bu policy i sağlayıp sağlamadığına bakalım.

Bunun için management studio da policy ı bulup sağ tıklayıp evaluate diyoruz. Şu tarz bir pencere açılacaktır.

 




Bu ekranda kırmızı ile belirtilen satırlar belirtilen policy i sağlamamakta, yeşil ile belirtilen satırlar ise sağlamaktadır.

Export Result özelliği ile de evaluate sonucunu xml formatında kaydetmemiz mümkün.


İ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


Merhaba arkadaşlar,

Sql Server 2008 ile gelen bir diğer özellik Change Data Capture. Bu özellik ile bir table üzerinde yapılan insert,update ve delete işlemlerinin logunu tutabilmekteyiz. Bu özelliği tablo bazında yada tablonun istediğimiz alanları bazında yapabilmekteyiz.

Şimdi bunu nasıl yapabildiğimize bakalım.

Örneğin aşağıdaki gibi bir table üzerinde çalıştığımızı düşünelim. Table ın oluşması için aşağıdaki sorguyu çalıştırabilirsiniz.

CREATE TABLE dbo.Employee(

EmpID int Primary Key NOT NULL,

EmpName nvarchar(100) NOT NULL,

EmpEmail nvarchar(100) NOT NULL)

GO

Daha sonra database in Change Data Capture özelliğini enable etmemiz gerekiyor. Bunun için uygulamamız gereken komut;

EXEC sp_cdc_enable_db

Şimdide bizim table ımızın Change Data Capture özelliğini enable etmemiz gerekiyor. Bunun içinde aşağıda ki sorguyu uygulamamız gerekiyor;

EXEC sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'Employee',

@role_name = NULL,

@filegroup_name = N'',

@supports_net_changes = 1

Enable işlemlerimiz tamam. Şu anda employee table ında bir insert update yada delete işlemi yaparsak bu işlem loglanacak.

İlk işlemi yapalım;

INSERT INTO dbo.Employee

values (1, N'Ahmet Sahtiyan', N'turgay@turgaysahtiyan.com')

Daha sonra bu kayıdı update edelim.

UPDATE dbo.Employee SET EmpName = N'Turgay Sahtiyan' WHERE EmpID = 1;

Şimdi loglanan kayıtlara bir bakalım. Bu kayıtlar cdc.dbo_Employee_CT table ında tutulmaktadır. Change Data capture özelliği enable edilen her kayıt için log bilgileri <schema>_<table>_CT formatındaki table larda saklanmaktadır.

Employee table ı için loglanan kayıtları görebilmek için select * from cdc.dbo_Employee_CT sorgusunu çalıştırdığımızda 3 kayıt göreceğiz. Bunların ilki insert işlemi. 2. si update işlemi, son kayıt ise update işleminden sonraki kayıtın durumunu vermekte.

Yani operation kolonunda değer 1 ise delete, 2 ise insert,3 ise update ten önceki kayıt,4 ise update ten sonraki kayıtın durumunu vermekte.Select işlemi ise loglanmamaktadır.

Şimdi aşağıdaki sorguyu çalıştıralım.

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_employee');

SET @to_lsn = sys.fn_cdc_get_max_lsn();

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_employee(@from_lsn, @to_lsn, 'all');

Bu sorgu ile 2 kayıt dönecektir. İlki insert işlemi, 2.si ise update işleminden sonraki kayıtın durumu. Filtre olarak kullanılan ‘all’ dikkatinizi çekmiştir. Bu özellik bütün değişiklikleri getirmekte.Fakat update işlemleri için sadece updateten sonraki kayıtı getirmekte. Eğer update işleminden önceki kayıtıda görmek istiyorsak ‘all’ yerine 'all update old’ filtresini kullanmamız gerekmekte.

fn_cdc_get_min_lsn sistem fonksiyonu belirtilen tablonun minimum lsn değerini almaya yarar. Aynı şekilde fn_cdc_get_max_lsn fonksiyonuda maksimum lsn değerini almaya yarar.

Şimdi aşağıdaki sorguyu çalıştıralım.

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_employee');

SET @to_lsn = sys.fn_cdc_get_max_lsn();

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_employee(@from_lsn, @to_lsn, 'all');

Bu sorguda fn_cdc_get_all_changes_dbo_employee fonksiyonu yerine fn_cdc_get_net_changes_dbo_employee fonksiyonu kullanılmıştır. Bu fonksiyon vasıtası ilede kayıdın en güncel halini almaktayız.

Aşağıdaki sorgu ile log tableını temizleyebiliriz.

DECLARE @end_time datetime;

DECLARE @to_lsn binary(10);

SET @end_time = GETDATE();

SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

exec sys.sp_cdc_cleanup_change_table @capture_instance = 'dbo_employee', @low_water_mark=@to_lsn

sp_cdc_cleanup_change_table belirlenen tabloda ki kayıtları @low_water_mark değerine göre temizlemekte.

İstediğimiz anda Change Data Capture işlemini disable edip loglama işlemini sonlandırabiliriz. Bunun için uygulayabileceğimiz sorgu aşağıdaki gibidir.

EXECUTE sp_cdc_disable_table

@source_schema = N'dbo',

@source_name = N'Employee',

@capture_instance = N'dbo_Employee'

İ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


Merhaba arkadaşlar,

Bazı tablolara select çektiğimizde input/output hatası alabilmekteyiz. Böyle bir durum indexlerde ki problemden kaynaklanmaktadır.

Bunu düzeltebilmek için aşağıda ki kodları uyguluyoruz.

Önce database in single user control özelliğini true yapıyoruz. DENEME diye yazdığımız yer düzeltmek istediğimiz database in adı.

exec sp_dboption 'DENEME','single user','true'

Daha sonra Data kaybını göze al seçeneği ile beraber CheckDB prosedure ünü uyguluyoruz.

DBCC CHECKDB ('DENEME', REPAIR_ALLOW_DATA_LOSS)

En son işlem olarak datayı tekrar single user false posizyonuna çekiyoruz.

exec sp_dboption 'DENEME','single user','False'

Bu işlemler sonucunda input/output hatası ortadan kalkacaktır.

Kolay gelsin

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