Plan چیست؟
در اینجا Plan کوئری سادهای را مشاهده میکنید. کار آن انتخاب نام، نام خانوادگی و آدرس ایمیل افرادی است که نام خانوادگی آنها با Whit شروع میشود و بر روی دو جدول که با هم جوین شدهاند عمل میکند.
اولین موردی را که باید در یک Plan به آن دقت کرد، عملگرهای آن است که شامل select، nested loop، index seek و clustered index seek میباشند. index seek بر روی جدول اشخاص و clustered index seek بر روی جدول ایمیلها صورت میگیرد. nested loop بیانگر جوین بین جداول است. این عملگرها بیانگر اعمال فیزیکی هستند که رخ دادهاند.
همچنین تعدادی پیکان (arrow) را هم مشاهده میکنید که بیانگر جهت سیلان دادهها است. اطلاعات از طریق index seek و clustered index seek به nested loop میرسند و در نهایت به عملگر select ارائه خواهند شد.
در این تصویر، هزینههای تخمینی مرتبط با هر عملگر نیز قابل مشاهدهاست که نسبت به کل کوئری محاسبه شدهاند. این هزینه، بدون واحد است و به معنای میزان زمان و یا CPU صرف شدهی برای انجام عمل خاصی نیست و صرفا برای مقایسهی هزینهی نسبی عملگرها در کل یک Plan کاربرد دارد. باید دقت داشت که هزینههای نمایش داده شدهی در یک Plan، همیشه تخمینی هستند. در قسمتهای قبل در مورد نحوهی دریافت estimated plan و actual plan بحث کردیم. هیچگاه چیزی به نام Actual cost در یک Actual plan وجود ندارد و همیشه تخمینی است. روش محاسبهی آنها توسط الگوریتمهای بهینه ساز است و مستقل از سخت افزار مورد استفاده.
در یک پلن، مدت زمان انجام یک کوئری، میزان I/O ، locks و wait statistics قابل مشاهده نیستند. البته اگر از SQL Server 2016 به بعد استفاده میکنید و یک Actual plan را محاسبه کردهاید، مدت زمان انجام یک کوئری و میزان I/O نیز در Plan قابل مشاهدهاند.
از چه جهتی باید یک Plan را خواند؟
اگر هدف، بررسی «سیلان کنترل» است (Control flow)، باید یک Plan را از «چپ به راست» خواند. یعنی از عملگر select شروع میکنیم که کوئری ما را کنترل میکند. سپس به nested loop میرسیم که نام و نام خانوادگی را از جدول اشخاص دریافت میکند. این nested loop نیز با کمک ایندکسهای تعریف شده، شرط کوئری را بر آورده میکند.
اما جهت «سیلان اطلاعات» در یک Plan از «راست به چپ» است (Data flow). اطلاعات از طریق index seekها به حلقه و سپس select میرسند.
چگونه یک Query Plan را شروع به بررسی کنیم؟
ابتدا در management studio از منوی Query، گزینهی Include actual execution plan را انتخاب میکنیم. سپس کوئری زیر را اجرا میکنیم:
USE [WideWorldImporters]; GO SELECT [s].[StateProvinceName], [s].[SalesTerritory], [s].[LatestRecordedPopulation], [s].[StateProvinceCode] FROM [Application].[Countries] [c] JOIN [Application].[StateProvinces] [s] ON [s].[CountryID] = [c].[CountryID] WHERE [c].[CountryName] = 'United States'; GO
در اینجا چهار عملگر select، nested loop، clustered index seek و clustered index scan مشاهده میشوند. شاید اینطور به نظر برسد که در این Plan، ابتدا clustered index scan و clustered index seek انجام میشوند و سپس به nested loop میرسیم (اگر Plan را بر اساس سیلان داده، از راست به چپ بخوانیم)؛ اما اینطور نیست. عملگرها در اینجا در حقیقت یک سری iterator هستند که با دریافت ردیفهای مرتبط، بلافاصله آنها را به nested loop ارسال میکنند. این nested loop نیز ردیفهایی را که با جوین انجام شده تطابق دارند، به سمت select ارسال میکند.
اگر به تصویر دقت کنید هر کدام از ایندکسها به یک جدول اشاره میکنند که نام آن بالای عدد هزینه درج شدهاست. برای مشاهده نام کامل شیء متناظر با آن، میتوان اشارهگر ماوس را بر روی ایندکس حرکت داد و به اطلاعات قسمت Object دقت کرد:
و یا اگر اطلاعات کاملتری از این popup را نیاز داشتید، عملگر مدنظر را انتخاب کرده و سپس دکمهی F4 را فشار دهید:
در برگهی خواص ظاهر شده میتوان ریز جزئیات تمام اطلاعات مرتبط با عملگر انتخاب شده را مشاهده کرد. برای مثال در اینجا حتی اطلاعات Logical reads را بدون روشن کردن SET STATISTICS IO ON میتوان مشاهده کرد:
همچنین با توجه به انتخاب گزینهی Include actual execution plan، تعداد ردیفهای بازگشت داده شدهی واقعی و تخمینی، با هدایت اشارهگر ماوس بر روی یکی از اشیاء مرتبط با بررسی ایندکسها، قابل مشاهده هستند:
گزارش این تعداد ردیفها، با حرکت اشارهگر ماوس، بر روی پیکانهای منتهی به nested loop و یا select نیز قابل مشاهده هستند:
به این ترتیب میتوان دریافت که چه مقدار اطلاعات در طول این Plan و قسمتهای مختلف آن، از سمت راست به چپ، در حال جابجایی است.
اکنون در ادامه سعی میکنیم توسط DMO's، این Plan را از Plan cache دریافت کنیم:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT [cp].[size_in_bytes], [cp].[cacheobjtype], [cp].[objtype], [cp].[plan_handle], [dest].[text], [plan].[query_plan] FROM [sys].[dm_exec_cached_plans] [cp] CROSS APPLY [sys].[dm_exec_sql_text]([cp].[plan_handle]) [dest] CROSS APPLY [sys].[dm_exec_query_plan]([cp].[plan_handle]) [plan] WHERE [dest].[text] LIKE '%StateProvinces%' OPTION(MAXDOP 1, RECOMPILE);
همانطور که مشاهده میکنید، اینبار تنها اطلاعات تخمینی در این Plan ظاهر شدهاند؛ چون اطلاعات آن از کش خوانده شدهاست. همچنین در اینجا اطلاعات I/O مانند حالت Actual Plan، در برگهی خواص عملگرهای این Plan، قابل مشاهده نیستند.
نگاهی به اطلاعات XML ای یک Plan
اگر کوئری زیر را با فرض انتخاب Include actual execution plan در منوی Query اجرا کنیم:
SELECT [o].[OrderID], [ol].[OrderLineID], [o].[OrderDate], [o].[CustomerID], [ol].[Quantity], [ol].[UnitPrice] FROM [Sales].[Orders] [o] JOIN [Sales].[OrderLines] [ol] ON [o].[OrderID] = [ol].[OrderID]; GO
در اینجا با کلیک راست بر روی Plan، میتوان گزینهی Show Execution Plan XML را نیز انتخاب کرد. گاهی از اوقات کار کردن با این اطلاعات، به صورت XML ای سادهتر است و فرمت آن از هر نگارش به نگارش دیگر SQL Server میتواند متفاوت باشد.
برای مثال اگر در برگهی نمایش این اطلاعات، دکمههای ctrl+f را فشرده و به دنبال runtime بگردیم، خیلی سریعتر میتوان به اطلاعات I/O ،CPU و تعداد ردیفهای بازگشت داده شده، رسید.
و یا حتی اطلاعات wait statistics را نیز میتوان به سادگی در اینجا مشاهده کرد تا مشخص شود چرا یک کوئری خوب عمل نمیکند:
اجرای چند کوئری با هم و بررسی Query Plan آنها
اگر دو کوئری زیر را با فرض انتخاب Include actual execution plan در منوی Query با هم اجرا کنیم:
USE [WideWorldImporters]; GO SELECT [CustomerID], [TransactionAmount] FROM [Sales].[CustomerTransactions] WHERE [CustomerID] = 1056; GO SELECT [o].[OrderID], [ol].[OrderLineID], [o].[OrderDate], [o].[CustomerID], [ol].[Quantity], [ol].[UnitPrice] FROM [Sales].[Orders] [o] JOIN [Sales].[OrderLines] [ol] ON [o].[OrderID] = [ol].[OrderID]; GO
هزینهی اولین کوئری نسبت به کل batch جاری، 10 درصد است و هزینهی دومین کوئری، 90 درصد. بنابراین اگر چندین کوئری را با هم اجرا کنیم، به این صورت میتوان هزینهی هر کدام را نسبت به کل عملیات، تخمین بزنیم. در هر کوئری نیز هزینههایی درج شدهاند که صرفا متعلق به همان کوئری هستند. برای مثال در اولین کوئری، key lookup سنگینترین عملگر کل کوئری است.