2015/11/09

نظرة بتعمق في الSQL Server Execution Plan cache

 نظرة بتعمق في ال Sql Server Execution Plan cache

مقدمة

هذا المقال هو امتداد للمقال السابق بعنوان مقدمة عن خطوات تنفيذ ال Query داخل ال Sql و سوف نتناول فيه بشكل اكثر من التوضيح طريقة تخزين ال Execution Plan في ال Cache و كيفية الاستعلام عن ال Plans المخزنة داخل ال SQL Server مع توضيح الفرق بين ال Execution Plan الفعلية و المستنتجة و طريقة اعادة استخدامها.

الفرق بين ال Execution Plan الفعلية (Actual)  و المستنتجة (Estimated)

ال Estimate Execution Plan هي التي يقوم بانشائها ال Query Optimizer كما ذكرت في المقال السابق وفي الحقيقة يتم استنتاجها عن طريق اجراء بعض العمليات المنطقية دون لمس البيانات ولذلك عملية استنتاجها تأخذ وقت بسيط مهما كان حجم البيانات الموجودة بجداول الاستعلام.
بينما ال Actual Execution Plan هي الخطوات التي يتم تنفيذها فعليا و بالطبع تحتوي على معلومات هامة لانها ما يتم تنفيذه بالفعل و لكن لا يمكن معرفتها الا بعد تنفيذ الاستعلام فتخيل معي انك تقوم بتحسين اداء استعلام يعمل على جداول بها ملايين السجلات (وهذا شيء عادي في قواعد بيانات برامج الشركات ERP) ففي كل مرة لابد و ان تنفذ الاستعلام لجلب ال Actual Execution Plan.
في معظم الاحيان تكون الPlan المستنتجة مساوية للفعلية ولكن بعض الاحيان يحدث اختلاف واغلب اسباب هذه الاختلافات يكون بسبب اختلاف ال Statistics التي اعتمدت عليها ال Estimated Plan عن الفعلية وقت تنفيذ الاستعلام.

اعادة استخدام ال Execution Plan من ال Plan Cache

كما ذكرت في المقال السابق فان ال ALGEBRIZER يقوم باعطاء ال Queryـ (Hash Code) وهو عبارة عن Code يتم استنتاجة من ال Query المرسلة ولا يتكرر ابدا الا اذا تكررت نفس Query ويعتبر هذا ال Code بمثابة بصمة ال Query.
ثم يقوم ال Optimizer بالبحث في ال Plan Cache فاذا وجد هذا الرقم يقوم بجلب ال Execution Plan و يرسلها لل Storage Engine للتنفيذ اما اذا لم يجد يقوم باستنتاج ال Estimated Execution Plan و حفظها في ال Cache و ارسالها للتنفيذ.
ويعتبر هذا من اهم مزايا ال SQL Server وهو قدرتة على استخدام الPlans المخزنة و توفير جهد استنتاج Plans جديدة قدر الامكان.

حذف ال Plan Cache

يوجد ال Plan Cache في مكان ما في ال Memory الخاصة بال Server ولا يقوم ال Sql Server بالاحتفاظ بال Plan Cache الخاصة بال Query الى الابد و لكنه يقوم بعملية حسابية لاستنتاج عمر الCache المخزن للاستعلام (غالبا تكون بضرب اجمالي تكلفة ال Plan في عدد مرات استخدامها) , ويوجد Process باسم lazywriter تعمل بشكل دوري لتفريغ كل انواع ال Cache ومن ضمنها ال Plan Cahe .
يمكن تلخيص الاسباب التي تؤدي الى حذف ال Plan Cache الخاص ب Query معين كالتالي
  • احتياج ال Server الى ذاكرة
  • وصول عمر ال Plan Cache الخاص بال Query الى صفر
  • لا يوجد اي اتصال حالي يستخدم هذه الPlan Cache الخاص بال Query

تحديث ال Plan Cache

يتم تحديث ال Plan Cache في حالة حدوث بعض الاشياء و لكن لابد من ملاحظة ان انشاء Plan جديدة هي من العمليات المجهدة لل Server و التالي هو بعض و ليس كل الاحداث التي تؤدي الى اعادة تحديث ال Plan Cache
  • تعديل في ال Schema بالجداول الخاصة بالQuery
  • تعديل او حذف ال Indexes بالجداول الخاصة بالQuery
  • تحيدث او تعديل ال Statistics بالجداول الخاصة بالQuery
  • استخدام sp_recompile 
  • استخدام Options(Recompile)

اشكال ال Execution Plan

يتم تخزين ال Execution Plan باشكال (Formats) مختلفة
Graphical
و تكون سهلة الاستعمال و هي المستخدمة غالبا لكونها مرئية
Text
تكون على شكل Text و مليئة بالتفاصيل ولكنها صعبة لكونها Text
XML
تكون على شكل XML و بالتالي تكون Structured و هي ايضا مليئة بالتفاصيل و هي مفيدة جدا حيث يمكن الاستعلام عن محتوياتها بال XQuery مثلا

جلب ال Plans من ال Cache باستخدام ال DMOs

ال Dynamic Management Objects (DMOs) هي دوال توفرها ال SQL Server لمعرفة ما يحدث بالداخل و في حالتنا هذه سنستخدمها لعرض ال Plan Caches المخزنة بالاستعلام التالي
SELECT 
[cp].plan_handle,
[cp].[refcounts] ,
[cp].[usecounts] ,
[cp].[objtype] ,
[st].[dbid] ,
[st].[objectid] ,
[st].[text] ,
[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

وتكون النتيجة بشكل مثل التالي

حيث يحتوي العمود text على ال Query و عمود ال Query_Plan على ال Execution Plan بصيغة XML و بالضغط عليها تظهر Graphical وعمود usecounts على عدد مرات الاستخدام و هكذا.

تفريغ ال Plan Cache بشكل يدوي

يمكن تفريغ كل ال Plan Cache الموجود على ال Server بالامر التالي
DBCC FREEPROCCACHE
و من الممكن حذف Query واحدة فقط من ال Cache باستخدام القيمة في عمود  plan_handle مثل الموجودة بالاستعلام السابق من ال DMOs بالامر التالي
DBCC FREEPROCCACHE(0x06000800E2E30F0E40A19DA2000000000000000000000000)
.....................
التالي : مقدمة عن ال Execution Plan
تم بحمد الله :) 

ليست هناك تعليقات:

مقدمة عن الDevOps

في هذا المقال سوف اتحدث عن مقدمة بسيطة عن ال DevOps ولماذا لابد ان تهتم فرق تطوير البرمجيات به وما هي النتائج المترتبة عن استخدام اساليبه ...