Execution Plan در SQL Server چیست

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

Execution Plan چگونه ایجاد می‌شود؟

زمانی که یک کوئری به SQL Server ارسال می‌شود، ابتدا وارد بخش Query Optimizer می‌شود. Optimizer چندین روش مختلف برای اجرای کوئری را بررسی می‌کند و هزینه تقریبی هر روش را بر اساس عواملی مانند موارد زیر محاسبه می‌کند:

    1. تعداد رکوردها .

    2. آمار ( Statistics )

    3. ایندکس های موجود .

    4. میزان I/O

    5. مصرف CPU

    6. مصرف حافظه

در نهایت، پلنی که کمترین هزینه تخمینی را داشته باشد به عنوان Execution Plan انتخاب می‌شود. به همین دلیل ممکن است دو کوئری که نتیجه یکسانی تولید می‌کنند، دارای Execution Plan کاملاً متفاوتی باشند .

چرا Execution Plan اهمیت دارد؟

Execution Plan مهم‌ترین ابزار برای تحلیل عملکرد کوئری‌ها است. به کمک آن می‌توانید موارد زیر را شناسایی کنید:

    1. دلیل کند بودن یک کوئری 

    2. علت استفاده نشدن از ایندکس

    3. عملیات Scan و Seek

    4. ترتیب اجرای Join ها

    5. میزان هزینه هر عملیات

    6. هشدارهای مربوط به اجرای کوئری

    7. حجم داده ای که بین Operator ها منتقل می شود 

به همین دلیل تقریباً تمام متخصصان Performance Tuning اولین قدم برای تحلیل یک کوئری را بررسی Execution Plan می‌دانند.

Execution Plan چه اطلاعاتی در اختیار ما قرار می‌دهد؟

هر Execution Plan مجموعه‌ای از Operatorها را نمایش می‌دهد که هر کدام مسئول انجام بخشی از پردازش کوئری هستند. 

برای هر Operator اطلاعات مختلفی نمایش داده می‌شود، از جمله :

    1. نوع عملیات انجام‌شده

    2. هزینه تخمینی (Estimated Cost)

    3. تعداد ردیف‌های تخمینی

    4. تعداد ردیف‌های واقعی (در Actual Execution Plan)

    5. میزان حافظه تخصیص‌یافته

    6. نحوه انتقال داده بین Operatorها

    7. هشدارهای مربوط به اجرای کوئری

همین اطلاعات به شما کمک می‌کنند علت اصلی مشکلات عملکردی را پیدا کنید.

Execution Plan چه کاربردی در Performance Tuning دارد؟

تقریباً تمام فرآیندهای بهینه‌سازی کوئری در SQL Server بر پایه تحلیل Execution Plan انجام می‌شود. برای مثال می‌توان از آن برای موارد زیر استفاده کرد:

    1. شناسایی Table Scanهای غیرضروری

    2. بررسی Index Scan و Index Seek

    3. تحلیل Nested Loops، Hash Match و Merge Join

    4. پیدا کردن Bottleneckهای کوئری 

    5. بررسی اختلاف بین Estimated Rows و Actual Rows

    6. شناسایی Memory Grantهای نامناسب

    7. بررسی هشدارهای Optimizer

به همین دلیل یادگیری Execution Plan یکی از مهم‌ترین مهارت‌های هر DBA و برنامه‌نویس SQL Server محسوب می‌شود.

Execution Plan چه زمانی تولید می‌شود؟

Execution Plan در مرحله Compilation توسط Query Optimizer ساخته می‌شود.اگر SQL Server تشخیص دهد که پلن تولیدشده در آینده نیز قابل استفاده است، آن را داخل Plan Cache ذخیره می‌کند تا دفعات بعد نیازی به تولید مجدد آن نباشد.استفاده مجدد از پلن باعث کاهش مصرف CPU و افزایش سرعت اجرای کوئری می‌شود.

آیا Execution Plan همیشه بهترین انتخاب است؟

خیر.

Execution Plan بهترین پلنی است که Optimizer با اطلاعات موجود در زمان کامپایل می‌تواند تولید کند. اگر اطلاعات آماری (Statistics) قدیمی باشند یا داده‌های جدول تغییر زیادی کرده باشند، ممکن است Optimizer پلن مناسبی انتخاب نکند. در چنین شرایطی معمولاً مشکلاتی مانند موارد زیر مشاهده می‌شود:

    1. استفاده از Scan به جای Seek

    2. تخصیص بیش از حد حافظه

    3. انتخاب Join نامناسب

    4. افزایش زمان اجرای کوئری

به همین دلیل تحلیل Execution Plan باید در کنار بررسی Statistics، Indexها و شرایط واقعی اجرای کوئری انجام شود.

جمع‌بندی :

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