SQL یک زبان اعلانی است به این معنی که به جای برنامه‌نویسی جزئیات نحوه بازیابی داده‌ها، توصیف می‌کنیم که چه داده‌هایی را می‌خواهیم و SQL Server متوجه می‌شود چگونه باید آن داده‌ها را برای ما نمایش دهد.SQL Server Query Optimizer چندین رویکرد را برای برگرداندن داده‌ها در نظر می‌گیرد. همچنین هزینه‌های رویکردهای مختلف را تخمین زده و یک رویکرد را که به شیوه‌ای کارآمد داده‌ها را برمی‌گرداند، انتخاب کرده و برای ما نمایش می‌دهد. این رویکردها همان query execution plans هستند.

اغلب اوقات، این فرآیند به خوبی عمل می‌کند و Query Optimizer در نهایت query plan مشخصی را انتخاب می‌کند که داده‌های درخواستی را به سرعت برمی‌گرداند. اما این بدان معنا نیست که SQL Server "کارآمدترین" query plan را انتخاب می‌کند. بلکه به دنبال plan یا طرحی است که کمترین هزینه را در پی داشته باشد.

وقتی Query Optimizer یک query plan را انتخاب می‌کند که گزینه مناسبی نیست، مشکلات زیادی ایجاد خواهد شد. بنابراین توجه به ترتیب اجرای مراحل در Execution Plan، نحوه خواندن و مشاهده آن و بررسی آن توسط SQL Server از اهمیت بالایی برخوردار است. در این مقاله در مورد نحوه خواندن Execution Plan و مفاهیم مرتبط با آن مطالبی را ارائه خواهیم داد.

 

مشاهده Execution Plan ها

برای مشاهده یک execution plan مربوط به کوئری، می‌توانید SET SHOWPLAN_ALL ON را اجرا کنید. این روش یک نمایش درختی مبتنی بر متن از plan را ارائه می‌دهد. برای این منظور می‌توانید از کد زیر کمک بگیرید:

SET SHOWPLAN_ALL ON
GO
--Query
GO
SET SHOWPLAN_ALL OFF
GO

از طریق SSMS نیز می‌توان execution plan را مشاهده نمود. اگر روی دکمه " Display Estimated Execution Plan" کلیک کنید یا SET SHOWPLAN_XML ON را اجرا نمایید execution plan به صورت گرافیکی نمایش داده خواهد شد. همچنین می‌توان روی execution plan گرافیکی کلیک راست کرده و "Show Execution Plan XML" را انتخاب نمود. با این کار XML مربوط به همه ویژوال‌های plan مورد نظر قابل‌مشاهده خواهند بود. در نسخه گرافیکی execution plan ممکن است برخی از مشخصه‌ها نمایش داده نشوند، بنابراین استفاده از روش مشاهده XML می‌تواند جزئیات بیشتری را در مورد plan برای ما مشخص کند.

 

ترتیب اجرای مراحل در Execution Plan

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

هنگامی که به یک عملگر join یا incorporation می‌رسیم که در آن چندین شاخه در یک عملگر ادغام می‌شوند، می‌توانیم به سمت راست عملگر یا یکی از شاخه‌های پایین‌تر رفته و روند خواندن از راست به چپ را دوباره شروع کنیم. به طور کلی، می‌توان گفت که ترتیب اجرای مراحل در execution plan از راست به چپ و از بالا به پایین است.

با دنبال کردن فلش‌ها از یک اپراتور به اپراتور دیگر، جریان داده‌های plan مشخص می‌شود. هر اپراتور یک ردیف از داده‌ها را در سمت چپ خود برای اپراتور بعدی ارسال می‌کند تا زمانی که تمام ردیف‌های داده مسیر خود را به آخرین اپراتور سمت چپ باز کنند. به منظور شناسایی ترتیب اجرای مراحل در یک plan و نیز تعیین جریان داده، آشنایی با یک سری مفاهیم ضروری است.

 

فلش ها

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

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

 

مشخصه‌های اپراتور

نگه داشتن ماوس روی یک اپراتور (یا یک فلش) اطلاعات بیشتری در مورد عملکرد آن اپراتور به ما می‌دهد. این اطلاعات عبارت‌اند از تعداد سطرهایی که انتظار می‌رود SQL Server بخواند در مقایسه با تعداد سطرهایی که در واقعیت خوانده است (در صورت استفاده از actual execution plan)، گزاره‌های اعمال شده، پیام‌های هشدار و غیره. 

پنجره properties (به طور پیش‌فرض با کلید F4 نمایش داده می‌شود) مربوط به هر اپراتور نیز اطلاعات بیشتری را ارائه می‌دهد. اطلاعاتی که در این پنجره نمایش داده می‌شود عبارت‌اند از میزان مصرف CPU و تغییراتی که SQL Server به عنوان بخشی از اجرای کوئری انجام می‌دهد.

 

هزینه‌ها

در زیر هر اپراتور درصد هزینه آن اپراتور نسبت به سایر هزینه‌های plan نمایش داده می‌شود. این هزینه‌های نسبی گاهی اوقات می‌توانند به شناسایی خطاهای plan کمک کنند. به عنوان مثال برای عیب‌یابی یک طرح، بهترین کار این است که از اپراتورهای پرهزینه برای جستجوی خطا شروع کنید. اما لزوماً یک اپراتور با هزینه بالا منشأ بروز خطا نخواهد بود.

 

هشدارها

هشدارها که با علامت تعجب زرد رنگ در گوشه آیکون اپراتور مشخص می‌شوند، نشان می‌دهند که اتفاق نامطلوبی در آن اپراتور رخ داده است. این هشدارها ممکن است بر عملکرد کوئری تأثیر منفی بگذارد، بنابراین توصیه می‌شود به این هشدارها توجه کرده و در صورت امکان خطاهای مشخص شده را رفع نمایید.

 

توصیه استفاده از ایندکس

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

 

نحوه خواندن Execution Plans 

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

در یک execution plan متنی، تورفتگی نشان‌دهنده رابطه والد/فرزند است. والد در هر مرحله اولین خط آن است که به سمت چپ فرورفتگی دارد. فرزندان نیز خطوطی هستند که در زیر خط والد قرار دارند. در یکexecution plan متنی، می‌توان ساختار plan را مانند یک درخت که به مرور به سمت پایین برگ‌های آن زیاد می‌شود، مدل کرد. 

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

 

انواع روش‌های خواندن Execution Plans

قبلاً در مورد نحوه خواندن execution plan های گرافیکی بحث کردیم. گفتیم که در این  planها به ترتیب به دنبال اپراتورها می‌گردیم. مبنای خواندن اپراتورها در این روش، ترتیب قرارگیری آن‌ها از راست به چپ و از بالا به پایین می‌باشد. اما روش دیگری هم برای خواندن execution plan وجود دارد. در این روش به جای توجه به محل قرارگیری اپراتورها، به سراغ اپراتوری می‌رویم که بیشترین هزینه را دارد.

هر execution plan در SQL Server دربرگیرنده هر آن چیزی است که بهینه‌ساز به عنوان هزینه هر عملیات تعیین کرده است. تمام این هزینه‌های برآورد شده با هم جمع شده و هزینه تخمینی کل execution plan را تشکیل می‌دهد. از آنجایی که بهینه‌ساز انتخاب‌های خود را بر اساس این هزینه‌های تخمینی انجام می‌دهد، خواندن execution plan بر اساس این هزینه‌ها یک روش معقول است. به همین دلیل در این روش ابتدا از اپراتوری شروع می‌کنیم که بیشترین هزینه را دارد.

برای شناسایی اپراتوری که بیشترین هزینه را دارد با استفاده از SSMS، می‌توانید از ابزارهای شخص ثالث استفاده کنید. در این ابزارها، همه اپراتورهای plan به ترتیب هزینه یا cost نمایش داده می‌شوند. بنابراین می‌توان XML مربوط به plan دارای بیشترین هزینه را با استفاده از این ابزارها شناسایی نمود. NodeID یکی از این ابزارها است که در SSMS مورد استفاده قرار می‌گیرد.

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

 

STATISTICS IO data

برای کسب اطلاعات از SQL Server علاوه بر خواندن execution plan، یک روش دیگر استفاده از داده STATISTICS IO است. STATISTICS IO جزئیات زیادی را در مورد اثر کوئری مورد نظر روی SQL Server در اختیار شما قرار می‌دهد. همچنین اطلاعاتی در مورد logical reads (شامل LOB)، physical reads (شامل read-ahead و LOB) و تعداد اسکن‌های جدول توسط STATISTICS IO در اختیار کاربر قرار می‌دهد. 

این اطلاعات در کنار اطلاعات حاصل از خواندن execution plan یک ابزار مهم است که به عیب‌یابی کوئری کمک خواهد کرد. برای تفسیر نتایج بررسی STATISTICS IO آشنایی با چند اصطلاح مفید است.

 

Logical Reads

این عدد تعداد واقعی صفحات خوانده شده از حافظه data cache را به ما می‌گوید. این عدد تغییر نمی‌کند مگر اینکه ساختار کوئری واقعی یا ساختارهای ایندکس تغییر کند. رایج‌ترین تغییرات، join های عبارت WHERE، مقادیر پارامتر یا ساختارهای ایندکس هستند.

 

Physical Reads 

این عدد، تعداد صفحاتی است که از دیسک خوانده می‌شود. این معیار در واقع نشان‌دهنده تعداد صفحاتی است که قبلاً در حافظه cash نبودند و بنابراین یک معیار مهم است زیرا تأثیر مستقیمی بر عملکرد کوئری دارد. 

 

Read-Ahead Reads

این عدد به ما می‌گوید که چه تعداد از physical read توسط مکانیسم «Read-ahead» در servers SQL اجرا شده است. این معیار به طور مستقیم با physical read مرتبط است، بنابراین اگر physical read وجود نداشته باشد، Read-Ahead Reads برابر صفر خواهد بود.

 

جمع بندی

معیارهای مختلف خواندن Execution Plan و همچنین ترتیب اجرای مراحل در Execution Plan در این مقاله مورد بحث قرار گرفت. به طور کلی روش‌های مختلفی برای خواندن execution plan های گرافیکی و متنی وجود دارد که در این مقاله به هر یک از این روش‌ها به صورت جداگانه پرداخته شد.

همچنین یک روش دیگر برای کسب اطلاعات servers SQL، خواندن STATISTICS IO است که اطلاعاتی در مورد logical reads، physical reads و Read-Ahead Reads در اختیار ما قرار می‌دهد. این اطلاعات به خصوص برای عیب‌یابی کوئری‌ها بسیار مفید است.
 

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