تحلیل پلن‌های اجرایی با استفاده از Query Store در SQL Server

تحلیل پلن‌های اجرایی با استفاده از Query Store در SQL Server

در پایگاه داده SQL Server، یکی از مهم‌ترین عوامل تأثیرگذار بر سرعت و عملکرد کوئری‌ها، پلن اجرایی (Execution Plan) است.
زمانی که یک دستور SQL اجرا می‌شود، موتور پایگاه داده قبل از اجرای آن، بهترین روش دسترسی به داده‌ها را انتخاب کرده و یک پلن اجرایی ایجاد می‌کند.
با گذشت زمان و تغییر حجم داده‌ها، ایندکس‌ها، آمارها (Statistics) یا حتی نسخه SQL Server، ممکن است پلن‌های اجرایی تغییر کرده و باعث افت عملکرد شوند.
برای مدیریت و تحلیل این تغییرات، مایکروسافت قابلیت قدرتمندی به نام Query Store را در SQL Server معرفی کرده است که امکان ذخیره‌سازی، بررسی و مقایسه پلن‌های اجرایی را فراهم می‌کند.

Query Store چیست؟

Query Store قابلیتی است که از SQL Server 2016 به بعد در دسترس قرار گرفته و اطلاعات مربوط به اجرای کوئری‌ها را در داخل خود پایگاه داده ذخیره می‌کند.
این قابلیت تاریخچه‌ای از کوئری‌ها، پلن‌های اجرایی و آمار عملکرد آن‌ها را نگهداری می‌کند و به مدیران پایگاه داده اجازه می‌دهد رفتار کوئری‌ها را در طول زمان بررسی کنند.

پیش از معرفی Query Store، تحلیل مشکلات عملکردی معمولاً به ابزارهایی مانند Dynamic Management Views (DMVs)، SQL Profiler یا Extended Events وابسته بود.
این ابزارها اطلاعات لحظه‌ای ارائه می‌کردند و در بسیاری از مواقع پس از ریستارت سرویس یا پاک شدن کش، داده‌های مورد نیاز از بین می‌رفتند.
اما Query Store اطلاعات را به صورت دائمی ذخیره کرده و امکان بررسی عملکرد گذشته را نیز فراهم می‌کند.

اهمیت تحلیل پلن‌های اجرایی در  SQL Server

پلن اجرایی نشان می‌دهد SQL Server چگونه یک کوئری را اجرا می‌کند. این پلن شامل جزئیاتی مانند:

• نوع Join مورد استفاده

• نحوه دسترسی به جداول

• استفاده از ایندکس‌ها

• عملیات Sort

• Aggregateها

• هزینه هر بخش از کوئری

در بسیاری از موارد، یک تغییر کوچک در پلن اجرایی می‌تواند باعث شود زمان اجرای یک کوئری از چند میلی‌ثانیه به چند دقیقه افزایش پیدا کند.
به همین دلیل تحلیل پلن‌های اجرایی یکی از مهم‌ترین وظایف DBAها و توسعه‌دهندگان پایگاه داده محسوب می‌شود.

فعال‌سازی Query Store  در  SQL Server

برای فعال کردن Query Store می‌توان از دستور زیر استفاده کرد:

ALTER DATABASE SalesDB

SET QUERY_STORE = ON;

پس از فعال‌سازی، SQL Server به صورت خودکار اطلاعات مربوط به کوئری‌ها و پلن‌های اجرایی را جمع‌آوری و ذخیره می‌کند.

برای مشاهده وضعیت Query Store می‌توان از دستور زیر استفاده کرد:

SELECT actual_state_desc

FROM sys.database_query_store_options;

اطلاعات ذخیره‌شده در Query Store

Query Store اطلاعات متنوعی را ذخیره می‌کند که مهم‌ترین آن‌ها عبارت‌اند از:

• Query Text

  متن کامل کوئری اجراشده.

• Execution Plans

  تمام پلن‌های اجرایی تولیدشده برای هر کوئری.

• Runtime Statistics

  اطلاعاتی مانند:

  • مدت زمان اجرا

  • مصرف CPU

  • تعداد Logical Reads

  • تعداد اجراها

  • مصرف حافظه

• Wait Statistics

  در نسخه‌های جدید SQL Server، اطلاعات مربوط به Waitها نیز ذخیره می‌شود که برای تحلیل گلوگاه‌های عملکردی بسیار مفید است.

مشاهده پلن‌های اجرایی از طریق Query Store

در SQL Server Management Studio می‌توان به مسیر زیر مراجعه کرد:

Database

└ Query Store

└ Top Resource Consuming Queries

در این بخش می‌توان:

• کوئری‌های پرمصرف را مشاهده کرد.
• پلن‌های مختلف یک کوئری را بررسی نمود.
• عملکرد پلن‌ها را با یکدیگر مقایسه کرد.
• روند تغییرات را در بازه‌های زمانی مختلف مشاهده نمود.

این قابلیت یکی از مهم‌ترین ابزارهای عیب‌یابی عملکرد در SQL Server محسوب می‌شود.

شناسایی تغییر پلن اجرایی

یکی از رایج‌ترین مشکلات در SQL Server پدیده Plan Regression است. این مشکل زمانی رخ می‌دهد که SQL Server پلن جدیدی برای یک کوئری انتخاب کند که نسبت به پلن قبلی عملکرد ضعیف‌تری داشته باشد.

فرض کنید یک کوئری همیشه در 200 میلی‌ثانیه اجرا می‌شده است. پس از به‌روزرسانی Statistics یا تغییر حجم داده‌ها، SQL Server پلن جدیدی تولید می‌کند و زمان اجرا به 15 ثانیه افزایش می‌یابد.

با استفاده از Query Store می‌توان:

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

این ویژگی باعث کاهش چشمگیر زمان عیب‌یابی می‌شود.

مقایسه پلن‌های اجرایی

یکی از امکانات ارزشمند Query Store قابلیت Compare Plans است.

در این حالت می‌توان دو پلن مختلف را انتخاب کرده و اختلاف آن‌ها را مشاهده کرد. برخی تفاوت‌های رایج عبارت‌اند از:

• تغییر Index Seek به Index Scan

• تغییر Nested Loop به Hash Join

• تغییر Parallel Execution

• تغییر Memory Grant

• تغییر ترتیب Joinها

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

 

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

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

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

استفاده از گزارش Top Resource Consuming Queries

این گزارش یکی از پرکاربردترین بخش‌های Query Store است.

در این قسمت می‌توان کوئری‌ها را بر اساس معیارهای مختلف مرتب کرد:

• CPU Time

• Duration

• Logical Reads

• Memory Consumption

• Execution Count

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

تحلیل مصرف CPU

گاهی اوقات مشکل اصلی سیستم، مصرف بیش از حد CPU است.

با Query Store می‌توان کوئری‌هایی را پیدا کرد که بیشترین CPU را مصرف می‌کنند.

به عنوان مثال ممکن است مشاهده شود:

SELECT *

FROM Orders

WHERE CustomerName LIKE '%Ali%'

به دلیل استفاده از Wildcard در ابتدای عبارت، SQL Server قادر به استفاده از ایندکس نیست و مجبور به اسکن کامل جدول می‌شود.

تحلیل پلن اجرایی این موضوع را به وضوح نشان می‌دهد.

تحلیل Logical Reads

یکی دیگر از شاخص‌های مهم عملکرد، تعداد Logical Reads است.

هرچه تعداد صفحات خوانده‌شده بیشتر باشد، فشار بیشتری بر حافظه و دیسک وارد می‌شود.

با استفاده از Query Store می‌توان کوئری‌هایی را که تعداد زیادی صفحه داده را می‌خوانند شناسایی و بررسی کرد.

در بسیاری از مواقع ایجاد یک ایندکس مناسب باعث کاهش شدید Logical Reads خواهد شد.

شناسایی Parameter Sniffing

Parameter Sniffing یکی از مشکلات رایج SQL Server است.

در این حالت SQL Server هنگام اولین اجرای یک Stored Procedure، پلنی بر اساس مقدار پارامتر تولید می‌کند و همان پلن را برای سایر مقادیر نیز استفاده می‌کند.

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

با Query Store می‌توان:

• چندین پلن تولیدشده برای یک کوئری را مشاهده کرد.
• زمان اجرای هر پلن را مقایسه نمود.
• رفتار پارامترها را تحلیل کرد.

این موضوع در سیستم‌های بزرگ اهمیت بسیار زیادی دارد.

Forced Plan در Query Store

یکی از قابلیت‌های فوق‌العاده Query Store امکان اجبار استفاده از یک پلن خاص است.

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

EXEC sp_query_store_force_plan

@query_id = 10,

@plan_id = 5;

مزایای این قابلیت:

• جلوگیری از Plan Regression

• حفظ عملکرد پایدار

• کاهش ریسک تغییرات ناخواسته

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

مانیتورینگ عملکرد پس از تغییرات در SQL

پس از انجام تغییرات زیر:

• ایجاد ایندکس

• حذف ایندکس

• بهینه‌سازی کوئری

• ارتقای SQL Server

• تغییر تنظیمات سرور

می‌توان از Query Store برای بررسی نتایج استفاده کرد.

این ابزار امکان مقایسه عملکرد قبل و بعد از تغییرات را فراهم می‌کند و نشان می‌دهد آیا بهینه‌سازی واقعاً مؤثر بوده است یا خیر.

بهترین روش‌های استفاده از Query Store

برای دستیابی به بهترین نتیجه، رعایت موارد زیر توصیه می‌شود:

• Query Store را در تمام پایگاه‌های داده مهم فعال کنید.

• حجم ذخیره‌سازی آن را مدیریت کنید.

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

• از Forced Plan تنها در شرایط ضروری استفاده کنید.

• پس از ارتقای SQL Server وضعیت پلن‌ها را کنترل نمایید.

• کوئری‌های دارای مصرف بالای CPU و IO را به طور منظم تحلیل کنید.

• از Query Store برای تشخیص Plan Regression بهره ببرید.

جمع‌بندی

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