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
<<  Aralık 2017  >>
PaSaÇaPeCuCuPa
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567
Keywords

Daha önce şu makalemde bir instance’da bulunan tüm veritabanlarının boyutlarını sp_msforeachdb sistem prosedürünü kullanarak nasıl raporlayabileceğimizi görmüştük.

[more]

Bir müşterimde aynı raporu cursor kullanarak alma ihtiyacımız doğdu. Raporun cursor versiyonlu halini aşağıda paylaşıyorum.

create table #tmp1 (DBName varchar(100),reservedpages float,usedpages float,pages float)
declare @cmd varchar(max)='',
		@DBname sysname
		
select DB_NAME (dbid) as DBName, Cast(sum(Size)/128. as numeric(18,2)) as FileSize_MB
into #tmp2	   
from sys.sysaltfiles 
where dbid > 4  and dbid<1000 and groupid <> 0
group by DB_NAME (dbid)

declare curx cursor
for
	select name from sys.databases
	open curx
	fetch next from curx into @DBname
	while (@@FETCH_STATUS = 0) begin
		set @cmd='use '+ @DBname +'
			insert #tmp1
			select '''+@DBname+''',sum(a.total_pages) as reservedpages,  
				sum(a.used_pages) as usedpages,  
				sum(  
				CASE  
					When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0  
					When a.type <> 1 Then a.used_pages  
					When p.index_id < 2 Then a.data_pages  
					Else 0  
				END ) as pages
			from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id  
			left join sys.internal_tables it on p.object_id = it.object_id'  
		exec (@cmd)
		fetch next from curx into @DBname
	end

close curx
deallocate curx

select  
	t1.DBName,
	t2.FileSize_MB,
	Cast(Round(t1.reservedpages * 8192 / 1024. /1024.,2) as Numeric(18,2)) as reserved_size_MB, 
	Cast(Round(t1.pages * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as data_size_MB,
	Cast(Round((t1.usedpages - t1.pages) * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as index_size_MB,
	Cast(Round(t1.usedpages * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as total_used_size_MB,
	Cast(Round((t1.reservedpages - t1.usedpages) * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as unused_from_reserved_size_MB,
	t2.FileSize_MB - Cast(Round(t1.reservedpages * 8192 / 1024. /1024.,2) as Numeric(18,2)) as unused_from_file_size_MB
from #tmp1 t1
join #tmp2 t2 on t2.DBName=t1.DBName
order by t1.DBName

drop table #tmp1
drop table #tmp2

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 veritabanları üzerinde yapılan auto growth işlemlerinin ne kadar sürdüğünü ve ne zaman yapıldığını sorgulamak için default trace’i kullanabilirsiniz.

[more]

İlk olarak default trace sonuçlarının hangi file’da tutulduğuna bakıyoruz.

select * from sys.traces
where is_default=1

Yukarıdaki sorgu sonucu gelen result set’te bulunan path bilgisini fn_trace_gettable fonksiyonuna parametre olarak göndererek default trace’i sorguluyoruz.

SELECT databaseid, filename, SUM(IntegerData*8) AS Growth_KB, Duration, StartTime, Endtime
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\Log\log_31.trc', default)
WHERE EventClass = 92 OR EventClass = 93
GROUP BY databaseid, filename, IntegerData, Duration, StartTime, Endtime
ORDER BY StartTime

image

Sorgu sonucunda benim instance’imda gerçekleşen auto growth işlemlerini sorgulamış olduk.

Default trace bildiğiniz gibi belirli boyutta kayıt tutmakta. O yüzden auto growth işlem loglarını kaybetmemek adına ya default trace sonuçlarını belirli periyotlarda başka bir log tablosuna yazmamız ya da auto growth’ları monitor etmek için ayrı bir trace başlatmamız daha sağlıklı bir yaklaşım olacaktır.

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


As you all know, on of the most important best practices is all data files of TempDB should be same size.

If you have 4 core processor on your box, then best practice is create Tempdb with 4 data files, and set the initial size for all of them. (let’s say 1 GB)

After that what if Tempdb needs to grow? At this time one of Tempdb data file will be grow and all data files’ size are not identical anymore.

There’s a trace flag for this purpose. With T1117 trace flag, you can keep all data files’ size same after auto growth. I mean all data files growth together if your database needs to grow

Let me give an example. Assume that your TempDB has 4 data files and each data file has 1GB initial size and 256Mb auto growth value. If Tempdb needs to grow, not only one data file grow, all data files grow together, so you can keep them same size.

One last word, this trace flag is instance wide. So every databases in this instance may affected by this behavior.

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


Biraz uzun bir makale başlığı oldu değil mi? Ama bir o kadar da ilginç. Peki ne demeye çalışıyorum?

[more]

Kısa bir “recovery model” bilgi tazelemesi yaparak konuya gireyim.

Bildiğiniz gibi SQL Server’da Full, Simple ve BulkLogged olmak üzere 3 adet recovery model bulunmakta. Simple recovery model’de commit edilmiş transaction’lar checkpoint’ten sonra otomatik olarak truncate edilirken Full recovery model’de commit edilmiş transaction’ların truncate edilebilmesi için log backup alınması gerekmekte.

Peki Full Recovery Model, Simple Recovery Model gibi davranabilir mi? Yani Full Recovery Model’de transaction log backup almadan transaction’ların otomatik olarak truncate olması mümkün mü?

Makale başlığına bakmadan yukarıdaki soruyu okusaydınız büyük ihtimal cevabınız “hayır” olacaktı. Smile Ama evet böyle bir şey mümkün.

Full Recovery Model’e sahip database’in eğer full backup’ı hiç alınmadı ise bu veritabanı recovery model’i simple gibi davranmakta ve commit edilmiş transaction’lar checkpoint’ten sonra silinmekte.

İlginç değil mi?

Bir örnek vererek konuyu pekiştirelim. Örneğimde full recovery model’e sahip bir veritabanı create edeceğim ve insert’ten önceki ve sonraki log boyutlarına ve doluluk oranlarına bakacağım. Bu şekilde log’un otomatik olarak truncate edilip edilmediğini görmüş olacağım.

Önce bir çalışma DB’si create edip Recovery Model’ini Full olarak set ediyorum.

CREATE DATABASE FullRecDB ON PRIMARY (
    NAME = FullRecDB_data,
    FILENAME = N'C:\SQLData\FullRecDB_data.mdf')
LOG ON (
    NAME = 'FullRecDB_log',
    FILENAME = N'C:\SQLData\FullRecDB_log.ldf',
    SIZE = 50MB);
GO

USE [master]
GO
ALTER DATABASE [FullRecDB] SET RECOVERY FULL WITH NO_WAIT
GO

Şu an ki log doluluk oranı bakıyorum.

dbcc sqlperf(logspace)
go

Daha herhangi bir işlem yapmadığım için log doluluk oranı %1 in altında.

Full1

Şimdi bir çalışma tablosu create ediyorum ve daha sonra bir transaction başlatıp bu tabloya 8000 adet veri insert ediyorum.

use FullRecDB
GO
create table abc(a char(4000))
go

begin tran
GO
insert abc select 'a'
go 8000

Başka bir session’dan log doluluk oranına tekrar bakıyorum

dbcc sqlperf(logspace)
go

ve beklediğim gibi log'un kullanımı %80'ler seviyesinde

Full2

Şimdi açtığım transaction’ı commit edip checkpoint gönderiyorum ve sonrasında log doluluk oranına bir daha bakıyorum.

commit tran

checkpoint

dbcc sqlperf(logspace)
go

Normalde recovery model’im Full olduğu ve log backup almadığım için log doluluk oranımın gene %80’ler civarında gelmesini bekliyordum. Ama hiç full backup almadığım için database’im simple recovery model gibi davranıyor ve commit edilmiş transaction’ları siliyor. Bu yüzden log’un doluluk oranının düşmüş olduğunu görüyorum.

Full3

Aynı işlemleri en başta full backup alarak deneseydim log truncate olmayacaktı. Denemesi bedava Smile

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


One of my customers had a strange issue with one of their database’s log file.

The database is in simple recovery model but log file keep growing and committed transactions not truncated automatically.

First of all I thought that there was an open transaction on this database and this transaction block the truncation. I have checked and there was no open transaction.

Then I thought it might be a good idea to see what happens when getting a log backup. So I have changed the recovery model to full and tried to get a log backup.

I got a message;

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

And also sys.databases.log_reuse_wait_desc column’s value was “REPLICATION”.

It is weird. Because there is no replication setup for this database and this database has never been published.

Here is the resolution;

  1. Setup replication for this database
  2. Run this script; sp_repldone NULL, NULL, 0, 0, 1
  3. Take log backup
  4. See the log is truncated
  5. Remove the replication
  6. If you want, shrink the log file
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.

[more]

Büyümelerin default değeri data dosyası için 1MB, log dosyaları için %10’dur. Değeri default haliyle bırakmak çoğu durumda performans sıkıntısı doğurmaktadır.

Peki bu değerleri kaç olarak ayarlamalıyız?

Bu konuda ilk söylemem gereken değerlerin kesinlikle % şeklinde bırakılmaması gerektiği. Bunun sebebi; düşük boyutlu dosyalarda sürekli auto growth yapma ihtiyacı doğacakken büyük boyutlu dosyalarda da tek seferde çok fazla büyüme yapılma ihtimalidir.

Örneğin dosyanız şu anda 10MB ve auto growth değeri %10 ise büyümeler 1MB’lık bloklar şeklinde yapılacak ve sürekli büyüme ihtiyacı ortaya çıkacaktır.

Tam tersini düşündüğümüzde yani dosyanın boyutunun 100GB olduğunu düşündüğümüzde, bu durumda da her büyüme için 10 GB’lık dosya allocate edilmeye çalışılacak. Bu da uzun sürecektir.

Dolayısıyla toparlayacak olursak; yüzdesel büyüme değerleri vermektense sabit değerler vermeliyiz. Peki bu sabit değer ne olmalı? 10MB, 100MB, 1GB, 10 GB?

Auto Growth değerinin çok büyük set edilmesi daha öncede söylediğim gibi işlemin yapıldığı anda beklemeye sebebiyet verecektir. Küçük olması da sürekli Auto Growth ihtiyacı anlamına gelmektedir. O yüzden bu konuda sunulan ilk best practice auto growth değerinin 1GB’dan büyük set edilmemesi.

Ben kendi ortamlarında data dosyaları için 1024MB ya da 512MB log dosyaları için de 512MB ya da 256MB set etmekteyim. Siz de bu değerleri gönül rahatlığıyla kullanabilirsiniz.

Bu arada otomatik büyümeleri gün içinde kendi hallerine bırakmaktansa mesai saatleri dışında çalışan bir job ile yapmak ta best practice’dir. Bu konu hakkında yazdığım aşağıdaki makaleyi okumanızı tavsiye ederim.

http://www.turgaysahtiyan.com/post/Veritabanc4b1-Otomatik-Buyumeleri-Kontrolunuz-Altc4b1nda-Olsun-(Database-Auto-Growth).aspx

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 page’lerdeki hataları ve bu hatalardan kaynaklı tamamlanamayan I/O işlemlerini loglamak için Page Verify özelliğini kullanılmaktadır. Page Verify özelliği None, TornPageDetection ve Checsum değerlerini alabilmektedir. Bu yazımda bu seçeneklerin neler olduklarını ve best practice olarak hangi seçeneğin seçilmesi gerektiğini inceliyor olacağız.

[more]

Page Verify özelliği veritabanı seviyesinde tanımlanan bir değerdir. TSQL ile şu şekilde değiştirilir;

ALTER DATABASE [dbDeneme] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT

 

Aynı işlemi SSMS’de veritabanı özeliklerinden şu şekilde yapabiliriz.

image

Page Verify özelliğinin alabileceği değerler ve açıklamaları şu şekildedir;

  • CHECKSUM : Bu seçenek seçildiği zaman, ilgili page disk’e yazılırken SQL Server Database Engine tarafından bir checksum değeri hesaplanır ve bu değer page’in header’ına konulur. Page disk’ten okunmak istendiğinde checksum değeri tekrar hesaplanır ve daha önce page header’a yazılan değer ile karşılaştırılır.
  • TORN_PAGE_DETECTION : Bu seçenek seçildiği zaman, ilgili page disk’e yazılırken page’in içerisindeki her 512 byte’lık sector için 2-bit’lik bir değer page header’a yazılır. Page disk’ten okunduğunda daha önce page header’a yazılan torn bit’ler gerçek page sector bilgileri ile karşılaştırılır.
  • NONE : Bu seçenek seçildiği zaman page diskten okunurken Checksum veya Torn Page olarak herhangi bir karşılaştırma ve kontrol yapılmaz.

Checksum, Torn_Page_Detection’a oranla performansa daha az etkisi olduğu ve daha güvenli olduğu için tercih edilmektedir.

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 DMV’lerden biri sys.dm_os_volume_stats DMV’si. Bu DMV ile data ve log file’larının bulunduğu sürücülerin toplam alan, boş alan gibi bilgilerinin öğrenilmesi mümkün.

[more]

SQL Server 2011 Denali’den önceki sürümlerde de sürücülerin doluluk-kullanım oranlarını öğrenmemiz mümkündü. Hatta bu konu ile ilgili şu makaleyi yazmıştım. Makaleyi incelerseniz eğer doluluk oranları için uzun bir kod kullanmış ve Ole Automation Procedures özelliğini enable etmiştik.

2011 Denali ile beraber bu isteğimizi tek bir DMV’e sorgu çekerek gerçekleştirebiliriz.

Örneğin sunucumuzda bulunan sürücülerden içinde DB olan sürücülerin doluluk-kullanım oranları için şu sorguyu kullanabiliriz.

SELECT volume_mount_point
	   , total_bytes/1024/1024 as ToplamMB_MB
	   , (total_bytes-available_bytes)/1024/1024 as KullanilanAlan_MB
	   , available_bytes/1024/1024 as BosAlan_MB
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
GROUP BY volume_mount_point, total_bytes, available_bytes

1

Data ve Log dosyalarının bulunduğu sürücülerde yer kalmadığı zaman veritabanının hizmet veremez duruma gelme ihtimalinden dolayı sürücülerdeki boş alanların sürekli monitor edilmesinde fayda vardır.

 

İ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


SQL Server Magazine’in Ocak 2011 sayısında Paul Randal’ın üzerinde durduğu, bir database’i aynı server üzerinde yeni bir lokasyona taşımanın güvenli yolu yöntemini, bu yazıyı okuduğumdan beri ben de sıklıkla kullanmaktayım. Bugün size bu yöntemin adımlarını gösteriyor olacağım.

[more]

Daha önceleri DB taşımalarını backup-restore ya da attach-detach yöntemleriyle yapmaktaydım. Bu yöntemlerde sıkıntı duyduğum noktalar, backup-restore seçeneğinde işlemin uzun sürmesi, detach-attach yönteminde ise yetkisel problemlerdi.

Paul Randal’ın yöntemini okuduktan sonra artık aynı makina içindeki DB taşımalarında bu yöntemi kullanmaktayım.

Yöntem şu şekilde;

  1. DB’yi offline’a çekiyoruz.
    		
    ALTER DATABASE AdventureWorks SET OFFLINE
    	
  2. Data ve log dosyalarını yeni lokasyona kopyalıyoruz. Yedek kalması açısından şu an için eski lokasyonda bir kopyasını bırakıyoruz.
  3. System Catalog’larında data ve log dosyalarının yeni lokasyonlarını ALTER DATABASE komutu ile belirtelim.
    USE master
    GO
    ALTER DATABASE [AdventureWorks]	
    	MODIFY FILE (NAME = 'AdventureWorks_Data', FILENAME = 'D:\Data\AdventureWorks_Data.mdf')
    GO
    ALTER DATABASE [AdventureWorks]	
    	MODIFY FILE (NAME = 'AdventureWorks_Log', FILENAME = 'D:\Data\AdventureWorks_Log.ldf')
    GO

  4. DB’yi online’a çekiyoruz.
    		
    ALTER DATABASE AdventureWorks SET ONLINE
    	

İ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


Bir önceki yazımda SQL Server 2011 “Denali” gelen çok önemli özelliklerden biri olan Contained Database mantığından bahsetmiştim. Bugünkü yazımda ise Database Engine’den izole bir Contained Database user’ı nasıl create edebileceğimizi ve kullanabileceğimizi görüyor olacağız.

[more]

Contained Database’in ne olduğunu tekrar hatırlatmak gerekirse; Contained Database  database’in tanımlanması için gerekli olan bütün metadata bilgilerini ve database özelliklerini kendi üzerinde tutar. Bu tarz database’lerde login’ler database engine level’ında authenticate olmazlar. User bilgileri istenirse şifresi ile birlikte database içerisinde saklanır. Bu şekilde database engine’den izole edilmiş olan database, server’lar arasında çok rahat bir şekilde taşınır ve ekstra yapılacak işlem sayısı çok aza indirilir.

Contained Database User Oluşturma

Contained Database’lerde user create etme ekranında diğer database’lerden farklı olarak “SQL User with password” seçeneği bulunmaktadır. Bu seçenek vasıtasıyla database engine’den bağımsız ve authentication’ı database’in yapacağı bir user create etmiş oluruz.

image_15

Aynı işlemi T-SQL ile şu şekilde yapabiliriz.

USE [SampleDB1]
GO
CREATE USER [CDBUser1] WITH PASSWORD=N'Abc12345'
GO

 

Contained Database User ile Connection Kurma

Contained Database User’lar database engine seviyesinde değil database seviyesinde tanımlanırlar dedik. Yani bu user’lar sadece tanımlandıkları database’e bağlantı kurabilirler. Dolayısıyla SSMS ile bağlantı kurarken Connect To Database kısmında ilgili database’in seçilmesi gerekmektedir yoksa bağlantı kurulamaz. Aşağıdaki resimlerde connection’ın nasıl kurabileceğini görebilirsiniz.

image_16

image_17

Bugünkü yazımızda database engine’den bağımsız bir contained database user’ın nasıl oluşturulabileceğini gördük. Tekrar not düşmek gerekirse bu database’i başka bir server’a taşıdığınızda herhangi bir user create’i yapmadan direk bu user’la bağlanabilmeniz 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


SQL Server 2011 “Denali” ile gelen çok güzel özelliklerden biride Contained Database mantığı. Contained Database ile database engine’den izole edilmiş bir database oluşturabilir ve instance’lar arasında sıkıntısız bir şekilde taşıma işlemi gerçekleştirebiliriz.

[more]

Hepimizin başına gelmiştir. A server’ında bulunan bir DB’yi B server’ına backup-restore ya da detach-attach metodu ile taşıdıktan sonra kullanıcıların gelmediğini görürüz ve bu kullanıcıları şifreleri ile beraber taşımak zorunda kalırız. Ya da kullanıcılar orphaned olarak kalır ve ekstra işlem yapmamız gerekir. Bunun nedeni login’lerin server seviyesinde saklanıyor olması ve authentication’ın server side’da yapılıyor olmasıdır.

Contained Database ise database’in tanımlanması için gerekli olan bütün metadata bilgilerini ve database özelliklerini kendi üzerinde tutar. Bu tarz database’lerde login’ler database engine level’ında authenticate olmazlar. User bilgileri database içerisinde saklanır. Bu şekilde database engine’den izole edilmiş olan database, server’lar arasında çok rahat bir şekilde taşınır ve ekstra yapılacak işlem sayısı çok aza indirilir.

SQL Server Denali ile beraber CONTAINMENT tipi olarak 2 seçenek gelmektedir. Bunlar Full ve Partial Containment’tır. Fakat CTP1’de şu an için sadece Partial Containment type’ı kullanabilmekteyiz.

Sunucuda Contained Database kullanabilmek için server side option olan “contained database authentication” özelliğinin true yapılması gerekmektedir. Bu özelliği hem SSMS’den hem de query ile yapmamız mümkün.

T-SQL ile Contained Database Özelliğinin Enable Edilmesi

Aşağıdaki script ile yapabilirsiniz.

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE ;
GO
sp_configure 'show advanced options', 0 ;
GO
RECONFIGURE ;
GO

 

SSMS ile Contained Database Özelliğinin Enable Edilmesi

SSMS’te server adına sağ tıklayıp properties kısmına geçelim. Advanced tab’ında bulunan Containment kısmında Enable Contained Databases özelliğini True yapalım.

image_13

Contained Database Oluşturma

Gene hem T-SQL ile hem de SSMS ile bu işlemi yapabiliriz. SSMS ile yaparken standart olarak new database ekranını açalım ve Options kısmına geçelim.

Burada gördüğünüz gibi Containment Type adında yeni bir özellik eklenmiş durumda. Bu kısımdan containment’ın tipini full ya da partial olarak seçebiliriz. Dediğim gibi CTP1 de sadece partial desteklenmekte.

image_14

Containment type’ı değiştirdiğimizde alt tarafta bulunan Containment kısmının aktif olduğunu görüyoruz. Burada bulunan 5 özelliği bu DB’ye özel tanımlayarak server’dan izole edebiliriz.

Örneğin Server’ın Default Language’ı Turkish’ken Contained DB’nin Default Language’ını English yapmamız mümkün. Bu durumda bu DB’yi başka bir server’a taşıdığımızda gene bu özellikler ile taşınmış olacaktır.

Contained Olmayan Bir DB’ye Contained Hale Dönüştürme

Gene bu işlemi bir önceki adımda anlattığım gibi SSMS arayüzünde Database özelliklerinden yapabilirsiniz. Ya da aşağıdaki script ile bu işlemi yapmanız mümkün.

USE [master]
GO
ALTER DATABASE [SampleDB1] SET CONTAINMENT = PARTIAL
GO

 

Bir sonraki yazımda Contained Database’lerde user’ların DB Engine bağımsız nasıl tanımlanacağına bakıyor olacağız.

 

İ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


Bildiğiniz gibi Autogrowth özelliği aktif olan DB’lerde ihtiyaç olduğunda diskten yeni bir alan allocate edilir ve mdf-ndf dosya boyutunda bir artış olur. Allocate edilen bu alandan SQL Server gerekli gördüğü yeri reserved eder ve bu alandan da kullanılmayan alan olabilir.

Yani toparlayacak olursak File Allocation- Reserved Size – Unused size olmak üzere 3 farklı boyuttan bahsetmemiz mümkündür. File boyutu olarak 10 GB yer kaplayan bir DB aslında bu 10 GB’ın belkide sadece 3 GB ını rezerve etmiştir ve bu 3GB ın da 1 GB’ı belkide unused dır. Dolayısıyla diskten gereksiz bir kullanım söz konusudur ve bu durum analiz edilerek shrink vasıtasıyla DB nin boyutunun düşürülmesi çözümüne gidebilir.

Bugünkü yazımda bir server da bulunan bütün DB’lerin File Allocation- Reserved Size – Unused size bilgilerini sorgulayabileceğimiz script üzerinde duracağız.

[more]

Aşağıda yazdığım script ile server da bulunan bütün DB’ler için File Allocation- Reserved Size – Unused Size bilgilerine ulaşılabilir.

create table #tmp1 (DBName varchar(100),reservedpages float,usedpages float,pages float)

select DB_NAME (dbid) as DBName,
	   Cast(sum(Size)/128. as numeric(18,2)) as FileSize_MB
into #tmp2	   
from sys.sysaltfiles 
where dbid > 4  and dbid<1000 and groupid <> 0
group by DB_NAME (dbid)

exec sp_msforeachdb 'use ?
insert #tmp1
select ''?'',sum(a.total_pages) as reservedpages,  
	sum(a.used_pages) as usedpages,  
	sum(  
	CASE  
		 When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0  
		 When a.type <> 1 Then a.used_pages  
		 When p.index_id < 2 Then a.data_pages  
		 Else 0  
	END ) as pages
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id  
left join sys.internal_tables it on p.object_id = it.object_id  
'
  
select  
	t1.DBName,
	t2.FileSize_MB,
	Cast(Round(t1.reservedpages * 8192 / 1024. /1024.,2) as Numeric(18,2)) as reserved_size_MB, 
	Cast(Round(t1.pages * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as data_size_MB,
	Cast(Round((t1.usedpages - t1.pages) * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as index_size_MB,
	Cast(Round(t1.usedpages * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as total_used_size_MB,
	Cast(Round((t1.reservedpages - t1.usedpages) * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as unused_from_reserved_size_MB,
	t2.FileSize_MB - Cast(Round(t1.reservedpages * 8192 / 1024. /1024.,2) as Numeric(18,2)) as unused_from_file_size_MB
from #tmp1 t1
join #tmp2 t2 on t2.DBName=t1.DBName
order by t1.DBName

drop table #tmp1
drop table #tmp2

 

Sorgu sonucu şu şekilde olacaktır.

1

  • DBName = İlgili Database Adı
  • FileSize_MB = Data dosyalarının diskte kapladığı toplam alan.
  • Reserved_size_MB = FileSize_MB boyutunun ne kadarının rezerve edildiği.
  • Data_size_MB = reserved_size_MB ‘ ın ne kadarının Data tarafından kullanıldığı.
  • Index_size_MB = reserved_size_MB ‘ ın ne kadarının Index tarafından kullanıldığı.
  • Total_used_size_MB = reserved_size_MB ‘ ın toplamda ne kadarının kullanıldığı.
  • Unused_from_reserved_size_MB = reserved_size_MB ‘ ın ne kadarının kullanılmadığı.
  • Unused_from_file_size_MB = FileSize_MB ‘ in ne kadarının kullanılmadığı.

AdventureWorks DB’si için bakarsak 1218 in FileSize ‘ ın 1032 sinin kullanılmadığını görüyoruz. Dolayısıyla bu DB yi shrink ederek diskte kapladığı alanı düşürebiliriz.

USE [AdventureWorks]
GO
DBCC SHRINKDATABASE(N'AdventureWorks', 10 )
GO

 

Sorguyu tekrar çalıştırırsak FileSize’ın ve unused size’ın düştüğünü göreceğiz.

2

Yukarıdaki script’in cursor versiyonlu haline şuradan erişebilirsiniz.

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 sıralar suspect duruma düşmüş DB’lerin nasıl kurtarılabileceği üzerine oldukça fazla soru görmekteyim. Bu konu üzerine Paul Randal’ın yazdığı aşağıdaki yazıyı paylaşmak istiyorum.

Not olarak şunu düşmek istiyorum ki, eğer bir database suspect durumdaysa sakın detach etmeyin, yoksa bir daha attach etme şansınız olmayabilir.

http://sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.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


Dünkü makalemde IO DMV lerine giriş yapmış ve sys.dm_io_virtual_file_stats DMV si ile disk response time ları nasıl monitor edebileceğimizi görmüştük. Bugün ise pending yani bekleme statüsünde olan IO isteklerini anlık olarak nasıl sorgulayabileceğimizi görüyor olacağız.

[more]

Pending durumunda olan IO isteklerini sorgulamak için sys.dm_io_pending_io_requests DMV sini kullanacağız.

Gelin önce bir genel bakış yapıp daha sonra detaylı sorgumuzu inceleyelim.

select * from sys.dm_io_pending_io_requests

 

Bu DMV pending statüsündeki IO işlemlerini sorgulamaktadır. Dolayısıyla eğer sorgu sonucu boş gelirse beklemede olan bir IO isteği olmadığı sonucuna varabiliriz.

Kolon açıklamalarına gelecek olursak;

  • io_type: IO request in şu an hangi aşamada pending durumda olduğunu gösterir.
  • io_pending_ms_ticks : Toplam ne kadar süredir pending durumunda olduğunu belirtir. Değer milisaniye cinsindendir.
  • io_pending : Request in gerçekten pending durumunda mı yoksa işlem bitirilmiş olmasına rağmen SQL Server’a ulaştırılmadığını gösterir. Değer 1 ise request pending durumdadır, 0 ise request tamamlanmış ama daha SQL Server bunu almamıştır.
  • io_handle : Bu kolonu kullanarak sys.dm_io_virtual_file_stats DMV sine join yapacağız.

Benim production ortamı için kullanmak olduğum detaylı sorgu ise şu şekilde.

select
   fs.database_id as database_id,
   db_name(fs.database_id) as database_name,
   mf.name as logical_file_name,
   ip.io_type,
   ip.io_pending_ms_ticks,
   ip.io_pending
from sys.dm_io_pending_io_requests ip
left join sys.dm_io_virtual_file_stats(null, null) fs on fs.file_handle = ip.io_handle
left join sys.master_files mf on mf.database_id = fs.database_id
	and mf.file_id = fs.file_id

 

Bu sorgu ile hangi DB’nin hangi file’ındaki bir IO request in pending durumda olduğunu sorgulamanız mümkün.

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

1


Sizde bu sorguyu kullanarak, anlık olarak bir performans sıkıntısı çektiğinizde bunun geciken disk response’undan mı kaynaklandığını sorgulayabilirsiniz.

 

İ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 performance analizi yaparken ilk kontrol edilmesi gereken noktalardan biride disk response time lardır. Disk Response Time, SQL Server isteğine disk’in ne kadar gecikmeli olarak cevap verdiğini gösteren bir ifadedir. SQL Server açısından best practise olarak read response değerinin 10ms nin altında olması arzu edilen bir durumdur.

Bugünkü konumuz olan sys.dm_io_virtual_file_stats DMV si ile DB lerin I/O istatistiklerinin ve response time larının nasıl inceleneceğini görüyor olacağız.

[more]

DMV’ye ilk select imizi çekelim.

select * from sys.dm_io_virtual_file_stats(null,null)

 

Bu DMV 2 parametre almaktadır.

  • 1. parametre DB ID parametresidir ve eğer null dan farklı bir değer geçilirse sadece kriter olarak verilen DB nin file larını sorgular.
  • 2. Parametre ise File ID parametresidir. Örneğin AdventureWorks DB sinin sadece log file ının response time larını bakmak için log file ın File ID si parametre olarak verilebilir.
  • Parametreler null,null geçilirse instance’da bulunan bütün DB ler ve File’lar için raporlama yapılacaktır.

Sorgu sonucuna bakacak olursak;

1  

  • database_id : Database in ID si. İsim bilgisi DB_NAME(database_id) function’ı ile alınabilir.
  • file_id : File’ın ID si. Bu kolonu sys.master_files a join yaparken kullanacağız.
  • sample_ms : SQL Server en son start olduğundan beri ne kadar süre geçtiğini milisaniye cinsinden gösterir.
  • num_of_reads : File’dan kaç defa okuma işlemi yapıldığını gösterir.
  • num_of_bytes_read : Bu yapılan okumalarda toplam kaç byte veri okunduğunu gösterir.
  • io_stall_read_ms : Bu yapılan okumalarda user ın toplam ne kadar beklediğini gösterir. Response time hesaplamasında bu bekleme süresini kullanacağız.
  • num_of_writes : File’a kaç defa yazma işlemi yapıldığını gösterir.
  • num_of_bytes_write : Bu yapılan yazmalarda toplam kaç byte veri yazıldığını gösterir.
  • io_stall_write_ms : Bu yapılan yazmalarda user ın toplam ne kadar beklediğini gösterir. Response time hesaplamasında bu bekleme süresini kullanacağız.
  • io_stall : Okuma ve yazma için toplam bekleme süresini gösterir. io_stall_read_ms + io_stall_write_ms değerine eşittir.
  • size_on_disk_bytes : File’ın içerisinde gerçekten kullanılmakta olan kısmı byte cinsinden ifade eder.

Biz response time kontrolünde “io_stall” kolonlarını kullanacağız. Bu kolonlardaki değerleri işlem adetlerine bölerek işlem bazında response time ları sorguluyor olacağız.

Ben instance larımda aşağıda yazdığım sorguyu kullanıp bu analizi yapmaktayım. Bu sorgu sonucunda file bazında okuma,yazma ve toplam olarak response time ları sorgulayabilmekteyiz.

select db_name(mf.database_id) as DatabaseName, mf.name as FileLogicalName,
	   io_stall_read_ms/num_of_reads as ReadResponseTime,
	   io_stall_write_ms/num_of_writes as WriteResponseTime,
	   io_stall/(num_of_reads+num_of_writes) as ResponseTime,
       num_of_reads, num_of_bytes_read, io_stall_read_ms,        
	   num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall, size_on_disk_bytes
from sys.dm_io_virtual_file_stats(DB_ID('AdventureWorks'),null) as divfs
join sys.master_files as mf 
	on mf.database_id = divfs.database_id 
		and mf.file_id = divfs.file_id

 

2


sys.dm_io_virtual_file_stats DMV side bütün DMV ler gibi SQL Server start’ından itibaren kümülatif olarak verileri toplamaktadır. Dolayısıyla delta olarak sorgu çekmek için daha önce sys.dm_os_wait_stats makalesinde yazdığım tekniği kullanabilirsiniz. Bu makaleye aşağıdaki URL den erişebilirsiniz.

http://www.turgaysahtiyan.com/post/SQL-Server-e28093-Her-Gun-1-DMV-Gun-6-sysdm_os_wait_stats.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


SQL Server ın bazı özellikleri database seviyesindedir. Bu özelliklerden bazıları Edition özellikleridir ve başka bir editionda kullanılamaz. Örneğin partitioning özelliği Enterprise ve Developer Edition’larda kullanılabilir ve eğer bu özelliği içeren bir database başka bir edition’daki server’a taşınırsa bu özellik kullanılamaz hale gelecektir.

Bugünkü yazımda X bir database’in bu özelliklerden herhangi birini kullanıp kullanmadığını sys.dm_db_persisted_sku_features DMV’si ile nasıl sorgulanacağına bakıyor olacağız.

[more]

select * from sys.dm_db_persisted_sku_features

 

Bu DMV aşağıdaki 4 özelliği sonuç olarak döndürebilir.

  • Compression
  • Partitioning
  • TransparentDataEncryption
  • ChangeCapture

Bu özelliklerin ne alama geldiği bugünkü yazı konumuz olmadığı için detaya girmiyorum.

Şimdi benim production’daki bir database’imde bu sorguyu çalıştıralım ve sonuca bakalım.

1


Ben bu database’imde Partitioning özelliğini kullanmaktaymışım. Ve şu sonuca varıyorum. Eğer bu database’imi alıp Enterprise ya da Developer edition olmayan bir edition’a taşırsam bu özelliği artık kullanamaz duruma geleceğim.

Sizde bu sorgu vasıtasıyla Edition değişimlerinizden önce herhangi bir özellik kaybı olup olmayacağına bakabilirsiniz.

 

İyi çalışmalar

Turgay Sahtiyan

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


Bu makalemde sizlere küçük batch size’lı DML komutları çalıştırılmasını anlatacağım.

Örneğin bir tablomuz var ve 1 milyon kayıt içeriyor, bunun tamamını delete veya update etmemiz durumunda çok gereksiz bir transaction log üretimi yapılır. Delete için truncate kullanabiliriz fakat bununda loglanmayacağı gerçeğini unutmamak lazım. Bunun için kayıtların tamamını silmek yerine belirli miktarlarda bir kaç kere delete çalıştırabiliriz.

[more]

Aşağıdaki aşağıda bunun için bir örnek bulabilirsiniz.

Örnek için 1 milyon kayıtlı bir temp tablo yaratacağım ve bu tablodan her seferinde 1000 kayıt silen bir delete scripti yazacağım.

-- 1 milyon tane sample kayıt yaratalım
create table #temptablom(a int,b int);
insert into #temptablom 
select top 1000000 a.object_id,b.object_id from 
sys.columns a 
cross join 
sys.columns b 
cross join 
sys.columns c

-- Kayıtları istenilen kadar miktarlarda(örnek için 10000) silme
declare @DeletedRowCount int=10000
delete top(@DeletedRowCount) from #temptablom
while @@ROWCOUNT=@DeletedRowCount
	delete top(@DeletedRowCount) from #temptablom

 

İyi çalışmalar

Kadir Evciler

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 da mdf,ndf ve ldf olmak üzere 3 çeşit file tipi bulunmaktadır. MDF file lar primary file ları ifade iderken, NDF DB lerdeki secondary file ları ifade etmektedir. Log file larının uzandıları ise LDF dir. Her ne kadar bu extensionların kullanılması önerilsede SQL Server extension kontrolu yapmamaktadır. Yani proddata.turgay diye bir primary datanız olabilir.

Bugünkü yazımda bu file ların özelliklerini kontrol edebileceğiniz bir sistem fonksiyonundan, FILEPROPERTY den bahsediyor olacağım. Bu fonksiyon ile bir DB nin file ının Readonly olup olmadığını, Log mu data file ımı olup olmadığını ve ne kadar dolu olduğunu sorgulayabilirsiniz.

[more]

FILEPROPERTY sistem fonksiyonunun syntax ı aşağıdaki gibidir.

FILEPROPERTY (file_name ,property )

file_name parametresi ile hangi file ı kontrol edebileceğinizi belirtebilirsiniz. Property parametresinin alabileceği değerler anlamları aşağıdaki gibidir.

Parametre Adı

Açıklaması

IsReadOnly

File ın Readonly olup olmadığını döndürür. Dönen değer 1 ise Readonly, 0 ise Readonly değil manasına gelir.

IsPrimaryFile

File ın Primary File olup olmadığını döndürür. Dönen değer 1 ise Primary File, 0 ise Secondary File manasına gelir.

IsLogFile

File ın Log File ı olup olmadığını döndürür. Dönen değer 1 ise Log File, 0 ise Data File manasına gelir.

SpaceUsed

File ın ne kadarının dolu olduğunu döndürür.


Aşağıdaki sorgu ile AdventureWorks Database File larının ReadOnly, PrimaryFile, LogFile, SpaceUsed değerlerini sorgulayabilirsiniz.

use AdventureWorks

select f.fileid, f.name as LogicalFileName
	, f.filename as FileNameWithURL
	, FILEPROPERTY(f.name, 'IsReadOnly') as IsReadOnly
	, FILEPROPERTY(f.name, 'IsPrimaryFile') as IsPrimaryFile
	, FILEPROPERTY(f.name, 'IsLogFile') as IsLogFile
	, 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
from sys.sysfiles f
order by f.fileid

 

Sorgu sonucu şu tarz bir şey olacaktır.

image

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