نگه‌داری SQL Server: فهرست کارهایی که نکردنشان آرام‌آرام هزینه دارد

سرورهای SQL به‌ندرت یک‌شبه خراب می‌شوند. معمولاً آرام‌آرام بد می‌شوند: کمی کندتر هر ماه، کمی پرتر هر هفته، تا روزی که یک اتفاق کوچک همه‌چیز را زمین می‌زند و آن اتفاق کوچک متهم می‌شود. علت واقعی، ماه‌ها نگه‌داری نشدن است.

این فهرست کارهایی است که باید انجام شوند، با فاصلهٔ پیشنهادی و دلیل — و مهم‌تر، کارهایی که نباید انجام شوند و در بسیاری از سرورها هر شب انجام می‌شوند.

هفتگی: CHECKDB

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

DBCC CHECKDB([Sales]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

سنگین است، پس در پنجرهٔ کم‌باری اجرا کنید. اگر دیتابیس خیلی بزرگ است، دو گزینه دارید: PHYSICAL_ONLY که سریع‌تر است و بیشترِ خرابی‌های سخت‌افزاری را می‌گیرد، یا اجرای CHECKDB روی نسخهٔ بازگردانده‌شده روی سرور دیگر. گزینهٔ دوم را ترجیح می‌دهم چون یک تیر و دو نشان است: هم سلامت داده را می‌سنجد و هم ثابت می‌کند بکاپ واقعاً برمی‌گردد.

و نکته‌ای که باید صریح گفته شود: REPAIR_ALLOW_DATA_LOSS راه‌حل نیست. اسمش را جدی بگیرید — داده حذف می‌شود. راه‌حل، بازگرداندن از بکاپ سالم است. این گزینه فقط وقتی معنا دارد که بکاپ سالمی وجود نداشته باشد، و آن وضعیت خودش نشانهٔ یک خرابی بزرگ‌تر است.

هفتگی: ایندکس و آمار — ولی نه آن‌طور که فکر می‌کنید

رایج‌ترین جاب نگه‌داری در ایران چیزی شبیه این است: هر شب، بازسازی همهٔ ایندکس‌ها. این کار تقریباً همیشه اشتباه است، به سه دلیل: لاگ ترنزکشن را باد می‌کند (و در مدل FULL یعنی حجم عظیم بکاپ لاگ)، در ساعت‌های کاری قفل می‌سازد، و بیشتر اوقات نتیجه‌ای که گرفته‌اید از به‌روز شدن آمار آمده، نه از بازسازی.

روش بهتر، بر اساس درصد پراکندگی:

SELECT  OBJECT_NAME(ips.object_id)              AS table_name,
        i.name                                  AS index_name,
        ips.avg_fragmentation_in_percent        AS frag_pct,
        ips.page_count
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN    sys.indexes i
        ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE   ips.page_count > 1000          -- زیر این حد، کار بی‌فایده است
  AND   ips.avg_fragmentation_in_percent > 5
ORDER   BY ips.avg_fragmentation_in_percent DESC;

قاعدهٔ متعارف: زیر ۵٪ کاری نکنید، بین ۵ تا ۳۰٪ REORGANIZE (سبک و قابل قطع)، بالای ۳۰٪ REBUILD. و ایندکس‌های کوچک‌تر از حدود هزار صفحه را کلاً رها کنید؛ آن‌ها در چند اکستنت جا می‌شوند و پراکندگی‌شان بی‌معناست.

آمار را جدی‌تر از ایندکس بگیرید. بهینه‌ساز بر پایهٔ آمار تصمیم می‌گیرد و آمار قدیمی یعنی نقشهٔ اجرای غلط — که هزینه‌اش از پراکندگی ایندکس بسیار بیشتر است. به‌روزرسانی خودکار روشن است، ولی آستانه‌اش برای جدول‌های بزرگ دیر عمل می‌کند. یک به‌روزرسانی هفتگی با نمونهٔ مناسب، ارزان‌تر و مؤثرتر از بازسازی شبانهٔ ایندکس‌هاست.

روزانه: نگاه به فایل‌ها و رشدشان

سه چیز را ببینید: فضای آزاد درایو، اندازهٔ فایل‌ها نسبت به هفتهٔ پیش، و تنظیم رشد فایل.

رشد فایل بر حسب درصد، تلهٔ کلاسیک است: فایل ۱۰ گیگی با رشد ۱۰٪ یعنی یک گیگ؛ همان فایل وقتی ۵۰۰ گیگ شد یعنی ۵۰ گیگ در یک نوبت، و آن نوبت یعنی چند دقیقه توقف. رشد را مقدار ثابت و متناسب بگذارید، و از همه بهتر، فایل‌ها را از پیش بزرگ کنید تا اصلاً در ساعت کاری رشد نکنند.

و Instant File Initialization را روشن کنید (مجوز Perform Volume Maintenance Tasks برای حساب سرویس SQL). بی آن، هر بزرگ شدن فایل داده یعنی صفر نوشتن روی تمام فضای جدید — روی ۵۰ گیگ، دقایق توقف.

هرگز: کوچک کردن دوره‌ای

جاب SHRINK دوره‌ای شایع‌ترین کار مضری است که به‌نیت نگه‌داری انجام می‌شود. کوچک کردن فایل داده، ایندکس‌ها را به‌شدت پراکنده می‌کند؛ بعد جاب بازسازی ایندکس همان فایل را دوباره بزرگ می‌کند؛ و شما یک چرخهٔ بی‌پایان ساخته‌اید که هر شب I/O می‌خورد و هیچ چیزی بهتر نمی‌شود.

کوچک کردن فقط یک کاربرد مشروع دارد: یک بار، بعد از حذف حجم بزرگی از داده که برنمی‌گردد. آن هم یک بار، با آگاهی، و با بازسازی ایندکس بعدش.

ماهانه: پاک‌سازی تاریخچه و بازبینی

• تاریخچهٔ بکاپ در msdb. اگر هرگز پاک نشود، msdb به چند ده گیگ می‌رسد و کوئری‌های تاریخچه کند می‌شوند. sp_delete_backuphistory با نگه‌داری چند ماه، کافی است.
• فایل‌های قدیمی بکاپ. سیاست نگه‌داری باید اجرا شود، نه فقط نوشته. و همیشه با یک کف امن: «حداقل سه فول سالم آخر را نگه دار» بهتر از «هر چیز قدیمی‌تر از ۱۴ روز را پاک کن» است — چون اگر بکاپ‌گیری دو هفته خراب بوده باشد، قاعدهٔ دوم آخرین نسخه‌های سالم را هم پاک می‌کند.
• ایندکس‌های بی‌استفاده. ایندکسی که خوانده نمی‌شود ولی در هر درج به‌روز می‌شود، هزینهٔ خالص است.
• لاگین‌ها و دسترسی‌ها. حساب‌هایی که دیگر لازم نیستند.

همیشه: ERRORLOG را کسی بخواند

بسیاری از خرابی‌های بزرگ، هفته‌ها قبلش در ERRORLOG اعلام شده‌اند: خطای I/O روی یک فایل، صفحهٔ مشکوک، شکست تخصیص حافظه. مشکل این است که کسی نگاه نمی‌کند — ۹۹٪ آن فایل پیام‌های عادی است و چشم انسان چیزی را که هر روز می‌بیند، نمی‌بیند.

SELECT * FROM msdb.dbo.suspect_pages;   -- اگر این جدول ردیف دارد، همین امروز رسیدگی کنید

راه درست این است که چیزی این فایل را بخواند و فقط الگوهای مهم را بیرون بکشد. هر ابزار پایشی این کار را می‌کند؛ اگر ابزار ندارید، دست‌کم هفته‌ای یک بار sp_readerrorlog را با فیلتر خطا اجرا کنید.

جدول خلاصه

هیچ‌کدام از این‌ها پیچیده نیست. چیزی که سخت است، انجام شدنشان در ماه ششم است، وقتی همه سرشان شلوغ است و سرور ظاهراً سالم است. به همین دلیل هر کدام باید یا خودکار باشند یا جایی ثبت شوند که نبودشان دیده شود.