پیشنهاد در جهت بالابردن کارایی دیتابیس در SQLServer - لاگ فایل را فراموش نکنید.

همانطور که میدانید هر دیتابیس دو فایل اصلی دارد: Data File و Log File. تمامی اتفاقاتی که بر روی دیتابیس می افتد به طور کامل در فایل لاگ(Ldf) ثبت می‌شود، این بدین معناست که این فایل به شدت مورد استفاده SqlServer است، پس توجه ویژه به آن در کارایی بهتر دیتابیس تاثیرگذار خواهد بود. در ادامه پیشنهادهایی  برای داشتن کارایی بهتر روی فایل لاگ را عنوان می‌کنم:

1. چند لاگ فایل ایجاد نکنید.

رفتار ذخیره سازی دیتا در فایل لاگ به صورت متوالی (Sequential) است به همین دلیل ایجاد کردن چندین فایل لاگ برای یک دیتابیس کمکی به کارایی آن نمی‌کند، زیرا تا فایل قبلی پر نشده باشد سراغ فایل بعدی نمی‌رود.

2. لاگ فایل  را در دیسک جداگانه ای قرار دهید. 

نوع دسترسی به لاگ فایل برخلاف دیتا فایل به صورت Sequential است، بهمین دلیل اگر بتوانید دیسک جداگانه ای به آن اختصاص دهید به شدت بر روی کارایی دیتابیس تان تاثیر گذار خواهد بود.
توجه کنید نباید در کنار آن لاگ فایل های دیگر یا فایل های Backup را قرار دهید زیرا باعث می‌شود دسترسی Random Access گردد و آن نتیجه مطلوب را به دست نیاورید.

تاکید می‌کنم منظور از دیسک در اینجا واقعا یک دیسک جداست، نه یک پارتیشن دیگر! 

همچنین جدا کردن لاگ فایل و دیتا فایل در دیسک های جداگانه اجازه می دهد تا I/O به طور همزمان بر روی هر دو فایل فعالیت کند و همین امر بر روی کارآیی دیتابیس تاثیر مثبتی خواهد داشت.

3. به تنظیمات اولیه دقت کنید.

چنانچه نگاهی به معماری فیزیکی لاگ فایل بیاندازیم متوجه می شویم،  تمامی دستوراتی که باعث تغییر بر روی دیتابیس می‌شوند در فایل های VLF ذخیره می‌شوند. هرچه تعداد فایل‌های VLF بیشتر شود در حالیکه سایزشان کم است، کارایی دیتابیس کاهش پیدا می کند، در اصطلاح گفته می‌شود که لاگ فایل دچار Internal Fragmentation شده است.

این اتفاق چه زمانی ممکن است رخ دهد؟ زمانیکه بدون توجه به میزان رشد دیتابیس از تنظیمات پیش فرض برای ایجاد دیتابیس استفاده شود. 

بهمین دلیل پیشنهاد می‌شود:

• در زمان ایجاد دیتابیس Initial Size آن را  1 گیگابایت قرار دهید، اینکار باعث می شود در همان ابتدا 16 فایل VLF بزرگ ایجاد کند و دچار Internal Fragmentation نشود.
• همچنین میزان Growthing آنرا نیز ترجیحا 1 گیگابایت قرار دهید ( درصدی قرار ندهید) تا زمانیکه نیاز به رشد پیدا کرد، فایل های بزرگ ایجاد کند.

 از Log Backup بهره ببرید.

با توجه به اینکه تمامی تغییرات در فایل لاگ ذخیره می‌شد، هرچه دیتابیس پرکاربردتر باشد سرعت رشد فایل لاگ نیز بیشتر است.  برای کنترل کردن این سرعت رشد دو راه استفاده می‌شود:

1. قرار دادن Recovery Model به صورت Simple  : در این حالت VLF ها به صورت گردشی مورد استفاده قرار می‌گیرند. ( این روش برای دیتابیس های غیرحساس معمول است.)
2. قرار دادن Recovery Model به صورت Full و استفاده از Log Backup :   این روش برای دیتابیس های پرکاربرد و حساس توصیه می‌شود، برای مثال اگر شما Log Backup دیتابیس خود را هر 15 دقیقه یکبار تعریف کرده باشید، اگر دیتابیس مشکلی پیدا کند با استفاده از آن می توانید تمامی تغییرات تا 15 دقیقه قبل از اتفاق را بازگردانید.

4.  RAID مناسب برای بانک اطلاعاتی (هم دیتافایل و هم لاگ فایل )  0 + 1 است.

5.  AUTO SHRINK  را بر روی دیتابیس غیرفعال کنید.

از Query  زیر برای بررسی وضعیت فایل لاگ خود می‌توانید استفاده کنید:

SELECT name , recovery_model_desc , log_reuse_wait_desc
FROM sys.databases
WHERE name = '[DATABASENAME]'