Tempdb Veritabanını Dolduran Sorgular

Tempdb performans açısından sistem veritabanlarının en önemlisidir. Bu yüzden tempdb’yi çok kullanan instance’larda tempdb’yi doğru yapılandırmak gerekir. “Sistem veritabanları” isimli makalemde tempdb’yi nasıl yapılandıracağınız hakkında detay bulabilirsiniz.

Bir gece instance’ım üzerinde tanımlamış olduğum alertler’den aşağıdaki gibi bir hata maili geldi.

Insufficient space in tempdb to hold row versions.  Need to shrink the version store to free up some space in tempdb. Transaction (id=xsn=3669532288 spid=elapsed_time=) has been marked as victim and it will be rolled back if it accesses the version store. If the problem persists, the likely cause is improperly sized tempdb or long running transactions. Please refer to BOL on how to configure tempdb for versioning.

Bu hata tempdb’nin diskinin dolduğunu gösteriyor. İlk olarak tempdb’nin üzerine sağ tıklayarak General sekmesinden boyutuna(size kısmında yazar) baktım. Tempdb’nin büyüklüğünün 3.2 TB olduğunu gördüm. Normalde tempdb bu kadar kullanılmıyordu.

Böyle bir durumda ilk akla gelmesi gereken soru peki bu 3.2 TB’ın ne kadar şu anda boşta? Olmalı.

İçindeki boşluk oranını tespit etmek için aşağıdaki script’i çalıştırdım ve 3.2 TB’ın 16 GB’ının boş olduğunu gördüm.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Bu şu anlama gelir. Şu anda çalışan sorgular tempdb’yi yaklaşık 3.2 TB veriyle doldurmuş. Çünkü tempdb’ye atılan sorgular session bittiği anda silinir, tempdb’nin içinde daha sonra tutulmaz.

Peki tempdb’yi neler tüketir?

1. Temp olarak oluşturulan,
   • Global veya lokal temp tablolar(“Global Ve Lokal Temp Tablo Oluşturmak” isimli makalemi okumanızı tavsiye ederim)
   • Temp stored procedure’ler
   • Table variables(“Table Variable Kullanımı” isimli makalemi okumanızı tavsiye ederim)
   • Cursor(“Cursor Kullanımı” isimli makalemi okumanızı tavsiye ederim)
2. Internal olarak oluşturulan nesneler,
   • Spools(“Execution Planda Spool Kavramı(Eager Spool, Lazy Spool)” isimli makalemi okumanızı tavsiye ederim)
   • Sorting(Sorgudaki sıralama işlemleri)
3. Row Versioning Sebebiyle,
   • Bazı Isolation Level’ler de row versioning kullanılır ve row versioning işlemi tempdb’de yapılır. 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. Isolation Leveller hakkında daha detaylı bilgi almak için “Isolation Level1“,”Isolation Level2” ve “Isolation Level3” makalelerini okuyabilirsiniz.
   • Online Index Operasyonları
   • (MARS)Multiple Active Result Sets(SQL Server 2005 ile gelen bir özelliktir. Eş zamanlı olarak bir connection üzerinden birden fazla batch çalıştırılabilmesi)
   • AFTER Triggers(“SQL Server Trigger Çeşitleri” isimli makalemde detaylarını bulabilirsiniz)

Row Versioning Sebebiyle mi doluyor?

Bu aşamadan sonra ilk olarak SNAPSHOT ya da Read Committed Snapshot Isolation Level’i kullanıyorsanız aşağıdaki script yardımıyla version store tarafından kullanılan page sayısını bulmalısınız. Aşağıdaki ekran görüntüsünde gördüğünüz gibi version store için tempdb kullanım oranı çok az. Problemimizin bu olmadığını görmüş oluyoruz.

USE tempdb
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
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;

Internal Olarak Oluşturulan Nesneler Nedeniyle mi doluyor?

Aşağıdaki sorgu yardımıyla internal olarak tempdb de oluşturulan page’lerin sayısını ve boyutunu gösterir. Benim sorunumun cevabı bu scriptteydi? Tek bir sorgu spool’lar nedeniyle tempdb’yi doldurmuştu. Bu yüzden “Execution Planda Spool Kavramı(Eager Spool, Lazy Spool)” isimli makaleyi yazdım.

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;

Temp olarak oluşturulan Nesneler Nedeniyle mi doluyor?

Aşağıdaki sorgu yardımıyla da kullanıcıların oluşturduğu temp nesnelerin tempdb de kapladığı alanı görebilirsiniz.

Use tempdb
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user 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.

Benim başıma gelen bir örnekte, bir sorgu 3.2 TB’ın neredeyse tamamını dolduruyordu. Uygulamacı ile iletişime geçtiğimde, boyutları 5’er TB’ın üstünde olan 3 veritabanından joinler yaptığını ve son 3 yılın verisini rapor olarak çektiğini söyledi. Uygulamacıya sorguyu parçalamasını(ay ay ya da 3 er aylık periyotlarla çekmesini) söyledim. Böylece her sorgunun her parçasının çalışması bittiğinde internal olarak tempdb’de oluşturulan alan boşaltılacak ve bir sonraki parça önceki parçadan boşalan alanı kullanacaktı. Sorun bu şekilde düzeldi.

;WITH task_space_usage AS (
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text,
                 ERQ.statement_start_offset / 2,
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
                  THEN 0
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC