كيف تراقب أداء خادم SQL Server لديك وقواعد بياناتك

إنّ مراقبة خادم SQL Server وقواعد البيانات يزودك بالمعلومات اللازمة لتشخيص واكتشاف مشاكل الأداء في خادم SQL Server ، إضافة إلى ضبط إعداداته لتحسينها. و ليس من السهل تعريف وتحديد الأداء الأمثل في SQL Server ، حيث يكون هناك دائما مفاضلة بين عدة عوامل من جهة البرامج واالأجهزة. كما أنه يعتمد على بيئة العمل، متطلبات العمل وكذلك سياسة العمل في الشركة.

أحيانا يكون الإبلاغ البطيء عن المشاكل مقبولاً في الشركات الصغيرة، الاّ أنه غير مقبول في الكبيرة منها ، حيث يؤثر التباطؤ ، السقطات والإحتناقات على أعداد مهولة من المستخدمين مما يؤثر سلباً على سير العمل.وهذه المشاكل المذكورة عادة تكون غير مقبولة ويجب حلّها بأقصى سرعة.

إذا تم ضبط الأداء في خادم SQL Server ، وجب عليك مراقبته بشكل مستمر، وذلك لأن التغير المستمر في البيانات والإعدادات تؤدي إلى حالات تتطلب المزيد من الضبط اليدوي للأداء. وأبسط مثال عليها هي الإحصاءات القديمة – حيث أن جملة استعلام سريعة ، وبدون أي سابق إنذار ، تصبح بطيئة جداً.

لذلك، حتى يتم ضبط الأداء بشكل جيد ، ويبقى أداء خادم SQL Server مثالي ، وجب على مدير قواعد البيانات أن يكون مبادراً ، ولا يغفل عن مراقبة الأداء بشكل مستمر ، ويعرّف بعض الإتجاهات والمعايير المسبقة لأداء خادم SQL ليقيس على أساسها التغيرات ، ويتم إعلامه عن المقاييس التي تكون خارج نطاق السليم ، عندها يقوم بالإجراء اللازم ليعيدها إلى حالة التوازن.

ما هي مقاييس SQL Server التي يجب مراقبتها

إن المقاييس التي يجب عليك مراقبتها تعتمد على أهداف الأداء لديك. ومع ذلك ، فهناك بعض المقاييس الشائعة التي تزودك بمعلومات كافية للاستكشاف المبدأي للمشاكل وحلها. اعتماداً على قيم هذه المقاييس ، يمكنك مراقبة مقاييس أخرى للوقوف على السبب الجذري للمشكلة.

ومن مقاييس الأداء الشائعة التي يجب مراقبتها في خادم SQL Server : الذاكرة ، المعالج ، الحِمل في الشبكة وفي النهاية نشاط قرص التخزين.

إضافة إلى مراقبة متغيرات الأداء في خادم SQL Server ، يفضّل أيضاً مراقبة بعض المتغيرات في قاعدة البيانات وفي نظام تشغيل ويندوز.

ومن أكثر هذه المقاييس شيوعا : processor time, processor queue length, page reads and writes per second, page life expectancy, target and total server memory, buffer cache hit ratio, batch requests, processor utilization, lazy writes, network usage, paging, user connections…. وغيرها الكثير.

يوفر خادم SQL Server أداتين متضمنتين فيه لمراقبة الأداء وهما : Activity Monitor و Data Collector.

مراقب نشاطات الخادم Activity Monitor

يتتبع مراقب نشاطات الخادم فقط أهم مقاييس الأداء في خادم SQL Server. للحصول عليها ، فإنه يقوم بتنفيذ جمل إستعلام على خادم SQL Server كل 10 ثواني. ولا تتم مراقبة الأداء إلا والمراقب مفتوح، مما يجعله حل خفيف ولا يؤثر كثيراً على أداء الخادم.

يتم عرض المقاييس في خمس خانات قابلة للإغلاق : Overview, Processes, Resource Waits, Data File I/O و Recent Expensive Queries .

تظهر صفحة النظرة العامة للأداء Overview نسبة مئوية لوقت المعالج ، عدد المهام في قائمة الإنتظار ، عمليات القراءة والكتابة في قاعدة البيانات في الثانية ، وعدد الطلبات الواردة لقواعد البيانات في الثانية.

وفي الجزء المخصص للعمليات Processes ، تظهر عمليات SQL Server التي يتم تنفيذها حالياً لكل قاعدة بيانات في هذا الخادم. و تشمل المعلومات التي تظهر لكل عملية إسم المستخدم ، البرنامج ، الجهاز المنفذ ،حالة المهمة ،الأمر الذي تقوم به ووقت الإنتظار لهذه المهمة وغير ذلك. ويمكن تصفية وترتيب المعلومات في هذا الجدول حسب القيم في العمود الذي تريده.

تقدم لك قائمة العمليات إمكانية تتبع المشاكل والتعمق فيها لإصلاحها ، وذلك من خلال تتبّع هذه العملية المختارة باستخدام أداة SQL Server Profiler.

يظهر في الجزء المخصص للموارد التي تنتظرها العمليات Resource Waits مجموعة متنوعة من الموارد، من ضمنها : الذاكرة ، الترجمة ، الشبكة وغير ذلك.

ويظهر في هذا الجدول الوقت التي تنتظهره المهمات في SQL Server لموارد النظام ، الوقت الحالي للانتظار ، الوقت التراكمي للانتظار ومتوسط وقت الإنتظار.

وفي الجزء المخصص لملفات البيانات Data File IO ، يتم عرض قائمة بكل ملفات قواعد البيانات في هذا الخادم : MDF، NDF و LDF ، أسماء هذه الملفات وأماكن تواجدها ، آخر نشاطات القراءة والكتابة عليها إضافة إلى وقت الإستجابة لهذه الملفات.

ويظهر في الجدول الأخير المخصص لأبهظ جمل الإستعلام ثمنا Recent Expensive Queries ، جمل الإستعلام التي تم تنفيذها في آخر 30 ثانية والتي استهلكت معظم موارد الخادم من معالج ، ذاكرة ، قرص التخزين و الشبكة. يتيح لك هذا القسم فتح جملة الإستعلام باستخدام SQL Server Management Studio وتتبع مخطط تنفيذها.

كيفية استخدام Activity Monitor

يمكنك فتح مراقب نشاطات الخادم Activity Monitor من خلال أداة SQL Server Management Studio عن طريق النقر بالزر الأيمن للفأرة على إسم الخادم في مستكشف العناصر ، من خلال أيقونة مراقب النشاطات في شريط الأدوات أو باستخدام الإختصار Ctrl+Alt+A من خلال لوحة المفاتيح.

كما هو مبين ، فإن مراقب النشاطات يتتبع فقط مجموعة مهمة محددة مسبقاً من مقاييس الأداء في SQL Server. فلا يمكن إضافة مقاييس جديدة للمراقبة ، ولا تستطيع إزالة المقاييس المحددة مسبقاً.والمراقبة الوحيدة المتاحة هي المراقبة الحالية للأداء، أما للتحليل العميق وضبط الأداء ، فإنك تحتاج إلى مراقبة للأداء باستخدام أداة تحدد فيها المقاييس ، وتكون القيم المعيارية محددة مسبقاً فيها ، مع امكانية تخزين البيانات القديمة.

أداة جامع البيانات Data Collector

جامع البيانات هو ميزة مضمنة في SQL Server Management Studio تستخدم لمراقبة الأداء وضبطه في SQL Server. حيث يقوم بجمع مقاييس الأداء من خادم SQL ، حفظ هذه المقاييس في مخزن محلي للبيانات ، حتى تتمكن من استخدامها للتحليل المستقبلي. تستخدم هذه الميزة كل من تخزين البيانات ، وكيل خادم SQL ، بالإضافة إلى الخدمات المتكاملة SSIS.

على العكس من مراقب النشاطات ، جامع البيانات يسمح بتحديد المقاييس التي تريد مراقبتها، ويتيح لك ثلاثة مجموعات من المقاييس المضمنة فيه ، إضافة إلى أهم وأكثر مقاييس الأداء المستخدمة. لمراقبة مقاييس أداء إضافية ، يمكنك إنشاء جامع بيانات مخصص عن طريق لغة T-SQL أو API.

كيفية استخدام جامع البيانات

تأكد أولاً من أن الخدمات المتكاملة SSIS مثبة على الخادم وأنّ كل من وكيل مهام SQL Server ، مستودع البيانات الإدارية MDW وجامع البيانات تم تفعيلها مسبقاً.

  1. من خلال متصفح العناصر في أداة SQL Server Management Studio اذهب إلى قسم Management.
  2. في سياق جامع البيانات Data Collection ، انقر بالزر الأيمن عليها واختر Configure Management Data Warehouse
  3. اختر Set up data collection لتبدأ بالإعدادات.

  4. انقر التالي Next

  5. حدد اسم خادم SQL وقاعدة البيانات التي سوف تخزن فيها البيانات الإدارية ، والمجلد المحلي الذي ستخزن فيه البيانات المجمعة مؤقتاً.

  6. انقر التالي Next ، راجع الإعدادات ثم انقر على Finish للإنهاء.

يقوم جامع البيانات بتزويدك بثلاثة مجموعات مراقبة للأداء محدد مسبقاً ، متاحة في مجلد System Data Collection Sets في مستكشف العناصر ، تحت اسم Data Collection وهي : استخدام قرص التخزين ، إحصاءات حول جمل الإستعلام وأخيراً نشاطات الخادم. لكل منها تقرير معرف مسبقاً.

تقوم المجموعة الأولى Disk Usage من جامع البيانات ، بتجميع المعلومات عن ملفات قواعد البيانات (MDF , NDF و LDF) ، إضافة إلى إحصاءات عن عمليات القراءة والكتابة عليها.

يتواجد تقرير استخدامات قرص التخزين ضمن قائمة تقارير جامع البيانات ، حيث يظهر المساحة المستخدمة لكل ملف من ملفات قواعد البيانات ، واتجاهات توسعها إضافة إلى التوسع اليومي لها.

أما المجموعة الثانية من جامع البيانات ، إحصاءات جمل الإستعلام Query Statistics ، فتقوم بجمع معلومات عن نص جملة الإستعلام ، النشاطات ومخطط التفيذ لأغلى 10 جمل استعلام في الخادم الحالي.

تقوم الفئة الأخير من جامع البيانات ، نشاطات الخادم Server Activity ، بجمع البيانات عن المعالج ، نشاطات قرص التخزين واستخدام الشبكة. يُظهِر التقرير استخدامات كل من المعالج والذاكرة وقرص التخزين والشبكة ، أنواع الإنتظار والتأخير في خادم SQL ، إضافة إلى نشاطات خادم SQL ونظام التشغيل.

كما هو مبين أعلاه ، فإن جامع البيانات يمكن إعداده بسهولة ليبدأ جمع البيانات في الخادم. ويقدم لك ثلاثة مجموعات مقاييس أداء مضمنة مسبقاً وتقارير وافية. لكن لا يوجد معالج لإضافة مقاييس أداء أخرى ، حيث يجب أن تضاف من خلال برامج نصية.

واحدة من مزايا جامع البيانات هو أنه لا داعي لجمع البيانات طوال الوقت، لكن هناك جدولة محددة مسبقاً للجمع ، مما يقلل الحمل الإضافي على الخادم. أما الجانب السلبي لهذه الخاصية هو عدم القدرة على اختيار قاعدة بيانات محددة في خادمك الحالي ليتم مراقبتها ، حيث أن جميع قواعد البيانات سيتم مراقبتها، مما يضيف حمل على الخادم ، إزعاج للبيانات التي يتم جمعها إضافة إلى مساحة إضافية على قرص التخزين.

هذه الخاصية غير متوافرة في كل إصدارات SQL Server قبل 2008 ، إضافة إلى انها متوفرة فقط في إلإصدارات التالية : Enterprise، Standard، Business Intelligence و Web.

على العكس من مراقب النشاطات ، لا يوجد خيار لمراقبة حالة الخادم الحالية ، لكن فقط يتم جمع البيانات وتخزينها لعدد محدد من الأيام. وهذه الميزة تقدم مجموعة أساسية من مقاييس الأداء فقط ، وتحتاج إلى معرفة وافية بالبرمجة لإضافة مقاييس أخرى للمراقبة.

مراقبة الأداء باستخدام ApexSQL Monitor

يقوم برنامج ApexSQL Monitor ، برنامج مراقبة الأداء في SQL Server ، بمراقبة أداء كل من النظام ، خادم SQL وقواعد البيانات بشكل حالي باستخدام أكثر من 30 مقياس مراقبة. حيث يتيح للمستخدمين اختيار خادم SQL وقاعدة البيانات التي تريد مراقبتها، ومن ثم اختيار المقاييس التي تريد مراقبتها لكل قاعدة بيانات ، لكل خادم SQL لكل جهاز ، وتحديد المقاييس والقيم القياسية والتي اذا تم تخطّيها ، سوف يتم إرسال تنبيه.

تظهر مقاييس الأداء ومعلومات جمل الإستعلام التي تم جمعها في رسوم بيانية في ستة تبويبات رئيسية، ولكن يمكن أيضاً إنشاء تبويبات مخصصة من قبل المستخدم.

في تبويب نظرة عامة Overview ، يمكنك رؤية أهم المعلومات والإعدادات في SQL Server ، إضافة إلى إحصائيات الإنتظار ومقاييس الأداء مثل (buffer cache hit ratio ، full scans per second ، lazy writes per second و lock requests per second).

Overview tab - image 1

Overview tab - image 2

يعرض تبويب أداء النظام System performance مقاييس أداء نظام التشغيل ، ومنها : processor queue length/utilization of processor time ، , average bytes per write/read ، network utilization ، paging file usage ، page input per second ، pages per second ، average and current disk queue length ، average read/write time ، , disk writes/reads per second و free space and available memory in MB.

System performance tab

يمكنك من خلال تبويب أداء الخادم SQL Server performance ايجاد المقاييس المرتبطة بأداء خادم SQL مثل: number of batch requests ، compilations and re-compilations per second ، longest running transaction ، full scans ، index searches، forwarded records ، free list stalls ، lazy writes ، lock requests ، deadlocks per second ، user connections ، target and total server memory in KBs ، memory grants pending ، buffer cache hit ratio ، page life expectancy ، page reads/writes و page splits per second.

SQL Server performance tab

يُظهِر تبويب أداء قاعدة البيانات Database performance معلومات عن حجم ملفات قواعد البيانات وسجلات الإجراءات ، إضافة إلى عدد الإجراءات في الثانية .

Database Performance tab

ومن خلال تبويب الإنتظار لجمل الإستعلام Query waits ، يمكن رؤية جمل الإستعلام التي لها أعلى وقت انتظار خلال الفترة المختارة ، ويتضمن هذا رسم بياني للجمل وأوقات الإنتظار. إضافة إلى ذلك ، يمكنك رؤية تفاصيل الجمل متفرقة أو مجتمعة مع مخططات التنفيذ لهذه الجمل ، بهدف مراجعتها وتحليلها.

Query waits tab

أما تبويب أداء جمل الإستعلام Query performance ، فإنه يظهر أبهظ جمل الإستعلام ثمناً من ناحية استهلاك موارد الخادم خلال الفترة المحددة ، إضافة إلى عرض بياني لأبطأ جمل الإستعلام مرتّبة حسب وقت المعالجة ، مدة التنفيذ وعمليات القراءة والكتابة بالثانية. كما يمكن للمستخدم تحديد عدد الجمل التي يريد عرضها.

Query performancetab

والقسم الأخير هي التبويبات المخصصة Custom tabs ، والتي يمكن تشكيلها حسب متطلبات كل مستخدم. حيث يمكن للمستخدم إعداد وعرض التبويب الخاص به ، حيث يعرض أهم المقاييس بالنسبة له بسرعة وسهولة عند تشغيل برنامج ApexSQL Monitor. يمكن إنشاء هذه التبويبات المخصصة بحيث تظهر ملخص سريع لأهم معايير قياس أداء معلومات النظام ، خادم SQL وقواعد البيانات ، مرتبة بطريقة تعطي صورة عامة وسهلة القراءة عن المشاكل الكبيرة.

كيفية استخدام برنامج ApexSQL Monitor

  1. إبدأ برنامج ApexSQL Monitor.
  2. انقر على زر Add من القائمة لإضافة خادم SQL Server الذي تريد مراقبته. ستجد على اليسار قائمة بكل خوادم SQL Server التي تتم مراقبتها.
  3. لإعدادات مقاييس مراقبة الأداء ، انقر على Metrics من القائمة. يمكنك اختيار المقاييس على مستوى الجهاز ، قاعدة البيانات ، الخادم و الكمبيوتر.

    Metrics tab

  4. من خلال نفس الشاشة ، يمكنك إعداد التنبيهات. لتفعيل الإنذار ، اختر المربع بجانب Alerting وأدخل القيم المرجعية ، أو استخدم قيم معيارية محددة Baselinesاذا كان قد تم حسابها مسبقاً.

إذا كان هناك تنبيهات، فسيتم اظهارها من خلال أيقونة بجانب إسم الخادم في قائمة الخوادم المراقبة. لرؤية قائمة وتفاصيل التنبيهات الفعالة ، انقر على Alerts من القائمة. سيظهر لك مقياس الأداء ، وقت البدء والانتهاء وعدد مرات الحدوث لكل درجة خطورة فيها.

Alerts information

يعتبر برنامج ApexSQL Monitor حل مثالي لمراقبة قواعد البيانات ، سهل التشغيل والإعداد ، دون الحاجة إلى معرفة وخبرة بالبرمجة. يوفر هذا البرنامج طريقة لطيفة للمراقبة ، على العكس من حلول مراقبة SQL العادية ، فإنه يسمح لك اختيار مقاييس المراقبة ، إضافة إلى قاعدة البيانات التي تريد مراقبتها. ويقدم تنوع كبير من المقاييس التي يمكن مراقبتها على مستوى الجهاز ، قاعدة البيانات ، خادم SQL و نظام التشغيل. مما يقلل من الحِمل الإضافي الناتج عن عملية المراقبة.

على النقيض من مراقب النشاطات وجامع البيانات، فإن برنامج ApexSQL Monitor يعرض مقاييس الأداء في الوقت الحالي في رسوم بيانية ، يخزن بيانات المراقبة في مستودع مركزي للبيانات ويعرض البيانات القديمة في رسوم بيانية. كما يمكنك إعداد تنبيهات بخطوات بسيطة وسهلة، باستخدام قيم مرجعية يمكنك تحديدها بمستويات خطورة منخفضة ، متوسطة وعالية.

موارد مفيدة:
Activity Monitor
Data Collection
Monitoring SQL Server Performance

مترجم Ahmad Yaseen

January 19, 2016