Recent comments

None


İçerik Ara











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

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

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 Error Log best practice’lerini anlattığım şu makalemde Error Log sayısının öneminden de bahsetmiştik. Bugün göstereceğim script ile error log dosya sayısını TSQL ile nasıl sorgulayabileceğimize bakıyor olacağız.

[more]

SQL Server error log sayısını sorgulamak için aşağıdaki script’i kullanabilirsiniz.

declare @NumErrorLogs int

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', 
	@NumErrorLogs OUTPUT

select @NumErrorLogs

Bu script’i registered servers özelliğini kullanarak tüm sunucular üzerinde tek seferde çalıştırarak best practice’e uymayan sunucularınızı kolayca bulabilirsiniz.

Bir diğer seçenek ise central management servers ile beraber policy based management kullanmak olabilir.

Son olarak SQL Server error log’lar ile ilgili aşağıdaki 2 makalemi okumanızı tavside ederek yazımı noktalıyorum.

SQL Server Logları’nı Okumak İçin Özelleştirilmiş Log Arama Stored Precedure ü

SQL Server Başarılı Backupların Error Log’a Yazılmasını Engellemek

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


Policy Based Management and Central Management Server feature can be used together to monitor some best practices over all SQL Server environment.

[more]

Today we are going to talk about how can we use policy based management to monitor Last Successful DBCC CheckDB date for all databases.

Basically we will use DBCC DBINFO() command to check last successful DBCC CheckDB date. In the result set, we will use the value of “dbi_dbcclastknowngood” field.

Here is the script which is used for condition;

ExecuteSql('Numeric', '
CREATE TABLE #tmp
	(ParentObject varchar(1000) NULL,Object varchar(1000) NULL,Field varchar(1000) NULL,Value varchar(1000) NULL)
insert into #tmp
EXEC (''DBCC DBINFO() WITH TABLERESULTS'')
select cast(value as datetime) from #tmp where field=''dbi_dbcclastknowngood''
drop table #tmp
')

Condition;

pbm1

and the policy;

pbm2

and a sample evaluation report for this policy;

pbm3

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


Recently one of my customer needs to query all tables’ sizes and row counts in order to track these information in a table periodically and determine the growth trend.

[more]

Here is the script;

create table #tmp1 (
      Servername SysName,
      DBName SysName,
      object_id int,
      ScheamaName sysName,
      TableName sysName,
      TableSize_KB int,
      row_count int
)

create table #tmp2 (
      Servername SysName,
      DBName SysName,
      object_id int,
      ScheamaName sysName,
      TableName sysName,
      row_count int
)

exec sp_msforeachdb 'use ?;
if DB_ID()>4 begin
      insert #tmp1
      select @@ServerName,DB_Name(),ps.object_id,
                  schema_name(o.schema_id) as SchemaName,
                  o.name as TableName,
                  SUM(ps.reserved_page_count)*8 as TableSize_KB,
                  0 as row_count
      from sys.dm_db_partition_stats ps
      join sys.objects o on o.object_id = ps.object_id
      where o.type=''U''
      group by ps.object_id,o.schema_id, o.name

      insert #tmp2
      select @@ServerName,DB_Name(),ps.object_id,
                  schema_name(o.schema_id) as SchemaName,
                  o.name as TableName,    
                   sum(ps.row_count) as row_count
      from sys.dm_db_partition_stats ps
      join sys.objects o on o.object_id = ps.object_id
      where o.type=''U'' and ps.index_id in (0,1)
      group by ps.object_id,o.schema_id, o.name
end
';


update t1
set t1.row_count=t2.row_count
from #tmp1 t1
left join #tmp2 t2 on 
      t2.ServerName=t1.ServerName and
      t2.DBName=t1.DBName and
      t2.object_id=t1.object_id


select * from #tmp1
order by TableSize_KB desc

drop table #tmp1
drop table #tmp2

And a sample result like that;

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


SQL Server 2012 RTM olduktan sonra büyük projelerde kullanılmaya şimdiden başlandı. Tabii ki sistemlerdeki best practice kullanımları oldukça önemli. SQL Server 2012 BPA tool’u ile sistemlerin best practice kontrollerini kullanışlı bir arayüz ile rahatça yapabiliriz.

Tool’u aşağıdaki adresten indirebilirsiniz.

http://www.microsoft.com/en-us/download/details.aspx?id=29302

SQL Server 2012 BPA tool’unun kurulum ve kullanımı SQL Server 2008 R2 sürümü için çıkarılan Best Practice Analyzer tool’una oldukça benzemekte. Bu konu ile alakalı yazdığım detaylı makaleye buradan erişebilirsiniz.

Best Practice’ler ile dolu günler geçirmeniz dileğiyle :)

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


Microsoft 26.01.2012’de System Center Advisor’ın release edildiğini anons etti. Cloud-Based bir hizmet olan System Center Advisor ile Windows ve SQL Server’larınızı analiz ettirerek potansiyel konfigurasyon problemlerinin ve performans problemlerinin önüne geçebilirsiniz.

[more]

System Center Advisor (SCA), Windows Azure üzerinde konuşlanan bulut tabanlı bir analiz aracıdır. SCA ile windows ve sql server sunucularınızı monitor edip hali hazırda olan ya da ileriye doğru çıkacak problemleri önceden görüp aksiyon alabilirsiniz.

Bulutta sizin adınıza tutulan alana local’inizde kurgulayacağınız bir gateway aracılığı ile server bilgilerinizi otomatik ve periyodik olarak yükleyebilirsiniz.

image

SCA bulut tabanlı bir hizmet olduğu için MS’e açtığınız case’lerde case’e bakan mühendis sizin bulutta tuttuğunuz verilere erişerek probleminize daha kısa sürede cevap bulabilir. Aynı zamanda siz de domain dışındaki bir noktadan sunucularınızın sağlığını kontrol edebilirsiniz.

SCA ile Windows Server 2008+, SQL Server 2008+ sunucuları monitor edebilirsiniz. Ne yazık ki Windows Server 2003 ve SQL Server 2005 desteği bulunmamakta.

26 ülkede hizmet vermeye başlayan SCA’ın hizmet verdiği ülkeler listesinde ne yazık ki Türkiye bulunmamakta. Ama SCA web sayfasından (www.systemcenteradvisor) açacağınız trial account ile 60 gün boyunca sunucularınızı ücretsiz olarak monitor ederek hizmeti test edebilirsiniz.

SCA ile ilgili aşağıdaki 3 dakikalık video’yu izlemenizi özellikle tavsiye ederim. Bu video ile SCA’nın neler yapabildiği konusunda fikir edinmiş olacaksınız.

http://www.microsoft.com/en-us/showcase/details.aspx?uuid=32e32209-71c4-43d2-b2ae-015598bf5b7d

Bulut tabanlı uygulamalar her geçen gün hayatımıza daha çok girmekte. SCA’da monitoring tarafındaki eksikliği gidermiş gibi gözüküyor.

Şahsi kanaatim tutacağı yönünde. Umarım yanılmam 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


Bugün sizlere Glenn Berry ve Brent Ozar tarafından yazılmış 2 ayrı health check script bloğundan bahsedeceğim. Bu scriptler ile instance ve database’lerinizi best practice’ler ışığında hızlı bir şekilde health check’ten geçirebilir ve alınması gereken aksiyonları belirleyebilirsiniz.

[more]

Glenn Berry’s SQL Server 2008 Diagnostic Information Queries

Glenn Berry tarafından yazılan bu script bloğu ile sunucu ve database’ler için bir bakışta öğrenilmek istenen bilgileri sorgulayabilirsiniz. Sorgulardan bazıları SQL Server 2008 R2+SP1 gerektirmekte. Query’lerin bir çoğu SQL Server 2012’de de çalışmakta. Ama gene de SQL Server 2012 versiyonunu da aşağıdaki linkten erişebilirsiniz.

http://sqlserverperformance.wordpress.com/2011/12/28/sql-server-2008-diagnostic-information-queries-december-2011/

Brent Ozar’s SQL Server Takeover Script

Brent Ozar tarafından yazılan bu script bloğuyla da aynen Glenn Berry’nin yazdığı scriptler gibi sunucu ve database’ler için health check yapabilirsiniz. En büyük farkı ise tek bir SP’yi çalıştırarak sonucu liste halinde alabilirsiniz. Bu şekilde zamandan tasarruf yapabilirsiniz. Tabii şunu unutmamak lazım; scriptlerin tamamı tek bir seferde çalıştırıldığı için özellikle çok fazla database ve object içeren server’larda tamamlanması biraz zaman alabilir.

http://www.brentozar.com/blitz/

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


Bugün sizlere Robert Pearl ve Glenn Berry tarafından yazılmış bir script paylaşacağım. Bu script ile son SQL Server restart’ından beri kullanılan CPU’nun veritabanlarına göre detaylandırılmış raporunu alacağız.

[more]

Script sys.dm_exec_query_stats DMV’si üzerinden çalışıyor. Bildiğiniz gibi bu DMV, sunucu üzerinde çalıştırılan bütün sorguların çalışma istatistiklerini (CPU,IO kullanım vs.) tutar.

SQL Server son restart’ından itibaren kullanılan CPU’nun raporunu alacağımızı söylemiştim. Çünkü bildiğiniz gibi DMV’ler SQL Server restart’ında sıfırlanmakta.

Script şu şekilde;

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
 GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
       DatabaseName, [CPU_Time_Ms]/1000 as [CPU_Time_Sec], 
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

 

Benim instance’ımda çıkan sonuç ise şu şekilde;

image

Raporu bize verdiği sonuç şu; Son SQL Server restart’ından itibaren CPU üzerinde geçirilen zamanın ne kadarı hangi DB tarafından geçirilmiş ve bu zamanların toplama oranı nedir?

Yukarıdaki resim için konuşursak; CPU üzerinde geçirilen zamanın %68’i ilk sıradaki DB için geçirilmiş.

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 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 2008 ile gelen Policy Management özelliğini gene SQL Server’ın bir diğer özelliği olan Central Management Servers ile birden fazla sunucuda aynı anda çalıştırabilmemiz mümkün. Bu makalemde bu işlemi nasıl yapabileceğimize bakıyor olacağız.

[more]

Central Management Server’ı şu şekilde ayarladığımızı düşünelim. (Daha fazla bilgi için şu makaleyi okuyabilirsiniz.)

1

Amacım “servers” grubu altında bulunan server’ların tamamına aynı policy’i tek seferde uygulamak.

Bunun için “servers” yazısına sağ tıklayıp “Evaluate Policies” yazısını tıklıyorum.

2

“Choose Source” kısmındaki butona basarak gelen ekranda policy’lerin olduğu sunucuya bağlantı kuruyorum.

3

Bir önceki ekrana döndüğümde bağlantı kurduğum sunucudaki bütün policy’ler ekranımda listelenecektir. “servers” grubuna uygulamak istediğim policy’leri seçip “Evaluate” butonuna basıyorum.

4

İşlem tamamlandığım “servers” grubunda bulunan bütün sunucular için policy sonuçları listelenecektir.

 

İ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 ile beraber gelen Policy Base Management özelliğinin ne gibi faydalar sağladığını şu makalede incelemiştik. Bu makalemde ise SQL Server 2008 kurulumu ile beraber default olarak gelen best-practice policy’lerini nasıl kullanabileceğimizi görüyor olacağız.

[more]

SQL Server 2008 kurulumu ile beraber kullanımımıza hazır olarak 50 tane database engine policy’si gelmektedir. Aralarında gerçekten güzel ve faydalı policy’ler bulunan bu policy’lere C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033 klasöründen erişebilirsiniz.

Bu policy’leri kullanabilmek için ilk yapmamız gereken istediğimiz policy’leri Policy Base Management altına import etmektir.

Policy Management >> Policies kısmında sağ tıklayıp import policy yazısına tıklıyoruz.

1

Gelen ekranda “Files to import” yazısının yanındaki butona basıyoruz.

3

Açılan ekranda C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033 klasörüne kadar inip bütün policy’leri seçerek “open” butonuna basıyoruz.

2

Bir önceki ekrana dönüyoruz ve bu ekranda OK’e basarak import işlemini sonlandırıyoruz.

Import işlemi tamamlandığında seçtiğimiz policy’ler artık Policy Management altında kullanılabilir hale gelmiş olacaktır.

4

Deneme yapmak için bir policy’i sağ tıklayıp “evaluate” yazısına tıklıyoruz.

5

Policy sonuçları yeni bir ekranda listelenecektir.

6

 

İyi çalışmalar

Turgay Sahtiyan

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


Daha önceki makalelerimde (1|2) sys.dm_exec_query_stats DMV’si ile IO ve CPU açısından pahalı sorguların nasıl kontrol edilebileceğini görmüştük. Yapılan insert-update-delete işlemleri sys.dm_exec_query_stats DMV’sinde toplanmakta ve bu DMV kullanılarak IO ve CPU açısından maliyetli sorgular bulunabilmektedir. Fakat bu DMV ile DML hareketler toplanamaz. Örneğin bir SP’nin içerisinde bazı DML işlemler ve örneğin Index oluşturmak gibi bir DDL işlem var ise sys.dm_exec_query_stats DMV’si sadece DML işlemleri toplayacaktır. Oysaki sys.dm_exec_procedure_stats DMV’si ise prosedürün tamamımının çalışma bilgilerini tuttuğu için DDL işlemi de bu hesaplamanın içine katılır. Bu makalemde bu 2 DMV arasındaki farka bir örnek ile bakıyor olacağız.

[more]

İçeriğinde hem DML hem de DDL işlemleri bulunan şu şekilde bir SP oluşturalım

Use AdventureWorks
GO
create proc SPTest
as
	select * into testPerson from Person.Person
	
	select LastName, FirstName
	from testPerson
	where LastName in ('turgay','sahtiyan')		

	create index IX_1 on testPerson (LastName, FirstName)
GO

 

SP’yi execute edelim.

exec SPTest

 

Şimdi Toplam IO ve CPU değerlerine hem sys.dm_exec_query_stats hem de sys.dm_exec_procedure_stats DMV’leri ile ayrı ayrı bakalım.

select plan_handle, text
	,SUM(total_logical_writes+total_logical_reads+total_physical_reads) as TotalIO
	,SUM(total_worker_time) as TotalCPU
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%SPTest%' and text not like 'select plan_handle%'
group by plan_handle, text
order by TotalIO

select plan_handle, text
	,total_logical_writes+total_logical_reads+total_physical_reads as TotalIO
	,total_worker_time as TotalCPU
from sys.dm_exec_procedure_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%SPTest%'

 

aa1(1)

Gördüğünüz gibi 2 DMV sonucunda alınan TotalIO ve TotalCPU değerlerinde farklılık var. Bunun nedeni, giriş paragrafında da söylediğim gibi, sys.dm_exec_ procedure_stats DMV’sinde DDL hareketlerin olması ve sys.dm_exec_query_stats DMV’sinde DDL hareketlerin olmamasıdır.

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


Bir önceki makalemde Estimated ve Actual Query Plan’ları nasıl görebileceğimiz üzerine konuşmuştuk. Bugünkü makalemde ise bu Query Plan’ları SQL Server Profiler ile otomatik olarak nasıl toplayabileceğimizi görüyor olacağız.

[more]

Daha önceki makalelerimde de değindiğim gibi Query Plan’lar sistem performansını analiz etmede ve çözüm bulmada kullanılan en önemli kaynaklardan biri. Bazı durumlarda gerçek sunucu üzerine gelen tüm Query Plan’ları analiz etmek gerekebilir. Böyle bir durumda SQL Server Profiler aracını kullanabiliriz.

Ayrıca SQL Server Profiler aracının bize sunduğu read ve cpu değerleri ilk aşamada hangi Query Plan’ların daha çok maliyetli olduğunu anlamamızda faydalı olmaktadır.

Bu makalemde SQL Server Profiler ile Query Plan’ların nasıl toplanacağına bakıyor olacağız.

Hemen SQL Server Profiler’ı açalım ve işlemlerimize başlayalım.

İlk olarak Event Selection  kısmında Query Plan’ları toplamak için Performance node’unun altında bulunan ShowPlan XML event’ini seçiyorum.

Ayrıca hangi Query’nin Query Plan’ı oluşturduğunu görmek adına aşağıdaki event’leri de seçmem de fayda var.

  • RPC: Completed
  • SQL: BatchCompleted
  • SQL: BatchStarting

Sonuç itibarıyla seçtiğim tüm event’ler aşağıdaki gibi.

1

ShowPlan XML event’ini seçtiğimiz anda üstteki resimde de göreceğiniz üzere Events Extraction Settings adında yeni bir tab açılacaktır. Bu tab vasıtasıyla toplanan Query Plan’larını tek bir dosyanın içine mi yoksa ayrı ayrı dosyalar içine mi atılacağını belirleyebiliriz. Ben bu örneğimde Query Plan’ları tek bir dosyanın içinde toplayacağım. Ayrıca gene bu ekranda bahsi geçen dosya ya da dosyaların nerede olacağını da belirleyebiliriz.

2

Tanımlamalar tamam. Run’a basıp trace işlemini başlatabiliriz.

Şimdi örnek bir sorgu çalıştıralım ve bu sorgunun SQL Server Profiler’a olan yansımasına bakalım.

select COUNT(*) from Person.Address where AddressID=15

 

3

SQL Server Profiler’dan ilgili sorgunun direk olarak Query Plan’ına bakabiliriz. Ayrıca Showplan XML satırında sağ tık yaptıktan sonra ilgili Query Plan’ı diske kaydedebiliriz.

4

 

İyi Çalışmalar

Turgay Sahtiyan

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


SQL Server sorgu performans analizi yapılırken en çok kullanılan DMV’lerden biri olan sys.dm_exec_query_statsDMV’sine SQL Server 2011 Denali ile beraber 4 yeni kolon eklendi.

[more]

Yeni eklenen bu kolonlar ile sorgu sonucu dönen satır sayısı istatistiklerini öğrenmek mümkün.

Eklenen kolonlar ve açıklamaları şu şekilde;

  • total_rows : Sorgu sonucunda dönen toplam satır sayısı. Bu değer kümülatif olarak hesaplanmaktadır. Yani sorgu her çalıştığında 5 satır döndürüyor ve toplamda 10 kez çalıştırıldıysa bu kolonda 50 değeri yazacaktır.
  • last_rows : Sorgunun en son çalışmasında kaç satır döndürdüğünü gösterir. Yukarıdaki örnek için bu kolonda 5 değeri yazacaktır.
  • min_rows : Sorgunun şu ana kadarki çalıştırılmalarında en az kaç kayıt döndürdüğü bilgisini verir.
  • max_rows : Sorgunun şu ana kadarki çalıştırılmalarında en fazla kaç kayıt döndürdüğü bilgisini verir.

Aşağıdaki sorgu ile SELECT sorgularının döndürdüğü satır sayılarının istatistiklerini analiz edebiliriz.

SELECT qs.execution_count,
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
                 (CASE WHEN qs.statement_end_offset = -1 
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
                       ELSE qs.statement_end_offset end -
                            qs.statement_start_offset
                 )/2
             ) AS query_text, 
     qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid, 
     qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE qt.text like '%SELECT%' 
ORDER BY qs.execution_count DESC

 

1

 

İyi Çalışmalar

Turgay Sahtiyan

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


SQL Server 2011 Denali ile gelen yeni 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


Her ne kadar arzu edilen durum, yazılım geliştiricilerin ve analistlerin production ortamında sorgu çalıştırmaması olsa da, bazı durumlarda kayıtların doğruluğunu ve işlemlerin sonuçlarını canlı ortamda görmek için bu kullanıcıların production ortamında işlem yapması gerekebilir.

Yapılan bu işlemler bazı durumlarda yanlış kod yazımından dolayı çok uzun sürüp gereksiz kaynak tüketimine neden olabilir, bu da sistemin performansına etkileyebilir.

Bugünkü yazımda bir SP vasıtasıyla, belirlediğimiz süreden daha uzun süredir çalışan session’ları otomatik olarak nasıl kill edebileceğimizi görüyor olacağız. Ayrıca ilgili session’ları kill etmeden önce çalıştırdıkları sorguları öğrenip, kill edilme işleminin detaylarını log tablosunda loglayacağız.

[more]

Kill edilme işlemlerini loglayacağımız tablo şu şekilde.

USE AdventureWorks2008R2

CREATE TABLE [dbo].[Killed_Long_Running_Users_Sessions](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[login_name] [varchar](50) NULL,
	[session_id] [int] NULL,
	[Command] [varchar](100) NULL,
	[database_id] [int] NULL,
	[start_time] [datetime] NULL,
	[total_elapsed_time] [int] NULL,
	[logical_reads] [int] NULL,
	[killing_login_name] [varchar](50) NULL,
	[killing_time] [datetime] NULL,
	[Message] [varchar](255) NULL,
	[sql_text] [varchar](max) NULL,
	[statement_text] [varchar](max) NULL,
 CONSTRAINT [PK_Killed_Users_Sessions] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
) ON [PRIMARY]

 

Bizim belirlediğimiz süreden daha uzun süredir çalışan session’ları bulup kill edecek SP ise şu şekilde.

USE AdventureWorks2008R2
GO

CREATE PROC [dbo].[Kill_Long_Running_Users_Sessions] (@Threshold_Second int, @login_name_prefix as varchar(10))
AS

declare @str varchar(max)

declare ACur cursor for
select es.login_name
	,er.session_id
	,er.command
	,er.database_id
	,er.start_time
	,er.total_elapsed_time
	,er.logical_reads
	,st.text as sql_text
	,SUBSTRING(st.text, (er.statement_start_offset/2)+1, 
			((CASE er.statement_end_offset
			  WHEN -1 THEN DATALENGTH(st.text)
			 ELSE er.statement_end_offset
			 END - er.statement_start_offset)/2) + 1) AS statement_text	
from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(er.sql_handle) st
join sys.dm_exec_sessions es on es.session_id=er.session_id
where er.session_id>50
	and (es.login_name like @login_name_prefix+'%' 
	--kill edilmesini istemediğimiz loginname'leri belirtiyoruz
	and es.login_name  NOT IN ('sa','adminturgay')) 
	
	and er.total_elapsed_time>(@Threshold_Second*1000)
	
declare @login_name varchar(50),@session_id int,@Command varchar(100),@database_id int
	,@start_time datetime,@total_elapsed_time int,@logical_reads int,@sql_text varchar(max),@statement_text varchar(max)
open ACur
fetch from ACur into @login_name,@session_id,@Command,@database_id,@start_time,@total_elapsed_time,@logical_reads,@sql_text,@statement_text
while @@FETCH_STATUS=0
begin
	select @str = 'kill ' + cast(@session_id as varchar(10))
	begin try
		exec(@str)
		
		insert dbo.Killed_Long_Running_Users_Sessions (login_name,session_id,Command,database_id,start_time,total_elapsed_time,logical_reads,killing_login_name,killing_time,Message,sql_text,statement_text)
		values (@login_name,@session_id,@Command,@database_id,@start_time,@total_elapsed_time,@logical_reads,suser_name(),getdate(),'Session Killed',@sql_text,@statement_text)
	end try
	begin catch
		insert dbo.Killed_Long_Running_Users_Sessions (login_name,session_id,Command,database_id,start_time,total_elapsed_time,logical_reads,killing_login_name,killing_time,Message,sql_text,statement_text)
		values (@login_name,@session_id,@Command,@database_id,@start_time,@total_elapsed_time,@logical_reads,suser_name(),getdate(),'An Error Accured When Killing Session',@sql_text,@statement_text)
	end catch
	fetch next from ACur into @login_name,@session_id,@Command,@database_id,@start_time,@total_elapsed_time,@logical_reads,@sql_text,@statement_text
end
close ACur
deallocate Acur

 

SP 2 tane parametre almakta.

  • @Threshold_Second : Kaç saniyeden uzun süren session’ların kill edilmesini istediğimizi bu parametre ile belirtebiliriz.
  • @login_name_prefix : Eğer developer ve analistler bir domain grubunun üyesi ise ve application user’lar sql authentication ile bağlanıyorlar ise bu parametreyi kullanabiliriz. Örneğin loginname’i “MS/” ile başlayan kullanıcıların session’ları kill edilsin şeklinde bir genelleme yapabiliriz. Eğer böyle bir genelleme yok ise bu parametreyi boş geçebiliriz.

Ayrıca kodun içinde istisna olarak kullanıcı adı tanımlayabilirsiniz. Bu kısım ile kill edilmesini istemediğiniz “sa” ya da buna benzer kullanıcı adları var ise bu kullanıcı adlarını bu işlemin dışında bırakabilirsiniz.

Şimdi bir örnek yapalım. Aşağıdaki kod bloğunu “sa” olmayan bir kullanıcı ile çalıştıralım.

waitfor delay '01:00:00'
select * from sys.tables

 

Şimdi de 60 sn’den daha uzun süredir devam eden sorguları kill edelim.

exec dbo.Kill_Long_Running_Users_Sessions 60,''

 

Ve son olarak log tablosunda kill olan session ile ilgili detaylara bakalım.

select * from dbo.Killed_Long_Running_Users_Sessions

 

1

2

Bu SP’yi bir job’ın içine tanımlayıp örneğin 1 dakikada 1 çalıştırabilirsiniz. Bu şekilde developer ya da analistler gibi kullanıcılar tarafından bilinçsizci yazılmış sunucunun performansını etkileyecek uzun süren sorguları otomatik olarak kill 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


Mart 2011’de yayınladığım SQL Server’da Index Kavramı başlıklı makalemden sonra bugün de performans iyileştirmenin bir diğer büyük adımı olan SQL Server’da İstatistis (Statistics) Kavramı üzerine yazılmış detaylı makalemi paylaşıyorum.

Bu makale, SQL Server’da istatistiklerin kullanım amacını,faydalarını ve güncel olmalarının önemini sorguladıktan sonra, istatistiklerinin içeriğinin incelenmesi ve istatistiklerin otomatik oluşturulma ve güncellenme parametrelerinin ve bu parametrelerin best practice’lerinin neler oldukları üzerinde duracaktır.

[more]

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

  • İstatistik (Stat) Nedir?
  • DBCC SHOW_STATISTICS Komutu ile İstatistik İçeriğini Görüntülemek
  • İstatistiklerin Güncel Olmasının Önemi
  • İstatistiklerin Otomatik Oluşturulma ve Güncellenme Parametreleri
    • Auto_Create_Statistics
    • Auto_Update_Statistics
    • Auto_Update_Statistics_Async
  • SQL Server Profiler ile İstatistik Oluşma ve Güncellenme İşlemlerini İzlemek
  • Sonuç

Makale çok uzun olduğu için word formatında yayınlayacağım. Dosyayı buradan indirebilirsiniz.

 

İ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


Stored Procedure(SP) ilk çalıştırıldığı zaman istatistikler göz önüne alınarak Query Optimizer tarafından en optimum Query Plan oluşturulur ve daha sonra kullanılmak üzere Plan Cache’e konulur. Aynı SP farklı bir zamanda tekrar çalıştırıldığında Cache’deki plan’ın geçerliliği kontrol edilir ve eğer plan geçerli yani güncel ise tekrar Query Plan oluşturulmak için zaman harcanmayıp plan Cache’den çağırılır ve kullanılır.

Query Plan oluşturma işlemi bazı durumlarda çok fazla CPU kaynağı tükettiği için bu şekilde bir cache’lenme mekanizması kullanılır. Fakat bazı durumlarda cache’lenen plan güncelliğini yitirmiş olabilir. Örneğin SP içinde geçen bir tabloda plan cache’lendikten sonra çok fazla data değişimi olduysa bu durumda istatistikler güncelliğini yitirecek dolayısıyla da cache’lenen plan güncelliğini yitirecektir. Ya da SP’nin içinde geçen tablolarda index ekleme,silme gibi DDL (Data Definition Language) değişiklikleri yapılırsa yine cache’lenen plan güncelliğini yitirmiş olacaktır. Böyle bir durumda SP’nin yeniden derlenip yeni bir Query Plan’ın oluşturulması gerekmektedir. İşte bu duruma ReCompilation denilmektedir.

[more]

Yukarıda anlattığım şekilde istatistik değişimi ya da DDL değişikliği gibi işlemler sonucunda recompilation gerçekleşebileceği gibi SP oluşturulurken yazılan “WITH RECOMPILE” anahtar kelimesi SP’nin her çalışmasın recompile edilmesi sağlanabilir. Örneğin aşağıdaki şekilde oluşturulan SP her çağırılışında tekrar derlenecektir.

CREATE PROC dbo.sp_1
WITH RECOMPILE
AS
	SELECT * FROM Tbl1

 

WITH RECOMPILE anahtar kelimesi ile oluşturulan SP’lerin planları Plan Cache’de saklanmaz, SP her çalıştırıldığında tekrar oluşturulur. Bu seçenek, SP’nin aldığı parametrelere göre değişik Query Plan’lar oluşturduğu durumda kullanılabilir. Örneğin SP “a” parametresi ile çağırıldığında index scan, “b” parametresi ile çağırıldığında index seek yapıyorsa yani gönderilen parametreye göre Query Plan’ı değişiyorsa WITH RECOMPILE anahtar kelimesinin kullanılması mantıklı olacaktır.

WITH RECOMPILE ile SP’nin tamamı recompile edilir. SP’nin tamamının değilde içinde geçen bazı sorgu parçacıklarının yeniden derlenmesi isteniyorsa sorguların WITH RECOMPILE anahtar kelimesi ile yazılması gerekir.

SP’ler aşağıdaki durumlardan herhangi biri oluştuğunda recompile olacak yani yeniden derlenecektir.

  • Schema değiştiğinde
  • İstatistik değiştiğinde
  • SP’nin oluşturulma kodunda “With Recompile” anahtar kelimesi kullanıldığında
  • Set opsiyonları değiştiğinde
  • Temp table değiştiğinde
  • Remote rowset değiştiğinde
  • For browse perms değiştiğinde
  • Query notification environment değiştiğinde
  • Partition view değiştiğinde
  • Cursor seçenekleri değiştiğinde
  • Statement’ta Recompile seçeneği kullanıldığında

SP’ler yukarıda anlattığım şekilde recompile olabileceği gibi recompile olması için elle de tetiklenebilir.

EXEC sp_recompile N'dbo.sp_1'

 

Yukarıdaki kod vasıtasıyla sp_1 SP’sine recompile olması için bir işaret konulur. Bu SP bir dahaki sefer çalıştırılmak istendiğinde yeniden derlenecektir. sp_recompile sistem prosedürüne parametre olarak tablo ya da view de verilebilir. Bu durumda tabloya referans olan SP’ler recompile olacaktır.

SQL Server 2005’ten önceki sürümlerde SP recompilation işlemi SP’nin tamamında gerçekleştirilmekteydi. Yani örneğin SP’nin içinde bulunan bir sorgunun recompile edilmesi gerektiğinde SP’nin tamamı recompile edilmekteydi. 2005’ten sonraki versiyonlarda ise artık sadece recompile edilmesi gereken sorgu recompile edilmektedir. Bu şekilde çok büyük SP’lerde bir sorgu için SP’nin tamamı recompile edilmektense sadece ilgili kısım recompile edilebilmektedir. Bu da performans artışı anlamına gelmektedir.

Ayrıca SP recompile edilirken blocking’lere sebebiyet vermektedir. Bu yüzden çok fazla recompile olan SP ya da sorguların analiz edilmesinde fayda vardır.

ReCompilation Olaylarının İzlenmesi

SQL Server Profiler - SQL:StmtRecompile - SP:Recompile

Recompilation olayları SQL Server Profiler ile izlenebilmektedir. Bunun için kullanılan olaylar SQL:StmtRecompile ve SP:Recompile olaylarıdır.

SP:Recompile olayı ile recompile olan SP’ler izlenebilir. SQL:StmtRecompile olayı ile ise recompile olan sorgular izlenebilir. Daha önce bahsettiğim gibi SQL Server 2005 sürümünden sonra bir sorgunun recompile edilmesi gerektiğinde SP’nin tamamı değil sadece ilgili sorgu recompile edilmektedir. Dolayısıyla recompile işlemlerini izlemek için SP:Recompile yerine SQL:StmtRecompile olayının kullanılması daha mantıklı olacaktır.

Windows Performance Counter - SQL Re-Compilation/sec

Recomplation işlemleri windows performance counter’lar ile de izlenebilmektedir. Bunun için kullanılan performance counter SQL Re-Compilation/sec performance counter’ıdır. Genelde bu counter’ın toplam batch request’in %10’undan az olması beklenir. Bu değerden daha yüksek değerlerde, recompile olan sorgu veya SP’lerin detaylı incelenmesinde, neden recompile olduklarının analiz edilmesinde fayda vardır. Çünkü tekrar etmek gerekirse compilation işlemi CPU kaynaklarının çok fazla tüketilmesine sebep olabilir.

Activity Monitor

Recompilation olan SP ya da sorguların izlenmesi için kullanılan bir diğer araç ise SQL Server 2008 ile gelen Activity Monitor ekranıdır. Bu ekranda bulunan Recent Expensive Queries kısmındaki Plan Count kolonu ilgili SP’nin ya da sorgunun kaç kez recompile edildiğini göstermektedir.

1

Sys.dm_exec_query_stats – plan_generation_num

Recompilation’ları izlemenin bir diğer yolu da sys.dm_exec_query_stats DMV’sidir. Bu DMV’de bulunan plan_generation_num kolonu ile ilgili sorgunun kaç kez recompile edildiği bulunabilir.

select
	st.text,
	qs.sql_handle,
	qs.plan_generation_num,
	qs.creation_time,
	qs.execution_count,
	st.dbid,
	st.objectid
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where qs.plan_generation_num>1 and st.objectid is not null
order by qs.plan_generation_num desc

 

2

Ayrıca gene bu DMV sonucunda gelen creation_time kolonu vasıtasıyla sorgunun en son ne zaman recompile edildiği bilgisine ulaşılabilmektedir.

Sonuç

SP’nin yeniden derlenmesi bazı durumlarda CPU kaynaklarının çok fazla kullanılmasına neden olabilir. Ayrıca yeniden derleme işlemi sırasında blocking’ler oluşabilir. Bu yüzden recompilation olayları izlenmeli, çok fazla recompile olan sorgu ya da SP’lerin recompile nedenleri analiz edilmelidir.

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