تغییر اندازه TempDB در SQL Server برای بهینه سازی عملکرد

راهنمای جامع تغییر اندازه فایل‌های TempDB در SQL Server برای بهینه‌سازی عملکرد

`TempDB` نقش حیاتی در عملکرد SQL Server ایفا می‌کند و مدیریت صحیح اندازه فایل‌های آن برای حفظ بهینگی سیستم ضروری است. این مقاله به بررسی روش‌های صحیح تغییر اندازه فایل‌های `TempDB` می‌پردازد تا از مشکلات احتمالی جلوگیری شود و عملکرد پایگاه داده شما بهبود یابد.

`TempDB` غالباً در طول عملیات‌های پایگاه داده بزرگ می‌شود و ممکن است وسوسه‌انگیز باشد که فایل‌های آن را مستقیماً در حین استفاده از پایگاه داده کوچک کنیم. این کار معمولاً منجر به خطا می‌شود، برای مثال:

`DBCC SHRINKFILE (‘tempdev’, 1);`

در این مثال، فایل داده `tempdb` هنوز توسط برخی فرآیندها در حال استفاده است. حتی اگر این دستور را در زمانی که هیچ کاربری به سیستم متصل نیست اجرا کنید، فرآیندهای داخلی SQL Server همچنان ممکن است در حال استفاده از `TempDB` باشند و مانع از کوچک کردن فایل‌ها شوند.

روش صحیح تغییر اندازه TempDB

روش صحیح تغییر اندازه فایل‌های `TempDB` شامل راه‌اندازی مجدد سرویس SQL Server و سپس اجرای دستورات خاص `DBCC` قبل از اتصال هر کاربر یا برنامه است. پس از راه‌اندازی مجدد، از SQL Server Management Studio (SSMS) یا SQLCMD برای اجرای دستورات زیر استفاده کنید:

1. پاک کردن حافظه پنهان طرح (Plan Cache):
`DBCC FREEPROCCACHE` حافظه پنهان طرح (plan cache) را برای تمام دسته‌ها (batches) پاک می‌کند.

DBCC FREEPROCCACHE;

2. پاک کردن بافرها (Buffers):
`DBCC DROPCLEANBUFFERS` تمام بافرها را از حافظه پنهان بافر (buffer cache) حذف می‌کند.

DBCC DROPCLEANBUFFERS;

3. اجرای Checkpoint:
`CHECKPOINT` تمام صفحات تغییر یافته (dirty pages) پایگاه داده فعلی را مجبور به ذخیره شدن روی دیسک می‌کند. توصیه می‌شود ابتدا به پایگاه داده `tempdb` سوئیچ کنید:

USE tempdb;
CHECKPOINT;

4. کوچک کردن فایل‌ها (Shrink Files):
اکنون که بافرها پاک شده‌اند و هیچ کاربری متصل نیست، می‌توانید فایل‌های `TempDB` را کوچک کنید. معمولاً هم فایل‌های داده و هم فایل‌های لاگ را کوچک می‌کنم. مثال زیر فایل داده `tempdev` و فایل لاگ `templog` را به ۱ مگابایت تنظیم می‌کند:

USE tempdb;
GO
DBCC SHRINKFILE (N'tempdev' , 1);
GO
DBCC SHRINKFILE (N'templog' , 1);
GO

تنظیم اندازه و رشد فایل‌های TempDB

پس از کوچک کردن فایل‌های `TempDB`، تنظیم مجدد اندازه صحیح آن‌ها بسیار حیاتی است. SQL Server هر بار که سرویس راه‌اندازی می‌شود، پایگاه داده `tempdb` را بازسازی می‌کند؛ بنابراین اندازه فایل‌های `tempdb` همیشه به آخرین اندازه پیکربندی شده باز می‌گردد. می‌توانید این اندازه را با استفاده از دستور `ALTER DATABASE` برای پایگاه داده `tempdb` پیکربندی کنید:

کد زیر فایل داده `tempdev` را به ۸ گیگابایت (۸,۳۸۸,۶۰۸ کیلوبایت) تغییر اندازه داده و رشد خودکار (auto-growth) آن را به ۱ گیگابایت (۱,۰۴۸,۵۷۶ کیلوبایت) تنظیم می‌کند. توجه داشته باشید که گزینه‌های `SIZE` و `FILEGROWTH` در دستور `ALTER DATABASE` اندازه را بر حسب کیلوبایت (KB) تعریف می‌کنند:

ALTER DATABASE TempDB
MODIFY FILE (NAME = N'tempdev', SIZE = 8388608KB, FILEGROWTH = 1048576KB);
GO

به طور مشابه، برای فایل `templog` نیز می‌توانید اندازه اولیه و رشد خودکار را تنظیم کنید. برای فایل لاگ، معمولاً با ۱ گیگابایت (۱,۰۴۸,۵۷۶ کیلوبایت) شروع می‌کنم و رشد خودکار را روی ۲۵۶ مگابایت (۲۶۲,۱۴۴ کیلوبایت) یا ۵۱۲ مگابایت (۵۲۴,۲۸۸ کیلوبایت) تنظیم می‌کنم:

ALTER DATABASE TempDB
MODIFY FILE (NAME = N'templog', SIZE = 1048576KB, FILEGROWTH = 262144KB);
GO

این دستورات بلافاصله اجرا نمی‌شوند، بلکه در پایگاه داده `master` ذخیره شده و دفعه بعد که سرویس SQL Server راه‌اندازی می‌شود، اعمال خواهند شد. می‌توانید تنظیمات جدید را با پرس‌وجو از `sys.master_files` و راه‌اندازی مجدد سرویس SQL Server تأیید کنید.

مدیریت فایل‌های داده چندگانه TempDB

مایکروسافت توصیه می‌کند که تعداد فایل‌های داده `tempdb` باید برابر با تعداد پردازنده‌های منطقی باشد، یا حداکثر ۸ فایل اگر تعداد پردازنده‌های منطقی بیش از ۸ باشد. همچنین می‌توانید اندازه اولیه و رشد خودکار این فایل‌ها را مشخص کنید.

کد زیر دو فایل داده `tempdb` دیگر به نام‌های `tempdev2` و `tempdev3` اضافه می‌کند که هر یک دارای اندازه اولیه ۸ گیگابایت (۸,۳۸۸,۶۰۸ کیلوبایت) و رشد خودکار ۱ گیگابایت (۱,۰۴۸,۵۷۶ کیلوبایت) هستند. اطمینان حاصل کنید که این فایل‌ها را برای عملکرد بهتر روی دیسک‌های جداگانه یا حداقل روی مجموعه‌های اسپیندل (spindle sets) مجزا قرار دهید:

ALTER DATABASE TempDB
ADD FILE (NAME = N'tempdev2', FILENAME = N'C:\SQL_Data\tempdb2.ndf', SIZE = 8388608KB, FILEGROWTH = 1048576KB); -- مسیر فایل را بر اساس ساختار دیسک خود تنظیم کنید
GO
ALTER DATABASE TempDB
ADD FILE (NAME = N'tempdev3', FILENAME = N'D:\SQL_Data\tempdb3.ndf', SIZE = 8388608KB, FILEGROWTH = 1048576KB); -- مسیر فایل را بر اساس ساختار دیسک خود تنظیم کنید
GO

اگر نیاز به تغییر اندازه این فایل‌های اضافی دارید، می‌توانید از دستور `ALTER DATABASE MODIFY FILE` مشابه فایل اصلی `tempdev` استفاده کنید. فقط اطمینان حاصل کنید که سرویس SQL Server قبل از اعمال تغییراتی که شامل کوچک کردن هستند، راه‌اندازی مجدد شده باشد.

نتیجه‌گیری

پیکربندی و تنظیم اندازه صحیح `TempDB` بخش مهمی از نگهداری یک محیط SQL Server سالم و با عملکرد بالا است. با دنبال کردن این مراحل، می‌توانید فایل‌های `TempDB` خود را به طور مؤثر مدیریت کرده و از مشکلات رایج مرتبط با اندازه و رشد آن جلوگیری کنید و به بهینه‌سازی عملکرد SQL Server کمک شایانی نمایید.