SQL Splitting TempDB into multiple data files

SQL server, kullanıcılar tarafından oluşturulan geçici tablolar ve birçok sistem görevinin işlenmesi için tempdb isimli bir veri tabanı kullanır. Bu veri tabanı SQL servisi her açıldığında otomatik olarak oluşturulur. Default olarak bir data bir log dosyasından ibarettir. Bu çoğu SQL kullanıcı için yeterlidir fakat iş yükü ağır olan sunucularda tempdb sayıları arttırarak iş yükünü bölmemiz gerekebilir.

Bu makalede SQL sunucumuzda bulunan tempdb sayılarını çoğaltarak sorgu performansımızı arttıracağız.

Önemli Not: Database sunucularda performansın arttırılması nedeni ile data ve log veri kaynaklarının ayrı disklerde bulundurulması tercih edilir. Eğer data ve log veri kaynaklarınız ayrı disklere taşımanız gerekiyor ise “SQL TempDB farklı diske taşıma işlemi” isimli makalemdeki adımları izleyerek taşıma işlemini gerçekleştirebilirsiniz.

Başlamadan önce, bu işlemlerin gerekliliğini anlamak için SQL performansını izlememiz gerekir. Aşağıda verdiğim sorguları kullanarak sunucu istatistiklerine ve bekleyen sorgularınız olup olmadığını belirleyebilirsiniz.

Bekleyen sorgular;

SELECT TEXT, query_plan, requested_memory_kb,
granted_memory_kb,used_memory_kb, wait_order
FROM sys.dm_exec_query_memory_grants MG
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)

Waits time listesi;

WITH Waits AS 
 ( 
 SELECT  
   wait_type,  
   wait_time_ms / 1000. AS wait_time_s, 
   100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, 
   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn 
 FROM sys.dm_os_wait_stats 
 WHERE wait_type  
   NOT IN 
     ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 
   'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 
   'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT') 
   ) -- filter out additional irrelevant waits 
    
SELECT W1.wait_type, 
 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, 
 CAST(W1.pct AS DECIMAL(12, 2)) AS pct, 
 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct 
FROM Waits AS W1 
 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn 
GROUP BY W1.rn,  
 W1.wait_type,  
 W1.wait_time_s,  
 W1.pct 
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

Eğer PAGELATCH_UP, PAGELATCH_EX veya PAGELATCH_SH satırlarında waits time değerlerinin yüksek olduğunu görüyorsanız sunucuda darboğaz var demektir.

Detayları microsoft resmi yayınından inceleyebilirsiniz https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d

TempDB bölme işlemi;

TempDB bölme işleminde SQL sunucumuzda tanımlı her mantıksal cpu için bir tempdb oluşturabiliriz. Eğer mantıksal cpu 8 den fazla ise sorun gözlemlenmediği sürece 8 in üstüne çıkılmaması tavsiye ediliyor.

Detayları yine üstteki linkten inceleyebilirsiniz.

Sunucumuzda tanımlı 8 mantıksal cpu bulunuyor. T-SQL ile tempdb mizi sekize bölelim ve 10GB lık statik veri boyutları tanımlayalım.

Aşağıdaki tempdb sayısını, adres yolu ve veri boyutlarını kendi yapınıza uygun şekilde düzenleyerek kullanabilirsiniz.

USE [master]; 
GO 
alter database tempdb modify file (name='tempdev', size = 10GB);
GO

USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev1', FILENAME = N'K:\TempDB\tempdb1.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'K:\TempDB\tempdb2.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = N'K:\TempDB\tempdb3.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev4', FILENAME = N'K:\TempDB\tempdb4.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev5', FILENAME = N'K:\TempDB\tempdb5.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev6', FILENAME = N'K:\TempDB\tempdb6.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev7', FILENAME = N'K:\TempDB\tempdb7.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev8', FILENAME = N'K:\TempDB\tempdb8.ndf' , SIZE = 10GB , FILEGROWTH = 0);
GO

TempDB mizi sekize bölmüş olduk

About the Author

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir