SQL Server Execution Plans راهنمای بهینه سازی کوئری

رمزگشایی از Execution Plans : راهنمای جامع بهینه‌سازی کوئری 

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

نقشه اجرایی چیست؟

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

به طور کلی، دو نوع نقشه اجرایی اصلی وجود دارد:

• نقشه اجرایی تخمینی (Estimated Execution Plan): این نقشه قبل از اجرای واقعی کوئری توسط بهینه‌ساز کوئری تولید می‌شود و نشان‌دهنده برنامه‌ای است که بهینه‌ساز قصد دارد برای اجرای کوئری استفاده کند. این نقشه بر اساس آمارهای موجود در پایگاه داده تولید می‌شود.
• نقشه اجرایی واقعی (Actual Execution Plan): این نقشه پس از اجرای واقعی کوئری تولید می‌شود و اطلاعات دقیق و واقعی از نحوه اجرای کوئری، شامل تعداد ردیف‌های بازگردانده شده، زمان اجرا و هزینه‌های واقعی هر مرحله را ارائه می‌دهد. این نوع نقشه برای عیب‌یابی دقیق‌تر بسیار مفید است.

چگونه نقشه‌های اجرایی را تولید کنیم؟

در SQL Server Management Studio (SSMS)، چندین راه برای تولید و مشاهده نقشه‌های اجرایی وجود دارد. این روش‌ها به شما کمک می‌کنند تا به راحتی به جزئیات عملکرد کوئری‌های خود دسترسی پیدا کنید:

برای مشاهده نقشه اجرایی تخمینی، می‌توانید از یکی از روش‌های زیر استفاده کنید:

• از طریق منوی Query، گزینه Display Estimated Execution Plan را انتخاب کنید.
• روی نوار ابزار، روی آیکون Display Estimated Execution Plan کلیک کنید (معمولاً شبیه یک نقشه با علامت + است).
• کلیدهای میانبر Ctrl + L را فشار دهید.

برای مشاهده نقشه اجرایی واقعی، که پس از اجرای کوئری اطلاعات دقیق‌تری را فراهم می‌کند، از روش‌های زیر استفاده کنید:

• از طریق منوی Query، گزینه Include Actual Execution Plan را انتخاب کنید (باید قبل از اجرای کوئری انتخاب شود).
• روی نوار ابزار، روی آیکون Include Actual Execution Plan کلیک کنید (شبیه یک نقشه با علامت چک است).
• کلیدهای میانبر Ctrl + M را فشار دهید. پس از انتخاب این گزینه، باید کوئری را به طور عادی اجرا کنید (با F5 یا کلیک بر روی Execute).

در ادامه، از پایگاه داده نمونه AdventureWorks2019 استفاده خواهیم کرد. اگر این پایگاه داده را ندارید، می‌توانید آن را از وب‌سایت مایکروسافت دانلود و نصب کنید.

فرض کنید کوئری زیر را داریم:

USE AdventureWorks2019;
GO
SELECT
    SalesOrderID,
    OrderDate,
    AccountNumber,
    TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2014-01-01' AND '2014-01-31';

پس از اجرای این کوئری با فعال کردن Include Actual Execution Plan، یک تب جدید به نام “Execution plan” در کنار تب “Results” و “Messages” ظاهر می‌شود که نقشه اجرایی واقعی را به صورت بصری نمایش می‌دهد.

خواندن یک نقشه اجرایی

یک نقشه اجرایی بصری، از مجموعه‌ای از اپراتورها (Operators) و فلش‌ها (Arrows) تشکیل شده است. هر اپراتور یک عملیات خاص را نشان می‌دهد (مانند خواندن داده، مرتب‌سازی یا فیلتر کردن) و فلش‌ها نشان‌دهنده جریان داده بین این اپراتورها هستند. داده‌ها همیشه از راست به چپ و از بالا به پایین جریان دارند.

هنگامی که ماوس را روی یک اپراتور نگه می‌دارید، یک پنجره پاپ‌آپ (tooltip) ظاهر می‌شود که جزئیات مهمی را در مورد آن اپراتور ارائه می‌دهد. این جزئیات شامل موارد زیر است:

• Logical Operation: نام عملیات منطقی (مانند Table Scan, Index Seek).
• Physical Operation: نام عملیات فیزیکی (معمولاً همانند عملیات منطقی است، مگر در موارد خاص).
• Estimated Row Count: تعداد ردیف‌های تخمینی که از این اپراتور عبور می‌کنند.
• Actual Row Count: تعداد ردیف‌های واقعی (فقط در نقشه‌های واقعی).
• Estimated Row Size: اندازه تخمینی ردیف‌ها.
• Estimated I/O Cost: هزینه تخمینی عملیات ورودی/خروجی.
• Estimated CPU Cost: هزینه تخمینی عملیات CPU.
• Estimated Subtree Cost: هزینه تخمینی کل زیردرخت از این نقطه به پایین.
• Parallelism: آیا عملیات به صورت موازی اجرا شده است یا خیر.
• Predicate: شرط فیلتر اعمال شده توسط این اپراتور.

این اطلاعات، به ویژه Predicate و مقادیر Cost، برای درک اینکه کدام بخش از کوئری بیشترین منابع را مصرف می‌کند، حیاتی هستند.

اپراتورهای کلیدی در نقشه‌های اجرایی

درک اپراتورهای رایج برای تجزیه و تحلیل موثر نقشه‌های اجرایی ضروری است. در اینجا به برخی از مهم‌ترین اپراتورها می‌پردازیم:

۱. Table Scan (اسکن جدول)

این اپراتور زمانی ظاهر می‌شود که SQL Server مجبور است برای یافتن ردیف‌های مورد نظر، کل یک جدول را اسکن کند. این بدترین نوع دسترسی به داده است و معمولاً نشان‌دهنده عدم وجود ایندکس مناسب یا عدم استفاده صحیح از آن است.

(Table Scan)

این اپراتور معمولاً دارای هزینه بالایی است و باید تا حد امکان از آن اجتناب شود.

۲. Index Scan (اسکن ایندکس)

این اپراتور زمانی رخ می‌دهد که SQL Server مجبور است تمام صفحات یک ایندکس (غیر کلاستر) را برای یافتن ردیف‌های مورد نظر اسکن کند. این کمی بهتر از Table Scan است، زیرا فقط داده‌های ایندکس را می‌خواند که معمولاً کوچک‌تر از کل جدول است، اما همچنان می‌تواند گران باشد.

(Index Scan (Nonclustered))

این عملیات زمانی بهینه است که درصد زیادی از ردیف‌های ایندکس باید بازیابی شوند.

۳. Index Seek (جستجوی ایندکس)

Index Seek یکی از کارآمدترین راه‌های دسترسی به داده‌هاست و نشان‌دهنده استفاده بهینه از ایندکس است. SQL Server مستقیماً به ردیف‌های خاص در ایندکس می‌رود، بدون اینکه نیازی به اسکن کل ایندکس باشد. این اتفاق زمانی می‌افتد که کوئری شما از ستون(هایی) استفاده می‌کند که در ایندکس وجود دارند و شرط WHERE شامل یک شرط جستجو (مانند =، >، <، BETWEEN) روی آن ستون‌ها است.

(Index Seek (Nonclustered))

این اپراتور معمولاً هزینه بسیار پایینی دارد و هدف اصلی بهینه‌سازی کوئری‌هاست.

۴. Clustered Index Scan (اسکن ایندکس کلاستر)

یک ایندکس کلاستر، در واقع، خود داده‌های فیزیکی جدول را ذخیره می‌کند. Clustered Index Scan به معنای اسکن کامل کل داده‌های جدول است که به ترتیب کلید کلاستر مرتب شده‌اند. اگرچه از نظر منطقی شبیه Table Scan است، اما از نظر فیزیکی، داده‌ها به ترتیب مرتب شده‌اند که در برخی سناریوها می‌تواند کارآمدتر باشد.

(Clustered Index Scan (Clustered))

این اپراتور زمانی دیده می‌شود که یک ایندکس کلاستر وجود دارد اما کوئری نیاز به خواندن بخش بزرگی یا تمام داده‌ها از آن ایندکس کلاستر دارد.

۵. Clustered Index Seek (جستجوی ایندکس کلاستر)

این بهترین نوع دسترسی به داده‌هاست. Clustered Index Seek به معنای دسترسی مستقیم و سریع به ردیف‌های خاصی از جدول از طریق کلید ایندکس کلاستر است. این اپراتور زمانی ظاهر می‌شود که شرط WHERE بر روی ستون یا ستون‌های کلید کلاستر اعمال شده باشد و به SQL Server اجازه می‌دهد تا مستقیماً به مکان فیزیکی داده‌ها برود.

(Clustered Index Seek (Clustered))

این اپراتور نشان‌دهنده بالاترین سطح بهینه‌سازی در دسترسی به داده‌هاست.

۶. Bookmark Lookup (جستجوی بوک‌مارک)

این اپراتور زمانی رخ می‌دهد که SQL Server از یک ایندکس غیر کلاستر برای یافتن آدرس ردیف‌های مورد نظر استفاده می‌کند و سپس برای بازیابی بقیه ستون‌هایی که در ایندکس غیر کلاستر پوشش داده نشده‌اند، به ایندکس کلاستر یا هیپ (heap) اصلی برمی‌گردد. این یک عملیات نسبتاً گران است زیرا شامل دو مرحله دسترسی به دیسک است: یکی برای ایندکس غیر کلاستر و دیگری برای داده‌های اصلی.

(Bookmark Lookup (Nonclustered))

بهینه‌سازی برای حذف Bookmark Lookup معمولاً با افزودن ستون‌های مورد نیاز به ایندکس غیر کلاستر (به عنوان ستون‌های شامل یا Included Columns) انجام می‌شود تا ایندکس به یک ایندکس پوششی (Covering Index) تبدیل شود.

۷. Sort (مرتب‌سازی)

اپراتور Sort نشان می‌دهد که SQL Server نیاز به مرتب‌سازی داده‌ها برای برآورده کردن شرط ORDER BY در کوئری یا برای پشتیبانی از اپراتورهای دیگر (مانند Merge Join یا Group By) دارد. عملیات مرتب‌سازی می‌تواند بسیار پرهزینه باشد، به خصوص برای مجموعه داده‌های بزرگ، زیرا ممکن است نیاز به استفاده از دیسک (tempdb) داشته باشد.

(Sort)

اگر مرتب‌سازی مکرراً در کوئری‌های مهم شما ظاهر می‌شود، ممکن است نیاز به ایجاد ایندکس مناسب با ستون‌های مرتب‌سازی داشته باشید.

۸. Hash Match (مقایسه هش)

این اپراتور برای انجام عملیات جوین (JOIN)، تجمیع (Aggregation) یا عملیات مربوط به متمایز کردن (DISTINCT) استفاده می‌شود. Hash Match معمولاً زمانی انتخاب می‌شود که یکی از جداول ورودی کوچک‌تر باشد یا حافظه کافی برای ساخت یک جدول هش از ورودی کوچک‌تر در دسترس باشد. این اپراتور می‌تواند برای مجموعه داده‌های بزرگ، حافظه و CPU زیادی مصرف کند.

(Hash Match (Inner Join))

مشاهده Hash Match در نقشه اجرایی برای جوین‌ها طبیعی است، اما اگر با هشدار سرریز حافظه (spill to disk) همراه باشد، نشان‌دهنده مشکل در عملکرد است.

۹. Merge Join (جوین ادغامی)

Merge Join یک اپراتور کارآمد برای جوین کردن دو ورودی مرتب‌شده است. این اپراتور به سرعت در دو مجموعه داده مرتب شده حرکت می‌کند و ردیف‌های تطبیق‌یافته را پیدا می‌کند. اگر ورودی‌ها از قبل مرتب نشده باشند، SQL Server ممکن است یک اپراتور Sort را قبل از Merge Join اضافه کند که می‌تواند هزینه‌بر باشد.

(Merge Join (Inner Join))

این اپراتور به ویژه برای جداول بزرگ که بر روی ستون‌های جوین ایندکس‌های مرتب‌شده دارند، عملکرد خوبی دارد.

۱۰. Nested Loops Join (جوین حلقه‌های تودرتو)

این اپراتور معمولاً برای جوین کردن یک جدول کوچک با یک جدول بزرگ استفاده می‌شود. برای هر ردیف از ورودی خارجی (outer input)، SQL Server تمام ردیف‌های منطبق را در ورودی داخلی (inner input) جستجو می‌کند. اگر ورودی داخلی به خوبی ایندکس شده باشد، Nested Loops Join می‌تواند بسیار کارآمد باشد، به ویژه برای بازیابی تعداد کمی از ردیف‌ها.

(Nested Loops (Inner Join))

اگر ورودی داخلی فاقد ایندکس مناسب باشد، این اپراتور می‌تواند منجر به Table Scan یا Index Scan‌های مکرر شود و هزینه بالایی پیدا کند.

نتیجه‌گیری

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

تمرین مشاهده و تحلیل نقشه‌های اجرایی برای کوئری‌های مختلف، بهترین راه برای تسلط بر این ابزار قدرتمند است. به یاد داشته باشید که هدف نهایی، نه فقط تولید یک کوئری که کار می‌کند، بلکه تولید یک کوئری کارآمد است.