مقایسه و بهبود پلن‌های اجرایی در SQL Server با Query Store

مقایسه و بهبود پلن‌های اجرایی در SQL Server با Query Store

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

Query Store چیست؟

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

این ویژگی اطلاعات زیر را ثبت می‌کند:

• متن کوئری‌ها

• Execution Planها

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

• مصرف CPU

• تعداد خواندن صفحات (Logical Reads)

• تعداد دفعات اجرا

• تغییرات پلن در طول زمان

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

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

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

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

• عملیات Join چگونه انجام شده است.

• چه میزان هزینه برای هر بخش وجود دارد.

• چه بخش‌هایی باعث کندی اجرا شده‌اند.

 

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

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

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

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

 

ALTER DATABASE SalesDB

SET QUERY_STORE = ON;

همچنین می‌توان تنظیمات پیشرفته‌تری را مشخص کرد:

 

ALTER DATABASE SalesDB

SET QUERY_STORE (

OPERATION_MODE = READ_WRITE

);

 

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

 

ساختار اطلاعات در Query Store  در SQL Server 

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

• Query Text

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

• Query

شناسه یکتا برای هر کوئری.

• Execution Plan

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

• Runtime Statistics

آمار مربوط به عملکرد مانند:

• • CPU Time
  • Duration
  • Logical Reads
  • Memory Usage
  • Wait Statistics

اطلاعات مربوط به انتظارهای سیستم در زمان اجرای کوئری.

 

مشاهده اطلاعات Query Store  در SQL Server 

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

 

Database

└ Query Store

├ Regressed Queries

├ Top Resource Consuming Queries

├ Tracked Queries

این بخش‌ها امکان تحلیل گرافیکی عملکرد کوئری‌ها را فراهم می‌کنند.

 

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

یکی از مهم‌ترین قابلیت‌های Query Store امکان مشاهده چندین پلن برای یک کوئری است.

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

 

SELECT *

FROM Orders

WHERE CustomerID = 100;

 

در طول زمان ممکن است SQL Server دو پلن متفاوت تولید کند:

پلن اول

استفاده از Index Seek

Index Seek → Key Lookup

مدت اجرا:

20 ms

پلن دوم

استفاده از Table Scan

Clustered Index Scan

مدت اجرا:

1200 ms

با Query Store می‌توان این دو پلن را در کنار هم مشاهده و مقایسه کرد.

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

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

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

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

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

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

شناسایی Query Regression  در SQL Server 

یکی از مشکلات رایج در SQL Server پدیده Query Regression است.

Regression زمانی رخ می‌دهد که:

• کوئری قبلاً سریع اجرا می‌شده

• پس از تغییر پلن کند می‌شود

Query Store به طور خودکار کوئری‌های دارای Regression را شناسایی می‌کند.

در بخش:

Regressed Queries

می‌توان مشاهده کرد:

• پلن قبلی
• پلن جدید
• میزان افت عملکرد

این قابلیت زمان عیب‌یابی را به شدت کاهش می‌دهد.

تحلیل مصرف منابع  در SQL Server 

Query Store امکان بررسی مصرف منابع را فراهم می‌کند.

وارد قابل اندازه‌گیری:

• CPU Usage

  مشخص می‌کند کدام کوئری‌ها بیشترین پردازش را مصرف می‌کنند.

• Memory Consumption

  میزان حافظه مورد استفاده توسط کوئری.

• Logical Reads

  تعداد صفحات خوانده شده از حافظه.

• Duration

  مدت زمان اجرای کوئری.

مثال:

Duration	CPU	Query
100 ms	50 ms	Query A
5000 ms	2000 ms	Query B

 

مشخص است که Query B نیاز به بهینه‌سازی دارد.

 

بررسی تغییرات پس از به‌روزرسانی SQL Server

پس از ارتقای نسخه SQL Server ممکن است رفتار Query Optimizer تغییر کند.

به عنوان مثال:

• SQL Server 2017

• SQL Server 2019

• SQL Server 2022

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

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

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

 

Force Plan در Query Store  در SQL Server 

یکی از قدرتمندترین قابلیت‌های Query Store امکان تثبیت یک پلن مناسب است.

فرض کنید:

پلن A:

 

50 ms

پلن B:

 

 

اگر SQL Server به اشتباه پلن B را انتخاب کند، می‌توان پلن A را اجباری کرد.

 

در SSMS:

 

Force Plan

یا با دستور:

 

EXEC sp_query_store_force_plan

@query_id = 10,

@plan_id = 5;

 

از این پس SQL Server تلاش می‌کند همان پلن سریع‌تر را استفاده کند.

 

مزایای Force Plan  در SQL Server 

مزایای اصلی:

• جلوگیری از افت ناگهانی عملکرد

• پایداری بیشتر سیستم

• کاهش زمان عیب‌یابی

• کنترل بهتر روی Query Optimizer

البته استفاده بیش از حد از Force Plan توصیه نمی‌شود زیرا ممکن است با تغییر حجم داده‌ها پلن انتخابی دیگر بهترین گزینه نباشد.

تحلیل کوئری‌های پرمصرف

 

Query Store بخشی به نام:

 

Top Resource Consuming Queries

دارد.

در این بخش می‌توان کوئری‌ها را بر اساس:

• CPU

• Memory

• Duration

• Reads

مرتب کرد.

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

 

استفاده از گزارش‌های داخلی Query Store

SQL Server گزارش‌های متنوعی ارائه می‌دهد:

• Top Duration Queries

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

• Top CPU Queries

  کوئری‌های پرمصرف از نظر پردازنده.

• Query Wait Statistics

  تحلیل زمان‌های انتظار.

• Query Variance

  بررسی نوسانات عملکردی.

این گزارش‌ها بدون نیاز به ابزار جانبی در دسترس هستند.

 

بهبود پلن‌های اجرایی با ایندکس‌گذاری

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

مثال:

 

SELECT *

FROM Customers

WHERE Email = 'test@test.com';

 

بدون ایندکس:

 

Table Scan

 

با ایندکس:

 

CREATE INDEX IX_Customers_Email

ON Customers(Email);

 

پلن تبدیل می‌شود به:

 

Index Seek

 

و زمان اجرا به شدت کاهش می‌یابد.

 

بهبود آمارها (Statistics)

گاهی مشکل از آمارهای قدیمی است.

به‌روزرسانی Statistics:

 

UPDATE STATISTICS Customers;

 

یا:

 

EXEC sp_updatestats;

پس از به‌روزرسانی، Query Optimizer قادر خواهد بود پلن بهتری تولید کند.

 

بررسی Parameter Sniffing

یکی از مشکلات مشهور SQL Server، Parameter Sniffing است.

مثال:

 

CREATE PROCEDURE GetOrders

@CustomerID INT

AS

SELECT *

FROM Orders

WHERE CustomerID = @CustomerID;

 

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

 

Query Store کمک می‌کند تفاوت پلن‌ها و اثر Parameter Sniffing به سرعت شناسایی شود.

 

مانیتورینگ بلندمدت عملکرد

مزیت مهم Query Store ذخیره اطلاعات در طول زمان است.

می‌توان عملکرد را بررسی کرد:

• روزانه
• هفتگی
• ماهانه

و روند تغییرات را مشاهده نمود.

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

 

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

برای بهره‌برداری بهتر از Query Store موارد زیر توصیه می‌شود:

• همیشه روی محیط Production فعال باشد.

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

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

• از Force Plan فقط در مواقع ضروری استفاده شود.

• پس از ایجاد ایندکس‌های جدید عملکرد مجدداً ارزیابی شود.

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

جمع‌بندی

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