معرفی مهم‌ترین شاخص‌های SQL Server به منظور مانیتورینگ کارایی I/O

۱۴۰۳/۰۵/۱۵

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

. Average Disk sec/Read

یکی از مهم‌ترین شاخص‌های کارایی دیسک این شاخص و شاخص Average Disk sec/Write می‌باشد. هر دو این شاخص‌ها می‌توانند در دو سطح منطقی و فیزیکی دیسک مانیتور شوند و تاخیر دیسک را نشان می‌دهند. هر چه مدت زمان خواندن و نوشتن داده کمتر باشد سیستم سرعت بالاتری خواهد داشت. مقدار این شاخص در واقع مدت زمانی است (به ثانیه) که برای هر خواندن طول می‌کشد. این شاخص متناسب است با مدت زمان لازم برای یک بار چرخش دیسک. برای مثال اگر یک دیسک در یک دقیقه 3600 بار چرخش داشته باشد، بنابراین یک بار چرخش آن 0.016=60/3600 ثانیه یا 16 میلی ثانیه طول می‌کشد. بنابراین Average Disk sec/Read آن بهتر است مضربی از 16 میلی ثانیه باشد. مقادیر پیشنهادی برای این شاخص به صورت زیر است :

. کمتر از 8 میلی ثانیه : کارایی عالی

. بین 9 الی 12 میلی ثانیه : کارایی خوب

. بین 12 الی 20 میلی ثانیه : کارایی نسبتا خوب

. بیشتر از 20 میلی ثانیه : کارایی بد

در طول بازه‌هایی که عملیات I/O بیشترین حد را دارد این شاخص می‌تواند مقداری حدود ۲۵ میلی ثانیه را نشان دهد اما به طور کلی مقدارهای بیشتر از 20 میلی ثانیه نشان‌دهنده کارایی ضعیف است.

· Average Disk sec/Write

یکی دیگر از شاخص‌های مناسب برای مانیتورنیگ کارایی دیسک شاخص Average Disk sec/Write است که نشان‌دهنده میانگین زمان (به ثانیه) برای نوشتن داده به دیسک است. معمولا سرعت نوشتن و خواندن دیسک متفاوت است. مقدار پیشنهادی برای non-cached write ها مشابه با شاخص Average Disk sec/Read است اما برای cached write ها این مقادیر بسیار متفاوت است. مقادیر بیشتر از 4 میلی ثانیه نشان‌دهنده کارایی ضعیف است. مقادیر پیشنهادی این شاخص به صورت زیر است:

. کمتر از یک میلی ثانیه: کارایی عالی

. بین 1 الی 2 میلی ثانیه: کارایی خوب

. بین 2 الی 4 میلی ثانیه: کارایی نسبتا خوب

. بیشتر از 4 میلی ثانیه: کارایی بد

در صورتی که مقادیر دو شاخص Average Disk sec/Read و Average Disk sec/Write به طور مداوم بیشتر از مقدار تعیین شده باشند نشان‌دهنده bottleneck برای دیسک است و نیاز است بررسی‌های دقیق‌تری برای علت بروز این مشکل انجام شود. برای مثال اگر مشخص شد که مقدار این دو شاخص همیشه بالاتر از حد معمول است، میتوان شاخص‌های مختص برای خواندن و نوشتن‌های دیسک را بررسی نمود. شاخص‌هایی مانند Disk Write Bytes/sec و Disk Read Bytes/sec برای logical disk. همچنین اگر مقدار دو شاخص Average Disk sec/Read و Average Disk sec/Write برای همه دیسک‌ها بالا باشد، مشکل می‌تواند در نحوه ارتباط بین دیسک‌ها باشد. اما اگر فقط در یک دیسک مقادیر شاخص‌ها بالا باشد احتمالا مشکل مختص به همان دیسک می‌باشد.

مانیتورینگ دو شاخص Average Disk sec/Read و Average Disk sec/Write می‌تواند برای تعیین اینکه آیا نیاز به تغییر تنظیمات disk controller cache وجود دارد استفاده شود. برای مثال اگر مقدار Average Disk sec/Read خیلی بیشتر از مقدار Average Disk sex/Write باشد می‌توان بهینه‌سازی cache را برای عملیات خواندن در نظر گرفت.

· Average disk writes/sec و Average disk reads/sec

این دو شاخص نشان‌دهنده نرخ خواندن و نوشتن دیسک است. شاخص Disk Transfers/sec نیز نشان‌دهنده مجموع این دو شاخص به معنای مجموع تمام درخواست‌های I/O دیسک است. اگر مقادیر شاخص‌ها پایین باشد نشان‌دهنده سرعت پایین پردازش‌های دیسک است (خواندن و نوشتن). برای این دو شاخص حد آستانه‌های دقیقی وجود ندارد و به عواملی مانند تنظیمات دیسک و سرور بستگی دارد. به این منظور پیشنهاد می‌شود مقادیر این شاخص‌ها را برای مدت زمانی مانیتور نمود تا ترند آن به این صورت مشخص شود. همچنین با توجه به ترند این دو شاخص می‌توان baseline هایی برای آن‌ها در نظر گرفت مانند شکل زیر که مقادیر دو شاخص را به همراه خطوط baseline آن‌ها را نشان می‌دهد.

Average disk writes/sec و Average disk reads/sec

· Page Reads/Sec

این شاخص تعداد عملیات خواندن از دیسک را در یک ثانیه به ازای تمام دیتابیس‌ها در یک instance نشان می‌دهد. با توجه به اینکه عملیات خواندن و نوشتن از دیسک هزینه بر است و سرعت پایینی دارد، هدف این است که تا حد امکان تعداد این عملیات را کاهش داد. به این منظور می‌توان از راهکارهایی همچون استفاده از حافظه کش داده با ظرفیت بیشتر، کوئری‌های کاراتر یا طراحی کاراتر پایگاه داده استفاده نمود. مقدار پیشنهادی برای این شاخص کمتر از 90 درصد است. مقداری بیشتر از 90 درصد برای این شاخص نشان دهنده حافظه ناکافی است.

· Page Writes/Sec

این شاخص نیز مانند شاخص Page Reads/Sec تعداد عملیات نوشتن بر دیسک را در یک ثانیه به ازای تمام دیتابیس‌ها در یک instance نشان می‌دهد. همانند استدلالی که برای شاخص Page Reads/Sec انجام شد، مقدار پیشنهادی برای این شاخص نیز کمتر از 90 درصد است و مقدار بیشتر از 90 درصد نشان‌دهنده حافظه ناکافی است.

· Disk Write Time%و Disk Read Time%

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

Disk Write Time%و Disk Read Time%

· Idle Time%

زمانی که دیسک درخواستی برای خواندن یا نوشتن نداشته باشد اصطلاحا بیکار (Idle) می‌باشد. این شاخص نیز درصد زمان‌هایی را نشان می‌دهد که دیسک بیکار بوده است. اگر مقدار این شاخص کمتر از حدود 20% شود اصطلاحا می‌گویند دیسک اشباع شده است (Saturated) و احتمالا لازم است با یک دیسک سریع‌تر جایگزین شود. در واقع اگر مقدار شاخص کمتر از حدود 20% شود دیسک قادر به پردازش درخواست‌های خواندن ونوشتن در زمان مناسب نیست. البته قبل از تغییر دیسک به دیسکی با سرعت بالاتر شاید بهتر است بررسی شود که آیا می‌ توان برخی از برنامه‌ها را حذف کرد یا به دیسک دیگری منتقل کرد یا خیر.

Idle Time%