تقييم أداء الاستعلامات باستخدام خطط التنفيذ وإدارة المركبات

مكتمل

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

اقرأ خطط التنفيذ

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

هناك نوعان من خطط التنفيذ:

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

لعرض خطة تقديرية، قم بتشغيل SET SHOWPLAN_XML ON خطة العرض قبل الاستعلام أو اختر عرض خطة التنفيذ المقدرة في SQL Server Management Studio (SSMS). لالتقاط خطة فعلية، قم بتشغيل SET STATISTICS XML ON أو اختيار تضمين خطة التنفيذ الفعلية في SSMS قبل تنفيذ الاستعلام.

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

تحديد المشكلات الشائعة في خطط التنفيذ

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

أنواع المشغلين تخبرك كيف يصل المحرك إلى البيانات. هناك العديد من أنواع المشغلات، كل منها يمثل طريقة مختلفة لاسترجاع أو معالجة البيانات. على سبيل المثال، يمثل عامل البحث عن الفهرس طريقة عالية الكفاءة تستهدف صفوفا محددة باستخدام مفاتيح الفهرس. أما مشغل مسح الجدول أو المسح الفهرسي ، فيمثل طريقة أقل كفاءة تقرأ كل صف. إذا رأيت مسحا ضوئيا على طاولة كبيرة، فمن المحتمل أنك بحاجة إلى فهرس. على سبيل المثال، إذا استعلام تطبيق التجارة الإلكترونية عن الطلبات حسب التاريخ وظهرت الخطة مسح مؤشر مجمع على Orders الجدول، فإن إضافة فهرس غير مجمع على العمود OrderDate قد يحول ذلك المسح إلى بحث (seek). لاحظ أن ليس كل الفحوصات سيئة. إذا كان الجدول صغيرا، أو إذا كانت حالة البحث تعود بمعظم الصفوف في الجدول، فقد يكون المسح هو الطريقة الأكثر كفاءة للوصول. دائما ما تأخذ في الاعتبار سياق الاستعلام وحجم البيانات. اعرف بياناتك واستخدم خطط التنفيذ للتأكد مما إذا كانت طريقة الوصول منطقية.

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

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

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

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

تظهر التحذيرات كمثلث أصفر مع علامة تعجب (⚠) على المشغل المتأثر. كل تحذير يشير إلى فرصة تحسين. التحذيرات الشائعة تشمل:

• الإحصائيات المفقودة: لم يتمكن المحسن من إيجاد إحصائيات لعمود، لذا خمن بعدد الصفوف بدلا من استخدام توزيع البيانات الفعلي. لحل هذه المشكلة، أنشئ إحصائيات على الأعمدة المستخدمة في استفساراتك أو حدث الإحصائيات الحالية إذا كانت قديمة.
• منح الذاكرة الزائدة: طلب الاستعلام ذاكرة أكثر مما يحتاج، مما أهدر الموارد التي يمكن للاستفسارات الأخرى استخدامها. غالبا ما تحدث هذه المشكلة عندما يبالغ المحسن في تقدير عدد الصفوف. تحديث الإحصائيات أو إعادة كتابة الاستعلام لتصفية الصفوف في وقت أبكر يمكن أن يساعد في تقليل منح الذاكرة.
• مسند عدم الاتحاد: يتم ربط جدولين بدون شرط صحيح، مما ينتج حاصل ضرب ديكارتي يعطي كل تركيبة صفوف ممكنة. تحقق من استفسارك بحثا عن فقرة مفقودة أو غير صحيحة ON .
• التحويل الضمني: عدم تطابق نوع البيانات يجبر المحرك على تحويل القيم أثناء وقت التشغيل، مما قد يحول بحث الفهرس إلى مسح. على سبيل المثال، إذا WHERE قارن جملة معاملة بعمود nvarcharvarchar ، يقوم المحرك بتحويل كل صف في العمود إلى nvarchar قبل المقارنة. لإصلاح التحويلات الضمنية، طابق أنواع البيانات في معلمات الاستعلام مع تعريفات الأعمدة.
• الفرز أو تسرب التجزئة: عملية الفرز أو التجزئة نفدت من ذاكرتها الممنوحة وأرسبت نتائج وسيطة إلى tempdb. هذه العمليات هي ثاني أكثر المحركات شيوعا لارتفاع وحدة المعالجة المركزية بعد المسح. إذا رأيت تحذير من الانسكاب، فمن المحتمل أن المحسن قلل من تقدير عدد الصفوف وطلب ذاكرة قليلة جدا. الجري UPDATE STATISTICS لتحديث إحصائيات الجدول أو إعادة كتابة الاستعلام لتقليل عدد الصفوف قبل الفرز غالبا ما يؤدي إلى إلغاء التسرب.

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

استعلام DMVs عن بيانات أداء وقت التشغيل

تعرض DMVs بيانات الأداء المتراكمة في الوقت الحقيقي والمتراكمة من محرك قاعدة البيانات. يتطلب VIEW DATABASE STATE Azure SQL Database إذنا للاستعلام عنها. بينما تظهر خطط التنفيذ كيف يعمل استعلام واحد، تظهر لك إدارة المركبات ما يحدث عبر جميع الاستعلامات، مما يساعدك على العثور على أغلى الاستفسارات أولا.

ابحث عن أغلى الاستعلامات

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

sys.dm_exec_query_stats يعرض إحصائيات الأداء المجمع لخطط الاستعلام المخزنة مؤقتا. قم بإضمامه لرؤية sys.dm_exec_sql_text نص الاستعلام واسترجاع sys.dm_exec_query_plan خطة التنفيذ.

الاستفسار التالي يحدد أفضل 10 استعلامات حسب متوسط وقت وحدة المعالجة المركزية:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_cpu_time DESC;

يساعدك هذا السكريبت على تحديد الاستفسارات التي تستحق انتباهك. الارتفاع avg_logical_reads بالنسبة لحجم مجموعة النتائج غالبا ما يشير إلى فهارس مفقودة أو خطط غير فعالة. ومع ذلك، كن حذرا عند تفسير هذه النتائج. الاستعلام الذي يبلغ متوسط وقت المعالج المتوسط ويعمل مرة واحدة في اليوم قد يكون أقل أهمية من استعلام متوسط يعمل آلاف المرات في الساعة. دائما ضع في اعتبارك متوسط التكلفة وعدد التنفيذ عند تحديد الأولويات. يمكنك أيضا الطلب حسب avg_logical_reads الاستفسارات التي تعتمد بشكل كبير على إدخال/إخراج، وغالبا ما يشير ذلك إلى فهارس مفقودة أو طرق وصول غير فعالة.

تحقق من الاستعلامات التي يتم تنفيذها حاليا

بينما يعرض الاستعلام السابق أغلى استعلامات تاريخية في ذاكرة التخزين المؤقت، يعطيك sys.dm_exec_requests لمحة عن كل طلب يعمل حاليا. يتضمن أعمدة لوقت وحدة المعالجة المركزية، والقراءة، والكتابة، ونوع الانتظار، ووقت الانتظار، ومعرف الجلسة المحجب (MEASURE). استخدم هذا العرض لاكتشاف الاستعلامات النشطة التي تستهلك الكثير من الموارد أو عالقة في انتظار الأقفال. هذا الرأي هو أحد أهم إدارة المركبات لمراقبة الأداء في الوقت الحقيقي واستكشاف المشاكل.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

يقوم هذا الاستعلام بتصفية جلسات النظام (معرفات الجلسات من 1 إلى 50) والأوامر حسب وقت وحدة المعالجة المركزية. يمكنك أيضا الطلب حسب logical_reads الرسائل للعثور على استفسارات ثقيلة الإدخال/الإخراج. wait_type أعمدة and wait_time تساعدك في تحديد ما إذا كان الاستعلام ينتظر الأقفال أو الإدخال/الإخراج أو موارد أخرى.

اكتشف الفهارس المفقودة

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

SELECT
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans) AS improvement_measure
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

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

‏‫ملاحظة‬

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

مراقبة الجلسات النشطة ومهام الانتظار

sys.dm_exec_sessions يعطيك معلومات عن جميع الجلسات المصادقة، بما في ذلك وقت تسجيل الدخول، اسم المضيف، اسم البرنامج، وعدد المعالج والقراءات التراكمية. اجمع ذلك مع sys.dm_os_waiting_tasks المهام التي تنتظر وما هي الموارد التي ينتظرونها. تصبح هذه الآراء ضرورية عندما تحدد الحظر والنزاع على الموارد في وحدة لاحقة.

وضع كل شيء معا

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

النقاط الموجزة الأساسية

تكشف خطط التنفيذ عن استراتيجية المحسن للاستعلام، وتشمل الخطط الفعلية مقاييس وقت التشغيل التي تكشف عن فروقات بين عدد الصفوف المقدر والفعلي. عند قراءة خطة، ركز على أنواع المشغلين (البحث مقابل المسح)، تقديرات عدد الصفوف، التحذيرات، ومشغلي البحث عن المفاتيح. توفر إدارة المركبات بيانات أداء على مستوى النظام: تستخدم sys.dm_exec_query_stats للعثور على أغلى الاستعلامات للاستعلامات sys.dm_exec_requests الجارية حاليا، وDMVs الفهارس المفقودة لفرص التحسين. ابدأ بشكل عام مع إدارة المركبات لتحديد أكبر المشاكل، ثم تعمق في خطط التنفيذ الفردية لفهم السبب.