افزایش سرعت SQL Server با تحلیل پلن‌های اجرای

افزایش سرعت دیتابیس: راهنمای کامل نمایش و تحلیل پلن‌های اجرای SQL Server

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

برای مشاهده پلن اجرای تخمینی در SQL Server Management Studio (SSMS)، می‌توانید کوئری خود را نوشته و سپس بر روی دکمه “Display Estimated Execution Plan” در نوار ابزار کلیک کنید یا از کلید میانبر `Ctrl + L` استفاده کنید. این پلن قبل از اجرای کوئری ایجاد می‌شود و بر اساس آمار موجود از جداول و ایندکس‌ها، پیش‌بینی SQL Server از نحوه پردازش کوئری را نشان می‌دهد.

برای بدست آوردن پلن اجرای واقعی (Actual Execution Plan)، که بعد از اجرای کوئری تولید می‌شود و شامل اطلاعات زمان اجرا مانند تعداد ردیف‌های واقعی بازگشتی و مصرف منابع است، کافیست کوئری را نوشته و سپس بر روی دکمه “Include Actual Execution Plan” در نوار ابزار کلیک کنید یا از کلید میانبر `Ctrl + M` استفاده کنید و سپس کوئری را اجرا کنید. این پلن اطلاعات بسیار دقیق‌تری برای بهینه‌سازی ارائه می‌دهد.

یکی از روش‌های کلاسیک برای مشاهده پلن‌های اجرا، استفاده از دستورات `SET SHOWPLAN` است. این دستورات پلن را به صورت متنی نمایش می‌دهند و برای اسکریپت‌نویسی یا محیط‌هایی که SSMS در دسترس نیست مفید هستند.

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

SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM Customers WHERE CustomerId = 1;
GO
SET SHOWPLAN_TEXT OFF;
GO

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

دستور `SET SHOWPLAN_ALL` اطلاعات جامع‌تری را در قالب ستون‌های متنی نمایش می‌دهد که شامل جزئیات بیشتری درباره هزینه‌ها و عملیات است:

SET SHOWPLAN_ALL ON;
GO
SELECT OrderId, OrderDate FROM Orders WHERE CustomerId = 10;
GO
SET SHOWPLAN_ALL OFF;
GO

این نیز یک پلن تخمینی است و کوئری را اجرا نمی‌کند.

برای مشاهده پلن اجرای واقعی به همراه آمار زمان اجرا، می‌توانیم از `SET STATISTICS PROFILE` استفاده کنیم. این دستور، کوئری را اجرا کرده و پلن اجرای متنی را همراه با تعداد ردیف‌ها و دفعات اجرای هر اپراتور، در نتایج بازمی‌گرداند.

SET STATISTICS PROFILE ON;
GO
SELECT ProductName, Price FROM Products WHERE CategoryId = 5 ORDER BY Price DESC;
GO
SET STATISTICS PROFILE OFF;
GO

این روش برای تحلیل عمیق‌تر عملکرد هر گام از پلن در حین اجرا بسیار مفید است.

علاوه بر این روش‌ها، می‌توانید پلن‌های اجرا را از کش پلن (Plan Cache) SQL Server بازیابی کنید. SQL Server پلن‌های کوئری‌های اخیر را کش می‌کند تا در اجراهای بعدی از کامپایل مجدد جلوگیری کند. با استفاده از DMV (Dynamic Management Views) می‌توانید این پلن‌ها را استخراج و تحلیل کنید.

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

SELECT
    qs.creation_time,
    qs.last_execution_time,
    qs.execution_count,
    st.text AS query_text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
    st.text LIKE N'%YourQueryText%'; -- جایگزین کنید با بخشی از کوئری مورد نظر

ستون `query_plan` در این خروجی حاوی پلن اجرای XML است که می‌توانید آن را در SSMS باز کرده و به صورت گرافیکی مشاهده کنید. این روش برای شناسایی کوئری‌های پرمصرف که در حال حاضر در کش هستند، بسیار کارآمد است.

برای تجزیه و تحلیل پیشرفته‌تر و جمع‌آوری پلن‌های اجرا در محیط‌های تولید، استفاده از Extended Events (XEvents) SQL Server توصیه می‌شود. XEvents یک سیستم جمع‌آوری داده سبک و مقیاس‌پذیر است که به شما امکان می‌دهد رویدادهای خاصی را در SQL Server، از جمله رویدادهای مربوط به پلن‌های اجرا، مانیتور و ثبت کنید. رویدادهای مانند `query_post_execution_showplan` یا `query_pre_execution_showplan` می‌توانند برای ثبت پلن‌های واقعی یا تخمینی استفاده شوند. ایجاد یک سشن XEvents به شما امکان می‌دهد پلن‌ها را بدون ایجاد سربار قابل توجه، در فایل‌ها یا حافظه ثبت کنید.

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