مقدمه

در این مقاله سعی شده است Query Execution Plan بطور خلاصه معرفی شود. این ابزار در برنامه ی Microsoft SQL Server Managment Studio یا بطور خلاصه SSMS قابل بکارگیری و مشاهده است. از آنجایی که کمتر برنامه نویسی از این ابزار و قابلیت برای بهینه کردن کوئری ها استفاده می کند تصمیم گرفتم در یک مقاله مختصر و مفید در پی معرفی این ویژگی در عمل بپردازم و کمک کنم به برنامه نویسانی که می خواهند کمی عمیق تر و با معلومات بیشتری این راه را ادامه بدهند. نقشه (plan) را از چند طریق می توان مشاهده کرد که در ادامه به آنها اشاره کرده ام، نوع گرافیکی آن برای شروع مناسب تر است بر همین اساس در مقاله به نوع گرافیکی پرداختیم.

نقشه ی اجرای پرس و جو (Query Exceution Plan)

query graphical execution plan یا بطور خلاصه plan ابزار و قابلیتی است که به ما می گوید کوئری چگونه اجرا خواهد شد (یا اینکه چگونه اجرا شده است). با کمک این نقشه می توانیم تیکه کد SQL که باعث افت عملکردی شده است را پیدا کنیم و اصلاحش کنیم. بطور مثال آیا کوئری تمام داده های جدول را Scan کرده است یا اینکه از یک Index مناسب برای استخراج داده های مورد نظر استفاده کرده است.

قبل از هرچیزی باید بدانید که نقشه (Plan) را از چند طریق می توانیم مشاهده کنیم؛ از سه طریق: عناصر گرافیکی (Graphical)، متنی و XML. و دو نوع نقشه وجود دارد: واقعی (Actual) که کوئری را اجرا می کند و سپس، نشان می دهد که هنگام اجرای کوئری واقعا چه اتفاق هایی رخ داده است و دیگری تخمین زده شده یا Estimated که نمایان گر خروجی Optimizer (بهینه کننده) است و نیازی به اجرای کوئری ندارد. Optimizer یک تکه نرم افزاری است که بر اساس یکسری آمار و ارقام یک راه بهینه برای اجرای یک کوئری را ایجاد میکند. هر کوئری که ما Submit می کنیم توسط Optimizer بطور اتوماتیک بهینه می شود.

نمایش نقشه

برای پیاده سازی و ارائه مطالب از بانک Northwind استفاده شده. اگر این بانک را در سیستم خود ندارید و در نتیجه Attach نکردین توصیه می شود آن را دریافت و Attach کرده تا همزمان با مطالعه مقاله عملا نیز مطالب را تجربه کنید.
از کوئری زیر در طول مقاله استفاده کرده ام که می توانید آن را در SSMS خود Copy و Paste کنید:

برای آوردن نقشه از چهار طریق اینکار صورت می گیرد: کلید میانبر (shortcuts)، منوی Query، میله ابزار(tool bar) و پنجره Pop-up (پنجره ای که با راست کلیک کردن روی کوئری باز می شود). در تصویر زیر دو دکمه مذکور در میله ابزار در کادر نارنجی قابل مشاهده است.
در تصویر زیر هر دو ابزار Actual و Estimated قابل مشاهده است. در صورتی که این ابزارها قابل رویت نبود کافیست از ابزارها گزینه SQL Editor را انتخاب کنید. برای مشاهده کلید های میانبر این دو ابزار می توانید با کمک منوی Query اینکار را انجام دهید. که Ctrl+L برای "تخمین زده شده" و  Ctrl+M برای "واقعی" مورد استفاده قرار می گیرد.

دکمه Include Actual Execution Plan بگونه ای است که خاموش و روشن می شود اگر روشن باشد و کلیک کنیم خاموش و اگر خاموش بوده روشن می شود. و زمانی که کوئری اجرا شد آن نیز می آید. ولی دکمه Display Estimated Execution Plan را اگر فشار دهیم نقشه تخمین زده شده نمایان می شود.

بعد از فشار دکمه Display Estimated Execution Plan خواهیم دید:

در این پنجره (Execution Plan) شما قادر هستید که عمل بزرگ نمایی (Zoom) را توسط راست کلیک روی یک قسمت خالی از صفحه باز شده انجام دهید. با عمل راست کلیک pop-up زیر باز خواهد شد:

گزینه اول برای ذخیره کردن، دوم برای نمایش فرم XML و سوم یعنی Zoom In برای بزرگنمایی... و Zoom to Fit برای بزرگنمایی به گونه ای که کل صفحه مانیتور را پوشش دهد استفاده می شوند.

طریقه خواندن نقشه

نقشه گرافیکی از یکسری آیکن ها (عملوند/operator) همراه با جهت ها که جریان (flow) نقشه را نشان می دهند تشکیل شده است. جهت خواندن آن از راست به چپ (right to left) و از بالا به پایین (top to bottom) است. از طریق این نقشه ما می توانیم بفهمیم در هر مرحله چه تعداد سطر برگشته یا فیلتر شده و کدام عمل (عملگر) بیشترین هزینه را به کامپیوتر متحمل کرده. مثلا عمل مرتب سازی (Sort) هفتاد درصد هزینه اجرای کوئری را در بر گرفته. برای مشاهده درصد هزینه هر عملگر کافیست به قسمت Cost زیر هر آیکن دقت کنید.

یکی از موارد استفاده ی نقشه گرافیکی، مقایسه چند کوئری است که نتایج یکسان را می دهند ولی از شیوه های متفاوتی نوشته شده اند. برای این منظور کافیست دو یا چند کوئری را در یک پنجره قرار دهید و اجرایشان کنید در سر برگ Execution plan درصد هزینه هر کوئری در بالای هر قسمت نمایش داده می شود. بطور مثال چون که ما یک کوئری تنها را اجرا کردیم، کوئری تمام هزینه یعنی 100 درصد را به خود اختصاص داده است (به قسمت Query 1 : Query Cost توجه کنید).

هر یک از عملوند ها و جهت ها حاوی یکسری اطلاعات هستند که با مکس کوتاهی روی آنها tool-tip ای ظاهر می شود. در ادامه خلاصه ای از اطلاعات که tool tip نشان می دهد را برای تک تک آیکن ها و فلش ها شرح می دهم.

_________________________________________________________________________________

1: عملگر Clustered Index Scan

اگر نقشه از نوع Actual بود Actual Number of Rows نیز بعد از Physical Operation نشان داده می شد. که نشان دهنده تعداد سطر واقعی است.

• Physical Operation: عملگر فیزیکی برای این بخش از Plan، مثل Seek، Join، Scan و ...

• Logical Operator: عملگر منطقی این بخش از Plan

• Estimated Operator Cost: این جمع مقادیر دو پارامتر Estimated CPU cost و Estimated I/O cost است. در واقعا هزینه هر عملگر به درصد که پایین آیکن هر عملگر دیده می شود به این مقدار اشاره می کند. برای محاسبه این درصد کافیست مقدار Estimated Subtree Cost موجود در tooltip عملگر SELECT را بدست آورده و عدد 100 را تقسیم بر این عدد کرده و مقدار Estimated Operator Cost را ضرب در این عدد کنیم تا درصد بدست آید. برای تمرین با کمک مقادیر همین پلن می توانیم درصد را محاسبه کنیم. عدد 163 جمع کل هزینه ها، 114 هزینه عملگر sort و 48 هزینه عملگر Scan. حالا 100 را تقسیم به 163 کرده و ضرب در 114 می کنیم که عددی نزدیک به 70 بدست می آید...

• Estimated Subtree Cost: مجموعه هزینه ی عملگرهای قبلی تا این عملگر. توجه داشته باشید که اجرای عملگر ها از راست به چپ است.

• Estimated Number of Rows: تعداد سطر های تخمین زده شده بر اساس آماری که در بهینه کننده کوئری (Query Optimizer) موجود است.

_________________________________________________________________________________

2.جهت جریان داده: از Scan به Sort

بسادگی از روی این tooltip می توانیم بفهمیم که نقشه گرافیکی از نوع Estimated است یا Actual. در نوع Actual مقدار Actual Number Of Rows نیز وجود دارد. اعداد این مرحله مقادیر هستند که تا قبل از عملگر بعدی موجود بودند. و توسط عملگر بعدی مورد تغییر (مثل فیلتر شدن) قرار می گیرند.

_________________________________________________________________________________

3. عملگر Sort

tooltip این مرحله نیز شبیه به عملگر Scan است ولی بدهیهی است که مقادیر متفاوت اند.

4.جهت جریان داده: Sort به SELECT

این نیز دقیقا مشابه مورد 2 است.

5. عملگر SELECT

آیتم جدیدی که در tooltip این عملگلر وجود دارد Catched Plan Size است که مقدار حافظه ای که به این نقشه کوئری جهت عمل Catch اختصاص داده شده است.

نتیجه گیری

با مطالعه این مقاله بسیار ساده متوجه ابزار بسیار جالبی شدیم که به ما کمک می کند تا بفهمیم کوئری ما دقیقا از چه روشی اجرا شده است و کدام قسمت از کوئری باعث افت عملکردی شده است و مثلا هنگامی که نیاز به استخراج زیر مجموعه ای از سطرهای جدول است آیا تمام جدول خوانده شده است یا اینکه از روی Index داده های مورد نظر بسرعت بدست آمده اند.

با خواندن این مقاله یاد گرفتیم که چگونه چند کوئری که نتیجه یکسان دارند را با همدیگر مقایسه کنیم تا متوجه شویم کدام یک هزینه کمتری دارند و نیاز به منابع سخت افزاری کمتری برای اجرا شدن دارند.
در پایان امید وارم این مقاله توانسته باشد شما را با این قابلیت در حد معرفی آشنا کرده باشد.