Bu makalede MSSQL Server Tempdb Veritabanını Dolduran Sorguları detaylı bir şekilde görmüş olacağız. Bazen beklenmedik bir şekilde Tempdb diskimiz büyür. Gerçek sistem üzerinde tempdb sistem veritabanının bulunduğu disk dolmuş durumda, böyle durumlarda neler yapılması gerektiğini detaylı bir şekilde inceleyelim.
Aşağıdaki script ile tempdb dosyalarının boyutu, bulunduğu path bilgisi ve growth değerini görebiliriz.
USE tempdb;
GO
SELECT
name AS [FileName],
size * 8 / 1024 AS [CurrentSizeMB], -- Şu anki boyut
growth * 8 / 1024 AS [GrowthIncrementMB], -- Artış miktarı
max_size * 8 / 1024 AS [MaxSize_MB], -- Maksimum boyut
physical_name AS [FilePath]
FROM sys.database_files;
Tempdb dosya sayısının yeterli olup olmadığını anlamak için sayfamızda tempdb contetion makalesinden okuyabilirsiniz.
Tempdb veritabanı, SQL Server’ın sistem veritabanlarından biridir ve geçici nesneler veya işlemler için kullanılır. Tempdb’nin dolmasına neden olan durumlar, genellikle yoğun kaynak tüketimi gerektiren işlemler veya kötü optimize edilmiş sorgular nedeniyle oluşur. İşte tempdb’nin dolma sebepleri ve bunları tespit etmek için kullanılabilecek detaylı açıklamalar ve scriptler:
Tempdb’nin Dolma Sebepleri
1. Geçici Tablolar
Kullanıcı tarafından oluşturulan (TempTable,Table variables,Cursor) geçici tablolar. Sistem tarafından oluşturulan geçici tablolar (ör. spool işlemleri).
2. Row Version Store
Snapshot izolasyon seviyesi veya online indeks işlemleri nedeniyle oluşan veri versiyonlama bilgileri. Bazı Isolation Level’ler de row versioning kullanılır ve row versioning işlemi tempdb’de yapılır. Bir satır güncellendiğinde orijinal halini tempdb’de saklar. Bu yüzden Isolation Level’i Snapshot Isolation ya da Read Committed Snapshot Isolation Level’e çekecekseniz tempdb veritabanınızın doğru yapılandırılmış olması ve yeterli disk alanının olması gerekir.
3. Sort İşlemleri
Sorgu sırasında yapılan sıralama işlemleri (ORDER BY, GROUP BY).
4. Hash Join İşlemleri
Büyük veri kümeleri arasında hash join işlemleri.
5. Kötü Optimize Edilmiş Sorgular
Planlama sırasında gereksiz miktarda tempdb kullanımı.
6. Index Rebuild veya Maintenance İşlemleri
Büyük indeksler üzerinde online rebuild işlemleri.
7. Yerleştirilmiş Objeler
Tablo değişkenleri veya diğer nesneler.
8. Otopilot Yükleme ve Büyütme
Tempdb’nin başlangıç boyutunun yetersiz olması ve sık sık büyüme işlemi yapması.
Tempdb Kullanımını İzlemek için Scriptler
1. Aşağıdaki komut ile tempdb veritabanının ne kadar boyutta olduğu ve ayrıca ne kadar boş alana sahip olduğunu görebiliriz.
USE tempdb;
GO
SELECT
SUM(size) * 8 / 1024 AS [TotalSizeMB], -- Toplam tempdb boyutu
SUM(FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS [UsedSizeMB], -- Kullanılan alan
(SUM(size) - SUM(FILEPROPERTY(name, 'SpaceUsed'))) * 8 / 1024 AS [FreeSizeMB] -- Boş alan
FROM sys.database_files;
Not: Tempdb’ye atılan sorgular session bittiği anda silinir, tempdb’nin içinde daha sonra tutulmaz.
2. Aşağıdaki komut ile hangi sorgu tempdb’de ne kadar yer kaplıyor görebiliriz.
SELECT
t1.session_id,
t1.user_objects_alloc_page_count * 8 / 1024 AS [UserObjectsMB],
t1.internal_objects_alloc_page_count * 8 / 1024 AS [InternalObjectsMB],
t2.text AS [Query_Text]
FROM sys.dm_db_task_space_usage t1
JOIN sys.dm_exec_requests r ON t1.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t2
WHERE t1.session_id > 50 -- Sistem oturumlarını hariç tutuyoruz
ORDER BY [UserObjectsMB] DESC, [InternalObjectsMB] DESC;
UserObjectsMB: Kullanıcı tarafından oluşturulan geçici nesnelerin kullandığı bellek miktarını gösterir. Örnek: Geçici tablolar (#temp), Tablolar içinde oluşturulan geçici değişkenler (@table) Cursors vs.. Kullanıcı işlemleri sırasında oluşur ve genelde geçici veri işlemleriyle ilişkilidir.
InternalObjectsMB: SQL Server’ın kendi işlemleri sırasında otomatik olarak oluşturduğu geçici nesneler için kullandığı bellek miktarını belirtir. Örnek: Karmaşık sorgularda oluşturulan geçici çalışma tabloları (örneğin: sort, hash join, spool işlemleri sırasında) Sorgu planlarında yer alan içsel hesaplamalarda kullanılmaktadır.
session_id = 434 olan oturumda çok yüksek miktarda InternalObjectsMB kullanımı var (örneğin 67001 MB). Bu genellikle büyük veri işleme ya da karmaşık sorguların çalıştırılması sırasında oluşur. UserObjectsMB değeri ise 0, yani bu oturum kullanıcı tarafından oluşturulan geçici nesneleri kullanmıyor.
3. Row Version Store Kullanımını aşağıdaki sorgu yardımıyla görebiliriz.
Version Store, snapshot izolasyon seviyesi veya uzun süren transaction’lar nedeniyle fazla kaynak kullanabilir. Uzun süren snapshot transaction’larını bulunup ve sonlandırılması gerekmektedir. Bu yapı bir satır güncellendiğinde orijinal halini tempdb’de saklar. Özellikle Read Committed Snapshot(RCSI) ya da SNAPSHOT Isolation seviyelerini kullanıyorsanız, temdb’nin büyüklüğünü yönetmek için bu dmw size çok yardımcı olacaktır.
USE tempdb;
GO
SELECT
SUM(allocated_extent_page_count) * 8 / 1024 AS [Allocated_MB], -- Ayrılmış toplam alan
SUM(version_store_reserved_page_count) * 8 / 1024 AS [Version_Store_MB], -- Version Store alanı
SUM(unallocated_extent_page_count) * 8 / 1024 AS [Unallocated_MB] -- Kullanılmayan alan
FROM sys.dm_db_file_space_usage;
Eğer tempdb veritabanının boyutunun büyük kısmı version store nedeniyle doluyorsa aşağıdaki script’i kullanarak uzun süredir çalışan sorguları bulmamız gerekiyor.
SELECT *
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
Bir başka kullanacağımız komut ise aşağıda bulunmaktadır.
SELECT
DB_NAME(database_id) as 'Veritabanı İsmi',
reserved_page_count,
reserved_space_kb
FROM sys.dm_tran_version_store_space_usage;
Version store nedeniyle ne kadar page ve alan ayırdığını yukarıdaki sorgu yardımıyla bulabiliriz.
4. En Çok Tempdb Kullanan Sorguları Bulma
SELECT TOP 10
r.session_id,
r.status,
r.start_time,
r.command,
t.text AS [Query_Text],
tsu.user_objects_alloc_page_count * 8 / 1024 AS [UserObjectsMB],
tsu.internal_objects_alloc_page_count * 8 / 1024 AS [InternalObjectsMB]
FROM sys.dm_exec_requests r
JOIN sys.dm_db_task_space_usage tsu ON r.session_id = tsu.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) DESC;
5. Internal olarak oluşturulan nesneleri tespit etmek için aşağıdaki script kullanılmaktadır. 4. Bölümde bulunan sorgu yardımıyla da öğrenilebilir.
SELECT
session_id,
internal_objects_alloc_page_count as Internal_Object_Page_Count,
internal_objects_alloc_page_count * 8 / 1024 AS [Internal_Objects_MB], -- Ayrılan toplam alan (MB)
internal_objects_dealloc_page_count as Internal_Object_Deallocated_Page_Count,
internal_objects_dealloc_page_count * 8 / 1024 AS [Deallocated_MB], -- Boşaltılan toplam alan (MB)
(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) * 8 / 1024 AS [Active_MB] -- Hâlâ kullanımda olan alan (MB)
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 -- Sistem oturumlarını hariç tutuyoruz
ORDER BY [Active_MB] DESC;
Aşağıdaki sorgu yardımıyla internal olarak tempdb de oluşturulan page’lerin sayısını ve boyutunu gösterir.
Use tempdb
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;
Tempdb’de o an çalışan sorguları da aşağıdaki script yardımıyla öğrenebilirsiniz. Öncelikle yukardaki sebeplerden hangisi sebebiyle tempdb doluyor bunu belirlemelisiniz. Daha sonra bu sorgu yardımıyla aradığınız sorguyu bularak uygulamacıya düzelttirebilir ya da kendiniz düzeltebilirsiniz. Tempdb dolması gibi durumlarda kill işlemi yapılmaktadır.
SELECT
r.session_id,
r.status,
r.start_time,
r.command,
t.text AS [Query_Text],
qp.query_plan AS [Query_Plan], -- Sorgunun yürütme planı
tsu.user_objects_alloc_page_count * 8 / 1024 AS [User_Objects_MB], -- Kullanıcı nesneleri için kullanılan alan (MB)
tsu.internal_objects_alloc_page_count * 8 / 1024 AS [Internal_Objects_MB], -- İçsel nesneler için kullanılan alan (MB)
tsu.user_objects_dealloc_page_count * 8 / 1024 AS [Deallocated_User_Objects_MB], -- Kullanıcı nesnelerinden boşaltılan alan
tsu.internal_objects_dealloc_page_count * 8 / 1024 AS [Deallocated_Internal_Objects_MB] -- İçsel nesnelerden boşaltılan alan
FROM sys.dm_exec_requests r
JOIN sys.dm_db_task_space_usage tsu ON r.session_id = tsu.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp -- Query planını alıyoruz
WHERE tsu.user_objects_alloc_page_count > 0
OR tsu.internal_objects_alloc_page_count > 0 -- Tempdb kullanan sorguları filtreler
ORDER BY (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) DESC;
Tempdb’nin Dolmasını Önlemek için Öneriler
1. Tempdb başlangıç boyutlarını yeterli düzeyde ayarla ve otomatik büyümeyi kontrollü yap.
2. Büyük sıralama, hash join veya spool işlemleri içeren sorgular optimize edilmeli.
3. Gerekirse alternatif izolasyon seviyeleri kullanılabilir.
4. Online indeks rebuild işlemlerini dikkatle planlanması lazım.
5. Performansı artırmak için tablo değişkenleri yerine geçici tabloları kullanmayı değerlendirin.
6. Uzun süre çalışan snapshot transaction’larını kısaltılması gerekmektedir.
Bu scriptler ile hem tempdb’nin anlık durumunu analiz edebilir hem de dolma sebeplerini detaylı bir şekilde öğrenebilirsiniz. Yukarıda sorunlu olan değeri kill ettikten sonra tempdb dolluluk yüzdesi eski haline gelmektedir. Tabi sunucu restart olduktan sonra, Başka bir makalede görüşmek dileğiyle..
Onlar, siz birbirinizi namaza çağırdığınızda onu alay ve oyun (konusu) edinirler. Bu, gerçekten onların akıl erdirmeyen bir topluluk olmalarındandır. Maide Suresi, 58. Ayet
