آشنایی با شاخص های مانیتورینگ SQL Server و نحوه استفاده از آن ها

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

مانیتورینگ SQL Server چیست؟

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

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

مهم ترین دلایل استفاده از شاخص های مانیتورینگ SQL Server

دلایل اصلی استفاده از شاخص های مانیتورینگ SQL Server عبارتند از:

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

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

انواع شاخص های مانیتورینگ SQL Server

در این بخش، با انواع شاخص های مانیتورینگ SQL Server آشنا می‌شویم تا بتوانیم نقش هر یک را در بهبود عملکرد پایگاه داده درک کنیم.

شاخص های مربوط به انجام دستورات Transact SQL

دستورات T-SQL یا همان Transact SQL، عملگرهای اضافه‌ای مثل کنترل اجرای دستورات، مدیریت خطاها، پردازش سطرهای جدول و… را در اختیار مدیر پایگاه داده قرار می‌دهند. شاخص های مانیتورینگ SQL Server در زمینه نحوه اجرای دستورات T-SQL عبارتند از:

• تعداد Batch‌ های اجرا شده در یک ثانیه

این شاخص، تعداد Batch‌ های اجرا شده در یک ثانیه را اندازه‌گیری می‌کند. یک Batch می‌تواند شامل یک یا چند دستور SQL Server و یا ارسال یک درخواست برای اجرای یک رویه (پروسیجر) باشد.

• تعداد دستورات کامپایل‌شده در هر ثانیه

این شاخص، تعداد دستورات SQL Server که هر ثانیه توسط این پایگاه داده، کامپایل (Compile) می‌شود را اندازه‌گیری می‌کند. در عمل، دستورات قبل از اجرا، کامپایل و ذخیره می‌شوند. تعداد کامپایل‌هایی که در هر ثانیه انجام می‌شود، باید کمتر از تعداد Batch‌ های ارسال‌شده به سرور در هر ثانیه باشد.

• تعداد دستوراتی که مجدداً در یک ثانیه کامپایل می‌شوند

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

• زمان اجرای دستورات اخیر

این شاخص که بر حسب میلی‌ثانیه مشخص می‌شود، برابر است با مدت زمان انتظار برای اجرای دستورات اخیری که به SQL Server ارسال گردیده. اگر این زمان بیش از حد انتظار ما باشد، باید تغییراتی در پایگاه داده اعمال کنیم تا زمان اجرای دستورات کاهش پیدا کند.

شاخص های مربوط به Buffer Cache

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

• Buffer Cache Hit Ratio

این شاخص، تعداد دفعاتی که اطلاعات، به جای دیسک، از حافظه کش (Cache) بازیابی می‌شوند را مشخص می‌کند. پایین‌بودن دفعات بازیابی اطلاعات از حافظه کش، باعث ایجاد تأخیر و افت تجربه کاربری می‌شود.

• طول عمر مورد انتظار برای هر Page

همانطور که در این مقاله نیز توضیح داده‌ایم، داده‌ها در SQL Server در ساختاری به نام Page ذخیره می‌شوند. به کمک این شاخص می‌توان زمانی که یک Page در بافر می‌ماند (بدون هیچ ارجاعی به آن Page) را بر حسب ثانیه، بیان کرد. طبق استانداردهای مایکروسافت، طول عمر یک Page در بافر نباید بیش از سیصد ثانیه باشد (به ازای هر ۴ گیگابایت RAM اختصاص‌داده‌شده به سرور). اگر این مقدار کمتر از ۳۰۰ ثانیه باشد، ممکن است نیاز به اضافه‌کردن RAM داشته باشید.

شاخص های فعال سازی Lock

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

• مدت زمان فعال بودن Lock در هر ثانیه

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

• متوسط زمان فعال‌بودن Lock

بررسی متوسط زمان فعال‌بودن Lock نیز بسیار مهم است؛ چراکه به ما کمک می‌کند تا تأخیرهای طولانی را شناسایی کرده و از پاسخگویی به‌موقع سرور به سایر کاربران، اطمینان حاصل کنیم.

شاخص های مربوط به استفاده از منابع (پردازنده، RAM و…)

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

• میزان استفاده از دیسک

مانیتورینگ این شاخص برای اطلاع از کمبود فضای ذخیره‌سازی، بسیار مهم است.

• درصد استفاده از پردازنده

به کمک این شاخص، می‌توان میزان تخصیص پردازنده به فعالیت‌ها، منابع و کاربران را مشخص کرد. تخصیص صحیح پردازنده، باعث بهبود عملکرد پایگاه داده می‌شود.

• بررسی تعداد عملیات‌های خواندن و نوشتن

بررسی عملیات‌های خواندن و نوشتن روی دیسک، زمان اختصاص داده‌شده به آن‌ها را مشخص می‌کند. اگر این زمان طولانی باشد، بر عملکرد پایگاه داده اثر منفی خواهد گذاشت.

• بررسی طول صف درخواست‌ها در دیسک

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

• بررسی زمان فعال ماندن Latch

این شاخص، عملکردی مانند Lock دارد؛ با این تفاوت که به جای دیسک، روی RAM اجرا می‌شود. Latch کمک می‌کند تا یکپارچگی داده‌های موجود در RAM حفظ شود. زمانی که سیستم، نتواند درخواست‌های نوشتن یا خواندن روی RAM را به‌موقع اجرا کند، تأخیر در عملکرد Latch رخ داده است. در چنین حالتی، دریافت اطلاعات از دیسک و کپی‌کردن آن‌ها درون RAM، زمان بیشتری را به خود اختصاص خواهد داد.

شاخص های مانیتورینگ SQL Server در بخش Indexing

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

• تقسیم‌بندی Page ها در هر ثانیه

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

• درصد از هم گسیختگی (Fragmentation)

اندازه‌گیری درصد Fragmentation، یکی از مهم‌ترین شاخص های مانیتورینگ SQL Server است. از هم‌ گسیختگی یا Fragmentation زمانی رخ می‌دهد که ترتیب ذخیره داده‌ها روی حافظه ذخیره‌سازی، با ترتیب ایندکس‌هایی که به این داده‌ها اشاره می‌کنند، متفاوت باشد. Fragmentation می‌تواند به علت افزایش حجم داده‌ها، تقسیم‌بندی Page ها و تغییرات ایجادشده در ایندکس‌ها بعد از انجام عملیات‌هایی مثل Update و Insert یا Delete رخ دهد. درصد بالای از هم گسیختگی، باعث استفاده بیش از حد از منابع و کندی سیستم خواهد شد؛ چراکه عملیات جستجو باید در میان Page های بیشتری انجام شود.

شاخص های مانیتورینگ SQL Server را چگونه اعمال کنیم؟

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

۱. نرم افزار Paessler PRTG

شاخص های مانیتورینگ SQL Server مانند موارد زیر را می‌توان به کمک نرم افزار Paessler PRTG اندازه‌گیری کرد:

• مدیریت استفاده از پردازنده
• مدیریت بافر
• مدیریت RAM
• مدیریت فضای خالی در حافظه ذخیره‌سازی
• مدیریت Batch
• مانیتورینگ کامپایل‌شدن دستورات در هر ثانیه

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

۲. نرم افزار ManageEngine Applications Manager

یکی از بهترین ابزارها برای اندازه‌گیری شاخص های مانیتورینگ SQL Server ، نرم افزار ManageEngine Applications Manager است. در این نرم‌افزار، می‌توان کارهایی مانند موارد زیر را انجام داد:

• مانیتورینگ Lock
• نظارت بر Indexing
• نظارت بر بافر و حافظه کش
• نظارت بر نحوه اجرای کوئری‌های SQL Server

این نرم‌افزار، کوئری‌هایی که اجرای آن‌ها به قدرت پردازشی قابل توجهی نیاز دارد را شناسایی می‌کند. همچنین می‌توان Indexing نامناسب را نیز به کمک آن شناسایی کرد.

۳. نرم افزار Idera SQL Diagnostic Manager

از نرم افزار Idera SQL Diagnostic Manager می‌توان برای انجام کارهایی مانند این موارد استفاده کرد:

• نظارت بر پردازنده
• نظارت بر فضای ذخیره‌سازی
• نظارت بر حافظه RAM
• نظارت بر نحوه اجرای دستورالعمل‌ها
• شناسایی مشکلات و خطاها

این ابزار را می‌توان به‌صورت تحت وب نیز استفاده کرد؛ بدون اینکه نیازی به نصب و اشغال حافظه ذخیره‌سازی وجود داشته باشد.

۴. نرم افزار SolarWinds SQL Sentry

نرم افزار SolarWinds SQL Sentry می‌تواند اطلاعات خوبی درمورد موارد زیر به شما بدهد:

• درصد استفاده از پردازنده
• میزان استفاده از ترافیک شبکه
• میزان حافظه و RAM اشغال‌شده توسط SQL Server
• میزان تأخیر در عملیات‌های خواندن و نوشتن

اندازه‌گیری شاخص های مانیتورینگ SQL Server تنها کاری نیست که این نرم‌افزار انجام می‌دهد؛ بلکه می‌تواند مشکلات عملکردی این پایگاه داده را شناسایی و رفع کند.

۵. نرم افزار New Relic

از نرم افزار New Relic می‌توان برای انجام کارهای زیر استفاده کرد:

• مدیریت لاگ (Log)
• مانیتورینگ شبکه
• مدیریت حافظه و RAM
• مانیتورینگ تعداد Lock در هر ثانیه

این نرم‌افزار می‌تواند اطلاعات زیادی درباره عملکرد SQL Server را جمع‌آوری کرده و در اختیار مدیر پایگاه داده قرار دهد.

جمع بندی: آشنایی با شاخص های مانیتورینگ SQL Server

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