SQL Server performans yönetiminde tempdb, sistemin “çöp kutusu”, “çalışma alanı” ve bazen de “kara deliği” olabilir. Geçici tablolar, sıralama işlemleri (sort operations) ve özellikle Snapshot Isolation ile gelen Version Store kullanımı, tempdb’nin kontrolsüz büyümesine neden olabilir.
Bu makalede, tempdb kullanım istatistiklerini düzenli olarak bir tabloda arşivleyen ve geriye dönük analiz yapmamıza olanak tanıyan bir yapının kurulumunu inceleyeceğiz.
Öncelikle verileri depolamak için bir tablo oluşturuyoruz.
USE [DBAMON]
GO
CREATE TABLE [dbo].[TempDBUsage](
[captureDate] [datetime] NOT NULL,
[user_object_pages_mb] [bigint] NULL,
[internal_object_pages_mb] [bigint] NULL,
[version_store_pages_mb] [bigint] NULL,
[total_in_use_pages_mb] [bigint] NULL,
[total_free_pages_mb] [bigint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TempDBUsage] ADD DEFAULT (getdate()) FOR [captureDate]
GO
Aşağıdaki prosedür, her çalıştığında o anki tempdb dosya kullanım metriklerini hesaplar ve MB cinsinden tabloya yazar.
USE [DBAMON]
GO
CREATE procedure [dbo].[CaptureTempdbUsage]
as
begin
BEGIN TRY
INSERT INTO [dbo].[TempDBUsage]
([user_object_pages_mb]
,[internal_object_pages_mb]
,[version_store_pages_mb]
,[total_in_use_pages_mb]
,[total_free_pages_mb])
SELECT (SUM(user_object_reserved_page_count)*8)/1024 AS user_object_pages_mb,
(SUM(internal_object_reserved_page_count)*8)/1024 AS internal_object_pages_mb,
(SUM(version_store_reserved_page_count)*8)/1024 AS version_store_pages_mb,
total_in_use_pages_mb = (SUM(user_object_reserved_page_count)+ SUM(internal_object_reserved_page_count)+ SUM(version_store_reserved_page_count)*8)/1024,
(SUM(unallocated_extent_page_count)*8)/1024 AS total_free_pages_mb
FROM Tempdb.sys.dm_db_file_space_usage ;
end try
begin catch
declare
@error_message nvarchar(4000),
@error_number int,
@error_state int,
@error_severity int,
@error_procedure nvarchar(126),
@error_line int
select @error_number = error_number()
, @error_state = error_state()
, @error_severity = error_severity()
, @error_procedure = error_procedure()
, @error_line = error_line()
set @error_message = left('CaptureTempdbUsage Error Message: ' + error_message(), 4000)
raiserror(700016, @error_severity, @error_state, @error_message)
exec [dbo].[Capture_Error] @error_message,
@error_number,
@error_state,
@error_severity,
@error_procedure,
@error_line
end catch
end;
Verilerin anlamlı olması için bu prosedürü bir SQL Server Agent Job aracılığıyla örneğin her 1 veya 5 dakikada bir çalışacak şekilde zamanlamalısınız. Bu sayede gün içindeki dalgalanmaları ve tempdb’yi şişiren anlık işlemleri yakalayabilirsiniz.
EXECUTE [DBAMON].[dbo].[CaptureTempdbUsage] ;
İlgili tablomuza select çektiğimizde sonuçların başarılı bir şekilde geldiği görüşmektedir.
Yukarıdaki kolonları açıklayacak olursak:
- captureDate Verinin kaydedildiği tarih ve saat bilgisidir. Zaman serisi analizi için kritiktir.
- user_object_pages_mb Kullanıcılar tarafından oluşturulan #temp tablolar, ##temp tablolar ve tablo değişkenleri tarafından kullanılan alandır.
- internal_object_pages_mb SQL Server’ın kendi işlemleri (Hash joins, Sort operations, Spooling) için kullandığı geçici alan miktarıdır.
- version_store_pages_mb Snapshot Isolation veya RCSI açıkken güncellenen kayıtların eski hallerinin tutulduğu alandır. Uzun süren transaction’lar burada şişmeye neden olur.
- total_in_use_pages_mb Yukarıdaki üç kategorinin toplamıdır; o anki aktif kullanılan toplam alanı gösterir.
- total_free_pages_mb tempdb dosyalarının toplam boyutu içinde henüz tahsis edilmemiş (boş) olan alanı gösterir.
MSSQL Server’da Tempdb Kullanım Durumunu Tabloya Kaydetme procedure yapısını görmüş olduk. Başka makalede görüşmek dileğiyle..
Emanetlerinizi ve sözlerinizi tutun. Müminun-8
