در این مقاله در ابتدا به بررسی پروسه اجرا کوئری در SQL Server پرداخته شده و به این سوال به طور خلاصه جواب می دهیم که چه اقداماتی صورت میگیره که یک کوئری اجرا بشه. و در نهایت به بررسی این که Optimizer در SQL Server چه کاری انجام میده یا به عبارت دیگه Query Plan چطور ایجاد میشه! بریم سر اصل مطلب!

سه گام اصلی در اجرای یک کوئری وجود داره که عبارتند از:

• Parsing
• Binding
• Optimization

Parsing

کوئری در قدم اول طی فرآیندی، به نام Algebrizer توسط Relational Engine خونده میشه (یعنی parse میشه) و در هر بار اجرای پلن از Storge Engine درخواست دیتاهای مورد نیاز برای ایجاد پلن رو میکنه. علاوه براین Algebrizer پس از parse کردن چک میکنه که آیا syntax خطایی داره یا نه و اگر خطا داشته باشه فرآیند رو بلافاصله متوقف میکنه. بعد از این که validation کوئری انجام شد؛ parse tree ایجاد میشه که در مرحله بعد مورد استفاده قرار میگیره. اما خود parse tree سلسه مراتبی از عناصر موجود در کوئریه که توسط parser ایجاد میشه و نشون دهنده روابط بین هر کدوم از عناصر کد هستش. نمونه ای شماتیک از parser tree  رو در تصویر زیر مشاهده می کنید.

Binding

با استفاده از نتایج گام قبلی و به کمک parse tree آبجکت های مورد نظر برای ایجاد پلن و اجرای کوئری قراره در این مرحله آماده و مشخص بشه. آبجکت هایی از جمله index ها، table ها و ستون ها و فیلدهایی که در group by  قراره استفاده شوند و همچنین نوع data type ها هم برای پردازش مشخص میشه و موارد دیگر. حتی امکان ایجاد تغییراتی در syntax توسط algebrizer  هم در این بخش وجود داره. بعد از انجام این مرحله هم یک structure کامل تر از مرحله قبل به نام query processor tree ایجاد میشه که در optimizer قراره از اون استفاده بشه.

Optimization

و اما خود Optimization که تشکیل شده از بخش های متفاوتیه. در تصویر زیر موارد اصلی برای ساخت یک پلن نشون داده شده.

Simplification

قسمت اول این پروسه با نام Simplification، که وظیفش مطمئن شدن از این موضوعه که تمام عناصری که در کوئری به اون ها اشاره کردید مورد استفاده قرار می گیرند یا خیر؟! به طور مثال اگر شما در کوئری چهار جدول رو join زده باشید، optimizer چک میکنه که آیا از این چهار جدول استفاده شده یا خیر؟ اگر مثلا در select یا where یا قسمت های دیگه کوئری فقط از دو جدول از چهار جدول استفاده شده از پردازش اون دو جدول صرف نظر میشه! به همین خاطر به این قسمت simplify یا هرس کردن یا ساده سازی سینتکس میگن.

Trivial Plan Match

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

Optimization Phases

اگر قسمت قبل به نتیجه نرسه، در صورتی که پلنی در قسمت قبل پیدا نشه، Optimizer وارد سه فاز ایجاد پلن میشه.

• Search 0 or Transaction: شامل کوئری های ساده با چند join و تغییرات کم (معمولا در OLTP)
• Search 1 or Quick Plan: شامل کوئری ها با عملیات های نسبتا پیچیده و تغییرات زیاد در ساختار داده
• Search 2 or Full Optimization: شامل کوئری های پیچیده! (در این فاز امکان تبدیل از serial plan به parallel plan وجود دارد)

براساس این که کوئری در کدوم فاز قرار میگیره Optimizer پلن ها رو ایجاد میکنه. به طور خلاصه این تصمیم با استفاده از داده های جمع آوری شده در مراحل قبل انجام میشه. از جمله با کمک statistics ها و index های مورد استفاده و constraint ها و غیره که با استفاده از اون ها میزان استفاده از منابع مشخص میشه.

One to n Execution Plans

پروسه قسمت قبل برای پلن های متفاوت صورت میگیره و استراتژی های متفاوت تست میشه و در نهایت براساس هزینه های ایجاد کننده در هر پلن تصمیم گیری میشه. برخی از این محاسبات رو میتونیم در بخش های زیر مشاهده کنیم:

 

Qualify Parallel Plan

در صورتی که کوئری ها به اندازه کافی پیچیدگی داشته باشند امکان استفاده از اجرای parallel مورد بررسی قرار میگیره. شرط لازم اجرای parallel اینه که بیشتر از یک واحد CPU برای اجرا کوئری در اختیار SQL Server باشه. که ساده ترین راه برای کنترل این موضوع در کوئری ها استفاده از MAXDOP هستش به این صورت:

به صورت پیش فرض این مقدار برابر 0 هستش که یعنی SQL Server از تمامی processor های موجود استفاده میکنه. البته که داستان مفصل تری داره!

Execution Plan Caching

و در نهایت plan منتخب، برای اجراهای بعدی در مموری ذخیره میشه و با این کار از انجام پروسه هزینه زا optimization در دفعات بعدی اجرا، جلوگیری میشه. البته که plan cache ها امکان داره به وسیله پروسه های متفاوتی پاک و حذف شوند. یکی از این پروسه ها aging نام داره. plan chache ها بخشی از buffer cache هستن که این بخش data page ها رو هم نگه داری می کنه. اضافه شدن پلن های جدید باعث میشه حجم بیشتری از فضای بافر اشغال بشه که به همین خاطر SQL Server اقدام به پاک کردن پلن هایی میکنه که مدت زیادیه استفاده نشدن و با این کار فضای مورد نیازه رو مدیریت میکنه.

و این بود خلاصه ای از نحوه کار Optimizer!