معرفی مهم‌ترین شاخص‌های SQL Server به منظور مانیتورینگ کارایی پردازنده

۱۴۰۳/۰۵/۰۹

یکی دیگر از مواردی که امکان رخداد bottleneck در آن وجود دارد پردازنده است. ممکن است بار کاری پردازنده به دلایل مختلفی زیاد شود و تعداد زیادی درخواست برای پردازش کوئری‌های مختلف در صف انتظار وجود داشته باشند. این مسئله می‌تواند منجر به کاهش سرعت و به طور کلی کاهش کارایی SQL Server شود. البته که کاهش کارایی در پردازش درخواست‌ها می‌تواند ریشه در کمبود حافظه نیز داشته باشد که در مقاله قبل به آن پرداخته شد. کاهش کارایی مرتبط با پردازنده به دلایل مختلفی ممکن است رخ دهد که یکی از آشکارترین آن‌ها کمبود منابع سخت‌افزاری و به طور کلی کافی نبودن ظرفیت پردازنده برای پاسخ به درخواست‌ها (کوئری‌ها) می‌باشد. به کمک شاخص‌های مختلف می‌توان نشانه‌های کاهش کارایی را تشخیص داد که در ادامه به بررسی برخی از مهم‌ترین شاخص‌های مرتبط با پردازنده در SQL Server می‌‌پردازیم.

· مدت زمان استفاده از پردازنده

این شاخص مشخص می‌کند که پردازنده چه درصد از زمان را مشغول به کار بوده است. در صورتی که مقدار این شاخص بیش از 80 درصد باشد با احتمال زیاد با یک bottleneck مواجه هستیم. دلیل بروز چنین مسئله‌ای می‌تواند در کوئری‌هایی باشد که به درستی tune نشده‌اند و زمان زیادی برای پردازش آنها طول می‌کشد.

· SQL Server Compilations/Sec (#/sec)

فرایند compilation در SQL Server از جمله فرایندهایی است که می‌تواند زمان زیادی از پردازنده را اشغال نماید. زمانی که یک کوئری به Query Optimizer داده می‌شود و پس از جستجو در Plan Cache هیچ matching ای برای آن کوئری یافت نمی‌شود (فلسفه Plan Cache ذخیره کوئری‌های پر استفاده در حافظه کش به منظور استفاده مجدد است)، لازم است کوئری توسط Optimizer کامپایل (Compile) شود و یک Execution Plan برای آن ایجاد شود و سپس به Plan Cache اضافه شود. سپس این Plan ایجاد شده برای کوئری به Query Execution Engine برای اجرا ارسال می‌شود. در صورتی که کوئری بزرگ و پیچیده باشد فرایند کامپایل کردن آن می‌تواند فرایندی زمان‌بر باشد و مدت زمان زیادی پردازنده را درگیر نماید. به این منظور شاخص Compilations/Sec تعداد عملیات کامپایل را در ثانیه نشان می‌دهد. هر چه مقدار این شاخص بیشتر باشد احتمال کاهش کارایی و سرعت پردازنده بیشتر خواهد شد.

· SQL Server Re-Compilations/Sec (#/sec)

فرایند Recompile به این صورت انجام می‌شود که در ابتدا Optimizer برای یافتن کوئری در Plan Cache جستجو می‌کند. سپس نمونه آن را پیدا کرده و به Execution Engine ارسال می‌کند. سپس زمانی که Validation Check بر روی Plan استخراج شده از Cache انجام می‌شود مشخص می‌شود که این Plan دیگر معتبر نیست و از Optimizer درخواست می‌کند مجدد کوئری را کامپایل نماید. تفاوت Recompile با Compile در این است که در فرایند کامپایل یک Plan جدید برای کوئری ایجاد می‌شود و در Cache ذخیره می‌شود اما در فرایند Recompile، Plan جدید جایگزین Plan قبلی در Cache می‌شود. شاخص Re-compilations/Sec تعداد Recompilation هایی که در ثانیه انجام می‌شود را نشان می‌دهد. هر چه مقدار این شاخص بیشتر باشد احتمال کاهش کارایی و سرعت پردازنده بیشتر خواهد شد. به این معنا که پردازنده مدت زمان بیشتری را به فرایند کامپایل کردن کوئری‌ها اختصاص داده است و اگر کوئری از قبل در Plan Cache وجود داشت نیاز به تخصیص چنین زمانی نبود.

· Batch Requests/sec

دسته‌ها یا Batch ها مجموعه‌ای از دستورات SQL هستند که به صورت یک گروه از دستورات اجرا می‌شوند یا یک دستور SQL است که شامل مجموعه‌ای از دستورات دیگر می‌باشد. در پردازش دسته‌ای (Batch Processing) مجموعه‌ای از دستورات برای اجرا در یک صف قرار می‌گیرند. برای مثال می‌توان مجموعه‌ای از دستورات را در یک صف ذخیره کرد و در زمان‌های بیکاری پردازنده آن‌ها را اجرا نمود. در این نوع از پردازش تضمینی برای موفقیت یا شکست اجرای دستورات داده نمی‌شود (برخلاف پردازش Transaction ها). اجرای دستورات به صورت batch می‌تواند مزیت‌هایی به همراه داشته باشد. یکی از این مزیت‌ها این است که اجرای دستورات به صورت batch کاراتر از اجرای دستورات به صورت تک خواهد بود. برای مثال می‌توان این مجموعه دستورات که به صورت یک گروه در کنار هم قرار گرفته‌اند را تواما با هم برای اجرا بهینه نمود.

شاخص Batch Requests/sec تعداد دستورات دسته‌ای (Batch) اجرا شده توسط سرور را در یک ثانیه نشان می‌دهد. در واقع این شاخص نشان‌دهنده میزان بار (load) بر روی سرور است. برای یک Database Administrator یکی از اولین شاخص‌هایی که در زمان کند شدن سرعت اجرای کوئری‌ها می‌تواند بررسی کند این شاخص است. البته برای تعیین اینکه آیا بار زیادی بر روی سرور وجود دارد یا خیر این شاخص به تنهایی کافی نیست و لازم است پارامترهای دیگری مانند میزان حافظه، نوع کوئری‌ها، تعداد عملیات ورودی/خروجی دیسک، ظرفیت شبکه نیز بررسی شود. تمام این پارامترها می‌تواند در تفسیر مقداری که این شاخص نشان می‌دهد تاثیرگذار باشد.

برای اینکه بتوان تفسیر درستی از مقدار این شاخص داشت لازم است تعداد دستوراتی که سرور بدون کاهش کارایی می‌تواند اجرا کند را دانست. این مقدار می‌تواند 500، 2000، 5000 یا حتی 10000 باشد. مقدار بالا برای شاخص Batch Requests/sec لزوما بد نیست مادامی که کارایی سرور پایین نباشد اتفاقا می‌تواند نشان‌دهنده این باشد که سرور از تمام ظرفیت برای پردازش کوئری‌ها استفاده می‌کند. بنابراین نکته مهم در تفسیر این شاخص برای یک DBA این است که ماکزیمم تعداد دستوراتی که سرور می‌تواند اجرا کند بدون پایین آمدن کارایی چه میزان است و این شاخص را با آن مقایسه نماید.

در مورد مقدارهای پایین برای این شاخص نیز مواردی که گفته شد صدق می‌کند. البته در برخی موارد ممکن است استثناهایی وجود داشته باشد. برای مثال فرض کنید تعداد دستوراتی که در حالت معمول سرور می‌تواند اجرا نماید حدود 3000 دستور باشد و شاخص batch Requests/sec مقدار 400 را نشان دهد که نشان می‌دهد سیستم از تمام ظرفیت خودش استفاده نمی‌کند. ممکن است مشکل در قطعه کدی باشد که به طور مناسبی بهینه‌سازی نشده است.

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

· Transaction/sec

یک SQL Transaction یک واحد کاری است که بر روی پایگاه داده SQL اجرا می‌شود. هر SQL Transaction شامل مجموعه‌ای از عملیات است که از ترکیبی از دستورات SQL تشکیل شده است که با ترتیب مشخصی اجرا می‌شوند. یکی از ویژگی‌های مهم Transaction ها در SQL این است که یا دستورات به طور کامل اجرا می‌شوند یا اجرای آنها به طور کامل شکست می‌خورد. به این معنی که حتی اگر اجرای یکی از دستورات با شکست مواجه شود کل transaction ناموفق خواهد بود. پس از اینکه اجرای یک transaction با موفقیت به اتمام برسد قابلیت rollback/undo برای آن وجود دارد به این معنی که می‌توان تمام تغییرات اعمال شده را به حالت اولیه برگرداند.

شاخص Transaction/sec تعداد transaction هایی که در واحد ثانیه شروع می‌شوند را نشان می‌دهد. این شاخص به دو فاکتور وابسته است: 1- فرکانس خواندن شاخص و 2- مدت زمان transaction. اگر فرکانس خواندن شاخص یک ثانیه باشد و transaction برای مدت 10 ثانیه در حال اجرا باشد، شاخص transaction/sec مقدار 10 را نشان می‌دهد. بنابراین اگر مدت زمان اجرای transaction ها بیشتر از مدت زمان خواندن شاخص باشد یک transaction ممکن است چندین بار محاسبه شود. مقدار این شاخص به این صورت محاسبه می‌شود که تعداد transaction های اجرایی در دو بازه خوانده می‌شود و تقسیم بر فاصله زمانی بین دو بازه خواندن می‌شود.

Transactions/sec= (Last transaction number – Previous transaction number)/Number of seconds between two readings

. با یک مثال می‌توان این شاخص را بیشتر شرح داد:

. تعداد transaction های فعلی: 1000

. تعداد transaction های قبلی: 500

. فاصله زمانی بین دو خواندن: 5 ثانیه

. شاخص Transaction/sec: (۱۰۰)=۵/(۵۰۰-۱۰۰۰)

با توجه به ماهیت این شاخص یک مقدار نسبی را مشخص می‌کند. بنابراین تفسیر درستی از این شاخص احتمالا به این صورت است که نسبت به دیروز (بسیار) بیشتر است یا نسبت به هفته گذشته (بسیار) کمتر است. بنابراین شاید مقدار بالا برای این شاخص لزوما نشان‌دهنده یک مشکل نباشد بلکه بیشتر نشان‌دهنده فعالیت و رفتار SQL Server است.