و سپس «افزودن به صفحه اصلی» ضربه بزنید
و سپس «افزودن به صفحه اصلی» ضربه بزنید
در معماری پایگاه دادههای سازمانی که حجم تراکنشها بالا، تعداد Sessionها زیاد و گزارشگیری همزمان فعال است، TempDB یکی از پر ترافیکترین و حساسترین اجزای SQL Server محسوب میشود. این Database موقتی، نقش انبار عملیاتی SQL Engine را ایفا میکند و تقریباً تمام Queryها حتی اگر مستقیماً از آن استفاده نکنند، بهنوعی به منابع آن متکی هستند.
اگر TempDB به درستی طراحی نشده باشد، نتیجه چیست؟
- کندی ناگهانی یا دورهای (Intermittent Slowness)
- صف شدن Queryها و Waitهای طولانی
- Latch Contention روی صفحات PFS / GAM / SGAM
- فشار روی CPU و IO بدون رشد محسوس حجم دادهها
- افزایش زمان Failover در Always On
به همین دلیل، طراحی و پیکربندی TempDB برای DBAهای حرفهای، بهویژه در محیطهای Failover Cluster / Always On Availability Groups یک مهارت حیاتی است.
در این مقاله، مرحلهبهمرحله:
- ساختار داخلی
- علتهای Contention
- نشانهها و روشهای تشخیص
- اصول طراحی استاندارد در محیطهای Cluster
- بهینهسازی و Script عملی پیادهسازی
را شرح میدهیم.
TempDB چیست و چرا اینقدر مهم است؟
یک Shared Workspace است که برای عملیاتهای موقت و میانی استفاده میشود:
| کاربرد | توضیح |
|---|---|
| Sort و Aggregation | ORDER BY، GROUP BY، DISTINCT |
| Hash Join و Query Memory Spill | هنگام کمبود Memory Grant |
| Version Store | هنگام استفاده از RCSI و Snapshot Isolation |
| Temporary Tables و Table Variables | در Stored Procedureها و Queryها |
| Row Versioning در Always On | برای خواندن سازگار و هماهنگ |
اگر حجم 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 در محیط Always On Cluster
۱) محل ذخیرهسازی
در هر نود از کلاستر، TempDB Local است (تحت Failover منتقل نمیشود).
پس:
| گزینه | توصیه |
|---|---|
| HDD / SATA | ممنوع |
| SSD | قابل |
| NVMe / PCIe | توصیهشده |
| Storage Shared | هرگز |
| Local Dedicated SSD | استاندارد Enterprise |
دلیل: TempDB I/O Local باعث کاهش تأخیر Failover و جلوگیری از Stale Locks میشود.
۲) تعداد فایلها
فرمول استاندارد:
تعداد فایلها = تعداد هستههای CPU / 4 (حداکثر ۸ فایل برای شروع)
مثال:
سروری با ۳۲ Core:
۳۲ / ۴ = 8 فایل TempDB
اگر باز هم Contention ادامه داشت → افزایش تدریجی تا سقف ۱ فایل به ازای هر Core
(اما فقط با مشاهده مانیتورینگ)
۳) اندازه اولیه (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)
۴) فعالسازی Trace Flagهای مدرن
در نسخههای جدید، رفتار Allocation بهینه شده است، اما در SQL Server قدیمیتر:
TF 1117 → رشد همزمان فایلها
TF 1118 → جلوگیری از SGAM Contention
برای SQL Server 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 طراحی استاندارد دارند.
اسکریپت پیادهسازی استاندارد 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
چکلیست نهایی طراحی TempDB (نسخه اجرایی)
| آیتم | وضعیت |
|---|---|
| ایجاد دیتابیس موقت روی Local NVMe SSD | ✅ |
| تعداد فایلها = CPU / 4 (حداقل ۴، حداکثر برابر هستهها) | ✅ |
| Initial Size >= 4GB هر فایل | ✅ |
| Filegrowth ثابت (مثلاً 256MB) | ✅ |
| بررسی PAGELATCH Waitها هر ۲۴ ساعت | ✅ |
| فعالسازی یا تأیید TF 1117 / 1118 یا معادل مدرن | ✅ |
سوالات متداول FAQ
| سؤال | پاسخ |
|---|---|
| آیا افزایش تعداد فایلها همیشه مفید است؟ | خیر، افزایش زیاد باعث Fragmentation و مدیریت پیچیده میشود. |
| چرا TempDB بین نودهای Always On مشترک نیست؟ | زیرا موقت است و وابسته به Instance نه Database. |
| بهترین دیسک برای TempDB چیست؟ | NVMe محلی با Latency < 1ms |
| آیا TempDB به Backup نیاز دارد؟ | خیر. خودکار با هر ریستارت ایجاد میشود. |
تماس و مشاوره با لاندا
اگر سرور SQL شما با کندی، صف شدن Queryها یا Waitهای PAGELATCH مواجه است، تیم مانیتورینگ و DBA لاندا میتواند در کمتر از ۴۸ ساعت تحلیل Contention و بهینهسازی طراحی را انجام دهد.
برای درخواست مشاوره تخصصی قابل ارائه در شرکتها و سازمانها با لاندا تماس ✆ بگیرید.
نظری داده نشده