بهبود عملکرد Tempdb در SQL Server

بهبود عملکرد Tempdb در SQL Server

بهبود عملکرد Tempdb در SQL Server یکی از مؤثرترین اقدامات برای افزایش سرعت و مقیاس‌پذیری کل نمونه SQL Server محسوب می‌شود.
Tempdb به طور مشترک توسط همه پایگاه‌های داده استفاده می‌شود و محل ذخیره جدول‌های موقت، مرتب‌سازی‌ها، عملیات هش جوین، نسخه‌سازی ردیف در ایزولیشن Snapshot و بازسازی ایندکس آنلاین است.
اصلی‌ترین راهکارهای بهبود شامل ایجاد چندین فایل داده هم‌اندازه (معمولاً به تعداد هسته‌های منطقی تا حداکثر ۸)، پیش‌تخصیص حجم کافی به Tempdb برای جلوگیری از رشد خودکار مکرر، قرار دادن فایل‌ها روی سریع‌ترین زیرسیستم ذخیره‌سازی مانند SSD یا NVMe، فعال‌سازی Trace Flag 1118 در نسخه‌های قدیمی‌تر (برای حذف mixed extents و کاهش رقابت روی SGAM)، و همچنین در SQL Server 2019 به بعد بهره‌گیری از کش ابرداده در حافظه که رقابت روی لاچ‌های سیستمی را به شدت کاهش می‌دهد.
در محیط‌های ابری مانند Azure SQL Database، این بهینه‌سازی‌ها تا حدی خودکار است، اما در نسخه‌های On-Premise همچنان نظارت مستمر بر wait stats مربوط به PAGELATCH و فضای مصرفی با کوئری‌های sys.dm_db_file_space_usage و sys.dm_os_wait_stats ضروری است.
رعایت این اصول باعث کاهش چشمگیر زمان پاسخ، افزایش توان تراکنش‌های همزمان و ثبات عملکرد در محیط‌های سنگین می‌شود.

کاربردهای اصلی Tempdb در SQL Server

کاربردهای اصلی Tempdb عبارتند از:

• جدول‌های موقت محلی و سراسری (Local/Global Temp Tables)

• متغیرهای جدولی (Table Variables)

• ذخیره نتایج مرتب‌سازی (Sort Spills)

• ذخیره هش برای Joinها (Hash Join)

• نسخه‌سازی ردیف (Row Versioning در Snapshot Isolation)

• عملیات بازسازی ایندکس آنلاین

• محرک‌های AFTER و INSTEAD OF

به دلیل استفاده سنگین و همزمان همه کاربران و فرآیندها از Tempdb، هر گونه مشکل عملکردی در آن می‌تواند کل نمونه SQL Server را تحت تأثیر قرار دهد.

تشخیص مشکلات Tempdb در SQL Server

قبل از هر اقدام بهینه‌سازی، باید وضعیت فعلی Tempdb را آنالیز کنید.

کوئری‌های کلیدی برای بررسی

بررسی استفاده از فضای Tempdb

SELECT 
    SUM(user_object_reserved_page_count) * 8 / 1024 AS UserObject_MB,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS InternalObject_MB,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS VersionStore_MB,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS FreeSpace_MB
FROM sys.dm_db_file_space_usage;

مشاهده رقابت بر روی صفحات (PFS، GAM، SGAM)

SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('PAGELATCH_UP', 'PAGELATCH_EX', 'PAGELATCH_SH',
                    'PAGELATCH_UP', 'PAGELATCH_UP_2', 'PAGELATCH_EX_2')
ORDER BY wait_time_ms DESC;

شناسایی سنگین‌ترین کاربران Tempdb

SELECT 
    session_id,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
ORDER BY internal_objects_alloc_page_count DESC;

استراتژی‌های بهبود عملکرد Tempdb در SQL Server

۱. استفاده از چندین فایل داده (Multi-File Tempdb)

مهم‌ترین بهبود: ایجاد چندین فایل داده یکسان برای Tempdb.

بهترین روش‌ها:

• تعداد فایل‌های داده = تعداد هسته‌های منطقی (تا حداکثر ۸)
• تمام فایل‌های داده باید اندازه یکسان و همان تنظیمات رشد خودکار داشته باشند.
• Auto-growth را به‌صورت مقدار ثابت به مگابایت تنظیم کنید (مثلاً ۱۰۲۴ مگابایت) نه درصدی.

مثال ایجاد ۴ فایل داده:

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Data\tempdb1.mdf', SIZE = 8GB, FILEGROWTH = 1024MB);

ALTER DATABASE tempdb 
ADD FILE (NAME = tempdev2, FILENAME = 'D:\Data\tempdb2.ndf', SIZE = 8GB, FILEGROWTH = 1024MB);

ALTER DATABASE tempdb 
ADD FILE (NAME = tempdev3, FILENAME = 'E:\Data\tempdb3.ndf', SIZE = 8GB, FILEGROWTH = 1024MB);

ALTER DATABASE tempdb 
ADD FILE (NAME = tempdev4, FILENAME = 'F:\Data\tempdb4.ndf', SIZE = 8GB, FILEGROWTH = 1024MB);

⚠️ فایل‌های داده را روی دستگاه‌های ذخیره‌سازی مجزا (مثلاً درایوهای فیزیکی متفاوت یا SAN با چندین LUN) قرار دهید تا از I/O موازی بهره‌مند شوید.

۲. پیش‌تخصیص اندازه (Pre-allocation)

Tempdb هنگام راه‌اندازی SQL Server از نو ساخته می‌شود. اگر اندازه آن کوچک باشد، مرتباً Auto-growth انجام می‌دهد که باعث:

• اختلال در فرآیندهای در حال اجرا
• تکه‌تکه شدن فیزیکی فایل
• افزایش زمان انتظار برای لاچ‌های رشد خودکار

راهکار:
اندازه اولیه Tempdb را بر اساس پیک مصرف معمول + ۲۰٪ فضای خالی تنظیم کنید.

از رشد خودکار فقط به عنوان یک تور ایمنی استفاده کنید.

۳. فعال‌سازی بهینه‌سازی فایل‌های موقت (TF 1117 و 1118)

• Trace Flag 1117

  باعث می‌شود همه فایل‌های یک فایل‌گروپ (مثل PRIMARY در Tempdb) همزمان رشد کنند.
  (در SQL Server 2016 به بعد به صورت پیش‌فرض برای Tempdb فعال است)

• Trace Flag 1118

  تخصیص کامل extent را اجباری می‌کند و از mixed extent جلوگیری می‌کند. این کار رقابت بر روی SGAM page را کاهش می‌دهد.

فعال‌سازی دائمی با -T1118 در پارامترهای راه‌اندازی SQL Server.

4. استفاده از حافظه بهینه (Optimize Tempdb Metadata)

از SQL Server 2019 به بعد، مشاور حافظه برای ابرداده Tempdb (که به متناسب با هسته معروف است) به صورت خودکار رقابت روی ابرداده را کاهش می‌دهد.

برای نسخه‌های قدیمی‌تر (2014/2016/2017):

• اطمینان از حافظه کافی برای جلوگیری از Spill به Tempdb
• بهینه‌سازی کوئری‌های با مرتب‌سازی و هش‌جوين حجیم

۵. جداسازی I/O Tempdb از دیگر فایل‌ها

Tempdb ذاتاً write-heavy است (الگوی نوشتن زیاد). آن را روی سریع‌ترین زیرسیستم ذخیره‌سازی قرار دهید:

• SSD/NVMe در اولویت
• جدا کردن Tempdb از فایل‌های user database و Log files
• در محیط مجازی، از ذخیره‌سازی محلی یا vSAN با کارایی بالا استفاده کنید.

۶. کاهش استفاده نادرست از Tempdb

بررسی کوئری‌هایی که بی‌جهت از Tempdb استفاده می‌کنند:

• جایگزینی SELECT INTO با INSERT INTO ... SELECT برای جداول بزرگ (اگر نیازی به لاگ تراکنش نیست)
• کاهش استفاده از متغیرهای جدولی با حجم بالا (چون متغیرهای جدولی آمار ندارند و باعث تخمین نادرست cardinality می‌شوند)
• جلوگیری از اسپول‌های غیرضروری ناشی از پارامتر sniffing با OPTION (RECOMPILE) یا OPTION (OPTIMIZE FOR UNKNOWN)
• استفاده از ایندکس‌سازی مناسب بر روی جداول موقت (اگر جدول موقت بزرگ است، ایندکس ایجاد کنید)

نظارت و نگهداری روزانه  Tempdb در SQL Server

کوئری نمایش وضعیت رقابت فایل‌ها

SELECT 
    DB_NAME(database_id) AS DB,
    file_id,
    io_stall_read_ms,
    io_stall_write_ms,
    num_of_reads,
    num_of_writes
FROM sys.dm_io_virtual_file_stats(2, NULL)
ORDER BY io_stall_write_ms DESC;

بررسی تعداد صفحات نسخه‌سازی (Snapshot Isolation)

SELECT 
    version_store_reserved_page_count * 8 / 1024 AS VersionStoreMB,
    version_store_reserved_page_count * 8 / 1024.0 / 1024 AS VersionStoreGB
FROM sys.dm_db_file_space_usage;

اگر Version Store بزرگ شد، تراکنش‌های طولانی با Snapshot Isolation را پیدا کنید:

SELECT 
    session_id,
    transaction_id,
    elapsed_time_seconds = DATEDIFF(SECOND, transaction_begin_time, GETDATE())
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

نمونه سناریوهای بهینه‌سازی عملکرد Tempdb در SQL Server

سناریو ۱: رقابت بالای PAGELATCH بر روی SGAM

علائم: PAGELATCH_UP در sys.dm_os_wait_stats بالا است.

راه‌حل: فعال کردن TF 1118 + افزایش تعداد فایل‌های داده (مثلاً از ۲ به ۴ فایل)

سناریو ۲: تأخیر نوشتن بالا در Tempdb

علائم: avg_write_stall_ms بالای ۵۰ میلی‌ثانیه.

راه‌حل: انتقال Tempdb به SSD یا افزایش تعداد فایل‌های داده روی چند LUN مجزا.

سناریو ۳: رشد ناگهانی Tempdb تا ترابایت

علل محتمل:

• کوئری بدون ایندکس با مرتب‌سازی بزرگ
• بازسازی ایندکس آنلاین روی جدول حجیم
• جدول موقت بزرگ بدون حذف (اتصال قطع نشده)

راه‌حل: یافتن کوئری عامل با sys.dm_db_task_space_usage و بهینه‌سازی آن.

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

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

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

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

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

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

نکات پیشرفته بهبود عملکرد Tempdb در SQL Server

• برای Always On Availability Groups

  Tempdb روی هر نسخه ثانویه نیز بهبود یابد (چون در ثانویه نیز Tempdb استفاده می‌شود).

• در SQL Server روی Linux 

  از فهرست /var/opt/mssql/data روی یک mount point سریع (مثل XFS با تنظیمات noatime) استفاده کنید.

• محیط Azure SQL Managed Instance 

  تعداد فایل‌های داده Tempdb از پیش بهینه شده ولی شما می‌توانید با تنظیم MAXDOP و بهینه‌سازی کوئری تأثیر غیرمستقیم بگذارید.

نتیجه‌گیری

بهبود Tempdb یکی از تأثیرگذارترین کارهایی است که می‌توانید برای افزایش سرعت و مقیاس‌پذیری SQL Server انجام دهید. خلاصه بهترین روش‌ها:

• چندین فایل داده برابر با تعداد هسته‌های منطقی.
• اندازه اولیه بزرگ و جلوگیری از رشد خودکار مکرر.
• قرارگیری روی سریع‌ترین ذخیره‌سازی (SSD/NVMe).
• فعال کردن TF 1118 در نسخه‌های قدیمی‌تر (SQL 2016 به بالا خودکار است).
• نظارت مستمر بر رقابت و مصرف فضا.
• بهینه‌سازی کوئری‌های پر مصرف از Tempdb.

با اجرای این راهکارها، می‌توانید انتظار کاهش چشمگیر زمان انتظار، افزایش Throughput و ثبات عملکرد در کل نمونه SQL Server را داشته باشید.