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

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


Geçenlerde SQL Server 2005 Performance Dashboard raporlarını incelerken ana ekranda olan bir rapor dikkatimi çekti. CPU utilization’ı görebildiğimiz bu rapor da dakika dakika CPU kullanımını görebilmekteyiz.

Hemen ilk aklıma gelen Performance Counter ile bu bilginin alındığıydı. Fakat daha sonra düşündüğümde bu raporu almadan herhangi bir performance counter toplamadığımdı. Bu durumda bu rapor bir şekilde geriye dönük olarak CPU bilgilerini getirebilmekte, çok büyük ihtimal de DMV üzerinden bu bilgiye erişmekte ki bu beni oldukça şaşırttı ve heyecanlandırdı.

[more]

Daha sonra konu üzerinde inceleme yapınca ve MS’ten Işıl’ın da yardımlarıyla CPU utilization bilgilerinin sys.dm_os_ring_buffers DMV’sinden alındığını gördük.

sys.dm_os_ring_buffers DMV’si CPU utilization bilgilerini dakika dakika olarak tutmakta. Bu DMV vasıtasıyla son 256 veriye erişebilmekteyiz. Yani bu DMV’den yaklaşık olarak son 4 saatin dakika dakika CPU utilization bilgilerini alabiliriz.

Sorgumuz aşağıdaki gibi. Sorgu hem 2005 hem de 2008 sunucularda kullanılabilmekte. Tek farklılık 2 ve 3. satırlarda. Zaten sorguda ilgili notuda düşmüş durumdayım.

declare @ts_now bigint
select @ts_now =  cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info --SQL Server 2008
--select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info --SQL Server 2005
select top 50 record_id,
    dateadd(ms, -1 * ((@ts_now - [timestamp])), GetDate()) as EventTime, 
    SQLProcessUtilization,
    SystemIdle,
    100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
    select 
          record.value('(./Record/@id)[1]', 'int') as record_id,
          record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
          record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
          timestamp
    from (
          select timestamp, convert(xml, record) as record 
          from sys.dm_os_ring_buffers 
          where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
          and record like '%%') as x
    ) as y 
order by record_id desc

Örneğin bir sunucumdaki son 60 dakikalık CPU utilization raporu aşağıdaki gibi.

cu1

Sorgu sonucunda gelen kolonlar için kısa bilgiler vermemiz gerekirse;

  • SQLProcessUtilization : SQL Server Exe’nin kullandığı CPU miktarı
  • SystemIdle : Boşta olan CPU miktarı
  • OtherProcessUtilization : Diğer exe’lerin kullandığı CPU miktarı.

 

İ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