مشاركة عبر

أنواع يمكن اكتشافها من ازدحام أداء الاستعلام في Azure SQL Database وAzure SQL Managed Instance

  • مقالة

ينطبق على:قاعدة بيانات Azure SQLمثيل Azure SQL المدار

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

يمكنك استخدام Intelligent Insights أو SQL Server DMVs لاكتشاف هذه الأنواع من ازدحام الأداء.

Workload states

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

  • الأقفال (الحظر)
  • الإدخال/الإخراج
  • المنافسة على الاتصال ذات الصلة باستخدام tempdb
  • انتظار تخصيص الذاكرة

مشاكل التحويل البرمجي الناتجة عن خطة استعلام دون المستوى الأمثل

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

حل الاستعلامات ذات خطط تنفيذ الاستعلام دون المستوى الأمثل

تناقش المقاطع التالية كيفية حل الاستعلامات ذات خطة تنفيذ الاستعلام دون المستوى الأمثل.

الاستعلامات التي تحتوي على مشكلات الخطة الحساسة للمعلمة (PSP)

تحدث مشكلة الخطة الحساسة للمعلمة (PSP) عندما يقوم محسن الاستعلام بإنشاء خطة تنفيذ استعلام مثالية فقط لقيمة معلمة معينة (أو مجموعة من القيم) وتكون الخطة المخزنة مؤقتاً ليست الأمثل لقيم المعلمات التي يتم استخدامها في عمليات التنفيذ المتتالية. الخطط التي ليست مثالية يمكن أن تسبب مشاكل في أداء الاستعلام ومن ثم انخفاض إجمالي معدل نقل حمل العمل.

لمزيد من المعلومات حول اكتشاف المعلمة ومعالجة الاستعلام، راجع دليل هندسة معالجة الاستعلام.

يمكن أن تخفف العديد من الحلول البديلة من مشكلات PSP. لكل حل بديل مفاضلات وسلبيات مرتبطة:

  • استخدم تلميح الاستعلام RECOMPILE في تنفيذ كل استعلام. هذا الحل البديل يداول وقت التحويل وزيادة وحدة المعالجة المركزية لتحسين جودة الخطة. RECOMPILEغالباً ما يكون الخيار غير ممكن لأحمال العمل التي تتطلب معدل نقل عالٍ.
  • استخدم تلميح الاستعلام OPTION (OPTIMIZE FOR…) لتجاوز قيمة المعلمة الفعلية بقيمة معلمة نموذجية تنتج خطة جيدة بما يكفي لمعظم احتمالات قيمة المعلمة. يتطلب هذا الخيار فهم جيد لقيم المعلمة المثلى وخصائص الخطة المرتبطة.
  • استخدم تلميح الاستعلام OPTION (Optimize for UNKNOWN) لتجاوز قيمة المعلمة الفعلية واستخدام متوسط خط متجه الكثافة بدلاً من ذلك. كما يمكنك القيام بذلك عن طريق التقاط قيم المعلمة الواردة في المتغيرات المحلية ثم استخدام المتغيرات المحلية داخل الفرضيات بدلاً من استخدام المعلمات نفسها. بالنسبة لهذا الإصلاح، يجب أن يكون متوسط الكثافة جيد بما فيه الكفاية.
  • تعطيل اكتشاف المعلمة بالكامل باستخدام تلميح الاستعلام DISABLE_PARAMETER_SNIFFING.
  • استخدم تلميح الاستعلام KEEPFIXEDPLAN لمنع عمليات إعادة التحويل البرمجي في ذاكرة التخزين المؤقت. يفترض هذا الحل البديل أن خطة العامة الجيدة بما يكفي هي الموجودة في ذاكرة التخزين المؤقت مسبقاً. كما يمكنك تعطيل تحديثات الإحصائيات التلقائية لتقليل فرص التخلص من الخطة الجيدة وتحويل خطة سيئة جديدة برمجياً.
  • افرض الخطة باستخدام تلميح استعلام "USE PLAN" بشكل صريح عن طريق إعادة كتابة الاستعلام وإضافة التلميح في نص الاستعلام. أو تعيين خطة معينة باستخدام Query Store أو عن طريق تمكين "الضبط التلقائي".
  • استبدال إجراء واحد بمجموعة متداخلة من الإجراءات التي يمكن استخدامها استناداً إلى المنطق الشرطي وقيم المعلمة المرتبطة.
  • إنشاء بدائل تنفيذ سلسلة ديناميكية لتعريف إجراء ثابت.

لمزيد من المعلومات حول حل مشكلات PSP، راجع منشورات المدونة التالية:

نشاط محول برمجياً ناجم عن معلمات غير صحيحة

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

فيما يلي مثال للاستعلام المُعلم جزئياً:

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

في هذا المثال، t1.c1 يأخذ @p1، ولكن t2.c2 يستمر في اتخاذ المعرف الفريد كقيمة حرفية. في هذه الحالة، إذا قمت بتغيير قيمة c2، يُعامل الاستعلام معاملة استعلام مختلف، وسيحدث تحويل برمجي جديد. لتقليل التحويل البرمجي في هذا المثال، يمكنك أيضاً تعليم GUID.

يظهر الاستعلام التالي عدد الاستعلامات حسب تجزئة الاستعلام لتحديد ما إذا كان استعلام معلم بشكل صحيح:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

العوامل التي تؤثر على تغييرات خطة الاستعلام

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

  • تتم الإشارة إلى التغييرات في المخطط بواسطة الاستعلام
  • تتم الإشارة إلى تغييرات البيانات إلى الجداول بواسطة الاستعلام
  • تم تغيير خيارات سياق الاستعلام

قد يتم إخراج خطة محولة برمجياً من ذاكرة التخزين المؤقت لأسباب مختلفة، مثل:

  • إعادة تشغيل المثيل
  • تغييرات تكوين نطاق قاعدة البيانات
  • ضغط الذاكرة
  • طلبات صريحة لمسح ذاكرة التخزين المؤقت

إذا كنت تستخدم تلميح RECOMPILE، فلن يتم تخزين الخطة مؤقتاً.

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

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

  • اختلافات موارد الخادم: عندما تختلف خطة في نظام عن الخطة في نظام آخر، يمكن أن يؤثر توفر الموارد، مثل عدد المعالجات المتوفرة، على الخطة التي يتم إنشاؤها. على سبيل المثال، إذا كان نظام واحد يحتوي على العديد من المعالجات، فقد يتم اختيار خطة متوازية. لمزيد من المعلومات حول التوازي في Azure SQL Database، راجع تكوين أقصى درجة من التوازي (MAXDOP) في Azure SQL Database.

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

  • مستوى توافق قاعدة بيانات متغير أو إصدار مقدر علاقة أساسية: يمكن أن تؤدي التغييرات في مستوى توافق قاعدة البيانات إلى تمكين إستراتيجيات وميزات جديدة ينتج عنها خطة تنفيذ استعلام مختلفة. خارج مستوى توافق قاعدة البيانات، يمكن أن تؤثر علامة تتبع معطلة أو ممكنة 4199 أو حالة تغيير تكوين نطاق قاعدة البيانات QUERY_OPTIMIZER_HOTFIXES أيضاً على خيارات خطة تنفيذ الاستعلام في وقت التحويل البرمجي. تتبع علامات 9481 (فرض القديم CE) و2312 (فرض الافتراضي CE) يؤثر أيضاً على الخطة.

مشكلات حدود الموارد

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

إذا قمت بتعريف المشكلة كمورد غير كافٍ، يمكنك ترقية الموارد لزيادة سعة قاعدة البيانات لاستيعاب متطلبات CPU. لمزيد من المعلومات، راجع موارد مقياس تغيير سعة بيانات مفردة في Azure SQL Database وموارد تغيير سعة التجمع المرنة في Azure SQL Database. للحصول على معلومات حول تغيير سعة مثيل مدار، راجع حدود موارد مستوى الخدمة

مشكلات الأداء الناجمة عن زيادة حجم حمل العمل

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

  • هل تتسبب الاستعلامات الواردة من التطبيق في مشكلة الاستهلاك العالي للمعالج CPU؟

  • بالنسبة إلى أهم الاستعلامات المستهلكة لمعالج CPU التي يمكنك تحديدها:

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

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

ليس من السهل دائماً تحديد تغيير حجم حمل العمل الذي يؤدي إلى حدوث مشكلة معالج CPU. خذ بعين الاعتبار هذه العوامل:

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

  • ظهور استعلام جديد: قد تؤدي أحد التطبيقات إلى ظهور مجموعة جديدة من الاستعلامات في أوقات مختلفة.

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

استخدم Intelligent Insights للكشف عن زيادات حمل العمل وتراجعات الخطة.

  • التوازي: يمكن أن يؤدي التوازي المفرط إلى تفاقم أداء حمل العمل المتزامن الآخر عن طريق تقليل الاستعلامات الأخرى من موارد مؤشر ترابط CPU والعامل. لمزيد من المعلومات حول التوازي في Azure SQL Database، راجع تكوين أقصى درجة من التوازي (MAXDOP) في Azure SQL Database.

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

  • الحظر:

    قد يقفل استعلام واحد العناصر الموجودة في قاعدة البيانات بينما يحاول الآخرون الوصول إلى نفس العناصر. يمكنك تحديد استعلامات الحظر باستخدام طرق عرض الإدارة الديناميكية أو Intelligent Insights. لمزيد من المعلومات، راجع التعرف على مشكلات حظر Azure SQL وحلها.

  • مشكلات IO

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

  • مشكلات Tempdb

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

  • المشكلات المتعلقة بالذاكرة

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

طرق لإظهار فئات الانتظار العليا

تستخدم هذه الطرق عادة لإظهار الفئات العليا من أنواع الانتظار:

  • استخدام Intelligent Insights لتحديد الاستعلامات المتدهورة الأداء بسبب زيادة مرات الانتظار
  • استخدم مخزن الاستعلام للبحث عن إحصائيات الانتظار لكل استعلام بمرور الوقت. في مخزن الاستعلام، يتم دمج أنواع الانتظار في فئات الانتظار. يمكنك العثور على تعيين فئات الانتظار إلى أنواع الانتظار في sys.query_store_wait_stats.
  • استخدم sys.dm_db_wait_stats لإرجاع معلومات حول جميع الانتظارات التي تواجهها مؤشرات الترابط التي تم تنفيذها أثناء عملية الاستعلام. يمكنك استخدام طريقة العرض المجمعة هذه لتشخيص مشاكل الأداء مع Azure SQL Database وكذلك مع استعلامات ودفعات معينة. يمكن أن تنتظر الاستعلامات على الموارد أو انتظارات قائمة الانتظار أو الانتظارات الخارجية.
  • استخدم sys.dm_os_waiting_tasks لإرجاع معلومات حول قائمة انتظار المهام التي تنتظر بعض الموارد.

في سيناريوهات الاستهلاك العالي للمعالج CPU، قد لا تعكس إحصائيات مخزن الاستعلام والانتظارات استخدام وحدة معالج CPU في الحالات التالية:

  • إذا استمر تنفيذ الاستعلامات ذات الاستهلاك العالي لمعالج CPU.
  • إذا كانت الاستعلامات ذات الاستهلاك العالي لمعالج CPU قيد التشغيل عند حدوث تجاوز الفشل.

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

تلميح

أدوات إضافية:

الخطوات التالية