روزانه در دنیای SQL Server سوال های تکراری بارها و بارها مطرح می شه، چرا کوئری کند اجرا شد؟ آیا ایندکس های ایجاد شده به درستی استفاده میشه؟ چرا ایندکس استفاده نشد؟ چرا این کوئری قبلا سریعتر اجرا می شد؟ پاسخ درست در هر مورد می تونه متفاوت باشه، اما آیا تا به حال برای پیدا کردن پاسخ سوال های تکراری نگاهی به کوئری پلن ( Execution Plan ) انداخته اید؟
به زبان ساده، کوئری پلن نتیحه تلاش Query optimizer برای محاسبه و پاسخگویی در بهینه ترین حالت ممکن به درخواست کوئری ارسال شده هست.
Execution plan میتونه به شما بگه که کوئری قراره چجوری اجرا بشه یا کوئری چجوره اجرا شده. بنابراین کوئری پلن راهکار اولیه DBA ها برای بررسی کوئری با کارایی پایین هست. به این وسیله می تونین حدس بزنین که چرا کوئری هزاران اسکن انجام داده و I/O رو به میزان زیادی بالا برده.
به کمک Execution Plan می تونین بفهمیم که دقیقا کدوم بخش کوئری ناکارآمده و دلیل ایجاد کننده مشکل چیه. مثلا ممکنه کوئری در حال اسکن تمام جدول باشه، در صورتی که می تونه با یک ایندکس کم حجم دیتا لازم رو استخراج کنه، با ایندکس مناسب، میتونه فقط Row هایی که مورد نیازه برگردونه. همه اینها به همراه کلی اطلاعات دیگه در Execution plan نمایش داده می شه.
هدف از این مطلب ایجاد توانایی درک اولیه و بررسی actual و estimated execution plan در حالت های گرافیکی، متنی و XML هست، برای این هدف ما عناوین زیر را دنبال خواهیم کرد:
وقتی شما درخواست اجرا کوئری رو به SQL Server database ارسال می کنین، چندین process مشغول به کار برروی کوئری می شه. هدف تمام این پردازش ها مدیرت سیستم برای برگرداندن دیتا درخواستی کوئری به شما و یا ذخیره کردن آن خواهد بود که باید تا جای ممکن به موقع و با حفظ صحت دیتا انجام بشه.
این فرآیند برای هر کوئری اجرا شده در سیستم طی خواهد شد. در حالی که هر یک از آنها تعداد زیادی عملیات متفاوت در SQL Server را شامل می شن، ما قصد داریم برروی پردازش های مربوط به T-SQL تمرکز کنیم. این پردازش ها به دو گروه تقسیم می شه:
در مرحله relational engine کوئری اول Parse شده و سپس توسط Query Optimizer پردازش می شه تا کوئری پلن مورد نیاز رو بسازه. سپس پلن در فرمت باینری به Storage Engine ارسال می شه تا داده ها رو بازیابی یا بروزرسانی کنه.
storage engine مربوط به پردازش های مانند locking ،index maintenance transactions هست. با توجه به اینکه کوئری پلن در مرحله relational engine ایجاد میشه ما برروی بخش اول تمرکز خواهیم داست و به بخش دوم نمی پردازیم.
هنگامی که شما کوئری T-SQL را به سیستم SQL Server ارسال می کنید، اولین جایی که میره relational engine هست.
به محض رسیدن T-SQL، چک میکنه که آیا کوئری به درستی نوشته شده یا نه. این مرحله رو query parsing و خروجی حاصل از پردازش Parser رو Parse Tree یا Query tree می گن. Parse tree بیان کننده مراحل منطقی ضروری هست که باید برای کوئری اجرا بشه.
اگر کوئری ارسال شده شامل (DML ( data manipulation language نباشه، نیازی به بهینه سازی اون نخواهد بود. چرا که برای مثال، یک راه بیشتر برای ایجاد جدول در دیتابیس SQL Server وجود نداره در نتیجه هیچ شانسی برای بهبود این کوئری در مرحله Optimize نخواهد داشت.
اما اگر کوئری T-SQL یه DML باشه، درخت Parse به مرحله با نام algebrizer میره. algebrizer درواقع تمام نام ها و Object ها رو مشخض می کنه، اینکه دقیقا نام جدول و نام ستون ذکر شده در کوئری در کجای دیتابیس قرار داره، آیا به صورت Synonyms هست و باید از دیتابیس دیگه ای منتقل بشه.
همچنین محل Aggregation ها (مثل max ,order by ) و انواع type ها (مثل varchar و nvarchar) رو تعیین می کنه.
خروجی algebrizer ازنوع باینری خواهد بود که query processor tree گفته می شه و این نتیجه به Query Optimizar فرستاده می شه.
با بکارگیری query processor tree که در مرحله قبل بدست اومده و Statictic هایی که درباره دیتا وجود داره با ایجاد مدل مورد نیاز، Query Optimizer اقدام به ایجاد کوئری پلنی می کنه که به نظرش بهینه ترین پلن ممکن هست.
به زبان ساده، Query Optimizer تصمیم میگیره که درخواست کوئری T-SQL ارسال شده را به چه روشی پیاده سازی کنه. در واقع تصمیم میگیره که آیا می تونه به دیتا از طریق ایندکس دسترسی داشته باشه، باید چه نوعی از join ها رو استفاده کنه و هر چیز دیگه.
تصمیماتی که Query Optimizer میگیره برمبنای محاسبه هزینه هریک از کوئری پلن ها، میزان CPU لازم برای پردازش و میزان I/O مورد نیاز خواهد بود و اینکه چقدر سریع می تونه اون پلن اجرا بشه، این مفهوم با عنوان cost-based plan شناخته می شه.
Optimizer می تونه چندین پلن ایجاد و برای هر کدوم محاسبه هزینه کنه (همچنین پلن های کش شده رو از قبل داره) و از میان اونها کم هزینه ترین رو انتخاب کنه. یعنی کوئری پلنی رو که درسریع ترین زمان ممکن اجرا می شه و حداقل مقدار از منابع CPU و I/O رو مصرف می کنه.
محاسبه زمان اجرا خودش شامل هزینه پردازش جداگانه CPU هست. گاهی اوقات Optimizer احساس می کنه، محاسبه تعداد زیادی کوئری پلن خودش پرهزینه هست لذا از ادامه این کار صرف نظر کرده و از کوئری پلن های محاسبه شده موجود بهینه ترین رو انتخاب می کنه.
اگر شما کوئری ساده ای رو ارسال کرده باشید، برای مثال یک جدول بدون ایندکس و بدون محاسبات، در اینصورت Optimizer بدون صرف زمان برای محاسبه کوئری پلن های ممکن یک trivial plan رو انتخاب و برای اجرا در نظر می گیره.
در غیر این صورت Optimizer محاسبات cost-based رو برای انتخاب پلن بهینه انجام خواهد داد و برای اینکار نیاز به statistic هایی داره که قبلا در SQL Server محاسبه و نگهداری شده اند.
Statistic ها به ازاء column ها و index ها در دیتابیس جمع آوری میشن و شامل اطلاعات distribution ،uniqueness و selectivity دیتا هستند. این اطلاعاتی که Statistic را بوجود میارن توسط histogram نگهداری می شه، در واقع این ها دیتاهایی هستند در باره دیتا موجود و به Optimizer کمک می کنن تا محسبات را انجام بده.
اگر Statistics برای یک ستون یا ایندکس وجود داشته باشه، Optimizer از اونها برای انجام محاسبات استفاده می کنه. Statistic ها به صورت پیش فرض به ازاء تمام ایندکس ها و جداول استفاده شده توسط کاربر در کوئری های شامل WHERE و JOIN به وجود اومده و هربار بروزرسانی می شوند.
Table variable ها شامل این روند نبوده و برای آنها Statistics به وجود نمیاد، لذا اونها همیشه توسط Optimizer به عنوان single row در نظر گرفته می شن. (این موضوع در بررسی قابلیت های SQL Server 2019 بیان شد و از نسخه 2019 به بعد این روند تغییر کرده و Optimizer عملکرد بهتری داره). Temporary table ها دارای Statistic هستند و دیتا اونها در Histogram جدول اصلی برای استفاده Optimizer ذخیره می شه.
Optimizer این statistic ها را در طول query processor tree بکار برده و تشخیص بهترین کوئری پلن را انجام میده. به این معنی که با ساخت کوئری پلن های متنوع join ها، index ها و هر چیز متفاوتی را امتحان میکنه تا به چیزی که فکر میکنه بهینه ترین حالت ممکنه برسه.
در طول این محاسبات اعدادی به هریک از مراحل پلن تخصیص داده می شه که بیان کننده حدس Optimizer در مورد هزینه Time هر مرحله است. این کار estimated cost به ازاء هر مرحله نامیده می شه. در نهایت مجموع هزینه های مراحل و هزینه Execution plan را مشخص خواهد کرد.
خیلی مهمه که در نظر داشته باشیم estimated cost فقط یه حدس براساس آمار موجود هست. در صورت داشتن زمان نامحدود و Statistic های بروز، Optimizer میتونست واقعا بهترین پلن رو انتخاب کنه.
در هر حال روش حدس زدن هزینه و انتخاب کوئری پلن بهینه در زمان محدود از میان کوئری پلن های موجود روش موثر و مفیدیه، اما همیشه به واقعیت نزدیک نیست.
زمانی که Optimizer به کوئری پلن مورد نظرش دست پیدا کنه Execution plan ایجاد میشه و در محلی از memory با نام plan cache ذخیره میشه – مگر اینکه پلن بهینه از قبل در plan cache وجود داشته باشه. در واقع Optimizer برای هر کوئری بین پلن هایی که ایجاد می کنه و پلن هایی که از قبل در Plan cache به ازاء اون کوئری وجود داره بهینه ترین رو انتخاب می کنه.
( این جمله آخر کاملا درست نیست، در صورتی که به ازاء کوئری، پلن کش شده وجود داشته باشه و تغییر جدی در دیتابیس اتفاق نیوفتاده باشه، مثل ایجاد ایندکس در یکی از جداول به کار رفته در کوئری یا تغییر اساسی در Statistic ها، Optimizer بدون محاسبه، سراغ پلن کش شده میره و زمانی برای محاسبه دوباره هدر نمیده).
همانطور که مطرح شد دو مدل execution plan وجود داره، Estimated execution plan بدون اجرا کوئری تولید میشه و در واقع داره خروجی Optimizer رو نشون میده و به همین دلیل Step ها و Operator ها در این مدل پلن با عنوان منطقی نام گذاری میشن . Actual execution plan نوع دوم کوئری پلن هست که با اجرا و قطعی شدن کوئری پلن نمایش داده میشه .
هزینه ایجاد کوئری پلن برای Server بالاس پس SQL Server سعی میکنه تا جایی که ممکنه کوئری ایجاد شده رو مجددا استفاده کنه لذا به محض ایجاد کوئری پلن، اون رو در بخش از مموری به نام plan cache ذخیره میکنه.
موقعی که درخواست اجرا کوئری به سرور ارسال میشه، estimated execution plan توسط Optimizer ساخته میشه ، در صورتی که پلن کش شده قبلی برای این کوئری وجود داشته باشه،Optimizer دو کوئری پلن estimated و actual پلنی که موجود هست رو مقایسه میکنه اگه کوئری پلن مشابه چیزی که estimate شده پیدا شد همون رو به عنوان کوئری پلن منتخب اجرا میکنه و از سخت مجدد کوئری پلن خودداری میکنه که باعث بهبود کارایی و کاهش هزینه های Server میشه.
در صورتی که Optimizer تشخیص بده کوئری پلن میتونه به صورت موازی اجرا بشه ممکنه در صورتی که کوئری پلن قبلی کش شده هم وجود داشته باشه باز کوئری پلن دوم موازی رو هم ایجاد کنه ، در این حالت کوئری شامل بیش از یک کوئری پلن کش شده میشه که در شرایطی ممکنه از هر کدوم استفاده کنه .
Execution plan تا ابد در مموری نگه داری نمیشه، کوئری پلن ها به مرور با توجه به سن بکارگیری آنها طبق محاسبه فرمول age از سیستم خارج میشن . مثلا کوئری با هزینه 10 که 5 بار رفرنس شده مقدار age برابر با 50 داره.
پروسس lazywriter به همراه پردازش های داخلی که برای خالی کردن همه انواع کش کار میکنند (including plan cache ) به طور دوره ای تمامی Object های در کش رو بررسی میکنه و این عدد رو با هر بار استفاده از کوئری پلن افزایش میده .
اگر شرط های زیر برقرار بشه ، پلن از مموری حذف خواهد شد :
کوئری پلن ها مقدس نیستند ، اتفاق یا فعالیت خاصی میتونه پلن Recompile بشه ، مهم است به یاد داشته باشیم بازسازی مجدد کوئری پلن میتونه پرهزینه باشه ، action های زیر میتونن باعث recompile شدن execution plan ها بشه :
از اونجایی که cache نقش موثری در نحوه اجرا Execution plane ایفا میکنه ، شما به ابزارهایی برای کوئری زدن و کار کردن با Plane cache نیاز دارین .اول از همه ، هنگام تست ، نیاز دارین تا بدونین چقدر زمان میبره تا پلن کامپایل بشه یا یک تغییر جزئی میتونه باعث ایجاد پلن متفاوت بشه :
برای پاک کردن کامل پلن کش دستور زیر رو اجرا میکنیم :
برای اینکه بفهمیم چطور Optimizer و Storage engine کوئری پلن رو درست میکنن نیاز هست که به محتویات cache نگاهی بیندازیم .به کمک dynamic management views و dynamic management functions ما میتوانیم به راحتی کوئری دیتا کاملی از Execution plan های موجود در سیستم استخراج کنیم .
SELECT [cp].[refcounts], [cp].[usecounts], [cp].[objtype], [st].[dbid], [st].[objectid], [st].[text], [qp].[query_plan] FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp
در این کوئری میبینم که sql در حال فراخوانی XML plan هست که ایجاد شده توسط Execution آن SQL میتوانیم XML را مستقیم ببینیم یا اون رو به صورت Execution plan گرافیکی باز کنیم .
احتمالا شما هیچ تفاوتی بین این دو نوع کوئری پلن ندیده باشین، اگرچه تغییراتی ممکنه باعث ایجاد تفاوت بین این دو کوئری پلن بشه مثلا وقتی که Statistic ها کهنه باشن.
دلیل عمده تفاوت بین پلن ها تفاوت بین Statistic ها و دیتا واقعی هست. این در طول زمانه ای درج و حذف دیتا رخ میده . این تغییرات باعث تغییر Key value های ایندکس ها و یا تغییر در پراکندگی اونها میشن .
در واقع با مرور زمان Statistic کمتر و کمتر به واقعیت دیتا نزدیک خواهد بود. این نه تنها باعث متفاوت بودن پلن ها میشه بلکه باعث ایجاد کوئری پلن های کند و نا کارآمد هم خواهد شد . در واقع ممکنه شما Execution plan بدی دریافت کنید فقط به این خاطر که Statistic بروزی ندارین .
در برخی Instance ها estimated plan کلا درست کار نمیکنه ، برای مثال برای کد مثال زیر سعی میکنیم تست کنیم :
CREATE TABLE TempTable (Id INT IDENTITY (1 , 1 ),Dsc NVARCHAR (50 ));
INSERT INTO TempTable ( Dsc )
SELECT [Name] FROM [Sales] .[Store] ;
SELECT * FROM TempTable ;
DROP TABLE TempTable ;
شما این خطا رو دریافت خواهید کرد :
Msg 208, Level 16, State 1, Line 7
Invalid object name ‘TempTable’.
Optimizer ، که برای ایجاد Estimated Execution plan استفاده میشه T-SQL اجرا نمیکنه . اون Estimated رو بر اساس Algebrizer همون پروسسی که برای مشخص کردن object های بکار رفته در کوئری انجام میشه تولید میکنه . با توجه به اینکه تو این مرحله کوئری اجرا نشه پس نمیتونه Temporary table وجود داشته باشه و خطا میده حالا اگه برای این کوئری حالت Actual رو اجرا کنیم کار خواهد کرد .
چه موقعی سراغ اجرای موازی میره ؟
در اینجا هم مفهوم حد آستانه وجود داره به این معنی که اگر هزینه اجرا کوئری به عدد خاصی برسه ، کوئری پلن به صورت موازی ایجاد خواهد شد. در واقع به جز کوئری پلن قبلی یک کوئری پلن موازی نیز برای کوئری ایجاد میشه و اینکه کدوم کوئری پلن اجرا بسته به Query engine هست.
پس ممکنه ما کوئری رو در هردو حالت موازی و معمولی ببینیم اما در هنگام اجرا ممنه کوئری متفاوتی ببینم اگر Query engine تشخیص بده که نمیتونه مدل Parallel رو پشتیبانی کنه .
سه فرمت قابل مشاهده برای Execution plan وجود داره :
انتخاب هر کدوم از این فرمت ها بستگی به این داره که چه جزئیاتی رو میخواهیم ببینیم و اینکه ترجیح خود DBA چی هست و کدوم روش براش راحتتره.
برای خواندن سریع و راحته اما جزئیات دیتا در اون قابل مشاهده نیست ، هر دو مدل Actual , Estimated رو میشه در این دید.
کمی خوندنش سختره اما اطلاعات بیشتری به سرعت در دسترسه ،
XML Plan ست کاملی از دیتا رو بصورت ساختار XML نمایش میده.
Execution plan ها برای این هستند تا شما رو کمک کنند کد T-SQL بهینه ای بنویسید ، کد T-SQL موجود رو رفع اشکال کنید و یا برای مانیتور و گزارش کردن سیستم موجود از اون استفاده کنین .
اینکه چگونه از اونها استفاده کنین و چگونه بررسی شون کنین بررسی به خودتون داره ، اما نخست نیاز هست که بتونیم اطلاعات موجود در پلن رو درک و تفسیر کنیم . بهترین راه یاد گرفتن کوئری پلن اینکه در عمل اونها رو مشاهده کنیم، پس شروع میکنیم.
توجه داشته باشین که کوئری هایی که در عمل باهاشون مواجه خواهین شد پیچیده تر از پلن هایی هست که قراره بررسی شون کنیم و این ها نمونه هایی برای آشنایی با نحوه خواندن خواهد بود . دقت کنین ممکن است در سیستم شما کوئری پلن ها با چیزی که در اینجا میبینید متفاوت باشد که میتواند به دلیل شرایط متفاوت سرورها و نسخه های متفاوت sql server باشد . خلاصه خیلی تعجب نکنین اگه نتیجه متفاوتی گرفتین.
منبع :
