query store-sql server-execution plan-query tuning-performance analysis-force plan-troubleshooting sql-مدیریت دیتابیس-مانیتورینگ SQL

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

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

ضرورت استفاده از Query Store

کوئری‌ها در طول زمان ممکن است به دلیل تغییر داده‌ها، آپدیت آمار یا تغییرات محیط (مثل اضافه یا حذف ایندکس) طرح اجرایی متفاوتی انتخاب کنند. این «فراریت طرح» (Plan Volatility) می‌تواند منجر به افت عملکرد ناگهانی شود.

ابزارهای سنتی مانند Plan Cache پس از ری‌استارت سرور پاک شده و امکان ترسیم تاریخچه عملکرد را ندارند. Query Store این خلأ را با نگهداری طولانی‌مدت طرح‌ها و آمار اجرا پر می‌کند.

معماری و اجزای داخلی

Query Store از سه انبار (Store) داخلی تشکیل شده است:

  • Plan Store: نگهداری تمام طرح‌های اجرایی ایجادشده برای هر کوئری.
  • Runtime Statistics Store: ثبت آمار اجرای هر کوئری (زمان متوسط، تعداد اجرا، میزان CPU و I/O).
  • Forced Plan Store: لیست طرح‌های اجرایی که توسط DBA اجباری شده‌اند.

در کنار اینها، یک Job داخلی دوره‌ای مسئول Flush کردن داده‌های جمع‌آوری‌شده از حافظه به دیسک است و با تنظیمات DATA_FLUSH_INTERVAL_SECONDS کنترل می‌شود.

ASCII Diagram:


+———————-+
| Query Execution |
+———-+———–+
|
Capture Thread
|
+———-v———–+
| In-Memory |
| Aggregated Buckets |
+———-+———–+
|
Flush JOB (Thread)
|
+———-v———–+
| Disk Tables |
| Plan | Stats | Forced|
+———————-+

نصب و پیکربندی

فعال‌سازی در سطح دیتابیس

ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON;

تنظیم اولیه پارامترها

ALTER DATABASE [YourDatabase]
SET QUERY_STORE (
  OPERATION_MODE             = READ_WRITE,
  CLEANUP_POLICY             = (STALE_QUERY_THRESHOLD_DAYS = 30),
  MAX_STORAGE_SIZE_MB        = 1000,
  INTERVAL_LENGTH_MINUTES    = 60,
  DATA_CAPTURE_MODE          = AUTO,        -- AUTO, ALL, NONE
  DATA_FLUSH_INTERVAL_SECONDS= 900
);

توضیح پارامترهای کلیدی

پارامترشرحمقدار پیشنهادی
OPERATION_MODEحالت کارکرد (OFF, READ_ONLY, READ_WRITE)READ_WRITE
CLEANUP_POLICYحذف خودکار داده‌های قدیمی (تعداد روز)۳۰
MAX_STORAGE_SIZE_MBحداکثر فضای مورد استفاده بر حسب مگابایتبراساس حجم دیتابیس
INTERVAL_LENGTH_MINUTESبازه تجمیع آمار (به دقیقه)۶۰
DATA_CAPTURE_MODEتعیین نوع کوئری‌های ضبط‌شده (AUTO یا ALL یا NONE)AUTO
DATA_FLUSH_INTERVAL_SECONDSزمان‌بندی Flush از حافظه به دیسک (ثانیه)۹۰۰

روند جمع‌آوری و ذخیره‌سازی داده‌ها

  1. هر بار اجرای کوئری، طرح اجرایی جدید یا موجود شناسایی می‌شود.
  2. معیارهای اجرا (زمان CPU، I/O، تعداد اجرا) در بازه‌ی زمانیِ مشخص‌شده توسط INTERVAL_LENGTH_MINUTES در حافظه تجمیع می‌گردد.
  3. با رسیدن به زمان DATA_FLUSH_INTERVAL_SECONDS، داده‌های in-memory به جداول دیسکی Query Store منتقل می‌شوند.

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

نظارت و تحلیل

گزارش‌های گرافیکی در SSMS

در SQL Server Management Studio به مسیر زیر بروید:

Management
└─Query Store
└─Reports
├─Regressed Queries
├─Top Resource Consuming Queries
└─Overall Resource Consumption

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

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

نمایش وضعیت کلی Query Store:

SELECT
  actual_state_desc,
  desired_state_desc,
  size_on_disk_bytes/1048576 AS size_mb,
  waited_for_flush,
  stale_query_threshold_days
FROM sys.database_query_store_options;

یافتن ده کوئری با بیشترین هزینه CPU:

SELECT TOP 10
  qs.query_id,
  qs.plan_id,
  qsrs.avg_cpu_time/1000.0 AS avg_cpu_ms,
  qsrs.count_executions
FROM sys.query_store_plan AS qs
JOIN sys.query_store_runtime_stats AS qsrs
  ON qsrs.plan_id = qs.plan_id
ORDER BY qsrs.avg_cpu_time DESC;

مدیریت و بهینه‌سازی طرح‌ها

Force کردن یک Plan

EXEC sp_query_store_force_plan
  @query_id = 42,
  @plan_id  = 137;

لغو Force

EXEC sp_query_store_unforce_plan
  @query_id = 42,
  @plan_id  = 137;

پاکسازی دستی

EXEC sp_query_store_remove_persisted_plan
  @plan_id = 137;

بازنشانی کامل Query Store

ALTER DATABASE [YourDatabase] SET QUERY_STORE CLEAR;

سناریوهای عملی

  1. عیب‌یابی افت ناگهانی پس از آپدیت آمار: با نمودار Regressed Queries نقطه شروع افت عملکرد را شناسایی و Plan قبل را فورس کنید.
  2. مقایسه عملکرد قبل و بعد از ارتقا به SQL Server 2022: با گرفتن Snapshot از Metrics و بررسی تغییرات CPU/IO در بازه‌های زمانی مشابه.
  3. تحلیل تاثیر ایندکس جدید: اجرای Load تست، بررسی تغییرات Avg Duration و logical reads.
  4. پایش بی‌وقفه کوئری‌های گزارش‌گیری سنگین: شناسایی الگوهای تکراری و انتقال به Reporting Warehouse.

بهترین شیوه‌ها

  • اندازه Max Storage را بر اساس تعداد کوئری‌ها و حجم دیتابیس تخمین بزنید.
  • مقدار INTERVAL_LENGTH_MINUTES را برای بارهای OLTP کمتر، به ۵ یا ۱۵ دقیقه کاهش دهید.
  • برای Workloadهای تجزیه‌گرا، Data Capture Mode را روی ALL بگذارید.
  • Cleanup Policy را طوری تنظیم کنید که داده‌های بیش از ۳۰ روز یک‌بار حذف شوند.
  • قبل از Production، Query Store را با تست شبیه‌سازی بار “warm-up” کنید.

محدودیت‌ها و ملاحظات

  • در کارکرد خیلی سنگین OLTP، نگهداری آمار لحظه‌ای می‌تواند بار اضافی CPU ایجاد کند.
  • Plan Store می‌تواند فضای زیادی مصرف کند؛ مانیتورینگ مداوم لازم است.
  • بعضی کوئری‌های کوتاه‌مدت یا پرچندبخشی (parameterized) امکان ثبت ناقص دارند.
  • تیم‌هایی که از Always On Availability Groups استفاده می‌کنند، نیاز به هماهنگی تنظیمات Query Store در Secondaryها دارند.

مقایسه با ابزارهای دیگر

ویژگیQuery StorePlan CacheExtended Events
نگهداری تاریخچهبلهخیربه صورت دستی پیاده‌سازی می‌شود
امکان Force Planبلهخیرخیر
گزارش‌های گرافیکیبله (SSMS)خیرتوسط ابزارهای ثالث
Persistency پس از ری‌استارتبلهخیربستگی به پیکربندی دارد
Overheadکم تا متوسطکمبسته به Session تعریف‌شده

آینده Query Store و نسخه‌های جدید

در SQL Server 2019 و بالاتر، قابلیت Automatic Plan Correction به‌صورت Experimental معرفی شد که می‌تواند براساس اطلاعات Query Store به‌طور خودکار Planهای بد را تشخیص و اصلاح کند.

در Azure SQL Database توانایی اعتبارسنجی Planهای Force شده در سناریوهای Hyperscale و Serverless نیز افزوده شده است.

نتیجه‌گیری

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

سؤالات متداول (FAQ)

۱. Query Store چه زمانی نمی‌تواند طرح یک کوئری را ثبت کند؟

وقتی که کوئری به‌صورت Ad-hoc بسیار کوتاه اجرا شود یا در حالت TRACE FLAGهای خاصی باشد که منجر به Cold Cache می‌شود.

۲. آیا امکان Replicate کردن داده‌های Query Store به دیتابیس دیگری وجود دارد؟

بله، با استفاده از Log Shipping و Transactional Replication می‌توان جداول داخلی sys.query_store_* را منتقل کرد.

۳. چگونه می‌توان هشدار حجم Storage بالا را فعال کرد؟

می‌توانید از Alert در SQL Server Agent روی Performance Condition query_store_space_used_percent استفاده کنید.

۴. می‌توان Query Store را در قالب Policy در Policy Based Management کنترل کرد؟

بله، با تعریف WMI Condition روی کلاس Database و خصوصیت QueryStoreOptions می‌توان قوانین پیاده‌سازی کرد.

ارتباط و مشاوره

آیا کوئری‌های شما هم گاهی بدون دلیل واضح کند می‌شوند؟
Query Store می‌تواند شفافیت کامل در تاریخچه اجرا، طرح‌ها و کارایی کوئری‌ها به شما بدهد. اما تنظیم نادرست آن ممکن است بار اضافه روی سیستم ایجاد کند.

تیم توسعه فناوری اطلاعات لاندا آماده است تا:

  • Query Store را در دیتابیس شما بهینه‌سازی و پیکربندی کند
  • افت عملکرد کوئری‌ها را شناسایی و برطرف نماید
  • بهترین شیوه‌های مانیتورینگ و بهبود عملکرد را پیاده‌سازی کند

همین حالا با لاندا تماس ✆ بگیرید و از مشاوره تخصصی رایگان بهره‌مند شوید.

روزبه امیرعصامی

بهینه سازی دیتابیس

SQL Serverاسکیوال سرورایندکسباز طراحی دیتابیسبهینه سازی کوئریپایگاه دادهطراحی دیتابیس

Xلینکدینواتساپتلگرامایمیل

نوشته های مرتبط

نظری داده نشده

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *