شرح خطط الاستعلام المقدرة والفعلية

مكتمل

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

كما هو موضح، يمكنك إنشاء خطة مقدرة في SSMS عن طريق تحديد الزر المشار إليه بواسطة مربع خطة الاستعلام المقدر (أو باستخدام أمر لوحة المفاتيح Control+L). يمكنك إنشاء الخطة الفعلية عن طريق تحديد الأيقونة المعروضة (أو باستخدام أمر لوحة المفاتيح Control+M)، ثم تنفيذ الاستعلام. يعمل زرا الخيار بشكل مختلف. يستجيب الزر تضمين خطة الاستعلام المقدرة على الفور إلى أي استعلام مميز (أو مساحة العمل بأكملها، إذا لم يتم تمييز أي شيء)، بينما يتطلب الزر تضمين خطة الاستعلام الفعلي تنفيذ الاستعلام.

توجد تكاليف إضافية لتنفيذ استعلام وإنشاء خطة تنفيذ تقديرية، لذلك يجب عرض خطط التنفيذ بعناية في بيئة التشغيل.

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

قراءة خطة الاستعلام

تظهر خطط التنفيذ المهام التي ينفذها محرك قاعدة البيانات في أثناء استرداد البيانات المطلوبة لتلبية استعلام. لنتعمق في الخطة.

SELECT [stockItemName]
 ,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
 ,[QuantityonHand]

FROM [Warehouse].[StockItems] s
 JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;

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

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

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

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

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

جمع معلومات استعلامات خفيفة

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

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

إذا لم يتم تمكين جمع المعلومات الخفيف بشكل عام، يمكنك استخدام USE HINT تلميح الاستعلام مع QUERY_PLAN_PROFILE لتمكين جمع المعلومات الخفيف على مستوى الاستعلام. عند اكتمال تنفيذ استعلام بهذا التلميح، يتم إنشاء حدث موسع query_plan_profile ، ما يوفر خطة تنفيذ فعلية. فيما يلي مثال على استعلام بهذا التلميح:

SELECT [stockItemName]
 ,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
 ,[ QuantityonHand]
FROM [Warehouse].[StockItems] s
    JOIN [Warehouse].[StockItems] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox 
OPTION(USE HINT ('QUERY_PLAN_PROFILE'));

آخر إحصائيات خطط الاستعلام

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

أيضا، هناك دالة إدارة ديناميكية تسمى sys.dm_exec_query_plan_stats، والتي يمكن أن تظهر لك آخر خطة تنفيذ استعلام فعلية معروفة لمقبض خطة معين. لمشاهدة آخر خطة فعلية معروفة للاستعلام من خلال الدالة، يمكنك تمكين إشارة تتبع 2451 على نطاق الخادم. بدلاً من ذلك، يمكنك تمكين هذه الوظيفة باستخدام خيار تكوين قاعدة بيانات محددة النطاق يسمى LAST_QUERY_PLAN_STATS.

يمكنك دمج هذه الدالة مع كائنات أخرى للحصول على خطة التنفيذ الأخيرة لجميع الاستعلامات المخزنة مؤقتا:

SELECT *
FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps; 
GO

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

كما ترى من خصائص Columnstore Index Scan في الصورة التالية، تحتوي الخطة التي تم استردادها من ذاكرة التخزين المؤقت على العدد الفعلي للصفوف التي تم استردادها في الاستعلام.