فهم خطط الاستعلام
يعد فهم كيفية عمل محسني قواعد البيانات أمرا ضروريا قبل التعمق في تفاصيل خطة التنفيذ. يستخدم SQL Server محسن استعلام يستند إلى التكلفة، والذي يحسب تكلفة خطط محتملة متعددة استنادا إلى الإحصائيات التي يحتوي عليها على الأعمدة المستخدمة والفهارس المحتملة لكل عملية في خطة الاستعلام. تساعد هذه المعلومات المحسن على تحديد التكلفة الإجمالية لكل خطة. يمكن أن تحتوي الاستعلامات المعقدة على الآلاف من خطط التنفيذ المحتملة، ولكن المحسن لا يقيم كل خطة واحدة. بدلا من ذلك، يستخدم الأساليب الإرشادية لتحديد الخطط التي من المحتمل أن تعمل بشكل جيد ثم تحدد أقل خطة تكلفة من تلك التي تم تقييمها.
نظرا لأن محسن الاستعلام يستند إلى التكلفة، فمن الضروري تزويده بمدخلات دقيقة لاتخاذ القرار. يعتمد SQL Server على الإحصائيات لتتبع توزيع البيانات في الأعمدة والفهارس، ويجب تحديث هذه الإحصائيات لتجنب إنشاء خطط تنفيذ دون المستوى الأمثل. على الرغم من أن SQL Server يقوم تلقائيا بتحديث إحصائياته مع تغير البيانات في جدول، فقد تكون التحديثات الأكثر تكرارا ضرورية لتغيير البيانات بسرعة. يأخذ المحسن في الاعتبار العديد من العوامل عند إنشاء خطة، بما في ذلك مستوى توافق قاعدة البيانات وتقديرات الصفوف استنادا إلى الإحصائيات والفهارس المتاحة.
عندما يقدم مستخدم استعلامًا إلى محرك قاعدة البيانات، تحدث العملية التالية:
- يتم تحليل الاستعلام لبناء الجملة المناسب، وإذا كان صحيحا، يتم إنشاء شجرة تحليل كائنات قاعدة البيانات.
- ثم يتم إدخال شجرة التحليل إلى مكون محرك قاعدة بيانات يسمى Algebrizer للربط. تتحقق هذه الخطوة من وجود أعمدة وعناصر في الاستعلام وتحدد أنواع البيانات التي تتم معالجتها. الإخراج هو شجرة معالج استعلام، والتي تعمل كمدخل للخطوة التالية.
- يعد تحسين الاستعلام كثيف الاستخدام لوحدة المعالجة المركزية، لذلك يقوم محرك قاعدة البيانات بذاكرة التخزين المؤقت لخطط التنفيذ في منطقة ذاكرة خاصة تسمى ذاكرة التخزين المؤقت للخطة. إذا كانت هناك خطة للاستعلام موجودة بالفعل، يتم استردادها من ذاكرة التخزين المؤقت. يحتوي كل استعلام في ذاكرة التخزين المؤقت على قيمة تجزئة تم إنشاؤها استنادا إلى T-SQL في الاستعلام، والمعروفة باسم query_hash. ينشئ المحرك query_hash للاستعلام الحالي ويتحقق من وجود تطابقات في ذاكرة التخزين المؤقت للخطة.
- إذا لم تكن هناك خطة، يستخدم محسن الاستعلامات المحسن المستند إلى التكلفة لإنشاء العديد من خيارات خطة التنفيذ استنادا إلى إحصائيات حول الأعمدة والجداول والفهارس المستخدمة في الاستعلام. الإخراج هو خطة تنفيذ استعلام.
- يتم تنفيذ الاستعلام باستخدام خطة تنفيذ من ذاكرة التخزين المؤقت للخطة أو خطة جديدة تم إنشاؤها في الخطوة السابقة. الإخراج هو نتائج الاستعلام الخاص بك.
إشعار
لمعرفة المزيد حول كيفية عمل معالج الاستعلام، راجع دليل هندسة معالجة الاستعلام
هيا بنا نلقي نظرة على مثال. خذ بعين الاعتبار الاستعلام التالي:
SELECT orderdate,
AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;
في هذا المثال، يتحقق SQL Server من وجود أعمدة OrderDate و ShipDate و SalesAmount في جدول FactResellerSales . إذا كانت هذه الأعمدة موجودة، ينشئ SQL Server قيمة تجزئة للاستعلام ويفحص ذاكرة التخزين المؤقت للخطة لقيمة تجزئة مطابقة. إذا تم العثور على قيمة تجزئة مطابقة، يحاول المحرك إعادة استخدام الخطة. إذا لم يتم العثور على قيمة تجزئة مطابقة، يقوم SQL Server بفحص الإحصائيات المتوفرة في عمودي OrderDate و ShipDate .
WHERE تعرف العبارة التي تشير إلى عمود ShipDate باسم دالة التقييم في هذا الاستعلام. إذا كان هناك فهرس غير متفاوت المسافات يتضمن عمود ShipDate ، فمن المحتمل أن يقوم SQL Server بتضمينه في الخطة، شريطة أن تكون التكاليف أقل من استرداد البيانات من الفهرس المجمع. ثم يختار المحسن أقل خطة تكلفة من الخيارات المتاحة وينفذ الاستعلام.
تجمع خطط الاستعلام بين سلسلة من عوامل التشغيل الارتباطية لاسترداد البيانات والتقاط المعلومات مثل أعداد الصفوف المقدرة. عنصر آخر من خطة التنفيذ هو الذاكرة المطلوبة لعمليات مثل الانضمام إلى البيانات أو فرزها، والمعروفة باسم منحة الذاكرة. تسلط منحة الذاكرة الضوء على أهمية الإحصائيات. إذا قدر SQL Server أن عامل التشغيل يرجع 10,000,000 صف عندما يقوم بإرجاع 100 بالفعل، يتم تخصيص منحة ذاكرة أكبر للاستعلام. يمكن أن يتسبب منح ذاكرة كبير بشكل مفرط في حدوث مشكلتين. أولا، قد يواجه الاستعلام انتظارا RESOURCE_SEMAPHORE ، مما يشير إلى أنه ينتظر SQL Server لتخصيص كمية كبيرة من الذاكرة. وقت الانتظار الافتراضي في SQL Server يساوي 25 ضعف تكلفة الاستعلام (بالثواني) قبل التنفيذ، إذ قد يصل إلى 24 ساعة. ثانيا، إذا لم تتوفر ذاكرة كافية عند تنفيذ الاستعلام، فإنه ينسكب إلى tempdb، وهو أبطأ من التشغيل في الذاكرة.
تقوم خطة التنفيذ أيضا بتخزين بيانات التعريف الأخرى حول الاستعلام، مثل مستوى توافق قاعدة البيانات ودرجة التوازي والمعلمات المقدمة إذا تم تحديد معلمات الاستعلام.
يمكن عرض خطط الاستعلام إما بتمثيل رسومي أو بتنسيق يستند إلى نص. يتم استدعاء الخيارات المستندة إلى النص باستخدام أوامر SET وتنطبق فقط على الاتصال الحالي. يمكن عرض هذه الخطط في أي مكان يمكنك فيه تشغيل استعلامات T-SQL.
تفضل معظم DBAs الخطط الرسومية لأنها تسمح لك برؤية الخطة ككل، بما في ذلك شكل الخطة. هناك عدة طرق لعرض خطط الاستعلام الرسومية وحفظها. الأداة الأكثر شيوعا لهذا الغرض هي SQL Server Management Studio. بالإضافة إلى ذلك، هناك أدوات تابعة لجهة خارجية تدعم عرض خطط التنفيذ الرسومية.
هناك ثلاثة أنواع مختلفة من خطط التنفيذ.
خطة التنفيذ المقدرة
يتم إنشاء هذا النوع من خطة التنفيذ بواسطة محسن الاستعلام. تستند بيانات التعريف وحجم منحة ذاكرة الاستعلام إلى تقديرات من الإحصائيات الموجودة في قاعدة البيانات في وقت تجميع الاستعلام. لمشاهدة خطة مقدرة مستندة إلى نص، قم بتشغيل الأمر SET SHOWPLAN_ALL ON قبل تنفيذ الاستعلام. عند تشغيل الاستعلام، سترى خطوات خطة التنفيذ، ولكن لن يتم تنفيذ الاستعلام، ولن ترى أي نتائج. يظل خيار SET ساري المفعول حتى تقوم بتعيينه إلى إيقاف التشغيل.
خطة التنفيذ الفعلية
وهذا النوع من الخطط هو نفس الخطة المقدرة؛ ومع ذلك، فإنه يتضمن أيضا سياق التنفيذ للاستعلام. يحتوي هذا السياق على عدد الصفوف المقدرة والفعلية، وأي تحذيرات تنفيذ، ودرجة التوازي الفعلية (عدد المعالجات المستخدمة)، وأوقات وحدة المعالجة المركزية المنقضية المستخدمة أثناء التنفيذ. لمشاهدة خطة فعلية مستندة إلى نص، قم بتشغيل الأمر SET STATISTICS PROFILE ON قبل تنفيذ الاستعلام. يتم تنفيذ الاستعلام، وتحصل على كل من الخطة والنتائج.
إحصائيات Live Query
يجمع خيار عرض الخطة هذا بين الخطط المقدرة والفعلية في خطة متحركة تعرض تقدم التنفيذ من خلال عوامل التشغيل. هذه الخطة يتم تحديثها كل ثانية وتظهر العدد الفعلي للصفوف المتدفقة من خلال عوامل التشغيل. فائدة أخرى من Live Query Statistics هي أنه يظهر التسليم من عامل التشغيل إلى المشغل، والذي يمكن أن يكون مفيدا في استكشاف مشكلات الأداء وإصلاحها. نظرا لأن هذا النوع من الخطة متحرك، فهو متاح فقط كخطة رسومية.