مانیتورینگ شاخص‌ های حافظه در SQL Server

در مقاله برخی عوامل تاثیرگذار بر کارایی SQL Server انواع bottleneck ها در SQL Server معرفی شد. bottleneck ها به طور کلی می­‌توانند در مواردی مانند: 1- حافظه، 2- پردازنده، 3- دیسک و 4- اتصالات کاربران رخ دهند. همچنین اشاره شد که هر یک از این bottleneck ها می­‌تواند منجر به کاهش کارایی سرور شود. لذا لازم است عوامل مختلفی که منجر به بروز آن­ها می‌­شوند را شناسایی و رفع نمود. برای شناسایی bottleneck ها لازم است تا به نشانه­‌های آن‌­ها در سیستم توجه نمود. به این منظور مانیتورینگ می‌­تواند ابزاری برای تحت نظر قرار دادن این نشانه‌­ها در سیستم باشد. به کمک مانیتورینگ و تعریف شاخص‌­های مختلف که هر کدام یکی از ابعاد bottleneck ها را پوشش می‌­دهند، می‌­توان نظارتی بر عملکرد کلی سیستم داشت.

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

1.معرفی شاخص‌های مرتبط با حافظه

به صورت پیش­فرض حافظه در SQL Server به صورت پویا (در زمان اجرا) و براساس منابع موجود سیستم تخصیص داده می‌­شود. اگر که SQL Server نیاز به حافظه بیشتری داشته باشد، از سیستم عامل درخواست می­‌کند که آیا حافظه آزاد برای تخصیص به آن وجود دارد یا خیر. میزان حداقل و حداکثر حافظه (min server memory و max server memory) را می­توان به صورت دستی در SQL Server تنظیم نمود. به منظور مانیتورینگ میزان حافظه‌­ای که SQL Server استفاده می‌­نماید از شاخص­‌های زیر استفاده می‌­­شود:

· SQL Server : Memory Manager : Total Server Memory (KB)

این شاخص میزان حافظه را که SQL Server memory manager از حافظه سیستم عامل به SQL Server تخصیص داده است مشخص می­‌نماید. انتظار می‌­­رود که این میزان حافظه تخصیص داده شده به SQL Server با شروع به کار آن و به مرور با افزایش تعداد و حجم کوئری‌­ها به پایگاه داده افزایش یابد. به منظور به دست آوردن مقدار این شاخص می‌­توان به فایل sys.dm_os_sys_info کوئری زد و ستون commited_kb را خواند.

· SQL Server : Memory Manager : Target Server Memory (KB)

این شاخص میان حافظه ایده­‌آل که SQL Server می‌تواند براساس بار کاری جاری سرور استفاده کند را مشخص می­‌نماید. بعد از این که مدت زمانی از اجرای SQL Server گذشت، برای اینکه بررسی شود آیا میزان حافظه مورد نیاز دریافت شده است یا نه، مقدار این شاخص با Total Server Memory مقایسه می‌­شود. بعد از گذشت مدت زمانی از شروع به کار SQL Server، مقدار Total Server Memory و Target Server Memory یکسان می‌­شود. در صورتی که مقدار Total Server Memory به طور محسوسی کمتر از Target Server Memory باشد، ممکن است SQL Server دچار کمبود حافظه وMemory pressure شده باشد یا اینکه مقدار Max Server Memory بسیار پایین تنظیم شده است. البته در زمان شروع به کار SQL Server و تا مدت زمانی پس از شروع به کار، مقدار Total Server Memory کمتر از Target Server Memory خواهد بود و این مسئله طبیعی است.

بنابراین با فرض این که مدتی از شروع به کار SQL Server گذشته باشد و حافظه هم به میزان لازم تخصیص داده شده باشد، لازم است رابطه زیر برقرار باشد:

· Total Server Memory  = Target Server Memory (Normal)

· Total Server Memory << Target Server Memory (Possibility of memory pressure)

. شاخص Buffer Cache Hit Ratio

این شاخص مشخص می‌­کند چه درصدی از درخواست‌­های صفحات حافظه در بافر موجود بوده است و از بافر خوانده شده است. صفحات حافظه­‌ای که در بافر موجود نباشند لازم است از دیسک خوانده شوند که سرعت پایین­تری هم نسبت به بافر دارد. در حالت ایده‌­آل باید داده­‌ها به نحوی در حافظه بافر ذخیره شوند که SQL Server تمام صفحات حافظه مورد نظر را از بافر بخواند و نیازی به دسترسی به دیسک نباشد. در این حالت مقدار شاخص Buffer Cache Hit Ratio مقدار 100 درصد خواهد بود. مقدار پیشنهادی برای این شاخص مقداری بالاتر از 90 درصد است.

· Page Life Expectancy

دومین شاخص که مرتبط با حافظه بافر است شاخص Page Life Expectancy است که بالاتر نیز توضیح داده شده است. این شاخص مدت زمان مورد انتظار برای ماندن یک صفحه حافظه در بافر را نشان می‌­دهد. به طور کلی اگر صفحات حافظه مدت زمان بیشتری در بافر بمانند احتمال یافتن آن­‌ها در زمان نیاز توسط سرور بیشتر است. اگر در زمان نیاز صفحات در حافظه بافر نباشند لازم است از دیسک خوانده شوند که سربار خواهد داشت. همچنین اگر حافظه SQL Server که به بافر تخصیص داده شده است کافی نباشد، صفحات به طور مداوم بین حافظه دیسک و بافر جابه‌­جا می‌­شوند که طول عمر آن­ها را در حافظه بافر کاهش خواهد داد. لذا در این حالت لازم است تخصیص حافظه بیشتر برای SQL Server را بررسی نمود. مقدار پیشنهادی برای این شاخص همانطور که پیش­تر نیز ذکر شد حدود 300 ثانیه یا 5 دقیقه است.

2.تحلیل کمتر بودن Total Server Memory از Target Server Memory با استفاده از چند شاخص

زمانی که مقدار Total Server Memory از مقدار Target Server Memory کمتر است می­‌تواند نشانه‌­ای از کمبود حافظه و اصطلاحا Memory Pressure باشد. اما لازم است به شاخص‌­های دیگری نیز در این راستا توجه نمود. در صورتی که مقادیر شاخص­‌های مرتبط نیز کمبود حافظه را تایید نماید می­‌توان با اطمینان بیشتری در مورد کم بودن حافظه تخصیص داده‌ ­شده به SQL Server اظهار نظر نمود. در ادامه این شاخص­‌ها را بررسی خواهیم نمود.

· Page Life Expectancy

این شاخص که پیش­تر نیز بررسی شد، مدت زمانی است که یک صفحه داده در حافظه بافر قرار دارد. این شاخص یکی از مواردی است که memory pressure را نشان می‌­دهد. به این صورت که زمانی که میزان حافظه SQL Server کم است، SQL Server مجبور است فضای حافظه را با صفحاتی که جدیدتر مورد نیاز هستند پر نماید لذا صفحات قبلی را از حافظه پاک می­نماید و طول عمر این صفحات در حافظه کمتر از حدی خواهد بود. حدآستانه برای این شاخص مقدار 300 ثانیه برای هر 4 گیگابایت حافظه رم بر روی SQL Server می‌­باشد. به این معنی که اگر کل حافظه 4 گیگابایت باشد، هر صفحه داده در حافظه باید برای مدت حداقل 5 دقیقه در حافظه نگهداری شود.

. Lazy Writes/Sec

Lazy Writer یک فرایند سیستمی است که صفحاتی که مدت زیادی است مورد استفاده نبوده­‌اند از حافظه بافر حذف می­‌نماید تا فضای حافظه برای استفاده­‌های دیگر آزاد باشد. این شاخص مشخص کننده تعداد دفعاتی است که SQL Server، dirty page هایی که (صفحاتی که خوانده شده­‌اند و/یا تغییراتی در آن­ها داده شده است) برای مدتی استفاده نشده ­اند را از حافظه بافر به دیسک منتقل می­‌نماید. در صورتی که این مقدار نزدیک به صفر باشد به این معنی است که احتمالا حافظه به مقدار مورد نیاز وجود داشته است و نیازی به جابه‌­جایی dirty page ها نبوده است.

. Memory Grants Pending

این شاخص تعداد کل SQL Server Process هایی را نشان می‌­دهد که منتظر تخصیص فضای حافظه هستند. در حالت ایده­‌آل مقدار این شاخص باید صفر باشد. وجود حتی یک Process که منتظر حافظه است می­‌تواند نشان­‌دهنده کمبود فضای حافظه باشد. بنابراین این شاخص نیز در کنار سایر شاخص­‌ها می­‌تواند به منظور بررسی کافی بودن میزان حافظه تخصیص داده شده به SQL Server استفاده شود.

3.معرفی شاخص‌های مرتبط با لاگ 

در SQL Server همانند تمامی سیستم­‌های مدیریت پایگاه داده دیگر لازم است دو فرایند Logging و Recovery به طور مداوم انجام شود. تمامی عملیات در SQL Server به صورت لاگ­‌هایی در فایل‌­های مخصوص به نام transaction log ثبت و نگهداری می‌­شوند. هر تغییری در پایگاه داده‌­ها لازم است به نوعی ذخیره شود. این لاگ­‌ها به نوعی ذخیره می‌­شوند تا در صورت بروز مشکلی در پایگاه داده یا در داده­‌ها بتوان تغییرات را معکوس کرد یا دوباره تکرار نمود. فایل­های transaction log ظرفیت‌­های مشخصی دارند (با مقدار ‘Log File(s) Size (KB)’ مشخص می­‌شود) که به مرور با ثبت تغییرات در فرمت لاگ در آن­‌ها، ظرفیت فایل پر خواهد شد (میزان استفاده شده فایل لاگ با مقدار ‘Log File(s) Used Size (KB)’ مشخص می­‌شود). برای اینکه بتوان میزان فضای استفاده شده لاگ‌­ها را مانیتور نمود شاخص ‘Percent Log Used’ معرفی می­‌شود که در ادامه بررسی خواهد شد.

· Percent Log Used

­این شاخص مشخص می‌­کند هر پایگاه داده چه درصدی از فضای لاگ را استفاده کرده است (میزان فضای استفاده شده در لاگ). به منظور دسترسی به این شاخص می­‌توان به فایل sys.dm_os_performance_counters کوئری زد و ستون Percent Log Used را خواند. زمانی که مقدار این شاخص از حد آستانه گذر کند نشان‌­دهنده این است که سرور فضای کافی برای ذخیره لاگ­‌ها ندارد و لازم است فضای بیشتری به فایل لاگ‌­ها اختصاص داده شود یا اینکه از طریق Recovery از لاگ­‌ها فضای فایل لاگ را خالی نمود. معمولا مقدار 80 درصد وضعیت هشدار و مقدار 90 درصد وضعیت بحرانی را برای این شاخص نشان می‌­دهد و لازم است اقدام مناسب در این راستا صورت گیرد.

· ارتباط بین پر شدن فایل لاگ و مدل Recovery در SQL Server

عملیات لاگ در SQL Server کمک می‌­کند تا در مواقع بروز مشکل transaction هایی که نهایی بوده‌­­اند و اصطلاحا commit شده­‌اند به درستی در پایگاه داده بعد از بروز مشکل اعمال شوند و همچنین transaction های uncommitted که نباید در پایگاه داده اعمال شوند بعد از بروز مشکل نیز اثری بر روی پایگاه داده نداشته باشند. فرایند لاگ با عملیاتی به نام Recovery همراه است. Recovery به معنای تکرار یا معکوس نمودن تغییراتی است که در فایل transaction log به صورت رکوردهای لاگ ثبت شده است. تکرار رکوردهای لاگ به فاز REDO (Roll forward) و معکوس نمودن رکوردهای لاگ به فاز UNDO (Roll back)  شناخته می‌­شود.

در SQL Server سه مدل Recovery مختلف وجود دارد: 1-  مدل FULL، 2- مدل SIMPLE و 3- مدل BULK_LOGGED. در مدل FULL تمامی اجزای یک عملیات به صورت لاگ باید ذخیره شود. در این مدل از Recovery تا زمانی که یک Log backup انجام نشود فایل لاگ‌­ها  فضای خالی آزاد نخواهد کرد. احتمال پرشدن سریع­تر فضای فایل لاگ در این مدل از Recovery بسیار بیشتر است. در مدل BULK_LOGGED اجازه می‌­دهد برخی از عملیات پایگاه داده به صورت کامل لاگ نشوند و اصطلاحا partially logged وجود دارد لذا در این حالت تعداد رکوردهای لاگی که ثبت می‌­شود بسیار کمتر از حالت FULL می­‌باشد و بنابراین احتمال transaction log growth یا افزایش سایز فایل لاگ را کاهش می‌­دهد. مدل SIMPLE اما رفتار logging آن همانند مدل BULK_LOGGED است با این تفاوت که رفتار log backup آن متفاوت است به این صورت که تا زمانی که به Checkpoint نرسیم عملیات log backup انجام نمی‌­شود.  هر کدام از این روش­‌ها مزایا و معایبی دارد که توجه به آن‌­ها و استفاده درست با توجه به کاربرد سیستم می­‌تواند بر روی میزان فضای آزاد و استفاده شده فایل لاگ تاثیر گذارد.