وحید نصیری وحید نصیری
بررسی کارآیی کوئری‌ها در SQL Server - قسمت پنجم - خواندن Query Plans
اندازه‌ی قلم متن
تخمین مدت زمان مطالعه‌ی مطلب: هفت دقیقه
برای هر کوئری که به SQL Server ارسال می‌شود، یک Plan تولید خواهد شد. این عملیات نیز توسط بخش Query Optimizer آغاز می‌گردد. به آن می‌توان همانند فریم‌ورکی که درون SQL Server قرار گرفته و کارش یافتن یک Query Plan مناسب مخصوص کوئری رسیده‌است، نگاه کرد. ابتدا عملیات Parsing صورت می‌گیرد. توسط آن Syntax کوئری رسیده بررسی شده و صحت آن تائید می‌گردد. پس از آن یک Parser tree تولید می‌شود که نمای درونی آن کوئری است. سپس فاز Binding رخ می‌دهد که در آن بررسی می‌شود که آیا تمام اشیاء موجود درخواستی توسط کوئری وجود داشته و توسط کاربر قابل دسترسی هستند. خروجی این فاز یک Query Tree است که به فاز بهینه سازی ارسال می‌شود. یک Query Tree به همراه اعمالی منطقی است. این اعمال منطقی توصیف رخ‌دادهایی می‌باشند که قرار است اتفاق بیفتند؛ مانند خواندن اطلاعات از یک جدول، مرتب سازی اطلاعات، ایجاد جوین و غیره. سپس بهینه ساز، این اعمال منطقی را تبدیل به اعمال فیزیکی می‌کند. برای مثال خواندن اطلاعات از یک جدول، تبدیل به یک Index seek می‌شود. یک جوین تبدیل به یک حلقه‌ی تو در تو می‌شود. در آخر این اعمال فیزیکی در کنار هم قرار گرفته و Query Plan را تشکیل می‌دهند و ما به عنوان یک توسعه دهنده می‌توانیم با بررسی این Plan دریابیم که SQL Server با کوئری رسیده، چگونه برخورد کرده و قرار است چگونه آن‌را اجرا کند.


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
نتیجه‌ی آن تولید Query Plan زیر است:


در اینجا چهار عملگر 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);
ستون آخر این کوئری به query_plan اشاره می‌کند که در management studio به صورت یک لینک قابل کلیک ظاهر می‌شود. اگر بر روی آن کلیک کنیم، به تصویر زیر خواهیم رسید:


همانطور که مشاهده می‌کنید، اینبار تنها اطلاعات تخمینی در این 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 خواهیم رسید که نوع بررسی ایندکس‌ها و جوین آن متفاوت است:


در اینجا با کلیک راست بر روی 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
به این Plan خواهیم رسید که نکته‌ی مهم آن، هزینه‌ی انجام کوئری‌ها است:


هزینه‌ی اولین کوئری نسبت به کل batch جاری، 10 درصد است و هزینه‌ی دومین کوئری، 90 درصد. بنابراین اگر چندین کوئری را با هم اجرا کنیم، به این صورت می‌توان هزینه‌ی هر کدام را نسبت به کل عملیات، تخمین بزنیم. در هر کوئری نیز هزینه‌هایی درج شده‌اند که صرفا متعلق به همان کوئری هستند. برای مثال در اولین کوئری، key lookup سنگین‌ترین عملگر کل کوئری است.
‫۵ سال و ۲ ماه قبل، چهارشنبه ۱۲ تیر ۱۳۹۸، ساعت ۱۸:۰۵
۲۹۵۸
۰
SQL Server
مطالب مشابه