Execution Plan چیست

نوشته شده توسط محسن بندامیر
تاریخ ایجاد در دوشنبه, 20 شهریور 1396
بازدید: 3063

Execution Plan چیست؟

بدون تردید، execution plan یکی از مهمترین ابزارهای داخلی sql server در جهت کشف مشکلات کارایی و کندی سرعت اجرای کوئری ها است. برای هر فرد برنامه نویس و ادمین دیتابیس، آشنایی با execution plan از مهارت های کاربردی می باشد. اگر بخواهیم بطور خلاصه کاربرد execution plan را بدانیم، مواقعی که سرعت اجرای یک کوئری قابل قبول نیست و در اصطلاح، کوئری کند اجرا می شود، قبل از اینکه به زیاد بودن تعداد رکوردهای جدول و یا ضعیف بودن منابع سرور شک کنیم و دلیل اجرای کند و زیاد بودن میزان I/O یک کوئری رو با حدس و گمان برطرف کنیم، بهترین روش این است که از execution plan کمک بگیریم و دقیقا قسمتی از کوئری که باعث ایجاد مشکل شده رو کشف و حل نماییم.

مطالبی که در این آموزش مطالعه خواهید کرد به عنوان یک پیش نیاز برای تعداد زیادی از آموزش های آتی در زمینه بهینه سازی کوئری ها (performance tuning) و همچنین آموزش هایی مانند معرفی عملگرها، نحوه خواندن execution plan و ... خواهد بود. در ادامه ابتدا بصورت مفهومی با execution plan آشنا خواهیم شد و تعدادی از مهمترین کاربردهای آن را اشاره می کنیم.

مفهوم Execution Plan

با توجه به این نکته که ما با مفاهیم برنامه نویسی آشنایی داریم، در ابتدا برای توضیح مفهوم execution plan از مروری بر فلوچارت استفاده می کنیم. در تصویر زیر یک نمونه فلوچارت ساده را مشاهده می کنید:

execution plan چیست

با یک توضیح مختصر می توانیم بگوییم که یک فلوچارت از تعدادی اشکال هندسی (دایره، لوزی، مستطیل و ...) تشکیل شده است که هر کدام نشان دهنده انجام یک عملیات (شروع،انجام محاسبات، پایان و ...) هستند و مجموعه این اشکال در کنار یکدیگر، مراحل انجام یک عملیات رو به صورت مرحله به مرحله بیان می کنند. این عملیات میتونه تشخیص زوج یا فرد بودن یک عدد باشد یا یک عملیات پیچیده با تعداد زیادی از اشکال و انشعاب ها.

و اما  execution plan در sql server هم، یک مجموعه از اشکال گرافیکی(آیکن) هست که مراحل انجام شدن یک کوئری (select - update و...) رو نمایش میده (به همین سادگی!!!). در execution plan هم هر کدام از این شکل های گرافیکی که بهشون آیکن می گیم، نشان دهنده یک عملیات خاص ( خواندن از جدول - مرتب سازی و ...) هستند که مجموعه این عملیات، منجر به اجرای یک کوئری در sql server می شود. در شکل زیر یک نمونه از یک execution plan ساده رو می بینیم:

execution plan چیست

همونطور که مشاهده می کنید ما یک کوئری در sql server اجرا کرده ایم که از جدول Student نام و فامیل ها رو نمایش بدهد و خروجی را بر اساس نام مرتب سازی کند. خوب در تصویر گرافیکی بالا (از راست به چپ بخوانید) می بینیم که در ابتدا جدول خوانده شده است (Table Scan) و در مرحله بعدی یک مرتب سازی انجام شده (Sort) و در انتها هم رکوردهای مرتب شده جهت نمایش انتخاب می شوند (Select). پس در اجرا شدن کوئری مورد نظر ما از سه عملیات Table Scan - Sort - Select استفاده شده است. نکته مثبت دیگری  که در execution plan وجود داره متناسب بودن شکل ظاهری آیکن ها با عملیات مربوط به اون هست که در تصویر بالا به خوبی مشخص است.

پس با همین مثال ساده متوجه شدیم که تعریف execution plan چیست. علاوه بر این مفهوم سادگی رو که در ابتدای مطلب بهش اشاره کردیم به خوبی قابل مشاهده است (به دلیل استفاده از آیکن های با مفهوم)

نمایش Execution Plan در Sql server

در sql server بصورت پیش فرض با اجرا شدن کوئری execution plan نمایش داده نمی شود و می بایست این ویژگی رو فعال کنید. قبل از توضیح این مورد، بهتره بدونیم که در sql server دو نوع execution plan وجود داره:

جهت نمایش execution plan روشهای مختلفی وجود داره ( کلید میانبر - منو - نوار ابزار) که در شکل زیر نحوه فعال سازی نمایش execution plan رو با استفاده از آیکن های مربوطه در نوار ابزار می بینیم. (از کلیدهای میانبر نمایش داده شده در tooltip هم میتونین استفاده کنید). برای این کار، ابتدا یکی از این آیکن ها (Estimated /Actual) رو انتخاب و سپس کوئری مورد نظر رو اجرا می کنیم. (در صورت نیاز به مشاهده execution plan یک کوئری خاص، از بین چندین کوئری، تنها کافیست قبل از اجرا، کوئری مورد نظر را انتخاب(highlight) کنیم)

 نمایش execution plan در sqlserver

noticeنکته اول:  execution plan در سه قالب گرافیکی، Text و Xml قابل نمایش است که در روش بالا تنها روش فعال سازی نمایش گرافیکی آن را دیدیم. جهت فعال سازی دوحالت نمایش دیگر از دستورات زیر استفاده کنید:

فرمت های نمایش Execution plan

شاید این سؤال برای شما مطرح شود که کاربرد حالت های نمایشی text و xml چیست؟ به عنوان مثال شما در حالت نمایش xml، تگ های مختلفی را مشاهده می کنید که حاوی عملگرهای (آیکن ها) Execution plan نیز می باشد. اما سایر تگ های xml، حاوی اطلاعات مفیدی هستند که در مباحث افزایش سرعت اجرای کوئری و دیتابیس (performance tuning) از این اطلاعات جهت کشف مشکلات کارایی استفاده خواهیم کرد ( به عنوان مثال از تگ Internal info برای کشف Statistics های استفاده شده در اجرای یک کوئری استفاده می کنیم). به عنوان مثالی دیگر، از فرمت text آن در نرم افزار osql.exe استفاده می شود.

noticeنکته دوم: استفاده از فرمت sqlplan جهت ارسال Execution plan یک کوئری دارای مشکل کارایی به یک منخصص، جهت کشف مشکلات آن:

فرض کنید در سیستم لوکال خودتون یک کوئری رو اجرا می کنید که  مشکل کندی سرعت داره و لازم است که جهت رفع مشکل کارایی آن مشاوره دریافت کنید. خوب راه حل های مختلفی مانند انتقال بک آپ دیتابیس و کوئری مربوطه،ایجاد ارتباط با teamviewer و ... وجود دارد. اما یکی از بهترین روش ها، ذخیره Execution plan در قالب یک فایل و ارسال آن برای متخصص مورد نظر می باشد. این فایل که با فرمت sqlplan ذخیره می شود، بعد از باز کزدن در (ssms (sql server management studio سیستم مقصد، دقیقا شکل گرافیکی معادل در سیستم لوکال شما را نمایش خواهد داد. در شکل زیر نحوه ذخیره سازی یک Execution plan در فرمت sqlplan نمایش داده شده است.

execution-plan-sqlplan-format

عناصر تشکیل دهنده یک Execution Plan

مهمترین عناصری که در یک execution plan  وجود دارند عبارتند از:

1. آیکن ها: یک شکل گرافیکی هستند که معرف عملگری از Sql server می باشند. در ادامه در مورد انواع و دسته بندی آیکن ها صحبت خواهیم کرد.

2. Tooltip: با نگه داشتن موس روی عناصر execution plan (آیکن، فلش) نمایش داده می شوند و حاوی اطلاعات مفیدی هستند که در مباحث performance tuning زیاد از آن ها استفاده خواهیم کرد.

3. فلش ها: نشان دهنده انتقال رکوردها از یک عملگر به عملگر دیگر هستند و با نگه داشتن موس بر روی آن ها اطلاعاتی مانند تعداد رکوردهای انتقالی، حجم آن ها و ... نمایش داده می شود.

4. هزینه (cost): در زیر هر آیکن (عملگر) execution plan یک عدد همراه با علامت درصد(%) نوشته شده که معرف هزینه اجرایی آن عملگر است. 

noticeنکته: هزینه هر عملگر، بطور نسبی (Ratio) محاسبه می شود و میانگینی از I/O و مصرف منابع  (cpu، ram و ...) است. بنابراین مشاهده عدد صفر، در هزینه یک عملگر به این معنی است که هزینه این عملگر، به نسبت سایر عملگرهای موجود در execution plan ناچیز است و نه صفر.

5. ویژگی (properties): با راست کلیک کردن روی هر آیکن execution plan گزینه properties وجود دارد که اطلاعات تکمیلی در مورد آن عملگر را در اختیار ما قرار می دهد از این اطلاعات هم در فرآیند بهینه سازی و رفع مشکلات کارایی استفاده خواهیم کرد.

در شکل زیر، عناصر معرفی شده execution plan  همراه با شماره معادل آن در قسمت بالا، نمایش داده شده است:

 عناصر execution plan

آیکن های Execution Plan

یکی از اولین اجزاء یک  execution plan که همیشه با آن سر و کار داریم آیکن های موجود در آن یا همان شکل گرافیگی است و همانطورکه در مطالب قبلی اشاره شد با دنبال کردن این آیکن ها مراحل اجرا شدن یک کوئری در sql server رو متوجه خواهیم شد. اما تعداد آیکن های موجود در execution plan تقریبا 78 عدداست! البته در حالت معمول همواره تعداد کمی از این آیکن ها رو در execution plan خواهیم دید. این 78 آیکن(عملگر) در چهار گروه با سه رنگ بندی مختلف قرار می گیرند:

1. آیکن های سبز رنگ: این آیکن ها معرف عملگرهای عناصر زبان T-sql یا language element هستند. آیکن مربوط به عملگرهایی مانند: select- while- convert- assign- declare  و... در execution plan با رنگ سبز نمایش داده می شوند.

2. آیکن های آبی رنگ: نمایش دهنده عملیات دستکاری داده ها (DML) هستند و به آن ها logical-physical operator گفته می شود. این نوع آیکن ها بیشتر از بقیه در execution plan مشاهده می شود. عملیاتی مانند update- insert- table scan- delete و ... با آیکن هایی آبی رنگ در execution plan نمایش داده می شوند.

3. آیکن های زرد رنگ: بیانگر عملیات کرسر هستند. در واقع این دسته عملگرها Row Set هستند و روش کار آن ها به این صورت است که یک کرسر روی جدول تغریف می شود و یک رکورد در کرسر تعریفی واکشی شده و عملیات مورد نظر، روی آن یک رکورد انجام می شود و در ادامه رکورد بعدی واکشی می شود (fetch next) و تا آخرین رکورد جدول این روش ادامه پیدا می کند (انجام عملیا روی یک رکورد در لحظه). عملگرهای cursor- fetch query از این نوع هستند.

4. آیکن های آبی رنگ: در واقع این عملگرها زیر مجموعهای از عملگرهای logical-physical  هستند و مربوط به اجرای موازی یک عملیت می شوند که به آن ها را parallelism می نامند. عملگرهایی مانند distribute streams- gather streams با این آیکن آبی رنگ که به شکل چند فلش است نمایش داده می شوند.

 آیکن های execution plan به تفکیک رنگ

لازم است به این نکته اشاره کنم که نگران تعداد زیاد آیکن های execution plan نباشید. چون اولا همانطور که ذکر شد معمولا با تعداد محدودی از این آیکن ها(عملگرها) در اجرای کوئری ها سرو کار داریم و ثانیا شکل گرافیکی این آیکن ها بسیار شبیه به عملگر مربوط به آن هستند و به خوبی معرف عملیات انجام شده می باشند. در طول آموزش ها به تدریج و متناسب با موضوع، با این آیکن ها آشنا خواهیم شد.

کاربردهای Execution Plan

خوب تا اینجا با توضیحات داده شده ممکنه این سؤال پیش بیاد که مشاهده کردن execution plan و دیدن مراحل اجرا شدن یک کوئری چه کاربردی داره؟ برای پاسخ دادن به این سؤال، شرایط زیر رو در نظر بگیرید:

   و ...

با اطلاعاتی که execution plan در اختیار ما قرار می دهد (آیکن ها - tooltip - اطلاعات (properties) هر عملگر و ...) می توانیم کلیه مشکلات بالا رو هندل کنیم. البته برای این منظور نیاز است که به تدریج و با تمرین، مهارت لازم برای این کار رو بدست بیاریم که در codeeatsleep با یک مجموعه آموزشی پیوسته به این هدف دست خواهیم یافت.

یکی از فواید آشنایی با execution plan و استفاده همیشگی از آن در sql server این است که در طراحی جداول و کوئری ها دقت بیشتری اعمال خواهیم کرد و در واقع با یک دید متفاوت این فرآیند را انجام می دهیم. به عبارتی دیگر با نوشتن یک کوئری، عملگرها و نحوه اجرا شدن آن در sql server را در ذهن خواهیم داشت و این عالیه !!!

به عنوان جمع بندی:

امیدوارم که با مطالعه این مطلب، با اهمیت خاص execution plan و لزوم آشنایی با آن در توسعه برنامه های کاربردی و طراحی کوئری ها آشنایی پیدا کرده باشید. برای اینکه به تسلط لازم جهت به کارگیری execution plan در بهینه سازی برسیم لازم است که با انواع آیکن ها، نحوه خواندن، استخراج اطلاعات تکمیلی مورد نیاز از آن و تا حدودی با نقش optimizer در sql server آشنایی پیدا کنیم. که حتما در نوشته های آینده به این موارد خواهم پرداخت.