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
<<  Ekim 2017  >>
PaSaÇaPeCuCuPa
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345
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


Periyodik olarak yapılan DBA işlemlerinden biri de sys.dm_db_index_usage_stats ile index’lerin kullanım istatistiklerini analiz etmektir. Bu DMV sayesinde az kullanılan ya da kullanılmayan index’ler bulunup drop edilmesine karar verilebilir.

Yalnız bu noktada kontrol edilmesi gereken çok önemli bir nokta silinmek istenen index’in stored procedure, function ya da view gibi SQL Server objelerinin içinde force edilip edilmediğidir. Çünkü eğer silinmek istenen index örneğin bir SP’nin içinde force edildiyse, index silindikten sonra ilgili SP çağrıldığında hata alacaktır.

[more]

Aşağıdaki script ile index force kullanılmış SQL Server objelerini bulabilirsiniz.

--Find SQL Server Objects which use index force
create table #tmp1 (DBName sysname,object_id int, name sysname, type varchar(3), type_desc varchar(50), OriginalText varchar(max), text varchar(max))
GO

insert #tmp1
	select db_name(),sm.object_id,o.name,o.type,o.type_desc
		  ,definition as OriginalText 
		  ,replace(
			replace(
				replace(
					replace(
						replace(
							replace(replace(upper(definition),' INDEX ','(INDEX')
						,'INDEX ','(INDEX'),'	','')
					,'CHARINDEX','')
				,'PATINDEX','')
			,'CHARINDEX','')
		,'PATINDEX','') as text
	from sys.sql_modules sm
	left join sys.objects o on o.object_id=sm.object_id
	where o.type in ('AF','FN','P','IF','FS','FT','PC','RF','TA','TF','TR','V','X')

select * 
from #tmp1 t
where t.text like '%(Index(%' or t.text like '%(Index=%' 
order by t.name

 

Şimdi yukarıdaki script’i denemek için bir test yapalım. Bunun için önce bir çalışma tablosu oluşturup daha sonra bu tabloyu kullanan 2 SP oluşturalım. SP’lerin ilkinde index force kullanırken ikincisinde kullanmayacağız.

--create a work database
Create database DBindexForce
GO
use DBindexForce
GO
--create a work table
Create table tblindexForce(col1 int, col2 char(10))
GO
create clustered index IX_1 on tblindexForce (col1)
GO

--Create 2 work SPs
--First one uses forced index
create proc mySP1
as
	select * from tblindexForce with(Index = IX_1)
GO

create proc mySP2
as
	select * from tblindexForce
GO

--Find SQL Server Objects which use index force
create table #tmp1 (DBName sysname,object_id int, name sysname, type varchar(3), type_desc varchar(50), OriginalText varchar(max), text varchar(max))
GO

insert #tmp1
	select db_name(),sm.object_id,o.name,o.type,o.type_desc
		  ,definition as OriginalText 
		  ,replace(
			replace(
				replace(
					replace(
						replace(
							replace(replace(upper(definition),' INDEX ','(INDEX')
						,'INDEX ','(INDEX'),'	','')
					,'CHARINDEX','')
				,'PATINDEX','')
			,'CHARINDEX','')
		,'PATINDEX','') as text
	from sys.sql_modules sm
	left join sys.objects o on o.object_id=sm.object_id
	where o.type in ('AF','FN','P','IF','FS','FT','PC','RF','TA','TF','TR','V','X')

select * 
from #tmp1 t
where t.text like '%(Index(%' or t.text like '%(Index=%' 
order by t.name

--CleanUp
Use master
GO
drop table #tmp1
GO
drop database DBindexForce
GO

 

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

image

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


SQL Server’da performans açısından belki de en önemli noktalardan biri sistem database’lerinden biri olan TempDB’nin iyi bir şekilde configure edilmesi ve yönetilmesidir. Bugünkü yazımda TempDB ve TempDB best practice’leri üzerine konuşuyor olacağız.

[more]

TempDB Nedir?

TempDB, SQL Server kuruluşu ile beraber gelen 4 sistem database’inden biridir. Her instance için 1 adet olan TempDB’de örnek olarak aşağıdaki bilgiler tutulur:

  • Kullanıcı tarafından create edilen temp table, table variable ya da cursor gibi geçici objeler
  • SQL Server tarafından sort, spool, hash join gibi işlemler için create edilen work table ya da ara sonuçlar gibi internal objeler
  • Read committed snapshot isolation ya da snapshot isolation özelliği kullanıldığında generate edilen row versioning bilgileri
  • Online index operation işlemlerinde tutulan row versioning bilgileri TempDB’de tutulurlar.

Yukarıda da görüldüğü gibi hem kullanıcı hem de internal işlemler için çok fazla durumda kullanılan TempDB bazı durumlarda performans sıkıntısı oluşturabilmektedir. Bu yüzden çok iyi configure ve monitor edilmelidir.

Ayrıca daha önce de belirttiğim gibi tüm instance için sadece 1 adet olan TempDB, konfigurasyonun ve monitoring’in önemini daha da arttırmaktadır.

 

TempDB Best Practice’leri

Bu bölümde TempDB için uyguladığımız aşaıdaki best practice’lerden bahsediyor olacağım.

  • TempDB Data File Sayısı
  • TempDB Data File’larının Aynı Boyutta Olması
  • TempDB File’larının Bulunduğu Storage
  • Yoğun TempDB Kullanımının Analiz Edilmesi
  • T1118 Trace Flag’i

TempDB Data File Sayısı

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

Allocation bitmap page’lerinde oluşan bu contention’i azaltmak için 1’den fazla tempdb data file’i kullanılabilir. Bu şekilde page sayısı arttırılacak ve round robin kullanım sayesinde contention azaltılacaktır.

TempDB data file sayısının best practice’i workload’a ve core CPU sayısına göre değişiklik göstermektedir. Bu konu üzerinde internette çok fazla best practice’e rastlayabilirsiniz. Benim kullandığım ise şu şekilde;

  • Core CPU sayısı 8’den az ise core cpu sayısı kadar data file yapmak
  • Core CPU sayısı 8’den fazla ise TempDB data file sayısının 8 yapılması ve wait type’larda PAGELATCH_XX wait type’ının analiz edilmeye devam edilmesidir. Contention görülmeye devam edildiği sürece data file sayısı her defasında 4 arttırılabilir.
“ But there’s now even better guidance, and what I also recommend. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL Product Support, espoused a new formula: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time.”
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

Bu noktada akla TempDB data file sayısını her defasında neden 4’er 4’er arttırıp tek seferde 32 ya da 40 yapmadığımız sorusu gelebilir. TempDB data file sayısının fazla olması da performance’a eksi yönde etki eden bir durumdur. Bu yüzden belirli bir sayıdan (8) başlanarak contention görüldüğü sürece (contention analizi için sys.dm_os_wait_stats DMV’si sonucunda çıkan result set’teki PAGELATCH_XX wait type’I gözlenebilir) data file sayısının arttırılması daha iyi bir yaklaşımdır.

Temp DB data file sayısının çok olmasının performans üzerindeki etkisi için aşağıdaki yazıya göz atılabilir;

“So, why is one-to-one not always a good idea? Too many tempdb data files can cause performance problems for another reason. If you have a workload that uses query plan operators that require lots of memory (e.g. sorts), the odds are that there won’t be enough memory on the server to accommodate the operation, and it will spill out to tempdb. If there are too many tempdb data files, then the writing out of the temporarily-spilled data can be really slowed down while the allocation system does round-robin allocation. The same thing can happen with very large temp tables in tempdb too.

Why would round-robin allocation cause things to slow down for memory-spills to tempdb with a large number of files? A couple of possibilities:

Round-robin allocation is per filegroup, and you can only have one filegroup in tempdb. With 16, 32, or more files in tempdb, and very large allocations happening from just a few threads, the extra synchronization and work necessary to do the round-robin allocation (looking at the allocation weightings for each file and deciding whether to allocate or decrement the weighting, plus quite frequently recalculating the weightings for all files – every 8192 allocations) starts to add up and become noticeable. It’s very different from lots of threads doing lots of small allocations. It’s also very different from allocating from a single-file filegroup – which is optimized (obviously) to not do round-robin.

Your tempdb data files are not the same size and so the auto-grow is only growing a single file (the algorithm is unfortunately broken), leading to skewed usage and an I/O hotspot.

Having too many files can lead to essentially random IO patterns when the buffer pool needs to free up space through the lazywriter (tempdb checkpoints don’t flush data pages) for systems with not very large buffer pools but *lots* of tempdb data. If the I/O subsystem can’t handle the load across multiple files, it will start to slow down.”
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

TempDB’ye yeni bir data file ekleme işini hem SSMS hem de TSQL komutu ile yapabilirsiniz. Örneğin aşağıdaki komut TempDB’ye başlangıç boyutu 1024 MB, auto growth değeri 512 MB olan yeni bir data file ekler.

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE 
	( NAME = N'Tempdev2', 
	  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Tempdev2.ndf' , 
	  SIZE = 1048576KB , 
	  FILEGROWTH = 524288KB )
GO

TempDB Data File’larının Aynı Boyutta Olması

Bir önceki bölümde TempDB data file’larının sayısı üzerine konuştuk ve diyelim ki core CPU sayımız 4 ve 4 adet TempDB data file bulundurmaya karar verdik. Şimdi ikinci best practice’imiz yani bu data file’ların aynı boyutta olmasının öneminden bahsedeceğiz.

TempDB’de bulunan data file’lar round robin olarak proportional fill algorithm özelliğini kullanırlar. Bu özelliğin performanslı bir şekilde kullanılabilmesi için tüm data file’ların aynı boyutta olması gerekmektedir. Ayrıca TempDB’ye özel bir problem, tempdb data file’larından eğer herhangi biri diğerlerinden büyükse, auto growth esnasında her zaman büyük olan data file büyütülecektir. Bu durum da proportional fill algorithm özelliğinin sağlıklı bir şekilde kullanılmamasına sebebiyet verecektir.

Aslında burada başka bir best practice’den daha bahsetmek gerekiyor. Bilindiği gibi data file’larda yer bittiğinde eğer auto growth özelliği aktif halde bırakılırsa data file için yeni bir alan otomatik olarak alloce edilecektir. Data file allocation işlemi file seviyesinde exclusive lock’a sebebiyet verdiğinden dolayı auto growth işlemi sırasında file’a erişim engellenecek ve eğer çok fazla auto growth oluşursa performans eksi yönde etkilencektir. Bu yüzden

  • TempDB data file’larına uygun bir başlangıç değeri vermek
  • Auto growth değerlerini daha büyük chunk’lar halinde ayarlamak önemli bir diğer best practice’dir

Bu şekilde TempDB data file’ları -ideal durumda- auto growth’a yakalanmayacak, yakalansa bile büyük chunk’lar halinde büyütüldükleri için sürekli büyüme ihtiyacı duymayacaklardır.

Bu bölümdeki ilk best practice yani TempDB data file’larına uygun bir başlangıç değeri vermek üzerine önemli bir noktanın altını çizmek istiyorum. Bildiğiniz gibi TempDB her SQL Server restart’ında baştan create edilir. Eğer TempDB data file’larının ilk başlangıç değerlerini çok yüksek tutarsanız restart işlemleri sonrası SQL Server’ın hazır hale gelmesi uzun sürecektir. Dolayısıyla uygun bir değerin belirlenmesi önem tşaımaktadır.

Bu bölümdeki best practice’leri toparlayacak olursak. Diyelim ki core CPU sayımız 4 ve 4 TempDB data file’i kullanmaya karar verdik. Ve eğer toplamda 10GB’lık TempDB boyutu bizim için yeterli ise yapmamız gerekenleri şu şekilde sıralayabiliriz.

  • TempDB’ye 3 adet daha data file eklenerek toplam data file sayısı 4’e çıkarılır.
  • Tüm data file’ların başlangıç boyutları 2.5 GB (10 GB/4) olarak ayarlanır.
  • Data file’ların auto growth değerleri büyük chunk’lar halinde auto growth gerçekleşsin diye uygun bir değere (örneğin 512 MB ya da 256 MB) set edilir.

Her ne kadar data file’lara uygun bir başlangıç değeri atansa da plansız ya da problemli bir durumda data file’lar büyüme ihtiyacı duyabilir. Bu durum da data file’lardan biri büyütülecek ve önemli best practice’lerimizden biri olan “TempDB data file’larının eşit boyutta olması” best practice geçersiz duruma gelecektir. Bu problemin önüne geçmek için T1117 trace flag’i kullanılabilir. Bu trace flag aktif hale getirildiğinde bir file group içerisindeki data file’lardan herhangi biri auto growth’a girdiğinde sadece ilgili file değil file group içerisinde bütün data file’lar beraber büyütülecek ve dolayısıyla TempDB data file’larının aynı boyutta olma best practice’i korunmuş olacaktır. Yalnız unutulmaması gereken önemli bir nokta T1117 trace flag’i sadece TempDB’yi değil instance’da bulunan tüm database’leri etkilemektedir.

TempDB data file’larına başlangıç değeri ve auto growth değeri verme işlemlerini SSMS’ten TempDB özelliklerine girerek yapabilirsiniz.

TempDB File’larının Bulunduğu Storage

TempDB’nin yoğun bir şekilde kullanıldığı ortamlarda TempDB performansının overall SQL Server performansını nasıl etkileyeceğinden bahsettik. Bu yüzden TempDB file’larının diğer user database file’larından ayrı bir yerde ve sistemde bulunan en yüksek performanslı disklerde (örneğin SSD disklerde) bulundurulması önemli bir best practice’dir.

TempDB data file’larının nasıl taşınabileceği ile alakalı şu blog post’umu okuyabilirsiniz.

Yoğun TempDB Kullanımının Analiz Edilmesi

Şu ana kadar bahsettiğimiz tüm best practice’ler, TempDB üzerinde yapabileceğimiz konfigurasyon değişiklikleri ile performans artışını sağlamak amacıyla kurgulayabileceğimiz değişiklikler. Aslında belki de bu best practice’lerden daha önce TempDB’nin neden yoğun bir şekilde kullanıldığı ve gerçekten bu kadar yoğun bir şekilde kullanılıp kullanılmamasını analiz etmek olmalıdır. Çünkü çoğu sorguda şunu görüyoruz ki; yoğun temp table’lar kullanılarak yapılan işlemler aslında temp table kullanılmadan da yapılabilmektedir.

Bu yüzden eğer yukarıdaki best practice’leri yapmanıza rağmen hala allocation bit page contention’ları görmeye devam ediyorsanız TempDB’nin neden yoğun bir şekilde kullanıldığının analiz edilmesi ve gerekiyorsa kodda değişiklik yapılarak bu kullanımın azaltılması gerekmektedir. Hatta daha da ideali bu analizi best practice’leri yapmadan önce yapmaktır.

Tekrar belirtiyorum; sadece 1 adet TempDB’miz var ve ona gerçekten iyi bakmalıyız.

T1118 Trace Flag’i

SQL Server’da tüm yeni allocation’larda ilk 8 page’e kadar yapılan page allocation’ları mixed extend’ler üzerinden yapılmaktadır. Obje için alloce edilen alan 8 page’i geçtikten sonra gelen yeni page istekleri artık uniform extend olarak sağlanmaktadır. İlk 8 page için mixed extend’lerden allocation yapılırken her allocation’da PFS, GAM ve SGAM allocation bit page’lerin tamamına bakılır. Bu durumda 8 page’in allocation’i için SGAM page’e 8 defa erişilmektedir ve bu da SGAM (örneğin 2:1:3) üzerinde contention görülmesine sebebiyet verir.

T1118 trace flag’i aktif edildiğinde tüm extend allocation’ları uniform olarak verilir. Bu durumda SGAM üzerindeki contention aşılabilir.

Eğer yukarıdaki best practice’leri uygulamanıza ragmen hala allocation bit page contention’larına rastlıyorsanız T1118 trace flag’ini aktif etmeyi düşünebilirsiniz

ÖZET

TempDB performans açısından SQL Server için oldukça önem taşımaktadır. Instance başına 1 TempDB olduğu için TempDB’nin konfigurasyonu ve yönetimi oldukça önemlidir.

TempDB performansı için base olarak şu best practice’ler uygulanabilir.

  • TempDB kullanımının azaltılması (örneğin gereksiz temp table kullanımının kaldırılması)
  • TempDB file’larının diğer kullanıcı database’lerinden farklı ve en yüksek performanslı disklere konulması.
  • TempDB data file’larının arttırılması
    • Core CPU sayısı 8’den az ise Core CPU sayısı kadar
    • Core CPU sayısı 8’den fazla ise 8 data file oluşturulup contention görülmeye devam edildiği sürece 4’er 4’er data file eklenmesi.
  • TempDB data file’larına uygun bir başlangıç boyutu verilmesi
  • TempDB data file’larının boyutlarının aynı yapılması (Proportional Fill Algorithm)
  • Auto growth değerlerine uygun bir değer verilmesi (Örneğin; 256 MB ya da 512 MB)
  • Gerekirse T1118 trace flag’i aktif hale getirilerek extend allocation’ının uniform extend olarak yapılmasının sağlanması.
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 uzun zamandır yazmayı düşündüğüm çok önemli bir konu & best practice üzerine konuşuyor olacağız.

[more]

Virtual Log File (VLF) Nedir?

Virtual Log File transaction log dosyası içerisindeki transactionları saklayan mantıksal parçalardır. Bir database’in sahip olduğu VLF’lere DBCC LOG_INFO() komutu ile bakılabilir.

image

Örneğin benim sistemimde bulunan AdventureWorks veritabanı 1 transaction log dosyası içerisinde 444 virtual log file içermekte.

Yeni Bir VLF Nasıl Eklenir?

Transaction log dosyası üzerinde yapılan her auto growth ya da manuel büyütme işleminde yeni 1 ya da 1’den fazla VLF eklenir. Kaç tane VLF’nin ekleneceği büyümenin boyutuna bağlıdır. Buna göre;

  • Büyüme 64 MB’a kadar ise 4 VLF
  • 64 MB ile 1 GB arasında ise 8 VLF
  • 1 GB’dan fazla ise 16 VLF eklenir

Örneğin eğer transaction log file’ı 512 MB büyütürseniz (yukarıdaki listede 2. sıraya denk gelmekte) bu durumda 8 VLF eklenecek ve her VLF’nin boyutu 512/8 = 64 MB olacaktır.

Dolayısıyla Auto growth ya da manuel büyütme boyutlarınız ne kadar küçükse (bildiğiniz gibi default auto growth değeri log dosyaları için 1 MB’dır) VLF sayınız o kadar fazla olacaktır.

 

Çok Fazla VLF’ye Sahip Olmanın Veritabanına Etkisi Nedir?

Transaction log dosyasındaki VLF sayısı 2 farklı açıdan bizi etkiler;

  • Her database recovery işleminde (örneğin restart ya da failover sonrası) veritabanı recovery edilirken redo ve undo operasyonları işletilir. Redo operasyonu başlamadan transaction log dosyasındaki bütün VLF’ler okunur. VLF sayısı arttıkça bu okuma süresi uzayacak ve dolayısıyla recovery süresi yani veritabanın ayağa kalkma süresi uzayacaktır
  • VLF sayısı arttıkça database üzerinde yapılan DML (insert-update-delete) işlemlerinin performansı etkilenir. Şu blog post’ta bu konu çok güzel bir şekilde analiz edilmiş. Buna göre diğer özellikleri aynı ama birinde 20.000 diğerinde 16 VLF bulunan 2 veritabanı üzerinde yapılan insert-update-delete işlemlerinin süresi aşağıdaki gibidir.

    image

    Tablodan da rahatça anlaşılacağı gibi VLF sayısı arttıkça DML performansı kötü yönde etkilenmektedir.

 

VLF Best Practice’i Nedir?

VLF sayısı için genel kabul görmüş best practice değeri her transaction log file için 1000’dir. Fakat veritabanı boyutuna göre bu best practice değeri azaltılabilir. Örneğin 1000 best practice değeri 300 GB’lık bir transaction log’a sahip veritabanı için anlamlıyken 2 GB transaction log dosyasına sahip bir veritabanında çok daha az sayıda VLF’e sahip olunması gerekir.

Tabii burda göz önünde bulundurulması gereken bir başka best practice ise çok büyüt boyutlarda VLF’lere sahip olunmamasıdır. Bu konuda Kimberly L. Tripp (blog | twitter) şu blog post’ta VLF dosyaları için maksimum 512 MB boyutuna sahip olunmasını tavsiye etmektedir. (512 MB’lık VLF’lere sahip olmak için büyümeler 8 GB’lık chunk’lar halinde yapılmalıdır.)

Bu bölümde son olarak SQL Server 2012 ile beraber gelen VLF kontrolünden bahsetmek istiyorum. Database’in her recovery işleminde (örneğin restart) bütün database’lerin VLF sayıları kontrol edilir ve eğer 1000’den fazla VLF’e sahip olan bir veritabanı var ise SQL Error Log’da aşağıdaki şekilde loglanır.

Database VLF_DB has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

VLF Sayısına Nasıl Bakılır?

Makalemin başında da bahsettiğim gibi bir database’in sahip olduğu VLF’lere ve sayısına DBCC LOGINFO() komutu ile bakılabilir.

 

VLF Sayısı Nasıl Azaltılır?

Daha önce de bahsettiğim gibi çok fazla VLF’e sahip olmanın en büyük sebeplerinden biri uygun bir auto growth değerinin kullanılmıyor olmasıdır. Bu yüzden ilk yapılması gereken daha sonraki büyümeleri kontrol altına almak adına uygun bir auto growth değeri verilmesidir. Log file’lar için 512 Mb ya da 1 GB’lık auto growth değerleri kullanılabilir.

Peki şu anki VLF sayısını nasıl düşürebiliriz. Bunun için benim uyguladığım adımlar aşağıdaki gibi;

  • İlk olarak log file ihtiyaç analizi yani ne kadarlık bir log file ihtiyaç olduğu belirlenmelidir. Diyelim ki 100 GB’lık bir log file’a ihtiyacımız olsun.
  • Daha sonra log file olabildiği en küçük değerine kadar shrink edilip küçültülmelidir. (Tabii ki bu operasyon mesai saatleri dışında yapılmalıdır.)
  • Akabinde log file büyük chunk’lar halinde büyütülmelidir. Daha öncede söylediğim gibi büyütmeyi maksimum 8 GB’lık chunk’lar halinde yapabilirsiniz. Dolayısıyla database 8 – 16- 24 olacak şekilde büyütülmelidir. (Daha küçük log file’a sahip olan database’ler için daha küçük chunk değerleri kullanılabilir)
  • Büyütme işlemi 100 GB’a erişilene kadar devam ettirilir.

 

VLF konusu oldukça az bilinen ama çok önemli best practice’lerden biridir. Bu yüzden periyodik olarak database’lerin VLF sayılarını kontrol edip best practice’lerin üzerinde VLF’e sahip olan database’lerde yukarıdaki işlemleri yapmanızı kesinlikle tavsiye ederim.

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


Index’lerin içinde bulundukları filegroup’ları değiştirmek istediğimizde drop edip tekrar create etmemiz gerekir. Bu işlemi yapabilmek için de elimizde 2 farklı seçenek var. “Create With Drop_Existing = On” ve “Drop&Create”. Bu yazımda bu 2 seçenek arasındaki farka bakıyor olacağız.

[more]

Aslında ilk bakışta 2 seçenek te aynı gibi duruyor.

  • Drop&Create seçeneğinde index’i önce drop edip daha sonra tekrar create ediyoruz.
    DROP INDEX CI1 on tbl1
    GO
    CREATE INDEX CI1 on tbl1 (Col1)
    on [NewFileGroup]
    GO
  • Drop_existing de kelime anlamına baktığımızda olan index’i önce drop edip sonra create et demek.
    CREATE INDEX CI1 on tbl1 (Col1)
    with (DROP_EXISTING=ON)
    on [NewFileGroup]
    GO

 

Ama aralarında çok önemli bir fark var

  • Eğer bir Clustered Index’i “Create with Drop_Existing” metodu yerine “drop&create” metodu ile tekrar create edersek tüm nonclustered index’ler 2 defa rebuild olur. Bunun sebebi ilk drop işleminde tüm nonclustered index’lerin leaf level page’lerındaki clustered index key’lerin kaldırılıp Heap Row Pointer bilgilerinin konulması, tekrar create edildiğinde de yeni clustered index’in key bilgilerinin tekrar tüm nonclustered index’lerin leaf level page’lerine yazılmasıdır. (Clustered Index key’lerinin nonclustered index’lerin leaf level’inde olması lookup’ların yapılabilmesi içindir. Daha detaylı bilgi için şu makaleyi inceleyebilirsiniz.)

    Bu davranış aşağıdaki msdn article’da şu şekilde açıklanıyor;

    Sometimes indexes are dropped and re-created to reorganize the index, for example to apply a new fillfactor or to reorganize data after a bulk load. It is more efficient to use CREATE INDEX and the WITH DROP_EXISTING clause for this, especially for clustered indexes. Dropping a clustered index causes all the nonclustered indexes to be rebuilt. If the clustered index is then re-created, the nonclustered indexes are rebuilt once again to replace the row pointers with clustered index keys. The WITH DROP_EXISTING clause of CREATE INDEX has optimizations to prevent this overhead of rebuilding the nonclustered indexes twice. DBCC DBREINDEX can also be used and has the advantage that it does not require that the structure of the index be known.
    http://msdn.microsoft.com/en-us/library/aa258837(SQL.80).aspx
  • Olaya nonclustered index açısından bakarsak; bir nonclustered index’in drop_existing metodu yerine drop&create metodu ile tekrar create edilmesi daha fazla IO yapılmasına ve dolayısıyla daha uzun sürmesine sebebiyet veriyor. Bunun nedeni; işlem drop&create metodu ile yapıldığında index tekrar create edilirken index page’ler tüm tablo okunarak oluşturuluyor. Drop_Existing metodu ile yapıldığında ise hali hazırda olan page’ler kullanılıp sadece index’in filegroup’u değiştiriliyor. Tüm tablo okunmadığı için drop_existing metodu bize daha performanslı bir sonuç vermiş oluyor. Tabi tanımlama olarak tamamen aynı index’i tekrar oluşturduğumuzu varsayarsak.

    Bu konuda www.sqlservercentral.com daki bir makalede detaylı bir analiz yapılmış. Drop_existing, drop&create ve rebuild işlemlerinin yaptıkları IO ve operasyon süreleri tablo halinde karşılaştırılmış. Detaylı bilgiye şu makaleden erişebilirsiniz.

 

Sonuç olarak özellikle Index filegroup değişimlerinde drop_existing metodunu tercih etmenizi tavsiye ederim. aynı işi boşu boşuna daha fazla kaynak tüketerek yapmanın bir anlamı yok değil mi? :)

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


Normal şartlarda beğendiğim İngilizce yazılmış bir makaleyi Türkçe’ye çevirmeyip sadece linkini blog’umda paylaşırım. Ama bu sefer konu oldukça önemli ve müşterilerimde sıkça rastladığım bir problem olduğu için daha fazla ilgi çekmesi açısından kendim de birşeyler katarak Türkçe yazmaya karar verdim.

[more]

Yazıma ilham veren makaleye SQL Server MCM’lerinden Gail Shaw’ın (blog | twitter) blog’unda rastladım. Konunun özü; opsiyonel olarak verilen yani null olma ihtimali olan parametrelerin where clause’da “OR” koşulu ile yazıldığında nasıl performans sıkıntısına sebebiyet verdiği ve bu sıkıntının nasıl çözümlenebileceği.

Ufak bir örnek verecek olursak aşağıdaki SP’de kullanılan 4 parametre de opsiyonel ve sadece değer verildikleri durumda where koşulunda kullanılıyor, aksi durumda yani NULL olarak bırakıldıklarında where koşulunda herhangi bir sınırlama etkisi yaratmıyorlar.

CREATE PROCEDURE SearchHistory(
	@Product int = NULL, 
	@OrderID int = NULL, 
	@TransactionType char(1) = NULL, 
	@Qty int = NULL)
AS
SELECT ProductID, 
	   ReferenceOrderID, 
	   TransactionType, 
	   Quantity,
	   TransactionDate, 
	   ActualCost 
from Production.TransactionHistory
WHERE (ProductID = @Product Or @Product IS NULL)
	AND (ReferenceOrderID = @OrderID OR @OrderID Is NULL)
	AND (TransactionType = @TransactionType OR @TransactionType Is NULL)
	AND (Quantity = @Qty Or @Qty is null)
GO

 

Bildiğiniz gibi SP'ler parameterize edilebilen objelerdir. Yani SP’lerin içerisindeki statement’lar için ilk çalıştırıldıkları parametreler ile Query Plan oluşturulur ve cache’lenir. Daha sonra aynı SP çağırıldığında tekrar Query Plan oluşturulmaz ve cache’lenen bu Query Plan kullanılarak statement’lar çalıştırılır.

İşte aslında problem biraz da bununla alakalı. Yukarıdaki sorguda farklı parametrelere göre farklı Query Plan’lar kullanılması gerekiyor. Örneğin sadece ProductID ve TransactionType parametrelerinin gönderildiği durumda farklı bir Query Plan, sadece Quantity gönderildiğinde ise apayrı bir Query Plan kullanılmalı. Ama bir önceki paragrafta da anlattığım gibi SP için 1 kez Query Plan oluşturulup daha sonra hep bu oluşturulan plan kullanıldığı için daha sonra gelen sorgular için bu plan uygun olmayabiliyor.

Diğer bir problem ise; sorgu ilk çalıştığında Query Optimizer tüm parametreleri dikkate alacak/cover edecek şekilde bir Query Plan oluşturuyor ki daha sonraki sorgularda da bu plan çok ta kötü bir plan olmasın. Lakin bu yaklaşımda da oluşturulan Query Plan ne ilk gelen parametreler için uygun plan oluyor ne de sonraki gelecek parametreler için iyi bir plan oluyor.

Peki çok sık karşılaşılan bu problemi nasıl çözeceğiz? Kullanacağımız yöntem Dynamic SQL!

Buyrun örneğe geçelim;

use AdventureWorks2012
GO
--Calisma Proseduru create ediyoruz
--Prosedur 4 parametre aliyor fakat bu parametreler null olabiliyor
--Where kosulunda parametrelerin null'ligi kontrol ediliyor
CREATE PROCEDURE SearchHistory(
	@Product int = NULL, 
	@OrderID int = NULL, 
	@TransactionType char(1) = NULL, 
	@Qty int = NULL)
AS
SELECT ProductID, 
	   ReferenceOrderID, 
	   TransactionType, 
	   Quantity,
	   TransactionDate, 
	   ActualCost 
from Production.TransactionHistory
WHERE (ProductID = @Product Or @Product IS NULL)
	AND (ReferenceOrderID = @OrderID OR @OrderID Is NULL)
	AND (TransactionType = @TransactionType OR @TransactionType Is NULL)
	AND (Quantity = @Qty Or @Qty is null)
GO

--Parametre gruplarından birinde kullanmak uzere
--	ProductID ve TransactionType uzerine nonclustered index olusturalim
create nonclustered index idx_TranHistory_TranTypeProductID 
	on Production.TransactionHistory (ProductID,TransactionType)

--io istatistigini acalim
set statistics io on

--Proseduru ilk olarak Product ve TransactionType parametreleri ile cagiralim.
--Normalde Product=978 ve TransactionType='W' sorgusu index seek yapacakken
--	diger parametrelerden dolayi index scan yapiyor
--Logical Read = 398
EXEC SearchHistory @Product = 978, @TransactionType = 'W'

--Sorguyu adhoc cekerek index seek yaptigini gorelim
--Logical Read = 170
SELECT ProductID, 
	   ReferenceOrderID, 
	   TransactionType, 
	   Quantity,
	   TransactionDate, 
	   ActualCost 
from Production.TransactionHistory
WHERE (ProductID = 978)
	AND (TransactionType = 'W')


--Ayni proseduru farklı parametre ile cagirinca gene ayni plan kullaniliyor
--Cunku prosedur icin plan bir kez olusturuldu ve cache'lendi
--IO : 347658 
EXEC SearchHistory @Qty = 100

--Oysaki Adhoc olarak sadece Quantity = 100 kullanildiginda cok daha optimal bir plan var
--IO 797
SELECT ProductID, 
	   ReferenceOrderID, 
	   TransactionType, 
	   Quantity,
	   TransactionDate, 
	   ActualCost 
from Production.TransactionHistory
WHERE (Quantity = 100)

--Peki bu problemi nasil asabiliriz
--En uygun yontem dynamic sql ile parametrelere gore sql script'i olusturmak
CREATE PROCEDURE SearchHistory_Dynamic (
	@Product int = NULL, 
	@OrderID int = NULL, 
	@TransactionType char(1) = NULL, 
	@Qty int = NULL)
AS
	DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''
	SET @sSQL = 'SELECT ProductID, ReferenceOrderID, TransactionType, Quantity, TransactionDate, ActualCost
	from Production.TransactionHistory '
 
	IF @Product is not null
		SET @Where = @Where + 'AND ProductID = @_Product '
	IF @OrderID is not null
		SET @Where = @Where + 'AND ReferenceOrderID = @_OrderID '
	IF @TransactionType IS NOT NULL
		SET @Where = @Where + 'AND TransactionType = @_TransactionType '
	IF @Qty IS NOT NULL
		SET @Where = @Where + 'AND Quantity = @_Qty '
 
	IF LEN(@Where) > 0
		SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)
 
	EXEC sp_executesql @sSQL,
		N'@_Product int, @_OrderID int, @_TransactionType char(1), @_Qty int',
		@_Product = @Product, @_OrderID = @OrderID, @_TransactionType = @TransactionType, @_Qty = @Qty
 
GO

--Daha once index scan - 398 IO yapan sorgu simdi
--	olmasi gereken sekilde index seek - 170 IO yapiyor 
EXEC SearchHistory_Dynamic @Product = 978, @TransactionType = 'W'
 
--Ayni SP'yi cagirmamiza ragmen yeni bir query plan olusuyor ve
--	daha once 347658 IO yapan sorgu olmasi gereken sekilde 797 IO yapiyor
EXEC SearchHistory_Dynamic @Qty = 100

--create ettigimiz objeleri siliyoruz
drop procedure SearchHistory
drop procedure SearchHistory_Dynamic
drop index idx_TranHistory_TranTypeProductID on Production.TransactionHistory

 

Bu yöntemde akla gelen sorulardan ilki “aynı parametreler gelse dahi her defasında yeni bir query plan'mı oluşuyor?” Cevabımız hayır. Aşağıdaki sorgu ile oluşan Query Plan’ları ve kaç kez kullanıldıklarını görebilrsiniz. Resimde gördüğümüz üzere “her gelen farklı parametre grubu” için 1 query plan oluşturulup cache’leniyor ve daha sonraki sorgularda kullanılıyor.

--Cache'lenen query Planlara bakalım.
select st.text,* 
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%SELECT ProductID, ReferenceOrderID, TransactionType, Quantity, TransactionDate, ActualCost%')
and st.text not like '%select st.text%'

 

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’da Istatistik Kavramı adlı makalemde İstatistikler hakkında detaylı bir şekilde konuşmuştuk. Kısaca özet geçmek gerekirse; İstatistikler, Query Optimizier tarafından sorgu planı oluşturulurken daha performanslı bir plan oluşturmak için kullanılan, tablodaki verinin dağılımını gösteren istatistik bütünüdür. İstatistikler sayesinde, sorgu planı (Query Plan) oluşturulurken sorgudaki where bloğunda kullanılan kolon için dönecek tahmini kayıt sayısı bulunur. Bulunan bu değer index’e erişim şeklini belirler. Temel amaç, datayı en hızlı ve en az maliyetli şekilde kullanıcının karşısına getirmektir.

[more]

Gene aynı makalede Auto_Update_Statistics database özelliğinin ne şekilde çalıştığından ve basic threshold değerinin kayıt sayısının %20+500 olduğundan bahsetmiştik. Yani örneğin 10.000 kayıttan oluşan bir tablomuz var ise ve bu tablodaki Col1 kolonu üzerine istatistik tanımlandıysa, Col1 kolonunu etkileyecek 2.500 güncelleme işleminden sonra istatistik ilk kullanılmak istendiği anda (örneğin select işleminde) otomatik olarak güncellenecektir.

İstatistikler doğru query plan oluşturulması için çok önemli olduğu için istatistiklerin olması kadar güncel olması da çok önem taşımaktadır. Bu yüzden best practice olarak Auto_Update_Statistics database özelliğinin açık olması önerilir. (Sharepoint gibi bazı database’lerde kapalı olması önerilmektedir) Fakat %20’lik threshold değeri oldukça fazla olduğundan çoğu durumda bu threshold değeri yakalanamamakta ve tablo çok fazla update görmesine rağmen istatistik update olmamaktadır. Örneğin 100.000.000 kayıttan oluşan bir hareket tablosundaki istatistiğin bu özellik sayesinde otomatik olarak update görmesi için yaklaşık 20.000.000 kaydın update olması gerekiyor ki reel hayatta böyle bir şey neredeyse mümkün değil.

İşte bu noktada karşımıza SQL Server 2008 R2 SP1 gelen 2371 Trace Flag’i çıkıyor. Bu trace flag aktif edildiğinde tablodaki kayıt sayısı arttıkça %20’lik threshold değeri daha erişilebilir seviyelere iniyor.

image

Kaynak : http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

Yukarıdaki grafiği incelediğimizde tablodaki kayıt sayısı 25.000’ken %20’lik threshold geçerliyken, kayıt sayısı arttıkça %20’lik threshold aşağılara inmekte. Örneğin 100.000 kayıt sayısı için %10’lu değerlere, 1.000.000 kayıt için %3.2 gibi bir değere inmekte ki bu değerler artık yakalanabilir seviyelere gelmiş olmaktadır.

Özellikle çok büyük tablolarınız var ise (Örneğin SAP uygulaması, http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx) ve out of dated istatistiklerden dolayı performans sıkıntısı çektiğinizi düşünüyorsanız bu trace flag’i aktif hale getirmeyi düşünebilirsiniz.

Son olarak bir örnek yaparak trace flag’in davranışı görelim ve yazımıza son verelim.

--Bir calisma DB’si create ediyoruz
create database StatsDeneme
GO
use StatsDeneme
GO
--Auto_Create_Statistics ve Auto_Update_Statistics parametrelerini on yapiyoruz
alter database StatsDeneme set Auto_Create_Statistics ON
alter database StatsDeneme set Auto_Update_Statistics ON
--Bir calisma tablosu create ediyoruz
if OBJECT_ID('StatsDeneme','U') is not null
  drop table StatsDeneme
create table StatsDeneme(col1 int, col2 varchar(10), col3 varchar(5))
GO
--Calisma tablosuna rasgele 100.000 veri basiyoruz.
declare @i int=0
while @i<100000 begin
  insert StatsDeneme select @i,CAST(@i as varchar(10)),'a' + cast((@i % 10) as varchar(6))
  set @i=@i+1
end

--Istatistik olusturmak icin select sorgumuzu cekiyoruz
--Auto_Create_Statistics database ozelligi on oldugu icin
--	asagidaki sorgu sonucu col3 kolonu icin istatistik olusacaktir
select * from StatsDeneme where col3='a1'

--Istatistigin son guncellenme zamanina bakiyoruz
select object_name(object_id),name as StatsName,STATS_DATE(object_id,stats_id) 
from sys.stats
where object_name(object_id) = 'StatsDeneme'

--Trace Flag'i aktif etmeden 10.000 kaydi update ediyoruz (%10)
update top(10000) StatsDeneme set Col3 = 'xx' 

--Auto_Update_Statistics ozelligini tetiklemek icin select sorgusunu tekrar cekiyoruz
select * from StatsDeneme where col3='a1'

--Istatistigin son guncellenme zamanina tekrar bakiyoruz ve 
--	daha %20+500'luk threshold dolmadigi icin update olmadigini goruyoruz
select object_name(object_id),name as StatsName,STATS_DATE(object_id,stats_id) 
from sys.stats
where object_name(object_id) = 'StatsDeneme'

--10.500 kaydi daha update ediyoruz ve threshold'u yakaliyoruz (%20%500)
update top(10500) StatsDeneme set Col3 = 'xx' 

--Auto_Update_Statistics ozelligini tetiklemek icin select sorgusunu tekrar cekiyoruz
select * from StatsDeneme where col3='a1'

--Istatistigin son guncellenme zamanina tekrar bakiyoruz ve istatistigin update oldugunu goruyoruz
select object_name(object_id),name as StatsName,STATS_DATE(object_id,stats_id) 
from sys.stats
where object_name(object_id) = 'StatsDeneme'

--Dolayisiyla 2371 trace flag'ini kullanmadan threshold degerimiz %20+500
--Simdi ayni islemleri 2371 trace flag'ini aktif ederek tekrar yapalim

--Trace Flag'i aktif ediyoruz 
DBCC TraceOn (2371)

--Not:Trace flag'i bu sekilde aktif etmektense trace flag startup parametrelerine konularak 
--	SQL Server her acildiginda trace flag otomatik olarak aktif ettirilebilir)

--10.000 kaydi update ediyoruz (%10)
update top(10000) StatsDeneme set Col3 = 'yy' 

--Auto_Update_Statistics ozelligini tetiklemek icin select sorgusunu tekrar cekiyoruz
select * from StatsDeneme where col3='a1'

--Istatistigin update oldugunu goruyoruz. 
--%10'luk kisim update olmasına ragmen istatistigin update olma nedeni 2371 trace flag'ini aktif etmis olmamiz
select object_name(object_id),name as StatsName,STATS_DATE(object_id,stats_id) 
from sys.stats
where object_name(object_id) = 'StatsDeneme'

--calisma database'ini drop ediyoruz
use master
go
drop database StatsDeneme
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 birden fazla kolon üzerine tanımlanan index’lere composite index denilmektedir. Örneğin aşağıdaki index 2 kolon üzerine tanımlanmış nonclustered bir composite index örneğidir.

create nonclustered index IX_1 on tbl1
	(Column1, Column2)

 

Klasik Index’ler (ColumnStore Index’de durum farklıdır) B-Tree yapısında organize olduklarından dolayı index’in hangi sıralama ile oluşturulduğu oldukça önem taşımaktadır. Çünkü yukarıdaki index örneğini ele alırsak kayıtlar önce column1’e göre dizilecek, daha sonra column2’e göre dizelecektir. (Index’lerdeki B-Tree yapısı hakkında daha detaylı bilgi almak için şu makaleye göz atabilirsiniz)

[more]

Composite index oluştururken uyguladığımız ilk best practice where koşulundaki eşitlik (equality) kolonlarının eşitlik olmayan (inequality) kolonlardan daha önce yazılması gerektiğidir.

2. best practice’imiz ise her grubun içinde (eşitlik ve eşitlik değil) selectivity’si yani seçiciliği daha fazla olan kolonun daha önde tanımlanmasıdır. Örneğin kişi bilgilerini tuttuğumuz bir tabloda cinsiyet ve il alanlarına index tanımlayacağımızı ve sorgumuzda cinsiyet ve il alanlarını eşitlik şeklinde kullandığımızı düşünelim. Cinsiyet alanı sadece 2 farklı değer alabilirken il alanı 81 farklı değer alabilmektedir. Dolayısıyla il alanının seçiciliği cinsiyet alanine göre çok daha fazladır. Bu yüzden bu 2 kolon için composite index tanımlanacaksa sıralamanın il,cinsiyet şeklinde olması gerekir.

Best practice’leri toplayacak olursak;

  • «Eşittir» kolonları «eşit değil» kolonlarından önce kullanılmalıdır.
  • En seçici «eşittir» kolonu ilk sıraya konulmalıdır.
  • En seçici «eşit değildir» kolonu son «eşittir» kolonundan sonraya konulmalıdır.

Örneğin aşağıdaki gibi bir sorgu için index create ediyorsak ve col2, col4 kolonları diğer kolonlara göre daha seçiciyse index’in şu şekilde olması best practice’dir

Select * from tbl1_
where Col1<>'a' and Col2='b' and Col3='c' and Col4<>'d'

create nonclustered index IX_1 on tbl1
	(Col2, Col3, Col4, Col1)

 

Şimdi bir örnek yaparak konuyu pekiştirelim.

--Bir calisma tablosu olusturuyoruz
Create table tbl_CompSample(Gender char(1), City int, Col1 Char(50), Col2 Char(50))
go

--Calisma tablosuna 1000 kayit basiyoruz
declare @i int = 1
while @i<1000 begin
	insert tbl_CompSample 
		select (case @i%2 when 1 then 'M' else 'F' end), (@i%81)+1, 'value1', 'value2'
	set @i=@i+1
end

--Kayit sayisini arttiriyoruz
declare @i int
set @i = 0
while @i < 8
begin
	insert into tbl_CompSample select * from tbl_CompSample
	set @i = @i + 1
end

--255.744 kayittan olusan bir calisma tablosu hazirladik
select count(*) from tbl_CompSample

--Sorgumuz su sekilde
--Sorguda Gender kolonu esitlik seklinde iken City kolonu esitlik degil seklinde kullanilmis
--	Bu yuzden Gender kolonunun selectivity'si daha az olmasına ragmen index'de bu kolonu basta kullanmak best practice'dir
select Col1, Col2 from tbl_CompSample
where Gender='M' and City between 34 and 50


--ilk olarak Gender, City seklinde bir nonclustered 
--index olusturup IO degerlerine bakiyoruz
create nonclustered index IX_1 on tbl_CompSample (Gender, City) include (Col1, Col2)

set statistics io on

select Col1, Col2 from tbl_CompSample
where Gender='M' and City between 34 and 50

--Bu index ile 389 logical read yapildi
--logical read degerini result kisminda gorebilirsiniz

--Simdi ayni index'i bu sefer City, Gender seklinde tanimlayalim
create nonclustered index IX_1 on tbl_CompSample (City, Gender) include (Col1, Col2)
with (drop_existing=ON)

select * from tbl_CompSample
where Gender='M' and City between 34 and 50

--Bu sekilde ise 754 IO yaptı.
--Cunku City daha secici olmasina ragmen esitlik degil seklinde
--	kullanildigi icin Gender, City daha iyi bir index'tir

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


As you know,statistics are used for row estimation during query plan generation phase. So they are really important for performance. If the “Auto Create Statistics” database option is On, for every column on where clause or join, statistics are automatically created by database engine.

[more]

These auto created statistics can be unused in the following days. So, how can we find these unused statistics?

As you know, if auto update statistics database option on, statistics are automatically updated if %20+500 row changed threshold is reached and related statistics are used. So first rule for automatic update operation is %20+500 row changes threshold (this thresholds are tracked in sysindexes.rowmodctr column), and second rule is related statistics must be used to be update.

Below script searches all statistics and find number of changes and last update time for all statistics. If the number of changes are higher than threshold but statistic is not updated, that means this statistic is not used since last update time.

use AdventureWorks2008
GO
select TableName, StatsName, auto_created, UpdatedRowCount, TableRowCount
      , case TableRowCount when 0 then 0 else UpdatedRowCount*1./TableRowCount end as UpdatedPercentage
      , StatsLastUpdatedTime 
from(
select OBJECT_NAME(id) as TableName
      ,s.name as StatsName
      ,s.auto_created
      ,rowmodctr as UpdatedRowCount
      ,(select SUM(row_count) from sys.dm_db_partition_stats where object_id=i.id and (index_id=0 or index_id=1)) as TableRowCount
      ,STATS_DATE(i.id,i.indid) as StatsLastUpdatedTime
from sysindexes i
left join sys.stats s on s.object_id=i.id and s.stats_id=i.indid
)xx
order by (case TableRowCount when 0 then 0 else UpdatedRowCount*1./TableRowCount end) desc

Sample result;

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’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


During DML (insert, update, delete) operations, every related indexes are updated. If the table has too many indexes, SQL Server will spend a lot of resource and time for these operations and it might be a performance problem.

[more]

You can use the below script to identify which tables have more indexes than column count.

create table #tmp1 (DBName sysName, SchemaName sysName, ObjectName sysName, IndexCount int, ColumnCount int)

exec sp_msforeachdb 'use ?;
insert #tmp1
select ''?'' as DBName, SchemaName, ObjectName, IndexCount, count(*) as ColumnCount
from (
      select object_schema_name(object_id) as SchemaName
            ,object_name(object_id) as ObjectName
            ,count(*) as IndexCount       
            ,object_id
      from sys.indexes i
      where ObjectProperty(object_id,''IsUserTable'')=1 and index_id>0 
      group by object_id
)xx   
join sys.columns c on c.object_id=xx.object_id
group by SchemaName, ObjectName, IndexCount
having IndexCount>count(*)
'

select * from #tmp1

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


If a table does not have clustered index then we call this table as “Heap Table”

For OLTP system, most of time, it is better to use Clustered Index instead of Heap Table for performance. If a table does not have clustered index, when a new row inserting, SQL engine try to find a page to locate this row by using PFS pages. And this behavior might be a performance issue. On the other hand, if the table has clustered index, then SQL engine already knows where the new record might have been located. Because clustered index define the physical order of the rows.

[more]

The second reason might be “forwarded record” issue. Forwarded record is a Heap Table issue. To avoid this issue, we should create clustered index.

And also, heap tables are out of the index maintenance operation. Index fragmentation is really important to get better performance from queries. So third reason might be that.

Finally, if you decided to create clustered index you need a script to identify heap tables. You can use the below script for this purpose.

create table #HeapTables(			
	DBName sysname, 		
	Objectid int, 		
	SchemaName sysname, 		
	TableName sysname,		
	TableRowCount int,		
	NonClusterIndexCount int,		
	TableHasIdentity smallint,		
	IdentityColumnName sysName null		
	)		
			
exec sp_MSforeachdb 'use ?;			
	insert #HeapTables		
		select DB_NAME() as DBName,t.object_id,schema_name(t.schema_id) as SchemaName,t.name as TableName	
			,(select sum(row_count) from sys.dm_db_partition_stats where object_id=t.object_id and index_id in (0,1)) as TableRowCount 
			,(Select count(1) from sys.indexes i where i.object_id=t.object_id and index_id>1) as NonClusterIndexCount
			,objectproperty(t.object_id,''TableHasIdentity'')
			,(Select name from sys.columns c where c.object_id=t.object_id and is_identity=1) as IdentityColumn
		from sys.tables t	
		where t.type=''U'' and objectproperty(t.object_id,''TableHasClustIndex'')=0	
'			
			
select * from #HeapTables			
where DBNAME not in ('master','model','msdb','tempdb')			
order by DBName, SchemaName, TableName			
			
drop table #HeapTables			

Sample output might be like that;

image

You can take the last 2 columns as advice. Identity columns are good candidate to be clustered index key. So if you don’t have better option you can use this identity column as clustered index key.

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


SMP mimarilerde en büyük problemlerden biri de; çok fazla CPU içeren sistemlerde system bus üzerindeki beklemedir. Bu tarz sistemlerde bütün CPU’lar memory’e erişmek için tek bir system bus kullanılır, bu da system bus üzerinde CPU beklemesi anlamına gelir. CPU sayısı ne kadar artarsa system bus üzerindeki bekleme o kadar artacaktır. NUMA yani Non-Uniform Memory Access, tek bir system bus kullanımını bypass etmek için dizayn edilmiştir.

[more]

NUMA Mimarisi

Numa mimarisinde CPU ve Memory gruplanarak node’lar oluşturulur. Her bir node’un kendi CPU’ları ve memory alanları hatta kendi I/O kanalları bulunur. Her node’da CPU’ların memory alanlarına erişmesi için kendi system bus’ları bulunur. Bu şekilde tek bir system bus’a bağımlılık ortadan kaldırılmış olur.

numa

Node’un kendi grubu içinde bulunan memory’e Local Memory, diğer node’daki memory’leri ise Foreign ya da Remote Memory denilir. Foreign Memory alanına erişim Local Memory alanına erişmekten daha yavaştır. Bu yüzden NUMA mimarisi iyi dizayn edilmeli ve Foreign Memory kullanımı minimize edilmelidir. Foreign Memory’e yapılan erişimler SQL Server:Buffer Node altında bulunan foreign page performance counter’ı ile izlenebilir.

SQL Server 2005 ile beraber full NUMA desteği sunulmaktadır. SQL Server’ın NUMA kullanıp kullanmadığı SQL Server error log’dan kontrol edilebilir. Eğer NUMA kullanılıyor ise aşağıdaki mesaja benzer bir mesaj SQL Server error log’da yer alacaktır.

Multinode configuration: node 0: CPU mask: 0x00000000000000f0 Active CPU mask: 0x00000000000000f0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

Multinode configuration: node 1: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

NUMA mimarisi Soft-NUMA ve Hardware-NUMA olmak üzere 2 şekilde yapılabilir. Hardware desteği ile yapılan NUMA konfigurasyonlarına Hardware-NUMA denmektedir. Hardware-Numa mimarisinde NUMA’nın sağladığı bütün avantajlar (CPU,Memory ayrımı vs.) kullanılabilir.

Hardware NUMA desteği olmayan sistemlerde CPU Affinity Mask ve Register üzerinden Soft-NUMA konfigurasyonu yapılabilir. Soft-NUMA’yı Hardware-NUMA’dan ayıran en önemli fark; Soft-NUMA’da node’lar kendi memory alanlarına sahip değildir ve bütün node’lar ortak bir memory alanına erişirler. Yani bir başka deyişle Soft-NUMA’da memory alanı node’lar için ayrı ayrı gruplanamaz.

SQL Server 2008’de CPU Affinity Mask ve Register üzerinden Soft-NUMA konfigurasyonun nasıl yapıldığını aşağıdaki BOL dokümanından inceleyebilirsiniz.

http://msdn.microsoft.com/en-us/library/ms345357.aspx

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


Bildiğiniz gibi her SQL komutu çalıştırıldığında ilgili komut için bir Query Plan oluşturulur ve Plan Cache’de saklanır. Eğer ilgili sorgu parameterize edilebilen bir sorgu ise (Stored Procedure gibi) ve bu sorgu için daha önce bir Query Plan oluşturulduysa, tekrar bir Query Plan oluşturulmaz ve daha önce Plan Cache’de saklanan Query Plan kullanılır.

Query Plan oluşturulma işlemi masraflı bir işlemdir. Bu yüzden oluşturulmuş Query Plan’larının saklanıp kullanılarak tekrar tekrar Query Plan oluşturulmaması performans açısından önemli bir durumdur.

İşte bu bakış açısıyla exec ve sp_executesql komutları ile Dynamic SQL kullanımında ortaya çıkan farklılıkları bu makalede inceleyip, sp_ExecuteSQL’in exec yerine neden tercih edilmesi gerektiğini örnekler ile görüyor olacağız.

[more]

Her iş gereksinimi sabit SQL komutları ile Stored Procedure’lere gömülemeyebilir. Örneğin parametre olarak verilen tablo adına çekilecek sorguda Dynamic SQL kullanma zorunluluğumuz ortaya çıkabilir.

Ya da başka bir örnek verecek olursak; sorguda bulunan where bloğunda kullanılacak ifadeler duruma göre farklılık gösterebilir ya da parametre olarak gönderilebilir.

İşte böyle bir durumda sabit bir SQL ifadesi yerine Dynamic SQL kullanmamız gerekebilir. Peki ama komut olarak Exec mi yoksa sp_executeSQL komutunu mu kullanmalıyız?

Bu soruya bir örnek ile cevap bulmaya çalışalım.

Örneğimizde kullandığımız where bloğu değişkenden alındığı için Dynamic SQL kullanmak zorunda kalıyoruz. İlk blokta exec komutu ile kullanıp Cache’lenen Plan’larının tekrar kullanılmadığını göreceğiz.

İkinci örnekte ise aynı işlemi sp_executesql ile yapacağız ve cache’lenen planların tekrar kullanıldığını göreceğiz.

Exec ile Dynamic SQL Kullanımı

--İşlemlere başlamadan önce Plan Cache'i temizliyoruz.
--DİKKAT:Bu işlemi production ortamında YAPMAYINIZ!!
dbcc freeproccache

--exec komutu ile Dynamic SQL kullanıyoruz.
declare @str varchar(max)='',
		@param1 varchar(50)='',
		@param2 varchar(50)=''
declare @strin varchar(50)
set @param1='1'
set @param2='2'
set @str='select * from AdventureWorks.Person.Address where AddressID in ('+@param1+','+@param2+')'
exec(@str)

--Cache'lenen query Planlara bakalım.
select st.text,* 
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%select * from AdventureWorks%')
and st.text not like '%select st.text%'

--exec komutu ile aynı sorguyu tekrar çekiyoruz
declare @str varchar(max)='',
		@param1 varchar(50)='',
		@param2 varchar(50)=''
declare @strin varchar(50)
set @param1='3'
set @param2='4'
set @str='select * from AdventureWorks.Person.Address where AddressID in ('+@param1+','+@param2+')'
exec(@str)

--Cache'lenen query Planlara bakalım.
select st.text,* 
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%select * from AdventureWorks%')
and st.text not like '%select st.text%'

--Her 2 sorgu için 2 ayrı plan oluşturulduğunu görüyoruz.

 

1

sp_executeSQL ile Dynamic SQL Kullanımı

Şimdi aynı işlemi sp_executeSQL ile tekrarlayalım.

--İşlemlere başlamadan önce Plan Cache'i temizliyoruz.
--DİKKAT:Bu işlemi production ortamında YAPMAYINIZ!!
dbcc freeproccache

--sp_executesql ile aynı işlemi yapalım
--sp_executesql 1
declare @param1 int,
	    @param2 int
set @param1=1
set @param2=2
exec sp_executesql N'select * from AdventureWorks.Person.Address where AddressID in (@1,@2)'
		,N'@1 int, @2 int'
		,@param1, @param2
		
--Cache'lenen query Planlara bakalım.
select st.text,* 
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%select * from AdventureWorks%')
and st.text not like '%select st.text%'		

--sp_executesql 2
declare @param1 int,
	    @param2 int
set @param1=3
set @param2=4
exec sp_executesql N'select * from AdventureWorks.Person.Address where AddressID in (@1,@2)'
		,N'@1 int, @2 int'
		,@param1, @param2
		
--Cache'lenen query Planlara bakalım.
select st.text,* 
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%select * from AdventureWorks%')
and st.text not like '%select st.text%'		

--Aynı Query Plan'ın 2 defa kullanıldığını
--tekrar yeni bir Query Plan oluşturulmadığını görüyoruz

 

2

Gördüğünüz gibi her 2 sorgu için ayrı ayrı Query Plan oluşturulmadı ve aynı Query Plan kullanıldı.

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


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

[more]

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

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

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

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

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

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

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

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

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

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

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


Insert,Update,Delete işlemlerinde tablonun üzerinde bulunan her index’in update görme ihtimali vardır. Dolayısıyla tablo üzerinde ne kadar çok index var ise DML işlemlerinin performansı o kadar düşecektir.

Bugün sizler ile paylaşacağım script ile kolon sayısından daha fazla index’e sahip tabloları listeleyeceğiz.

[more]

Script’imiz şu şekilde

create table #tmp1 (DBName sysName, SchemaName sysName, ObjectName sysName, IndexCount int, ColumnCount int)

exec sp_msforeachdb 'use ?;
insert #tmp1
select ''?'' as DBName, SchemaName, ObjectName, IndexCount, count(*) as ColumnCount
from (
	select object_schema_name(object_id) as SchemaName
		,object_name(object_id) as ObjectName
		,count(*) as IndexCount		
		,object_id
	from sys.indexes i
	where ObjectProperty(object_id,''IsUserTable'')=1 and index_id>0 
	group by object_id
)xx	
join sys.columns c on c.object_id=xx.object_id
group by SchemaName, ObjectName, IndexCount
having IndexCount>count(*)
'

select * from #tmp1

drop table #tmp1

 

Örnek bir çıktı aşağıdaki gibi olacaktır.

image

Örneğin ilk tabloda 2 kolon varken bu tablo 3 adet index’e sahip.

Yukarıdaki script’in sizin ortamlarınızda kaç sonuç döndürdüğünü merak ediyorum. Bu script’i kendi ortamınızda çalıştırıp çıkan sonucu yorum olarak paylaşır mısını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