به عنوان یک متخصص داده، باید بدانیم که چگونه کوئری‌هایی را بنویسیم که نتایج سریع‌تری داشته باشند و چگونه کوئری‌های با عملکرد کُند را اصلاح کنیم تا عملکرد آن‌ها بهبود یابد. برای اینکه بفهمیم موتور پایگاه داده چگونه کار می‌کند، باید عملیات منطقی انجام شده توسط پردازشگر کوئری را بدانیم. این هدف را می‌توان با execution plan های تولید شده توسط پردازشگر کوئری انجام داد. یک execution plan در SQL Server یک نمایش گرافیکی ساده از عملیاتی است که بهینه‌ساز کوئری یا همان query optimizer به منظور محاسبه کارآمدترین روش برای تولید نتایج صحیح، اجرا می‌کند. در این مقاله سعی خواهیم کرد در مورد انواع execution plan و عملکرد آن ها توضیحاتی را برای شما عزیزان ارائه دهیم.

 

Estimated Plan چیست؟

همان‌طور که قبلاً بیان شد، execution plan در SQL Server Management Studio یک نمایش گرافیکی از مراحل مختلفی است که در تولید نتایج از جداول پایگاه داده نقش دارند. هنگامی که یک کوئری اجرا می‌شود، موتور پردازش کوئری به سرعت execution plan های متعددی را تولید کرده و برنامه‌ای را انتخاب می‌کند که نتایج بهتری را تولید کرده است. 

به بیان دیگر execution plan ها به شما می‌گویند که یک کوئری چگونه اجرا می‌شود. بنابراین، execution plan ها ابزار اصلی DBA برای عیب‌یابی و شناسایی کوئری‌های ضعیف هستند. چرا یک کوئری هزاران اسکن را انجام می‌دهد؟ چرا I/O بیش از حد مصرف می‌شود؟ سؤالاتی از این دست را می‌توان با شناسایی قطعه کد SQL که باعث مشکل شده است به کمک execution plan به راحتی پاسخ داد.

 

استفاده مجدد از Execution Plan 

تولید execution plan ها برای سرور گران است، بنابراین SQL Server تا جایی که امکان دارد planها را نگه داشته و مجدداً استفاده می‌کند. پس از ایجاد، این planها در بخشی از حافظه به نام plan cache ذخیره می‌شوند.

هنگامی که یک کوئری به سرور ارسال می‌شود، یک execution plan تخمینی توسط بهینه‌ساز کوئری ایجاد می‌گردد. وقتی که plan ایجاد شد، و قبل از اینکه به موتور ذخیره‌سازی منتقل شود، بهینه‌ساز کوئری این plan تخمینی را با execution plan های واقعی که از قبل در plan cache وجود دارد، مقایسه می‌کند. اگر یک plan واقعی پیدا شود که با plan تخمینی مطابقت داشته باشد، بهینه‌ساز کوئری از plan موجود مجدداً استفاده خواهد کرد، زیرا قبلاً توسط موتور کوئری تولید شده است. این استفاده مجدد از plan، سربار ناشی از استفاده‌های مکرر از execution plan های ایجاد شده برای کوئری‌های پیچیده را از بین خواهد برد. 

با این حال، execution plan ها برای همیشه در حافظه plan cache نگهداری نمی‌شوند. آن‌ها به مرور با استفاده از فرمول "age" که هزینه تخمینی plan را در تعداد دفعات استفاده از آن ضرب می‌کند، از سیستم خارج می‌شوند (مثلاً یک plan با هزینه 10 که 5 بار ارجاع داده شده است دارای فاکتور age برابر 50 است). فرآیند lazywriter، یک فرآیند داخلی است که برای پاک کردن انواع حافظه کش (از جمله plan cache) استفاده می‌شود. در این فرآیند، به صورت دوره‌ای آبجکت‌های موجود در حافظه کش اسکن شده و هر بار یک سری از آبجکت‌هایی که فاکتور age برای آن‌ها از حد مشخصی بیشتر شود، حذف خواهند شد. 

 

انواع Execution Plan و چگونگی تولید آن‌ها

دو نوع execution plan وجود دارد:

• Estimated Execution Plan: همان‌طور که از نام آن پیداست، این نوع execution plan تنها بیانگر داده‌های احتمالی پردازشگر کوئری در مورد چگونگی انجام مراحل خاصی است که در هنگام بازگرداندن نتایج اجرا می‌شوند. اغلب این plan قبل از اجرای کوئری ایجاد می‌گردد.
• Actual Execution Plan: این plan پس از اجرای کوئری ایجاد شده و عملیات و مراحل واقعی در هنگام اجرای کوئری را نشان می‌دهد. این plan ممکن است با Estimated Execution Plan متفاوت باشد یا در برخی از موارد، این دو plan یکی باشند.

همان‌طور که گفته شد، execution plan  ها را در SQL Server می‌توان قبل و بعد از اجرای کوئری ایجاد کرد. در این بخش مراحل مختلف ایجاد برنامه‌های Estimated execution plan و Actual Execution Plan را ذکر خواهیم کرد.

 

Estimated execution plan در SQL Server Management Studio

راه‌های مختلفی برای ایجاد Estimated execution plan در SQL Server وجود دارد.

1. هنگامی که کوئری به طور کامل نوشته شد، می‌توانید "Ctrl + L" را فشار داده و estimated execution plan را تولید کنید.
2. همچنین می‌توانید روی پنجره کوئری کلیک راست کرده و از منوی context که ظاهر می‌شود، گزینه «Display Estimated Execution Plan» را انتخاب کنید.
3. روش سوم این است که می‌توانید مستقیماً روی آیکون «Display Estimated Execution Plan» که در نوار ابزار SQL Server Management Studio موجود است کلیک نمایید.

 

Actual execution plan در SQL Server Management Studio

به همین ترتیب، می‌توانید روش‌های زیر را برای ایجاد actual execution plan در SQL Server اجرا کنید.

1. "Ctrl + M" را فشرده و پس از اجرای موفقیت‌آمیز کوئری، actual execution plan را تولید کنید.
2. بر روی پنجره کوئری کلیک راست کرده و از منوی context گزینه " Display Actual Execution Plan" را انتخاب کنید.
3. همچنین، می‌توانید مستقیماً روی آیکون «Display Actual Execution Plan» که در نوار SQL Server Management Studio موجود است کلیک نمایید.

 

فرمت‌های Estimated Plan

SQL Server سه فرمت مختلف را برای مشاهده execution plan ارائه می‌دهد که عبارت‌اند از:

• planهای گرافیکی
• planهای متنی
• planهای XML

فرمتی که انتخاب می‌کنید به سطح جزئیات و روش‌های DBA بستگی دارد.

 

Plan های گرافیکی

خواندن این planها سریع و آسان است، اما اطلاعات دقیق plan در آن غیرقابل‌نمایش است. هر دو plan یعنی estimated execution plan و actual execution plan را می‌توان در فرمت گرافیکی مشاهده کرد.

 

Plan های متنی

خواندن این planها کمی سخت‌تر است، اما اطلاعات بیشتری را نمایش می‌دهند. سه فرمت کلی برای plan های متنی وجود دارد:

•  SHOWPLAN_ALL: مجموعه‌ای کاملی از داده‌های Estimated execution plan را برای کوئری نشان می‌دهد.
•  SHOWPLAN_TEXT: مجموعه بسیار محدودی از داده‌ها را برای استفاده با ابزارهایی مانند osql.exe فراهم می‌کند. همچنین فقط Estimated execution plan را نشان می‌دهد.
•  STATISTICS PROFILE: مشابه SHOWPLAN_ALL است با این تفاوت که داده‌های Actual execution plan را نشان می‌دهد.

 

Plan های XML

Plan های XML کامل‌ترین مجموعه داده‌های موجود در یک plan را ارائه داده و همه در فرمت XML ساختار یافته نمایش داده می‌شوند. دو نوع plan XML وجود دارد:

•  SHOWPLAN_XML: plan قبل از اجرا توسط بهینه‌ساز ایجاد می‌شود.
• STATISTICS_XML: فرمت XML برای ذخیره Actual execution plan.

 

تفسیر Execution Plan درSQL Server Management Studio

هنگامی که execution plans را مطابق با روش‌های فوق تولید کردید، نتیجه به صورت گرافیکی نمایش داده خواهد شد. در مورد estimated execution plans، این نتایج به محض انجام فرآیند، تولید می‌شود در حالی که در مورد actual execution plan تنها پس از اجرای کوئری، نتایج نمایش داده خواهند شد. 

در مورد کوئری‌های ساده، estimated execution plans معمولاً مانند actual execution plans هستند. بنابراین به منظور تفسیر execution plan، تنها یک حالت ساده را در این بخش توضیح می‌دهیم. در محیط SQL Server Management Studio، اگر مکان‌نما را روی کامپوننت‌ها قرار دهید، می‌توانید آمار دقیق هر یک از عملیات و کامپوننت‌های نمایش داده شده در execution plan را مشاهده کنید. این plan از راست به چپ و از بالا به پایین تفسیر می‌شود. 

می‌توان هر plan را به کمک پنج روش زیر، شناسایی و تفسیر کرد:

• Clustered Index Scan
• Data Flow از clustered Index Scan 
• اپراتور مرتب‌سازی یا Sort
• Data Flow از اپراتور مرتب‌سازی 
• انتخاب اپراتور 

در این بخش تنها روش  Clustered Index Scanرا بررسی می‌کنیم. سایر روش‌ها نیز مشابه همین روش هستند.

 

Clustered Index Scan

اولین کامپوننت در اسکن راست به چپ، کامپوننت Clustered Index Scan است. این اسکن بر روی Primary Key در جدول یعنی "PK_Dimension_Customer" انجام می‌شود. در این روش اسکن، هر اپراتور در plan مورد نظر دارای یک مفهوم خاص است. برای مشاهده تفسیر هر اپراتور، کافی است نشانگر ماوس را روی هر اپراتور نگه دارید تا توضیحات آن نمایان شود. به عنوان نمونه چند اپراتور به همراه تفسیر آن‌ها را در ادامه بیان می‌کنیم.

• Physical Operation: اپراتورهایی هستند که عملیات را طبق دستور اپراتورهای منطقی اجرا می‌کنند. همه اپراتورهای فیزیکی معمولاً آبجکت هستند و یک عملیات را انجام می‌دهند. 
• Logical Operation: این اپراتورها عملیات جبری واقعی را توصیف می‌کنند که برای پردازش کوئری استفاده می‌شود. به عنوان مثال می‌توان به Right Anti Semi Join، Hash Join و غیره اشاره کرد.
• Actual Execution Mode: این اپراتور توسط موتور پردازشگر برای اجرای کوئری استفاده می‌شود. 
• Estimated Execution Mode: این اپراتور شبیه Actual Execution Mode است اما مقدار تخمینی را نشان می‌دهد.

همچنین می‌توانید روی هر اپراتور یا فلش کنار آن در plan کلیک راست کرده و Properties را انتخاب کنید تا توضیحات دقیق‌تری درباره معیارهای نمایش داده شده در tooltip برای هر اپراتور برای شما قابل‌مشاهده باشد.

 

ذخیره‌سازی Execution Plan درSQL Server Management Studio

پس از تولید و تفسیر plan توسط کوئری، مرحله بعدی ذخیره کردن آن برای استفاده‌های بعدی است. SSMS قابلیت ذخیره plan را در فایل سیستم با پسوند ".sqlplan" فراهم می‌کند. برای ذخیره‌سازی مراحل زیر را دنبال نمایید:

1. روی پنجره plan کلیک راست کنید.
2. Save Execution Plan As را انتخاب کنید.
3. محل ذخیره‌سازی را انتخاب کرده و یک نام برای execution plan در نظر بگیرید و روی Save کلیک کنید.
به همین سادگی execution plan ذخیره شده و می‌توانید هر زمان که خواستید آن را باز و اجرا کنید.

 

جمع‌بندی

در این مقاله در مورد execution plan ها در SQL Serve، انواع آن‌ها و نحوه تولید و تفسیر آن‌ها بحث کردیم. همچنین معیارهای مختلفی را که در اپراتورهای مورد استفاده در plan در نظر گرفته شده است، بررسی کردیم. در نهایت، نحوه ذخیره یک execution plan را در SQL Server Management Studio توضیح دادیم. Execution plan در SQL Serve امکان تفسیر و مشاهده گرافیکی نتایج اجرای کوئری‌ها را فراهم می‌کند.

 

     مدرس : استادغلامحسین عبادی