Recent comments

None


İçerik Ara











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

© Copyright 2009-2013
Takvim
<<  Aralık 2017  >>
PaSaÇaPeCuCuPa
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567
Keywords

Workgrup ortamlarda SQL Server Mirroring kurulumu domain ortamlarda olduğu kadar kolay yapılamamakta. Örneğin GUI yani ekranları kullanarak workgrup ortamlarda DB Mirroring kurulumu yapılamamakta, script kullanımı şart olmaktadır.

Aşağıdaki blog post’ta bu kurulum adım adım çok güzel bir şekilde anlatılmış.

Step-by-step guide to configure Database Mirroring between SQL Server Instances in a Workgroup

 

Bu bloğun başına birşey gelme ihtimalini göz önüne alarak blog post'u buraya da kopyalıyorum.

[more]

=================

Step-by-step guide to configure Database Mirroring between SQL Server Instances in a Workgroup

 

This post is again in response to requests from customers who want to know how to configure Database Mirroring between instances of SQL Server 2005 in a WORKGROUP.

As you might have noticed, while configuring Database Mirroring using the GUI, it requires us to enter FQDNs of the servers; hence using the GUI, we can configure Database Mirroring between instances of SQL Server in a domain environment.

All credit goes to Varun for creating this step-by-step guide. I take the liberty to post it in my blog link.

Step 1: Create encryption key, certificate and end-points on Principal Instance

/* Execute this against the Principal Instance */
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_PRIN_cert
    WITH SUBJECT = 'HOST_PRIN certificate',
    START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE HOST_PRIN_cert,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = ALL
    )
GO
BACKUP CERTIFICATE HOST_PRIN_cert
    TO FILE = 'D:\certificate\HOST_PRIN_cert.cer'
GO

 

Step 2: Create encryption key, certificate and end-points on Mirror Instance

/* Execute this against the Mirror Instance */
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_MIRR_cert
    WITH SUBJECT = 'HOST_MIRR certificate',
    START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE HOST_MIRR_cert,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = ALL
    )
GO
BACKUP CERTIFICATE HOST_MIRR_cert 
    TO FILE = 'D:\certificate\HOST_MIRR_cert.cer';
GO

Step 3: Create login, user and associate certificate with user on Principal Instance

/* 
*  Execute this against the Principal Instance. The HOST_MIRR_cert.cer
*  needs to be copied on the Principal Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO
CREATE CERTIFICATE HOST_MIRR_cert
    AUTHORIZATION HOST_MIRR_user
    FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO

 

Step 4: Create login, user and associate certificate with user on Mirror Instance

/* 
*  Execute this against the Mirror Instance. The HOST_PRIN_cert.cer
*  needs to be copied on the Mirror Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins, 
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO
CREATE CERTIFICATE HOST_PRIN_cert
    AUTHORIZATION HOST_PRIN_user
    FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer'
    GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
GO

 

Step 5: Create encryption key, certificate and end-points on Witness Instance

/* Execute this against the Witness Instance */
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_WITT_cert
    WITH SUBJECT = 'HOST_WITT certificate',
    START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE HOST_WITT_cert,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = Witness
    )
GO
BACKUP CERTIFICATE HOST_WITT_cert
    TO FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO

 

Step 6: Create login, user and associate certificate with user on Principal Instance

/*
*  Execute this against the Principal Instance. The HOST_WITT_cert.cer
*  needs to be copied on the Principal Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login
GO
CREATE CERTIFICATE HOST_WITT_cert
    AUTHORIZATION HOST_WITT_user
    FROM FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

 

Step 7: Create login, user and associate certificate with user on Mirror Instance

/*
*  Execute this against the Mirror Instance. The HOST_WITT_cert.cer
*  needs to be copied on the Mirror Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login
GO
CREATE CERTIFICATE HOST_WITT_cert
    AUTHORIZATION HOST_WITT_user
    FROM FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

 

Step 8: Create login, user and associate certificate with user on Witness Instance

/*
*  Execute this against the Witness Instance. The HOST_PRIN_cert.cer
*  and HOST_MIRR_cert.cer needs to be copied on the Witness Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO
CREATE CERTIFICATE HOST_PRIN_cert
    AUTHORIZATION HOST_PRIN_user
    FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO
CREATE CERTIFICATE HOST_MIRR_cert
AUTHORIZATION HOST_MIRR_user
FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO

 

Step 9: Create the Mirrored Database on the Mirror Server using backups from the Principal Server

/*
*  Execute this against the Principal Instance.
*/
USE MASTER
GO
BACKUP DATABASE MirrorDB
    TO DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
GO
BACKUP LOG MirrorDB
    TO DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
GO
/*
*  Copy MirrorDB_FullBackup.bak and MirrorDB_LogBackup.trn to the
*  Mirror Server.
*  Execute this against the Mirror Instance.
*/
USE MASTER
GO
RESTORE DATABASE MirrorDB
    FROM DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
    WITH NORECOVERY
GO
RESTORE LOG MirrorDB
    FROM DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
    WITH NORECOVERY
GO

 

Step 10: Setup Mirroring

/*
*  Execute this against the Mirror Instance.
*/
ALTER DATABASE MirrorDB
    SET PARTNER = 'TCP://<>:5022'
GO
/*
*  Execute this against the Principal Instance.
*/
ALTER DATABASE MirrorDB
    SET PARTNER = 'TCP://<>:5023'
GO
ALTER DATABASE MirrorDB
    SET WITNESS = 'TCP://<>:5024'
GO

 

At this point your Database Mirroring should be up and running. You can use the Database Mirroring Monitor to verify the setup as well as to monitor the Synchronization status.

image

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Merhaba arkadaşlar

Türkiye’de 4. defa düzenlediğimiz SQLSaturday etkinliğinde sunmuş olduğum “How Latch Contention Affects the Performance? Common Scenarios and Workarounds from the Field” başlıklı sunumun video kaydını aşağıda bulabilirsiniz.

[more]

Sunumda kullandığım script’lere ve sunuma ise aşağıdan erişebilirsiniz.

https://www.sqlsaturday.com/downloadsessionfile.aspx?presentationid=25921

İyi Seyirler

MSDTC Nedir?

MSDTC (Microsoft Distributed Transaction Coordinator) birden fazla instance üzerinde işlem yapan transaction’ları koordine etmek için windows cluster tarafından kullanılan bir resource’dur. Örneğin A sunucusunda başlayan bir transaction B sunucusunda da işlemler yapar ve bu tarz transaction’lara Distributed transaction denir. Olası bir rollback ya da failover durumundan dolayı her 2 node’u ilgilendiren transaction bilgilerinin bir resource tarafından log’lanması gerekir. Bu işlemi MSDTC yapar.

[more]

Windows Server 2008 Öncesi MSDTC

Windows Server 2008’den önce windows cluster’da sadece 1 adet MSDTC resource’u bulundurulabiliyordu. Yani cluster üzerindeki bütün application’lar distributed transaction yönetimi için aynı MSDTC resource’unu kullanıyordu. Bu durumun kötü yanları aşağıdaki gibiydi;

· Tüm application’lar aynı MSDTC resource’unu kullandığı için bir application’ın MSDTC üzerinde oluşturduğu yük diğer application’ların performansını etkiliyordu.

· Tek bir MSDTC resource olduğu için ve MSDTC ile application’lar farklı node’lar üzerinde çalışma ihtimalinden dolayı network kaynaklı performans problem ile karşılaşılabiliyordu. Örneğin MSDTC resource’u A node’unda iken SQL Server resource’larından biri B node’unda çalışıyor ve MSDTC erişimi network üzerinden gerçekleşiyordu. Bu da performans sıkıntısına sebebiyet verebiliyordu.

Windows server 2008 ile beraber gelen multiple MSDTC bulundurma özelliği ile bu problemler aşılabilmektedir.

 

Windows Server 2008 Sonrası MSDTC

Windows Server 2008 ile beraber bir WSFC’de birden fazla MSDTC resource oluşturulabilir. Seçenekler aşağıdaki gibidir.

· Birden fazla MSDTC resource grubu yapmak

· Resource grupların içerisinde MSDTC resource’u bulundurmak

· Local MSDTC kullanmak

Şimdi bu seçenekler ile uygulanabilecek senaryolara bakalım.

 

Her SQL Server resource grubu içerisinde MSDTC resource’u tanımlamak

Bu seçenek en iyi high performance’i sunan seçenektir. MSDTC resource’i SQL Server resource grubunun içinde olduğundan dolayı MSDTC ile SQL Server resource’u her daim aynı node’un üzerinde çalışır.

Dolayısıyla network üzerinden MSDTC kullanımı olmayacak ve performans iyi yönde etkilenecektir.

SQL Server otomatik olarak grubun içerisindeki MSDTC resource’unu kullanır. Eğer bu resource fail ya da offline durumdaysa diğer MSDTC resource’ları otomatik olarak kullanılmaz ve distributed transaction’lar hata alır. Eğer diğer MSDTC resource’larının kullanılması isteniyorsa fail durumda olan MSDTC resource’unun SQL Server resource grubundan çıkartılması gerekir.

MSDTC kullanımı kritik durumda ise MSDTC resource’unun “affect group” özelliği active edilebilir. Bu durumda eğer MSDTC resource’u fail eder ve aynı node üzerinde tekrar çalıştırılamazsa grubun tamamı diğer node’a failover yapılacaktır. Hatta SQL Server resource grubu MSDTC resource’una dependent hale getirilebilir. Bu şekilde MSDTC resource fail eder etmez SQL Server resource’u da fail edecektir.

 

Her SQL Server Resource grubu için dedicated MSDTC resource grubu

Bu yöntem seçenekler arasında en yüksek high availability’i sunar. SQL Server resource grubu ve MSDTC resource grubu farklı gruplar olduğu için bağımsız olarak failover yapabilecek yani fail durumları birbirilerine etkilemeyecektir.

Fakat, bağımsız olarak failover yapılabildiği için MSDTC ve SQL Server farklı node’lar üzerinde çalışabilir. Bu yüzden network’ten MSDTC kullanımının performansınının kabul edilebilir bir değer olup olmadığı test edilmelidir.

Bu konfigurasyonda MSDTC için ayrı bir grup yapıldığı için her MSDTC grubu ayrı bir diske ihtiyaç duyacaktır.

Eğer aynı cluster ortamında çok fazla SQL Server resource grup var ise her biri için ayrı MSDTC resource grupları yapılacağı için yönetilebilirlik düşünülmesi gereken bir başka noktadır.

Bu senaryoda SQL Server resource grubu ve MSDTC grubu eşleşmeleri mapping üzerinden yapılmaktadır. Örneğin MSSQL$INST1 adında bir SQL Server resource grubunu MSDTC1 adında bir MSDTC resource grubu ile mapping yapmak için command prompt’ta aşağıdaki kod çalıştırılabilir.

msdtc -tmMappingSet -name “Mapping1” -service “MSSQL$INST1” -clusterResourceName “MSDTC1”

Burada dikkat edilmesi gereken önemli noktalardan biri; yanlış bir mapping yapılsa bile yukarıdaki kod hatasız tamamlanacak ama mapping çalışmayacaktır. Böyle bir durum olup olmadığı yeni bir query ekranında “BEGIN DISTRIBUTED TRAN” komutu çalıştırılarak gözlemlenebilir.

Mapping ile ilgili diğer işlemler ise aşağıdaki gibidir

· Tüm mapping’leri görmek için;
msdtc -tmMappingView *

· Bir mapping’i silmek için;
msdtc -tmMappingClear -name <MappingName>

· Mapping’in test edilmesi için yeni bir query ekranında
BEGIN DISTRIBUTED TRAN

Tek bir MSDTC Resource Grubu

Windows Server 2003’teki gibi tüm application’lar için tek bir MSDTC resource grubu yapıldığı senaryodur. En basit yöntem olarak bilinir. Bu yöntemde tüm application’lar aynı MSDTC’yi kullandığı için MSDTC’de bottleneck oluşabilir. Ayrıca SQL Server ile MSDTC’nin farklı node’lar üzerinde çalışma ihtimalinden dolayı da performans sıkıntısı yaşanabilir. Eğer MSDTC uygulama için çok önemli değilse bu senaryo kullanılabilir.

 

Local MSDTC

Windows Server 2008 ile beraber node’ların kendisinde de local MSDTC instance’i bulunmaktadır. MSDTC logları node’ların kendi üzerinde saklandığı için high availability sunmaz yani olası bir failover’da eğer local MSDTC kullanılıyorsa distributed transaction bilgileri kaybedilecektir. Yüksek erişilebilirlik sunmadığı için production ortamlarında kullanılması tavsiye edilmez.

 

Hybrid Senaryo

Yukarıda anlatılan senaryoların birlikte kullanıldıkları senaryodur. Örneğin aynı cluster’da bulunan bazı application’lar için dedicated MSDTC resurce group’lar yapılırken, bazı application’lar için ortak MSDTC resource’u kullanılabilir. Hatta cluster aware olmayan application’lar için de local MSDTC kullanılabilir.

 

MSDTC Kullanım Önceliği

Eğer bir SQL Server resource grubunda MSDTC resource’u varsa ve aynı zamanda başka bir MSDTC resource’u ile mapping yapıldıysa hangi MSDTC resource’unun kullanılacağı aşağıda sıralanmış kullanım önceliğine göre belirlenir.

1. Eğer varsa SQL Server resource grubunda bulunan MSDTC kullanılır.

2. Eğer varsa mapping yapılmış MSDTC resource’u kullanılır.

3. Cluster’daki default MSDTC resource kullanılır. (Cluster’a ilk eklenen MSDTC resource’una default MSDTC denmektedir.)

4. Local MSDTC kullanılır.

1 ve 2 numarada bulunan MSDTC resource’lar fail durumdaysa otomatik olarak sonraki seçeneklere geçilmez. Öncelikle bu resource’ların silinmesi ya da gruptan çıkartılması gerekmektedir.

Örneğin ilk olarak SQL Server resource grubunda bulunan MSDTC kullanılmak istenir. Eğer bu resource fail durumdaysa otomatik olarak 2. adıma geçilmez. 2. adıma geçilebilmesi için SQL Server resource grubunda bulunan MSDTC resource’unun remove edilmesi gerekmektedir.

Ya da mapping edilmiş MSDTC resource’u fail durumdaysa 3. adıma geçilebilmesi için mapping’in kaldırılması gerekir.

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.

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

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.

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.

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.

13 farklı teknolojide 20 webcast’i konusunda uzman Microsoft mühendislerinden dinleme fırsatını kaçırmayın.!

 

Orjinal duyuru için : http://blog.microsoft.com.tr/microsoft-global-business-support-mea-ekibinin-web-seminer-takvimi-belli-oldu.html

 

Hemen kayıt olun ve bu fırsatı kaçırmayın!

Önümüzdeki 3 aylık periyotta gerçekleştirilecek, benim de organizasyon ekibinde bulunduğum ücretsiz webcast serisininin duyurusunu yapmak istiyorum.

Konusunda uzman Microsoft mühendis ve danışmanları tarafından verilecek ücretsiz webcast listesini aşağıda bulabilrsiniz

Linklere tıklayarak kayıt olabilir ve Microsoft ürün ve çözümlerinin Microsoft tarafından nasıl kullanıldığı hakkında detaylı bilgi alma şansını yakalayabilirsiniz.

Görüşmek üzere!

Session

Level

Date

Time

What is new in Hadoop 2.0 and Yarn

Ahmed Abd Allah - Senior Consultant

200

1/6/2014

2:00 PM (GMT+2)

HA & DR with SQL Server AlwaysOn Availability Groups

Turgay Sahtiyan – Senior Premier Field Engineer

200

1/8/2014

11:00 AM (GMT+2)

BizTalk Server Roadmap & What’s new in BizTalk Server 2013
Mohamed Malek – Principal Consultant

200

1/12/2014

2:00 PM (GMT+2)

SQL Server Database Migration

Ahmad Shahin - Consultant

300

1/14/2014

2:00 PM (GMT+2)

SQL Server AlwaysOn with Log Shipping Implementation, Experience from the Field

Situmalli Chandra Mohan – Senior Premier Field Engineer

300

1/14/2014

11:00 AM (GMT+2)

SQL Server Performance Troubleshooting – Which tools can be used for troubleshooting?

Batuhan Yildiz - Senior Premier Field Engineer

300

1/26/2014

11:00 AM (GMT+2)

Big Data Reference Architecture

Ahmed Abd Allah - Senior Consultant

300

2/6/2014

2:00 PM (GMT+2)

BizTalk Rules Engine Concepts

Mohamed Malek – Principal Consultant

300

2/16/2014

2:00 PM (GMT+2)

SQL Server Consolidation Strategies & Practices

Ahmad Shahin - Consultant

300

2/18/2014

2:00 PM (GMT+2)

Top Ten Performance Tips for SQL Server Developers

Turgay Sahtiyan – Senior Premier Field Engineer

300

2/19/2014

11:00 AM (GMT+2)

Writing Custom BizTalk WCF LOB Adapter Deep Dive

Mohamed Malek – Principal Consultant

400

3/9/2014

2:00 PM (GMT+2)

Journey in the life of the Data Scientist

Ahmed Abd Allah - Senior Consultant

200

3/12/2014

2:00 PM (GMT+2)

SQL Server AlwaysOn Availability Groups Deep Dive

Turgay Sahtiyan – Senior Premier Field Engineer

400

3/19/2014

11:00 AM (GMT+2)

SQL Server Internals – Wait Types

Batuhan Yildiz - Senior Premier Field Engineer

300

3/23/2014

11:00 AM (GMT+2)

SharePoint & DR Scenarios

Ahmad Shahin - Consultant

300

3/25/2014

2:00 PM (GMT+2)

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

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ı.

SQL Server’a windows authentication yani windows account’lar ile bağlantı kurulurken önerilen bağlantı protokolü KERBEROS’tur. Bu yazımda güvenlik açısından oldukça önemli olan bu best practice üzerine konuşuyor olacağız.

[more]

Kerberos Nedir?

Kerberos Windows 2000 ile beraber gelmiş bir güvenlik protokolüdür. NTLM’e oranla daha güvenli olduğu için windows authentication’ların KERBEROS üzerinden bağlantısı kurması best practice’dir.

Kerberos bağlantı protokolü authentication esnasında SPN’leri kullanır. SPN (Server Principal Name), bir servis ya da instance’ı tanımlayan unique bir addır. Eğer SQL Server service account’u için uygun bir SPN oluşturulmadıysa Kerberos bağlantı isteği düzgün bir şekilde karşılanamayacak ve bağlantı NTLM’e dönecektir.

Bağlantılarım Kerberos mu?

SQL Server’a kurulmuş windows authentication bağlantıların Kerberos protokolünü kullanıp kullanmadığı sys.dm_exec_connections DMV’sinden sorgulanabilir.

select es.login_name, ec.auth_scheme, *
from sys.dm_exec_connections ec
left join sys.dm_exec_sessions es on es.session_id = ec.session_id
where auth_scheme <> 'SQL' and net_transport = 'TCP'

Bu sorgunun benim sistemimdeki örnek çıktısı aşağıdaki gibidir.

image

Sorgu sonucundaki auth_scheme değerlerinin KERBEROS olmasını bekliyoruz. Eğer değil ise SPN problemimiz olduğunu düşünebiliriz.

SPN’ler Nasıl Sorgulanır?

Bir service account’una tanımlanmış SPN’leri sorgulamak için aşağıdaki komut kullanılabilir.

SetSPN -L <domain>\<account>

Bir SPN’in olup olmadığına ise aşağıdaki komut ile bakılabilir.

setspn -Q <SPN>

Örneğin MySQLServer isimli sunucumuzun default instance’ında MyDomain\MyServiceAccount service account’unun kullanıldığını düşünelim. Bu durumda aşağıdaki komutlar ile olması gereken SPN’lerin olup olmadığını sorgulayabiliriz.

setspn -Q MSSQLSvc/MySQLServer

setspn -Q MSSQLSvc/MySQLServer.MyDomain.com

setspn -Q MSSQLSvc/MySQLServer.MyDomain.com:1433

Eğer default değil de named instance (örneğin INST1) kullanılıyorsa bu durumda SPN kayıtları şu şekilde olacaktır.

setspn -Q MSSQLSvc/MySQLServer:INST1

setspn -Q MSSQLSvc/MySQLServer.MyDomain.com INST1

SPN’lerin olması kadar doğru service account’u üzerinde olması da önemlidir. Örneğin şöyle bir senaryo düşünelim. SQL Server “A” service account’u ile çalışıyordu ve gerekli SPN’leri create etmiştik. Daha sonra service account’unu “B” yapıyoruz. Bu durumda SPN kayıtları var gözükecek ama yanlış service account’u üzerinde olduğundan dolayı Kerberos protokolü kullanılamayacaktır. Bu yüzden hem SPN’leri kontrol etmeli hem de aşağıdaki komut ile ilgili service account’una bağlı SPN’ler sorgulanmalı ve SQL Server service SPN’lerinin bu listede olduğu teyit edilmelidir.

setspn -L MyDomain\MyServiceAccount

SPN Nasıl Oluşturulur?

SQL Server service’i için SPN oluşturmanın 2 yolu vardır.

  • İlk yöntem SQL Server service account’una active directory üzerinden “read servicePrincipalName” ve “Write servicePrincipalName” yetkilerinin verilmesidir. Bu yetkiler verildikten sonra service restart edilir. Service açıldığında otomatik olarak SPN kaydı oluşturulur. Service kapandığında ise ilgili SPN kaydı otomatik olarak silinir.

    SQL Server service account’una nasıl yetki verileceği ilgili şu dokümandan bilgi alabilirsiniz. (http://support.microsoft.com/kb/319723 - Step 3)

    Örnek bir ekran görüntüsü aşağıdaki gibidir.

    image
  • İkinci yöntem ise SPN’lerin manuel olarak yönetilmesi yani create, drop edilmesidir. Bu yöntem her ne kadar best practice olsa da sıkı bir şekilde yönetilmesi gereken bir konudur. Yanlış SPN’lerin olması ya da SPN’lerin hiç olmaması Kerberos kullanımını imkansız hale getirecektir.

 

Manuel SPN Oluşturma

Manuel olarak SPN oluşturmak için aşağıdaki komutu kullanabilirsiniz.

setspn -a mssqlsvc/MySQLServer.MyDomain.com:1433 MyDomain\MyServiceAccount

Bu komutun aynı zamanda MySQLServer ve MySQLServer.MyDomain.com için de çalıştırılması gerekmektedir.

Daha önce de dediğim gibi security açısından servis account’una SPN create/drop hakkı verilmektense SPN’lerin yukarıdaki şekilde manuel olarak create edilmesi best practice’dir

SPN Silme

SPN’leri manuel olarak silmek için aşağıdaki komutu kullanabilirsiniz.

setspn -d mssqlsvc/MySQLServer.MyDomain.com:1433 MyDomain\MyServiceAccount

Gene gerekiyorsa bu komutu MySQLServer ve MySQLServer.MyDomain.com için de çalıştırabilirsiniz.

Sonuç

SQL Server’da windwos authentication bağlantıların Kerberos protokolü üzerinden kurulması önemli bir security best practice’idir. Bağlantılar periyodik olarak kontrol edilmeli, eğer Kerberos yerine NTLM protokolü görülüyorsa SPN’ler kontrol edilmelidir.

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.

SQLPass Turkey Chapter (www.sqlserveronculeri.com), 5 Ekim 2013'te üçüncüsünü düzenleyeceği SQLSaturday etkinliğinde bu sene bir farklılık katarak etkinliğin bir önceki günü olan 4 Ekim 2013 Cuma günü tam gün PreCon eğitimi düzenliyor.

SQL Server MCM Andreas Wolter'ın "SQL Server Performance Analysis & Tuning Techniques" başlıklı bu eğitimine 15 Eylül'e kadar erken kayıt indiriminden faydalanarak kayıt olabilirsiniz.

Detaylar için;
http://www.sqlserveronculeri.com/Files/SQLSaturday258/SSOPreCon/SSOPreConDuyuru.html

Uygulama geliştirme sürecinde SP'lerin adlarını değiştirmemiz gerekebilir. Bu değişikliği sp_rename ile yapmaktansa drop&create ile yapmak best practice'dir

[more]

İsim değişikliğini sp_rename ile yaptığımızda sys.sql_modules'teki definition alanında bulunan SP adı değiştirilmez. Değiştirilmediği için bu catalog view'i ya da sp_helptext system procedure'unu kullanırsanız yanlış bir code'a erişmiş olursunuz. Bu yüzden SP değişikliklerini drop&create şeklinde yapmanızı kesinlikle tavsiye ederim. (Detaylı bilgi için http://msdn.microsoft.com/en-us/library/ms188351.aspx)

Şimdi bu durumu görmek için. Önce bir SP create edelim ve daha sonra bu SP'nin adını sp_rename ile değiştirip sys.sql_modules ve sp_helptext sonuçlarına bakalım.

--bir calisma DB'si create edelim
create database dbTurgay
go
use dbTurgay
go
--Bir SP create edelim
create proc mySP 
as
	select * from sys.databases
GO
--SP'nin adini sp_rename ile mySP_New seklinde degistirelim
exec sp_rename 'mySP','mySP_New'
GO
--ve daha sonra sys.modules ve sp_helptext sonuclarina bakalım
select * from sys.sql_modules
where object_id =object_id('mySP_New')
GO
sp_helptext 'mySP_New'
GO

image

Gördüğünüz gibi SP’nin adını değiştirmemize rağmen sys.sql_modules ve sp_helptext hala eski SP adını göstermekte.

Değişikliği SSMS GUI üzerinden yapacak olursan gene aynı problemli sonuç ortaya çıkacaktır.

Problemsiz bir şekilde isim değişikliği yapmanın tek yöntemi SP’yi önce drop etmek daha sonra tekrar create etmektir.

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? :)

SQL Server 2012 AlwaysOn availability group kuruluşu yaparken aşağıdaki hatayı alaiblirsiniz.

Msg 41131, Level 16, State 0, Line 2
Failed to bring availability group 'availability_group' online. The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

[more]

Aynı Availability group kuruluşunu TSQL ile yaparsanız hata şu şekilde olacaktır.

Msg 41066, Level 16, State 0, Line 1
Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID '8d0cd20e-f78b-413e-b4e4-b293e52630bd') online (Error code 5018).  The WSFC service may not be running or may not be accessible in its current state, or the WSFC resource may not be in a state that could accept the request.  For information about this error code, see "System Error Codes" in the Windows Development documentation.
Msg 41160, Level 16, State 0, Line 1
Failed to designate the local availability replica of availability group 'AGContoso1' as the primary replica.  The operation encountered SQL Server error 41066 and has been terminated.  Check the preceding error and the SQL Server error log for more details about the error and corrective actions.
Msg 41152, Level 16, State 2, Line 1
Failed to create availability group 'AGContoso1'.  The operation encountered SQL Server error 41160 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

SQL Server Error Log’da ilgili hatalara baktığınızda ise şu mesajları göreceksiniz;

2013-06-26 20:43:26.86 spid59      The state of the local availability replica in availability group 'AGContoso1' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'. The replica state changed because of either a startup, a failover, a communication issue, or a cluster error. For more information, see the availability group dashboard, SQL Server error log, Windows Server Failover Cluster management console or Windows Server Failover Cluster log.
2013-06-26 20:43:27.01 Logon       Error: 18456, Severity: 14, State: 5.
2013-06-26 20:43:27.01 Logon       Login failed for user 'CORP\CONTOSOSQL2$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
2013-06-26 20:43:27.05 spid59      The state of the local availability replica in availability group 'AGContoso1' has changed from 'RESOLVING_NORMAL' to 'NOT_AVAILABLE'. The replica state changed because of either a startup, a failover, a communication issue, or a cluster error. For more information, see the availability group dashboard, SQL Server error log, Windows Server Failover Cluster management console or Windows Server Failover Cluster log.

Hatanın sebebi [NT AUTHORITY\SYSTEM] account’unun SQL Server login listesinde bulunmamasıdır. Bu account’u aşağıdaki makalede anlatıldığı gibi tüm Availability Group replica’larda create edersiniz yukarıdaki hatalardan kurtulabilirsiniz.

http://support.microsoft.com/kb/2847723/en-us

SQL Server’ın yeni versiyonu olan SQL Server 2014’ün ilk kullanıcı preview’i dün itibarıyla çıktı. Aşağıdaki adresten indirip kurabilirsiniz.

http://technet.microsoft.com/en-US/evalcenter/dn205290?WT.mc_id=Social_TW_OutgoingEvents_20130625_25292_SQLServer

SQL Server 2014 ile gelen yeni özellikleri en kısa zamanda blog’umda incelemeye başlayacağız. Şimdiden duyurusunu yapayım :)

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