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

Geçtiğimiz günlerde SQL Server Latch Contention üzerine yazmış olduğum makaleleri ve referans kaynakları tek bir blog post’ta toplamanın faydalı olacağı düşündüm.

[more]

Yazdığım 4 makaleyi aşağıda bulabilirsiniz.

  1. SQL Server Latch Contention
  2. SQL Server Latch Contention Analiz Yöntemleri
  3. Latch Contention Senaryoları ve Çözüm Yöntemleri
  4. Last Page Insert Latch Contention’i ve Çözüm Yöntemleri

 

Diğer referans kaynakları ise aşağıda bulabilirsiniz.

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 önce SQL Server Latch Contention hakkında yazdığım makalelerde Latch Contention’ın ne olduğuna, Latch Contention analiz yöntemlerine ve Latch Contention Senaryolarına bakmıştık. Bu makalemde ise Last Page Insert Latch Contention’ını için bir demo yapıp çözüm yöntemlerine bakıyor olacağız.

[more]

Last Page Insert Contention’ini repro etmek için high step adımlarımız şu şekildedir;

  • Sürekli artan (identity, datetime) bir kolona sahip bir tablo create edilir
  • Sürekli artan kolon üzerine clustered index tanımlanır
  • Paralel olarak 50 farklı process ile ilgili tabloya insert yapılır

Repro’yu setup etmek için gerekli script’leri aşağıdaki zip dosyasında bulabilirsiniz.

https://onedrive.live.com/redir?resid=5165D1797A4D83F1!1645&authkey=!AArrT1qtx_CFwTE&ithint=file%2c.zip

Repro’yu çalıştırdıktan sonra ilk olarak sys.dm_os_wait_stats DMV’si ile kümülatif wait tiplerine bakıyoruz.

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',                       N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                    N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

Sys.dm_os_wait_stats direk sorgulandığında son SQL Server restart tarihinden beri toplanan tüm beklemeler sorgulanmış olur. Snapshot olarak bakmak için aşağıdaki script’i kullanabiliriz.

SELECT getdate() as 'Run_Time' --script in calisma zamani
    , wait_type --wait type
	, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --saniye cinsinden bekleme zamani
	, CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(12, 2)) AS pct --toplam beklemeye orani
INTO #tmp_wait_times
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
		'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 
		'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
		'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
		'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 
		'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
		'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
		'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
		'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
		'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
		'RESOURCE_QUEUE' )
ORDER BY 4 DESC
GO

WAITFOR DELAY '00:01:00'
GO

--Delta Sorgula
SELECT getdate() as 'Run_Time' --script in calisma zamani
    , w.wait_type --wait type
	, CAST(w.wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --sn cinsinden toplam bekleme zamani
	, CAST(w.wait_time_ms / 1000. AS DECIMAL(12, 2)) - t.wait_time_s AS wait_time_s --sn cinsinden delta bekleme zamani
	, CAST(100. * (w.wait_time_ms-t.wait_time_s*1000) / SUM(w.wait_time_ms-t.wait_time_s*1000) OVER() AS DECIMAL(12, 2)) AS pct --toplam delta beklemeye orani
FROM sys.dm_os_wait_stats w
LEFT JOIN #tmp_wait_times t on t.wait_type = w.wait_type
WHERE w.wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
		'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 
		'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
		'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
		'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 
		'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
		'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
		'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
		'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
		'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
		'RESOURCE_QUEUE' )

ORDER BY 4 DESC

Benim repro’mda 1 dk’lık sys.dm_os_wait_stats snapshot sonucu aşağıdaki gibidir.

image

En büyük latch tipinin PageLatch_EX olduğunu ve toplam bekleme zamanının %17.34’üne tekabül ettiğini görüyoruz.

2. adım olarak sys.dm_os_waiting_tasks DMV’si ile latch contention yaşanan resource’un ne olduğuna bakıyoruz.

/*WAITING TASKS ordered by wait_duration_ms
*******************************************************************/
SELECT wt.session_id, wt.wait_type
, er.last_wait_type AS last_wait_type
, wt.wait_duration_ms
, wt.blocking_session_id, wt.blocking_exec_context_id, resource_description
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
AND wt.wait_type <> 'SLEEP_TASK' and wt.wait_type like '%LATCH%'
ORDER BY wt.wait_duration_ms desc

image

Contention’ın 16:14:907670 numaralı page’te oluştuğunu görüyoruz. Yani 16.numaralı DB’nin 14 numaralı file’ındaki 907670 nolu page’inde PageLatch_EX contention’i yaşıyoruz.

Bu page’in hangi objeye ait olduğuna ve içindeki kayıtları DBCC Page komutu ile baktığımızda repro’da kullandığımız sampletable olduğunu görüyoruz.

--enable trace flag 3604 to enable console output
dbcc traceon (3604)
--examine the details of the page
--and find objectid
dbcc page (16,14, 907670, -1)
--use found objectid on below query
select object_name(7)

DBCC CheckDB page’lere tek tek bakmak zor olacağı için tüm latch contention yaşanan page’lerin bilgilerini öğrenmek için sys.dm_os_buffer_descriptors script’ini kullanıyoruz.

Page’lerin bilgileri buffer cache’e bakılarak getirildiği için özellikle yüksek memory’e sahip sunucularda bu sorgunun gelmesi zaman alabilir.

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms 
, s.name AS schema_name 
, o.name AS object_name 
, i.name AS index_name 
, resource_description
FROM sys.dm_os_buffer_descriptors bd 
JOIN ( 
SELECT *
--resource_description 
, CHARINDEX(':', resource_description) AS file_index 
, CHARINDEX(':', resource_description, CHARINDEX(':', resource_description)+1) AS 
page_index 
, resource_description AS rd 
FROM sys.dm_os_waiting_tasks wt 
WHERE wait_type LIKE '%LATCH%' 
) AS wt 
ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index) 
AND bd.file_id = SUBSTRING(wt.rd, wt.file_index+1, 1) --wt.page_index) 
AND bd.page_id = SUBSTRING(wt.rd, wt.page_index+1, LEN(wt.rd))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id 
JOIN sys.schemas s ON o.schema_id = s.schema_id
order by wt.wait_duration_ms desc

image

Latch contention’ların dbo.TMP tablosundaki PK_TMP index’i üzerinde yaşandığını görüyoruz.

Ve son olarak sys.dm_exec_requests DMV’sini kullanarak Latch Contention yaratan process’lerin çalıştırdığı sorgulara erişiyoruz.

select DB_NAME(er.database_id) as DBName,
	es.login_name,
	es.host_name,
	st.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,
   er.wait_type,
   er.wait_time,
   er.wait_resource,
   er.blocking_session_id,
   er.status
from sys.dm_exec_requests er
left join sys.dm_exec_sessions es on es.session_id=er.session_id
cross apply sys.dm_exec_sql_text(er.sql_handle) st
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where er.session_id>50 
	and er.session_id!=@@SPID 
	and wait_type like '%latch%'

image

Sorgu detaylarına baktığımızda bizim repro esnasında çalıştırdığımız kodlara erişiyoruz. Aynı zamanda gene bu sorgu sonucunda wait_type, wait_resource ve command bilgilerine de erişebiliyoruz.

 

Latch contention’i gördükten sonra bu contention’i ortadan kaldırmak için önerilen 2 farklı workaround uygulayacağız.

Workaround’ların işe yarayıp yaramadığı kontrolünü ise 1 dakika sürede yapılan insert sayısını karşılaştırarak analiz etmeye çalışacağız.

sp_spaceused 'SampleTable'
GO

WAITFOR Delay '00:01:00'
GO

sp_spaceused 'SampleTable'
GO

Herhangi bir workaround uygulanmamış durumda 1 dk’da 46.247 insert yapılabilmekte.

Aynı zamanda snapshot sys.dm_os_wait_stats kümülatif wait sonuçlarınıda karşılaştıracağız.

Workaround detayları ve sonuçları aşağıdaki gibidir.

 

Dummy kolon yöntemi ile last page insert contention çözümü

Bu yöntemde ana amaç büyük boyutlu dummy bir kolon kullanarak bir page’in içerisine sığabilecek kayıt sayısını azaltıp birden fazla process’in aynı page’e erişme ihtimalini düşürerek contention’i azaltmaktır.

Bu yöntem page içerisindeki kayıt sayısını azaltarak contention’i çözebilir fakat diğer taraftan daha fazla page kullanımına sebebiyet vereceğinden dolayı performans sıkıntısı oluşturabilir. Dolayısıyla bu noktada performans testlerinin de yapılması ve contention-performans karşılaştırması yapılması önem arzetmektedir.

Yöntemi uygulamak için SampleTable’a char(4000) şeklinde yeni bir kolon ekliyoruz.

ALTER TABLE SampleTable ADD c3 char(4000)
GO

Ve daha sonra repro’yu tekrar çalıştırıyoruz ve sys.dm_os_wait_stats snapshot’ına bakıyoruz.

SELECT getdate() as 'Run_Time' --script in calisma zamani
    , wait_type --wait type
	, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --saniye cinsinden bekleme zamani
	, CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(12, 2)) AS pct --toplam beklemeye orani
INTO #tmp_wait_times
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
		'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 
		'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
		'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
		'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 
		'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
		'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
		'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
		'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
		'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
		'RESOURCE_QUEUE' )
ORDER BY 4 DESC
GO

WAITFOR DELAY '00:01:00'
GO

--Delta Sorgula
SELECT getdate() as 'Run_Time' --script in calisma zamani
    , w.wait_type --wait type
	, CAST(w.wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --sn cinsinden toplam bekleme zamani
	, CAST(w.wait_time_ms / 1000. AS DECIMAL(12, 2)) - t.wait_time_s AS wait_time_s --sn cinsinden delta bekleme zamani
	, CAST(100. * (w.wait_time_ms-t.wait_time_s*1000) / SUM(w.wait_time_ms-t.wait_time_s*1000) OVER() AS DECIMAL(12, 2)) AS pct --toplam delta beklemeye orani
FROM sys.dm_os_wait_stats w
LEFT JOIN #tmp_wait_times t on t.wait_type = w.wait_type
WHERE w.wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
		'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 
		'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
		'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
		'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 
		'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
		'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
		'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
		'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
		'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
		'RESOURCE_QUEUE' )

ORDER BY 4 DESC

image

Burada ilginç olan PageLatch wait type’ların artmış olmasıdır. Fakat yapılan insert değerlerine bakılınca 57.184 insert (orj. hali 46.247) yapılabildiği yani insert performansının arttığını görüyoruz.

Gene de Latch contention’lar devam ettiği için bu workaround’un uygun bir workaround olmadığını görüyoruz.

 

Hash partitioning yöntemi ile last page insert contention çözümü

Bu workaround’da ana amaç tablodaki bir kolon üzerinden oluşturulan bir hash value üzerine partitioning yapmaktır. Bu şekilde gelen insert’ler sıralı olarak farklı partition’lara dağılacak ve hep aynı page’e erişim olmayacağından dolayı contention çözümlenmiş olacaktır.

Partitioning yapıldığı için select sorgularının performansının analiz edilmesi önemli bir noktadır. Ayrıca hali hazırda partitioning kullanan bir tabloda bu yöntem uygulanamamaktadır.

Workaround’u uygulamak için adımlar aşağıdaki gibidir.

  1. Partition function ve function’i kullanacak bir partition scheme tanımlıyoruz. Partition sayısını eğer Core CPU sayımız 32’den az ise Core CPU sayısı kadar yapabiliriz. Eğer 32’den fazla ise 32 ile sınırlıyoruz. Ben bu senaryoda 16 partition kullanacağım.
  2. Tabloya partitioning’i üzerine tanımlayacağımız bir computed kolon ekliyoruz ve değerini tablodaki otomatik artan kolondan hesaplatıyoruz.
  3. Son olarak tablodaki clustered index’i eski clustered index key’i + hash value üzerine partition olacak şekilde oluşturuyoruz.
USE DB_LastPageInsertContention
GO

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16] (tinyint) AS RANGE LEFT FOR VALUES
(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)

CREATE PARTITION SCHEME [ps_hash16] AS PARTITION [pf_hash16] ALL TO ( [primary] )

-- Add the computed column to the existing table (this is an OFFLINE operation)
-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[SampleTable]
ADD [HashValue] AS (CONVERT([tinyint], abs(binary_checksum([c1])%(16)),(0))) 
PERSISTED NOT NULL

--Create the index on the new partitioning scheme 
CREATE UNIQUE CLUSTERED INDEX [CI_1] 
ON [dbo].[SampleTable]([c1] ASC, [HashValue]) 
ON ps_hash16(HashValue)

Tanımlamaları yaptıktan sonra repro’yu tekrar çalıştırıp ilk olarak sys.dm_os_wait_stats snapshot’ına bakıyoruz.

SELECT getdate() as 'Run_Time' --script in calisma zamani
    , wait_type --wait type
	, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --saniye cinsinden bekleme zamani
	, CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(12, 2)) AS pct --toplam beklemeye orani
INTO #tmp_wait_times
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
		'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 
		'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
		'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
		'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 
		'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
		'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
		'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
		'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
		'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
		'RESOURCE_QUEUE' )
ORDER BY 4 DESC
GO

WAITFOR DELAY '00:01:00'
GO

--Delta Sorgula
SELECT getdate() as 'Run_Time' --script in calisma zamani
    , w.wait_type --wait type
	, CAST(w.wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --sn cinsinden toplam bekleme zamani
	, CAST(w.wait_time_ms / 1000. AS DECIMAL(12, 2)) - t.wait_time_s AS wait_time_s --sn cinsinden delta bekleme zamani
	, CAST(100. * (w.wait_time_ms-t.wait_time_s*1000) / SUM(w.wait_time_ms-t.wait_time_s*1000) OVER() AS DECIMAL(12, 2)) AS pct --toplam delta beklemeye orani
FROM sys.dm_os_wait_stats w
LEFT JOIN #tmp_wait_times t on t.wait_type = w.wait_type
WHERE w.wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
		'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 
		'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
		'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
		'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 
		'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
		'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
		'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
		'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
		'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
		'RESOURCE_QUEUE' )

ORDER BY 4 DESC

image

Yukarıdaki çıktıda da görüldüğü gibi Latch’ler oldukça küçük değerlere gelmiş durumda.

1 dk’da yapılan insert adetine baktığımızda ise 69.397 (orjinal hali 46.247) insert yapıldığını görüyoruz.

Dolayısıyla hash partitioning workaround’unun bu problem için en uygun çözüm olduğunu görüyoruz.

Son olarak problemin orjinal halinin ve workaround’ların wait stats ve insert adeti olarak karşılaştırmalarına bakalım.

 

 

Orijinal

Workaround1
(dummy Kolon)

Workaround 2
(Hash Partitioning)

Latch Wait Type’ların Toplam Oranı

% 18.69

% 42.18

% 0.22

1 dk’da yapılan insert toplamı

46.247

57.184

69.397

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 2 makalemde Latch Contention’ın ne olduğuna ve Latch Contention analiz yöntemlerine bakmıştık. Bu makalemde ise en sık rastlanan Latch Contention senaryolarına ve çözüm yöntemlerine bakıyor olacağız.

[more]

Last page/trailing page insert contention

Sürekli artan bir kolon üzerine index tanımlanan tablolarda yeni gelen insert’ler ilgili index’in hep en son page’ine yapılacaktır. Eğer çok fazla process aynı anda insert işlemi gerçekleştiriyorsa son page üzerinde contention olacaktır.

Örneğin ATM hareketlerinin tutulduğu bir tablomuz olduğunu ve bu tablodaki sequential TransactionId kolonu üzerinde clustered index olduğunu düşünelim. Böyle bir tabloda tüm ATM’lerden yapılan insert’ler hep son page’e yapılacağı için latch contention olma ihtimali çok yüksektir. Aşağıdaki resimde bu durum gösterilmiştir.

image

Bu contention için aşağıdaki workaround’lar uygulanabilir.

  • Index’in sıralaması değiştirilerek insert’lerin distribution’i sağlanabilir. Yukarıdaki ATM tablosu örneği için konuşacak olursa ATMID kolonu clustered index’in başına konularak clustered index ATMID,TransactionID şeklinde değiştirilebilir. Bu şekilde aynı anda aynı ATM’den işlem olmayacağı için ve toplamda tüm işlemler farklı page’lere distribute edileceği için contention problem aşılabilir. Fakat leading kolon olarak non-sequential bir kolon seçildiği için Page Split ve index fragmentation ile karşılaşılacaktır.
  • Yeni bir hash value kolon tanımlanarak bu kolon üzerinden partitioning yapılır. (Bir sonraki makalemde bu workaround üzerine bir demo yapıyor olacağız)

 

Latch contention on small tables with a non-clustered index and random inserts (queue table)

Bu tarz tablolarda hem aynı page’e yazma isteğinden hem de index’teki level sayısının az olmasından kaynaklı page split operasyonlarından dolayı latch contention yaşanabilir. Page split yapıldığında tüm B-Tree’ye Latch_SH ve page split yapılacak page’e ise Latch_EX konulur. Sys.dm_db_index_operational_stats DMV’si ile çok fazla page split yaşanan index’ler analiz edilebilir.

Eğer çok fazla insert-delete operasyonu olursa B-Tree root splits de görülebilir. Bu durumda split işlemi bitene kadar tüm Latch_EX istekleri yani tüm insert-update-delete operasyonları bekler. Bu durum sys.dm_os_latch_stats DMV’sinde bulunan ACCESS_METHODS_HBOT_VIRTUAL_ROOT latch tipi analiz edilerek bulunabilir.

 

Latch contention on allocation bit map pages (GAM-SGAM-PFS)

Object allocation&deallocation (örneğin temp table) işlemi TempDB’de diğer DB’lerden daha sık yapıldığı için bu tarz contention’lara genelde TempDB’de rastlanır.

TempDB üzerinde yapılan allocation ve deallocation işlemlerinde PFS, GAM ve SGAM page’leri kullanılır. Çok yoğun temp table kullanılan workload’larda sürekli ufak boyutlu temp table’lar create ve drop edildiğinde bu page’ler üzerinde contention görülebilir. (Örneğin 2:1:3 SGAM page’inde PAGELATCH_XX wait tipi görülebilir)

Workaround olarak TempDB best practice’leri uygulanabilir. Bunun için daha önce yazdığım şu makaleye bakmanızı tavsiye ederim.

 

Bu makalemde en sık rastlanan SQL Server Latch Contention senaryolarına ve çözüm yöntemlerine baktık. Bir sonraki makalemde gerçek bir senaryo üzerinde Last Page Insert Latch Contention’ına ve Workaround’larına bakıyor olacağız.

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 SQL Server Latch ve Latch contention kavramından bahsetmiştim. Bu makalemde ise Latch Contention’ı nasıl analiz edebileceğimize bakıyor olacağız.

[more]

Latch contention analizi için SQL Server’ın sunduğu birden fazla analiz tool’u ve tekniği vardır. Bu makalede özellikle DMV’ler üzerinde duruyor olacağız.

DMV’lere tek tek bakmaktansa bir Latch Contention oluşturup gerçek bir contention üzerinden DMV detayları göstermenin daha anlaşılır olacağını düşünüyorum.

Bunun için TempDB’de sürekli bir tablo create-drop eden bir scripti 50 farklı process ile çalıştırıcaz ve ilgili DMV’leri inceleyeceğiz. Latch contention’i repro etme scriptleri aşağıdaki zip file’ın içerisinde bulunmaktadır.

https://onedrive.live.com/redir?resid=5165D1797A4D83F1!1644&authkey=!AEb375Fjb1ZcJ0E&ithint=file%2c.zip


Sys.dm_exec_wait_stats

Bu DMV ile SQL Server’ın son restart tarihinden itibaren tüm bekleme zamanlarını kümülatif olarak olarak görülebilir.

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',                       N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                    N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

 

image

Sys.dm_os_wait_stats DMV’si son SQL Server çalışma zamanından itibaren tüm değerleri kümülatif olarak getirdiği için snapshot olarak analiz yapmak daha mantıklıdır. Snapshot analizi için aşağıdaki makaledeki script kullanılabilir.

http://www.turgaysahtiyan.com/post/SQL-Server-e28093-Her-Gun-1-DMV-Gun-6-sysdm_os_wait_stats.aspx

Snapshot olarak bakıldığında aşağıdaki gibi bir result set ile karşılaşıyoruz.

image

En yüksek 2 bekleme tipimizin PageLatch_EX ve PageLatch_SH olduğunu görüyoruz ve bu 2 wait’in toplam beklemelere oranı %92 civarında. Şimdi biraz daha detaya inip wait resource’ları ve script detaylarını görmeye çalışacağız.

 

Sys.dm_os_waiting_task

Genel beklemelere bakıp herhangi bir latch problemi olup olmadığını analiz ettikten sonra bir sonraki adım anlık olarak hangi process’in hangi latch tipinde beklediğine ve hangi resource’u beklediğine bakmak için sys.dm_os_waiting_task DMV’sini kullanıyoruz.

/*WAITING TASKS ordered by wait_duration_ms
*******************************************************************/
SELECT wt.session_id, wt.wait_type
, er.last_wait_type AS last_wait_type
, wt.wait_duration_ms
, wt.blocking_session_id, wt.blocking_exec_context_id, resource_description
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
AND wt.wait_type <> 'SLEEP_TASK' and wt.wait_type like '%LATCH%'
ORDER BY wt.wait_duration_ms desc

Bu DMV ile process’lerin hangi DB’deki hangi Page için latch beklediğine erişerek Page bilgisine erişmiş oluyoruz.

image

Görüldüğü gibi beklemelerin çoğu 2:1:576 page’inde yani 2 id’io database(tempdb)’in 1 nolu file’ındaki 576 nolu page’inde.

Bulunan page’in hangi table’a ait olduğuna DBCC CheckDB komutu ile bakabiliriz.

--enable trace flag 3604 to enable console output
dbcc traceon (3604)
--examine the details of the page
dbcc page (2,1, 576, -1)

 

Sys.dm_os_buffer_descriptors

DBCC CheckDB page’lere tek tek bakmak zor olduğu için tüm latch contention yaşanan tüm page’lerin ve index’lerin bilgilerini öğrenmek için sys.dm_os_buffer_descriptors DMV’sini kullanabiliriz.

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms 
, s.name AS schema_name 
, o.name AS object_name 
, i.name AS index_name 
, resource_description
FROM sys.dm_os_buffer_descriptors bd 
JOIN ( 
SELECT *
--resource_description 
, CHARINDEX(':', resource_description) AS file_index 
, CHARINDEX(':', resource_description, CHARINDEX(':', resource_description)+1) AS 
page_index 
, resource_description AS rd 
FROM sys.dm_os_waiting_tasks wt 
WHERE wait_type LIKE '%LATCH%' 
) AS wt 
ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index) 
AND bd.file_id = SUBSTRING(wt.rd, wt.file_index+1, 1) --wt.page_index) 
AND bd.page_id = SUBSTRING(wt.rd, wt.page_index+1, LEN(wt.rd))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id 
JOIN sys.schemas s ON o.schema_id = s.schema_id
order by wt.wait_duration_ms desc

image

Gördüğünüz gibi latch contention’ların tamamı tempdb’de bulunan sysallocunits tablosunda yaşanmakta.

Bu DMV beklemelere anlık olarak baktığı için script çalıştırıldığı anda bir latch’e denk gelmeme ihtimali olabilir. Bunun için aşağıdaki script kullanılarak belirli bir periyotta tüm latch beklemelerini bulup son olarak sys.dm_os_buffer_descriptors ile tablo ve index detaylarına erişebiliriz.

/*
Query Buffer Descriptors to Determine Objects Causing Latch Contention
*/

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [name] like '#WaitResources%') 
	DROP TABLE #WaitResources;

CREATE TABLE #WaitResources (session_id INT, wait_type NVARCHAR(1000), wait_duration_ms INT
		,resource_description sysname NULL, db_name NVARCHAR(1000), schema_name NVARCHAR(1000),object_name NVARCHAR(1000)
		,index_name NVARCHAR(1000));
GO

declare @WaitDelay varchar(16), @Counter INT, @MaxCount INT, @Counter2 INT
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'-- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
	INSERT INTO #WaitResources(session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
	SELECT wt.session_id,
		wt.wait_type,
		wt.wait_duration_ms,
		wt.resource_description
	FROM sys.dm_os_waiting_tasks wt
	WHERE wt.wait_type LIKE 'PAGELATCH%' AND wt.session_id <> @@SPID
--select * from sys.dm_os_buffer_descriptors
	SET @Counter = @Counter + 1; 
	WAITFOR DELAY @WaitDelay;
END;

--select * from #WaitResources
update #WaitResources 
	set db_name = DB_NAME(bd.database_id),
	schema_name = s.name,
	object_name = o.name,
	index_name = i.name
FROM #WaitResources wt
JOIN sys.dm_os_buffer_descriptors bd
	ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
		AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description,	CHARINDEX(':', wt.resource_description) +1 ) - CHARINDEX(':', wt.resource_description) - 1)
		AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) +1 ) + 1, LEN(wt.resource_description) + 1)
--AND wt.file_index > 0 AND wt.page_index > 0
JOIN sys.allocation_units au ON bd.allocation_unit_id = AU.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id

select * from #WaitResources order by wait_duration_ms desc
GO

Uyarı : sys.dm_os_buffer_descriptors DMV’si page bilgileri buffer cache’e bakarak bulduğu için özellikle yüksek memory’e sahip sunucularda bu sorgunun gelmesi zaman alabilir.

 

Sys.dm_exec_requests

Şu ana kadar kullanılan DMV’ler ile hangi process’in hangi tablo üzerinde latch’de beklediği bilgilerini bulmuş olduk. İlgili process’lerin problem anında çalıştırdığı sorguları bulmak için ise sys.dm_exec_requests DMV’sini kullanabiliriz.

select DB_NAME(er.database_id) as DBName,
	es.login_name,
	es.host_name,
	st.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,
   er.wait_type,
   er.wait_time,
   er.wait_resource,
   er.blocking_session_id,
   er.status
from sys.dm_exec_requests er
left join sys.dm_exec_sessions es on es.session_id=er.session_id
cross apply sys.dm_exec_sql_text(er.sql_handle) st
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where er.session_id>50 
	and er.session_id!=@@SPID 
	and wait_type like '%latch%'

image

Latch contention’da bekleyen script’lerin reproda çalıştırdığımız script’ler olduğunu görüyoruz.

 

Sonuç

Bu makalemde Latch contention’ın nasıl analiz edilerek resource, page, table, index ve script bilgilerine erişilebileceğini gördük. Bir sonraki makalemde Latch Contention Senaryoları ve Çözüm Yöntemlerine bakıyor olacağız.

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


Latch Contention nedir?

Latch’ler, in-memory structure’daki data, index page ya da B-Tree’nin leaf level’indaki page’ler gibi internal objelerdeki tutarlığı korumak amacıyla SQL Server tarafından kullanılan bir senkronizasyon özelliğidir. Örneğin aynı anda 2 farklı process memory’deki bir page’i değiştirmek istediğinde SQL Server ilgili page’e ilk isteyen process için latch (PageLatch_EX) koyar ve diğer page’in de aynı anda değişiklik yapmasına izin vermeyerek page’in tutarlılığını korumuş olur. Aksi durumda 2 process aynı anda page’i değiştirebilseydi page’in tutarlılığı bozulmuş olacaktı.

Aynı durum memory’de olmayan bir page’in diskten memory’e getirilmesi esnasında da yaşanabilir. Örneğin bir process bir kaydı değiştirmek istediğinde SQL Server ilk olarak bu page’in memory’de olup olmadığına bakar ve eğer yoksa diskten okuyarak memory’e alır. Okuma devam ederken bir başka process aynı page’e erişmek isterse ilk process’in okuma operasyonunu bitirmesini beklemek zorundadır (PageIOLatch_EX). Aksi halde 2 process aynı anda page’e erişir ve memory’e alırsa, memory structure’ın da tutarsızlık olacaktır.

Eğer DB Server’da çok fazla CPU var ise bu şekilde aynı objeye parallel olarak erişen process sayısı artacaktır. Yukarıdaki bahsettiğim gibi bir process yoluna devam edebilmesi için ilgili objede latch olmaması derektiğinden dolayı çok fazla process aynı objeye erişmek istediğinde bu process’ler arasında çekişme yani contention olabilir.

Bir başka deyişle, Latch contention, birden fazla process’in aynı anda aynı in-memory yapısı üzerine birbiriyle uyumsuz latch’leri koymak istediğinde yaşanır.

[more]

Latch ve Lock Arasındaki Farklar

Buraya kadar anlatılan kısımda Latch’ler ile lock’ların birbirlerine oldukça benzedikleri görülmektedir. Fakat aralarında çok önemli bir fark bulunmaktadır.

Örneğin bir page’in içerisindeki kayıtların değiştirilme operasyonundan bahsedelim. Bu şekildeki bir operasyonda lock operasyonu (X-Lock) transaction’ın bitimine kadar konulurken, SQL Server latch’i (PageLatch_EX ya da PageIOLatch_EX) sadece fiziksel operasyon bitimine kadar koyar. Yani ilgili page’deki fiziksel işlem (okuma, yazma vs.) bittiğinde latch kalkar ve diğer process’ler page’e fiziksel olarak erişmeye devam edebilirken, transaction devam ettiği sürece lock page’in üzerinde kalır.

Dolayısıyla latch’ler SQL Server tarafından memory tutarlılığını korumak için otomatik olarak kullanılırken (yani latch konusunda kullanıcının herhangi bir insiyatifi yoktur), lock’lar logical transaction tutarlığını korumak için kullanılır ve kullanıcılar isolation level’lar ile oynayarak lock konulma sürelerini değiştirebilir.

Latch SQL Server tarafından otomatik olarak konulduğu gibi analiz anlamında da Lock analizinden farklılık göstermektedir. Blocking analizinde ana amaç resource’u lock’layan ana process’i ve yaptığı işi bulmaya yöneliktir. İlgili process bulunduğunda neden uzun süreli lock koyduğu analiz edilebilir. Latch analizinde ise latch fiziksel bir operasyon olduğu için özellikle page’i “t” anında latch ile tutan process’i bulmanın pek bir esprisi yoktur. Çünkü çoğu durumda bu process sürekli değişmekte ve t anında latch koymak için bekleyen bir process t+1 anında diğerlerine engelleyen process olmaya başlayabilir. Bu yüzden latch koymak için bekleyen process’lerin analizini yapmak daha mantıklıdır. Latch contention analizi bölümünde bu konuya daha detaylı olarak değiniyor olacağız.

 

SQL Server Latch Modları ve Uyumlulukları

Beraber çalışabilirliği maksimize etmek ve tutarlığı sağlamak amacıyla SQL Server değişik tiplerde latch’ler kullanılır. Aşağıda 5 farklı latch mode’un detayları bulunmaktadır.

  • KP (Keep latch) : Keep latch, üzerine konulduğu page’in destroy edilmesini engellemek için örneğin memory yapısına bakılma operasyonu anında kullanılır. KP latch destroy latch hariç diğer bütün latch mode’lar ile compatible yani uyumlu olduğu için en hafif latch tipidir. Yani örneğin bir page üzerinde KP latch varken başka bir process ilgili page’e PageLatch_EX koyarak değiştirebilir ya da PageLatch_SH koyarak okuyabilir. Ama KP latch devam ederken başka bir process ilgili page’i memory’den atamaz yani destroy latch (PageLatch_DT) koyamaz.
  • SH (Shared Latch) : Page’in okunması esnasında konulur.
  • UP (Update Latch) : Update latch SH ve KP latch ile uyumluyken diğer latch mode’larıyla uyumlu değildir ve bir başka process’in EX latch koymasına yani page’i değiştirmesine izin vermez.
  • EX (Exclusive Latch) : EX latch konulan page diğer process’ler tarafından okunamaz ya da yazılamaz.
  • DT (Destroy Latch) : Bir page memory’den destroy edilmeden önce destroy latch konulması gerekir. Örneğin lazywriter operasyonu ilgili page’i memory’den atmadan önce DT latch konulur. (Lazywriter operasyonu ile ilgili detaylı bilgi için şu makaleye bakabilirsiniz.)

Latch mode’ların birbiriyle uyumlulukları farklılık göstermektedir. Örneğin Shared Latch bir başka Shared Latch ile uyumluyken yani 2 process aynı anda bir page’i fiziksel olarak okuyabilirken, Exclusive Latch bir başka exclusive latch ile uyumlu değildir yani 2 process aynı anda bir page’i fiziksel olarak değiştiremez.

Yukarıda bahsettiğimiz 5 latch mode’unun birbiri ile uyumluluk matrisi aşağıdaki gibidir.

image

 

Latch Wait Tipleri

SQL Server’da temel olarak 3 latch wait tipi vardır.

  1. Buffer (BUF) Latch : Memory’deki kullanıcı objelerine ait index ve data page’lerinin tutarlığını korumak için kullanılır. Aynı zamanda PFS, GAM ve SGAM gibi SQL Server sistem objelerinin de birden fazla process tarafından erişilmek istendiğinde tutarlık gene Buffer Latch’ler tarafından sağlanır. SQL Server’daki kümülatif wait’lerin analizi için kullanılan Sys.dm_os_wait_stats DMV’si ile bakıldığında PageLatch_* wait tipleri olarak tek tek görülebilir. (* bir önceki bölümde anlatılan 5 latch mode’undan biri olabilir. Örneğin PageLatch_SH ya da PageLatch_EX gibi.)
  2. IO Latch : Memory’de olmayan bir page üzerinde işlem yapılmak istendiğin page’in ilk olarak diskten okunup memory’e alınması gerekmektedir. İşte bu esnada ilgili page’e IO latch konulur. Sys.dm_os_wait_stats DMV’sinde PageIOLatch_* olarak görülür.

    Bu aşamada örneğin page’in içerisindeki kayıtların birden fazla process ile aynı anda değiştirilmesi operasyonunda hangi latch tiplerinin konulduğuna bakalım.

    Bir thread bir Page’i okuduğunda eğer bu page memory’de yoksa ilk olarak PageIOLatch_Ex konulur. Bunun sebebi başka bir thread’in Shared Latch dahi koymasını yani okuma bile yapmasını engellemektir. Aynı anda bu page’i başka bir thread okumak isterse page için disk aktivitesi yapıldığı için bu threadler PageIOLatch_SH’de bekliyor olacaktır.

    Yukarıdaki örnek eğer memory’de olan bir page için yapılsaydı bu sefer wait tipleri BUF latch olacak yani PageLatch_EX ve PageLatch_SH kullanılacaktı. (Daha fazla detay için -> http://blogs.msdn.com/b/psssql/archive/2009/07/08/q-a-on-latches-in-the-sql-server-engine.aspx)
  3. Non-Buffer (Non-Buf) Latch : Yukarıdakiler dışında kalan in-memory yapılar için kullanılan latch wait tipleridir. Toplu olarak Sys.dm_os_wait_stats DMV’sinde Latch_* olarak görülebilir. Latch tipi detayına inmek için sys.dm_os_latch_stats DMV’si analiz edilmelidir.

 

Bir sonraki makalemde Latch Contention analiz yöntemlerine bakıyor olacağız.

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