TempDB Design در SQL Server طراحی اصولی و جلوگیری از Contention در محیط‌های Always On

تصور کنید ساعت ۱۰ صبح است و یکی از سامانه‌های حیاتی سازمان، درست در اوج ساعات کاری، ناگهان با افت شدید سرعت مواجه می‌شود. کاربران از کندی ثبت اطلاعات شکایت می‌کنند، گزارش‌های مدیریتی با تأخیر باز می‌شوند و حتی برخی درخواست‌ها با Timeout پایان می‌یابند. تیم زیرساخت بلافاصله مصرف CPU، حافظه، دیسک و وضعیت شبکه را بررسی می‌کند، اما تقریباً همه چیز طبیعی به نظر می‌رسد.

چند ساعت بعد، بررسی دقیق‌تر Wait Statistics و DMVهای SQL Server نشان می‌دهد ریشه مشکل نه در سخت‌افزار، نه در Queryهای پیچیده و نه در افزایش تعداد کاربران، بلکه در طراحی نامناسب TempDB قرار دارد. دیتابیسی که بسیاری از مدیران تنها آن را فضایی موقت برای ذخیره داده‌ها می‌دانند، در واقع یکی از حیاتی‌ترین اجزای موتور SQL Server است و کوچک‌ترین اشتباه در طراحی آن می‌تواند کل Performance سرور را تحت تأثیر قرار دهد.

در محیط‌های Enterprise، به‌ویژه زمانی که از Always On Availability Groups، Failover Cluster یا سیستم‌های OLTP پرتراکنش استفاده می‌شود، TempDB دیگر یک دیتابیس موقت ساده نیست. این بخش به فضای کاری مشترک SQL Server تبدیل می‌شود؛ جایی که عملیات Sort، Hash Join، Version Store، Temporary Objectها و بسیاری از پردازش‌های داخلی موتور پایگاه داده انجام می‌شوند. بنابراین اگر طراحی فایل‌ها، محل ذخیره‌سازی یا تنظیمات آن مطابق Best Practiceهای مایکروسافت نباشد، احتمال بروز Contention، افزایش Wait Typeهای PAGELATCH و افت عملکرد به‌شدت افزایش پیدا می‌کند.

TempDB چیست و چرا این‌قدر مهم است؟

یک Shared Workspace است که برای عملیات‌های موقت و میانی استفاده می‌شود:

اگر حجم Queryها بالا باشد یا Memory کافی تخصیص نیابد، حجم زیادی از عملیات‌ها به TempDB منتقل می‌شود و فشار هم‌زمان روی فایل‌های TempDB احتمال Contention را افزایش می‌دهد.

ساختار داخلی TempDB و ریشه Contention

دیتابیس موقت از Allocation Bitmap‌هایی استفاده می‌کند که تصمیم می‌گیرند هر Object در کدام Page قرار گیرد:

• PFS (Page Free Space) → وضعیت فضای آزاد صفحات
• GAM (Global Allocation Map) → صفحات تخصیص‌یافته
• SGAM (Shared Global Allocation Map) → صفحات تقسیم‌شده مشترک

وقتی تعداد Sessionها زیاد باشد، همه آن‌ها برای دسترسی به این صفحات در صف می‌مانند → Latch Contention.

نشانه واضح:

Wait Type = PAGELATCH_UP / PAGELATCH_EX

نشانه‌های Contention

SELECT 
    wait_type, 
    waiting_tasks_count, 
    wait_time_ms 
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%';

اگر waitهای زیر زیاد بود → مشکل قطعی TempDB:

• PAGELATCH_EX
• PAGELATCH_SH
• PAGEIOLATCH_UP/EX (در صورت IO Bottleneck)

چرا طراحی TempDB در Replicaهای Always On باید یکسان باشد؟

یکی از اشتباهات رایج در پیاده‌سازی Always On Availability Groups این است که تنها Replica اصلی (Primary) به‌درستی پیکربندی می‌شود و سایر Replicaها با تنظیمات پیش‌فرض باقی می‌مانند. در حالی که TempDB یک Database محلی است و بین Replicaها همگام‌سازی نمی‌شود. هر نود، TempDB مستقل خود را دارد و هنگام Failover دقیقاً همان تنظیمات محلی مورد استفاده قرار می‌گیرد.

به همین دلیل، تعداد فایل‌ها، اندازه اولیه، تنظیمات Auto Growth، محل ذخیره‌سازی و حتی نوع Storage باید در تمام Replicaها یکسان باشد. در غیر این صورت، ممکن است پس از Failover کاربران با افزایش Wait Typeها، افت Performance و افزایش زمان پاسخ‌گویی Queryها مواجه شوند؛ در حالی که مشکل از خود Always On نیست، بلکه به تفاوت طراحی TempDB در نودهای مختلف بازمی‌گردد.

در معماری‌های Enterprise، استانداردسازی TempDB باید بخشی از فرآیند استقرار SQL Server باشد تا همه Replicaها رفتار و کارایی یکسانی داشته باشند.

طراحی اصولی TempDB در محیط Always On Cluster

1) محل ذخیره‌سازی

در هر نود از کلاستر، TempDB Local است (تحت Failover منتقل نمی‌شود).
پس:

دلیل: TempDB I/O Local باعث کاهش تأخیر Failover و جلوگیری از Stale Locks می‌شود.

2) تعداد فایل‌ها

فرمول استاندارد:

تعداد فایل‌ها = تعداد هسته‌های CPU / 4  (حداکثر 8 فایل برای شروع)

مثال:
سروری با 32 Core:

32 / 4 = 8 فایل TempDB

اگر باز هم Contention ادامه داشت → افزایش تدریجی تا سقف 1 فایل به ازای هر Core
(اما فقط با مشاهده مانیتورینگ)

3) اندازه اولیه (Initial Size) و Growth ثابت

اشتباه رایج:
ایجاد با Size بسیار کوچک و Auto-Growth کوچک یا درصدی.

راهکار:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 4GB, FILEGROWTH = 256MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 2GB, FILEGROWTH = 256MB);

• Size اولیه بزرگ تنظیم شود.
• Growth درصدی ممنوع → فقط رشد ثابت (MB)

4) فعال‌سازی Trace Flagهای مدرن

در نسخه‌های جدید، رفتار Allocation بهینه شده است، اما در SQL Server قدیمی‌تر:

TF 1117 → رشد هم‌زمان فایل‌ها
TF 1118 → جلوگیری از SGAM Contention

برای SQL Server 2016 به بالا  به‌صورت پیش‌فرض فعال است و اگر نسخه شما پایین تر از 2016 است دستورات زیر را اجرا کنید:

DBCC TRACEON(1117, -1);
DBCC TRACEON(1118, -1);

تأثیر RCSI و Snapshot Isolation بر TempDB

اگر داده‌ها با RCSI خوانده شوند، نسخه‌برداری ردیف‌ها در TempDB ذخیره می‌شود.

فعال‌سازی اشتباه RCSI = انفجار TempDB.

ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON;

توصیه: فقط در دیتابیس‌هایی که IO و TempDB طراحی استاندارد دارند.

نقش Memory Grant در ایجاد فشار روی TempDB

یکی از دلایل کمتر شناخته‌شده افزایش مصرف TempDB، تخصیص نامناسب Memory Grant برای Queryها است. زمانی که SQL Server حافظه کافی برای اجرای عملیات‌هایی مانند Sort، Hash Match یا Aggregate در اختیار Query قرار ندهد، داده‌های میانی به TempDB منتقل می‌شوند که به این فرآیند Memory Spill گفته می‌شود. تکرار این وضعیت در Workloadهای سنگین می‌تواند علاوه بر افزایش I/O، باعث رشد سریع TempDB و افزایش Waitهای مرتبط شود. بررسی Actual Execution Plan و هشدارهای Sort Warning و Hash Warning یکی از بهترین روش‌ها برای شناسایی این مشکل است.

اشتباهات رایجی که هنوز در طراحی TempDB مشاهده می‌شوند

در بسیاری از سازمان‌ها، مشکل TempDB به کمبود منابع سخت‌افزاری مربوط نیست، بلکه نتیجه چند اشتباه ساده اما تأثیرگذار در طراحی اولیه است. ایجاد تنها یک Data File روی سرورهای چند هسته‌ای، استفاده از اندازه‌های متفاوت برای فایل‌های TempDB، تنظیم Auto Growth به‌صورت درصدی، قرار دادن TempDB روی همان Storage فایل‌های اصلی دیتابیس یا Backup و همچنین کوچک در نظر گرفتن اندازه اولیه فایل‌ها، از رایج‌ترین این اشتباهات هستند.

یکی دیگر از خطاهای متداول، متفاوت بودن تنظیمات TempDB در Replicaهای مختلف Always On است. این موضوع معمولاً تا زمان وقوع Failover پنهان می‌ماند و سپس باعث کاهش محسوس Performance می‌شود.

بررسی دوره‌ای Wait Stats، وضعیت Version Store و الگوی رشد فایل‌های TempDB می‌تواند بسیاری از این مشکلات را قبل از تبدیل شدن به بحران شناسایی کند.

اسکریپت پیاده‌سازی استاندارد TempDB (مناسب Always On Cluster)

USE master;
GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 4096MB, FILEGROWTH = 256MB);
GO

DECLARE @i INT = 2;
WHILE @i <= 8
BEGIN
   EXEC ('ALTER DATABASE tempdb ADD FILE (NAME = tempdb' + CAST(@i AS VARCHAR(3)) 
   + ', FILENAME = ''D:\SQLData\tempdb' + CAST(@i AS VARCHAR(3)) + '.ndf'', SIZE = 4096MB, FILEGROWTH = 256MB)');
   SET @i += 1;
END
GO

نکته مهم:

مقادیر Size، File Growth و تعداد فایل‌های TempDB باید متناسب با تعداد هسته‌های پردازنده، میزان RAM، حجم تراکنش‌ها و الگوی بار کاری هر سرور تعیین شوند. اسکریپت فوق یک نمونه استاندارد است و بهتر است قبل از اجرا در محیط Production با توجه به معماری سیستم بررسی و در صورت نیاز سفارشی‌سازی شود.

بررسی وضعیت فعلی TempDB با یک اسکریپت کاربردی

قبل از بخش «اسکریپت پیاده‌سازی استاندارد»، این قسمت را اضافه کن:

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

SELECT
    name,
    physical_name,
    size / 128 AS SizeMB,
    growth,
    is_percent_growth
FROM tempdb.sys.database_files;

مانیتورینگ مستمر TempDB شرط حفظ پایداری Performance

طراحی صحیح TempDB تنها نقطه شروع است. حتی اگر تعداد فایل‌ها، محل ذخیره‌سازی و تنظیمات Growth بر اساس Best Practice پیاده‌سازی شده باشند، تغییر الگوی بار کاری، افزایش تعداد کاربران یا توسعه سامانه می‌تواند در طول زمان رفتار TempDB را تغییر دهد.

به همین دلیل، در محیط‌های Enterprise توصیه می‌شود شاخص‌هایی مانند Wait Stats، میزان استفاده از Version Store، نرخ رشد فایل‌های TempDB، Disk Latency و Memory Grant به‌صورت مستمر پایش شوند. این اطلاعات معمولاً اولین نشانه‌های بروز Contention یا کمبود منابع را پیش از آنکه کاربران کاهش Performance را احساس کنند، آشکار می‌کنند.

سازمان‌هایی که مانیتورینگ مستمر TempDB را در فرآیند نگهداری SQL Server قرار می‌دهند، معمولاً قبل از وقوع اختلال، مشکلات احتمالی را شناسایی و برطرف می‌کنند و از بروز Downtime یا افت شدید عملکرد جلوگیری خواهند کرد.

چک‌لیست نهایی طراحی TempDB (نسخه اجرایی)

یک تجربه واقعی از طراحی ناهماهنگ TempDB

در یکی از پروژه‌های بهینه‌سازی SQL Server، سازمانی از Always On Availability Groups با سه Replica استفاده می‌کرد. عملکرد سیستم در حالت عادی مناسب بود، اما پس از هر Failover، کاربران افزایش زمان پاسخ‌گویی و کندی محسوس سامانه را گزارش می‌کردند.

بررسی CPU، حافظه و شبکه هیچ گلوگاه مشخصی را نشان نمی‌داد، اما تحلیل Wait Stats افزایش شدید PAGELATCH_EX را آشکار کرد. در ادامه مشخص شد که سرور اصلی با هشت فایل TempDB هم‌اندازه روی NVMe پیکربندی شده بود، در حالی که Replica ثانویه همچنان تنها یک فایل TempDB با تنظیمات پیش‌فرض SQL Server داشت.

پس از یکسان‌سازی طراحی TempDB در تمام Replicaها، اصلاح اندازه فایل‌ها و تنظیم Auto Growth، مشکل به‌طور کامل برطرف شد و پس از Failover نیز عملکرد سیستم بدون افت محسوس ادامه پیدا کرد. این تجربه نشان می‌دهد که در معماری‌های High Availability، هماهنگی تنظیمات TempDB در تمام نودها به اندازه خود مکانیزم Failover اهمیت دارد.

نتیجه‌گیری

TempDB یکی از مهم‌ترین مؤلفه‌های معماری SQL Server است که تأثیر آن تنها به عملیات موقت محدود نمی‌شود. در محیط‌های پرتراکنش و زیرساخت‌های مبتنی بر Always On، طراحی صحیح TempDB می‌تواند تفاوت میان یک سامانه پایدار و یک زیرساخت پر از Contention و افت عملکرد باشد.

انتخاب تعداد مناسب فایل‌ها، استفاده از Storage پرسرعت، تنظیم صحیح Auto Growth، یکسان‌سازی پیکربندی در تمام Replicaها و مانیتورینگ مداوم، مجموعه اقداماتی هستند که به شکل محسوسی Performance و پایداری SQL Server را بهبود می‌دهند.

در بسیاری از پروژه‌های واقعی، رفع مشکلات TempDB بدون هیچ ارتقای سخت‌افزاری، باعث کاهش Waitها، افزایش سرعت اجرای Queryها و بهبود تجربه کاربران شده است. به همین دلیل، طراحی TempDB باید بخشی از استاندارد استقرار و نگهداری هر زیرساخت SQL Server در سطح Enterprise باشد، نه صرفاً یک تنظیم اولیه که پس از نصب فراموش شود.

سوالات متداول FAQ

آیا افزایش تعداد فایل‌های TempDB همیشه باعث بهبود عملکرد می‌شود؟
خیر. افزایش تعداد فایل‌ها تنها زمانی مفید است که مشکل Latch Contention وجود داشته باشد. ایجاد فایل‌های بیش از نیاز نه‌تنها کمکی به عملکرد نمی‌کند، بلکه مدیریت TempDB را نیز پیچیده‌تر خواهد کرد.

چرا TempDB بین نودهای Always On مشترک نیست؟
زیرا TempDB یک دیتابیس موقت وابسته به Instance است و در فرآیند Replication یا Synchronization شرکت نمی‌کند. هر Replica نسخه مستقل TempDB خود را ایجاد و مدیریت می‌کند.

بهترین نوع Storage برای TempDB چیست؟
در محیط‌های Enterprise، استفاده از NVMe یا SSD محلی با تأخیر بسیار کم بهترین انتخاب است. قرار دادن TempDB روی Storage اشتراکی معمولاً توصیه نمی‌شود.

آیا TempDB به Backup نیاز دارد؟
خیر. TempDB در هر بار راه‌اندازی مجدد SQL Server به‌صورت خودکار ایجاد می‌شود و نیازی به تهیه نسخه پشتیبان ندارد.

چگونه می‌توان تشخیص داد که TempDB دچار Contention شده است؟

افزایش Wait Typeهایی مانند PAGELATCH_UP و PAGELATCH_EX، رشد غیرعادی Version Store، کند شدن Queryها و افزایش زمان پاسخ‌گویی از مهم‌ترین نشانه‌های فشار روی TempDB هستند.

آیا تغییر تعداد فایل‌های TempDB نیاز به Restart دارد؟
خیر. اضافه کردن فایل‌های جدید یا تغییر اندازه آن‌ها معمولاً بدون Restart امکان‌پذیر است؛ اما اگر محل ذخیره‌سازی TempDB را تغییر دهید، اعمال این تغییر پس از راه‌اندازی مجدد SQL Server انجام خواهد شد.

آیا فعال‌سازی RCSI همیشه پیشنهاد می‌شود؟
خیر. اگرچه RCSI می‌تواند Blocking را کاهش دهد، اما باعث افزایش استفاده از Version Store در TempDB می‌شود. بنابراین قبل از فعال‌سازی باید ظرفیت I/O، فضای ذخیره‌سازی و الگوی بار کاری سیستم بررسی شود.

هر چند وقت یک‌بار باید وضعیت TempDB بررسی شود؟
در سرورهای Production بهتر است وضعیت TempDB به‌صورت مستمر توسط ابزارهای مانیتورینگ کنترل شود. در صورت نبود سیستم مانیتورینگ، بررسی روزانه Wait Statistics، فضای Version Store، نرخ رشد فایل‌های TempDB، Disk Latency و Memory Grant می‌تواند بسیاری از مشکلات را پیش از ایجاد اختلال شناسایی کند.

مشاوره تخصصی بهینه‌سازی TempDB و Performance در SQL Server

اگر در SQL Server خود با افزایش Wait Typeهایی مانند PAGELATCH، رشد غیرعادی TempDB، کندی Queryها، افزایش زمان پاسخ‌گویی کاربران یا افت Performance پس از Failover مواجه هستید، کارشناسان لاندا می‌توانند با تحلیل دقیق Wait Statistics، بررسی Execution Plan، ارزیابی طراحی TempDB، بررسی وضعیت Memory Grant و بهینه‌سازی تنظیمات SQL Server، علت اصلی مشکل را شناسایی و برطرف کنند.

خدمات تخصصی لاندا شامل تحلیل Performance، بررسی Contention، طراحی استاندارد TempDB، بهینه‌سازی Always On Availability Groups، تنظیمات High Availability و عیب‌یابی زیرساخت SQL Server برای سازمان‌ها و شرکت‌های بزرگ است.

برای دریافت مشاوره تخصصی، تحلیل Performance و بهینه‌سازی TempDB در SQL Server با تیم لاندا تماس ✆ بگیرید.

آحرین بروزرسانی

• آپدیت تیرماه 1405
  • این مقاله براساس آخرین Best Practiceهای SQL Server و تجربیات عملی تیم لاندا در پروژه‌های بهینه‌سازی Performance، مدیریت TempDB و پیاده‌سازی Always On Availability Groups بازبینی و به‌روزرسانی شده است. در این نسخه، مباحثی مانند طراحی استاندارد TempDB در Replicaها، تأثیر Memory Grant، مانیتورینگ مستمر، اشتباهات رایج در پیکربندی و توصیه‌های عملی برای محیط‌های Enterprise به مقاله اضافه شده است.