نظارت بر TempDB در SQL Server به‌وسیله Viewها

نظارت بر TempDB در SQL Server به‌وسیله Viewها

پایگاه داده TempDB یکی از مهم‌ترین و در عین حال حساس‌ترین بخش‌های SQL Server محسوب می‌شود.
این پایگاه داده سیستمی به‌صورت موقت برای ذخیره‌سازی داده‌های موقتی، جداول موقت، متغیرهای جدولی، عملیات مرتب‌سازی، هشینگ، ایندکس‌سازی آنلاین و بسیاری از پردازش‌های داخلی موتور SQL Server مورد استفاده قرار می‌گیرد.
به همین دلیل هرگونه مشکل در TempDB می‌تواند باعث کاهش عملکرد سرور، افزایش زمان اجرای کوئری‌ها و حتی توقف برخی سرویس‌های حیاتی شود.

مدیران پایگاه داده (DBA) باید به‌صورت مداوم وضعیت TempDB را بررسی و نظارت کنند.
خوشبختانه SQL Server مجموعه‌ای از Viewها و Dynamic Management Viewها (DMV) را در اختیار ما قرار می‌دهد که امکان مشاهده و تحلیل وضعیت TempDB را فراهم می‌کنند.

TempDB چیست؟

TempDB یک پایگاه داده سیستمی است که هنگام راه‌اندازی SQL Server ایجاد می‌شود و برای ذخیره داده‌های موقتی مورد استفاده قرار می‌گیرد.

برخی از کاربردهای TempDB عبارت‌اند از:

• ذخیره جداول موقت (Temporary Tables)

• نگهداری Table Variables

• عملیات Sort

• پردازش Hash Join

• ایجاد Work Tables

• Snapshot Isolation

• Online Index Operations

• پردازش Queryهای پیچیده

با توجه به استفاده گسترده از TempDB، نظارت بر فضای مصرفی و عملکرد آن اهمیت بسیار زیادی دارد.

اهمیت نظارت بر TempDB  در SQL Server

عدم نظارت بر TempDB می‌تواند مشکلات زیر را ایجاد کند:

• پر شدن فضای دیسک

• کند شدن اجرای Queryها

• افزایش Blocking

• افزایش Wait Time

• بروز خطاهای مرتبط با کمبود فضا

• کاهش کارایی سرور

به همین دلیل بررسی منظم وضعیت TempDB یکی از وظایف اصلی DBAها محسوب می‌شود.

مشاهده اطلاعات فایل‌های TempDB

اولین View مفید برای بررسی TempDB، نمای sys.master_files است.

SELECT

name,

size,

max_size,

growth,

physical_name

FROM sys.master_files

WHERE database_id = 2;

در SQL Server شناسه TempDB برابر 2 است.

این Query اطلاعات زیر را نمایش می‌دهد:

• نام فایل

• اندازه فایل

• حداکثر اندازه

• میزان رشد خودکار

• مسیر ذخیره‌سازی فایل

مشاهده فضای مصرف شده در TempDB

برای بررسی میزان فضای اشغال شده می‌توان از DMV زیر استفاده کرد:

SELECT

SUM(user_object_reserved_page_count) * 8 AS UserObjectsKB,

SUM(internal_object_reserved_page_count) * 8 AS InternalObjectsKB,

SUM(version_store_reserved_page_count) * 8 AS VersionStoreKB,

SUM(unallocated_extent_page_count) * 8 AS FreeSpaceKB

FROM sys.dm_db_file_space_usage;

این DMV اطلاعات مهمی درباره مصرف فضای TempDB ارائه می‌دهد.

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

بررسی فضای آزاد TempDB  در SQL Server

جهت مشاهده میزان فضای باقی‌مانده:

SELECT

SUM(unallocated_extent_page_count) * 8 / 1024 AS FreeSpaceMB

FROM sys.dm_db_file_space_usage;

این گزارش به DBA کمک می‌کند قبل از پر شدن کامل TempDB اقدامات لازم را انجام دهد.

مشاهده Sessionهای مصرف‌کننده TempDB

گاهی لازم است بدانیم کدام Session بیشترین استفاده را از TempDB دارد.

SELECT

session_id,

user_objects_alloc_page_count,

internal_objects_alloc_page_count

FROM sys.dm_db_session_space_usage

ORDER BY

user_objects_alloc_page_count DESC;

این DMV میزان استفاده هر Session از TempDB را نمایش می‌دهد.

بررسی مصرف TempDB توسط درخواست‌ها

برای مشاهده مصرف TempDB در سطح Request:

SELECT

request_id,

session_id,

user_objects_alloc_page_count,

internal_objects_alloc_page_count

FROM sys.dm_db_task_space_usage;

این اطلاعات برای عیب‌یابی Queryهای سنگین بسیار کاربردی هستند.

مشاهده Version Store  در SQL Server

در Snapshot Isolation و Read Committed Snapshot داده‌های نسخه‌بندی شده در TempDB ذخیره می‌شوند.

برای مشاهده میزان مصرف Version Store:

SELECT

SUM(version_store_reserved_page_count) * 8 / 1024

AS VersionStoreMB

FROM sys.dm_db_file_space_usage;

افزایش غیرعادی این مقدار می‌تواند نشانه وجود تراکنش‌های طولانی باشد.

شناسایی تراکنش‌های طولانی

تراکنش‌های باز می‌توانند باعث رشد Version Store شوند.

SELECT

transaction_id,

elapsed_time_seconds

FROM sys.dm_tran_active_snapshot_database_transactions;

این DMV اطلاعات ارزشمندی درباره تراکنش‌های فعال ارائه می‌دهد.

بررسی اندازه فایل‌های TempDB  در SQL Server

USE tempdb;




SELECT

name,

size * 8 / 1024 AS SizeMB

FROM sys.database_files;

این Query اندازه فعلی فایل‌های داده و لاگ TempDB را نمایش می‌دهد.

مشاهده رشد فایل‌های TempDB  در SQL Server

بررسی تنظیمات Auto Growth اهمیت زیادی دارد.

SELECT

name,

growth,

is_percent_growth

FROM tempdb.sys.database_files;

تنظیمات نامناسب رشد فایل می‌تواند باعث کاهش عملکرد سرور شود.

مانیتورینگ Waitهای مرتبط با TempDB

گاهی مشکل TempDB از طریق Wait Statistics مشخص می‌شود.

SELECT

wait_type,

wait_time_ms

FROM sys.dm_os_wait_stats

WHERE wait_type LIKE 'PAGELATCH%';

مقادیر بالای PAGELATCH معمولاً نشان‌دهنده فشار زیاد روی TempDB هستند.

بررسی فایل‌های متعدد TempDB در SQL Server

در سرورهای پرترافیک معمولاً چند فایل داده برای TempDB ایجاد می‌شود.

SELECT

file_id,

name,

physical_name

FROM tempdb.sys.database_files;

وجود چند فایل می‌تواند رقابت روی Allocation Pageها را کاهش دهد.

شناسایی Queryهای پرمصرف

یکی از راه‌های نظارت بر TempDB شناسایی Queryهایی است که Sort یا Hash سنگین ایجاد می‌کنند.

SELECT TOP 10

total_worker_time,

execution_count,

total_logical_reads

FROM sys.dm_exec_query_stats

ORDER BY total_worker_time DESC;

این گزارش کمک می‌کند Queryهای مشکل‌ساز شناسایی شوند.

بررسی وضعیت فعلی TempDB  در SQL Server

Query زیر نمای کلی از وضعیت TempDB ارائه می‌دهد:

SELECT

DB_NAME(database_id) AS DatabaseName,

SUM(size) * 8 / 1024 AS TotalSizeMB

FROM sys.master_files

WHERE database_id = 2

GROUP BY database_id;

ساخت View برای مانیتورینگ TempDB

برای سهولت در بررسی وضعیت TempDB می‌توان یک View اختصاصی ایجاد کرد.

CREATE VIEW vw_TempDBUsage

AS

SELECT

SUM(user_object_reserved_page_count) * 8 / 1024 AS UserObjectsMB,

SUM(internal_object_reserved_page_count) * 8 / 1024 AS InternalObjectsMB,

SUM(version_store_reserved_page_count) * 8 / 1024 AS VersionStoreMB,

SUM(unallocated_extent_page_count) * 8 / 1024 AS FreeSpaceMB

FROM sys.dm_db_file_space_usage;

پس از ایجاد View:

SELECT * FROM vw_TempDBUsage;

مزایای استفاده از Viewها برای نظارت در SQL

استفاده از Viewها مزایای متعددی دارد:

• سادگی دسترسی به اطلاعات

• کاهش پیچیدگی Queryها

• امکان استفاده در داشبوردها

• قابلیت اتصال به ابزارهای مانیتورینگ

• گزارش‌گیری سریع‌تر

• استانداردسازی فرآیندهای نظارتی

بهترین روش‌های مدیریت TempDB

برای جلوگیری از مشکلات TempDB رعایت نکات زیر توصیه می‌شود:

• استفاده از چند فایل داده

  در سرورهای پرترافیک بهتر است چند فایل Data با اندازه برابر ایجاد شود.

• تعیین اندازه اولیه مناس

  از رشد مکرر فایل جلوگیری کنید.

• تنظیم Auto Growth منطق

  افزایش‌های کوچک می‌تواند باعث Fragmentation شود.

• بررسی منظم DMVها

  گزارش‌های مصرف فضا باید به‌صورت دوره‌ای بررسی شوند.

• شناسایی Queryهای سنگین

  کوئری‌های دارای Sort و Hash زیاد را بهینه‌سازی کنید.

• مانیتورینگ Version Store

  رشد بیش از حد Version Store باید بررسی شود.

ابزارهای کمکی برای نظارت بر TempDB

علاوه بر Viewها می‌توان از ابزارهای زیر نیز استفاده کرد:

• SQL Server Management Studio (SSMS)

• SQL Server Profiler

• Extended Events

• Query Store

• Performance Monitor

• SQL Server Agent Alerts

این ابزارها در کنار DMVها دید کاملی از وضعیت TempDB ارائه می‌دهند.

جمع‌بندی

TempDB یکی از حیاتی‌ترین بخش‌های SQL Server است و عملکرد صحیح آن تأثیر مستقیمی بر سرعت و پایداری کل سیستم دارد.
با استفاده از Viewها و Dynamic Management Viewهای مختلف مانند sys.dm_db_file_space_usage، sys.dm_db_session_space_usage، sys.dm_db_task_space_usage و sys.database_files می‌توان مصرف فضا، Sessionهای پرمصرف، Version Store و وضعیت فایل‌های TempDB را به‌صورت دقیق بررسی کرد.
پیاده‌سازی Viewهای اختصاصی و مانیتورینگ منظم این اطلاعات به مدیران پایگاه داده کمک می‌کند تا مشکلات احتمالی را قبل از تبدیل شدن به بحران شناسایی و برطرف کنند.