مقدمه‌ای بر Query Store در SQL Server و نحوه عملکرد

مقدمه‌ای بر Query Store در SQL Server و نحوه عملکرد

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

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

Query Store چیست؟

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

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

این ویژگی باعث می‌شود بتوان تغییرات عملکرد کوئری‌ها را در بازه‌های زمانی مختلف بررسی و علت افت کارایی را شناسایی کرد.

Query Store چگونه کار می‌کند؟

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

فرآیند کلی عملکرد به این صورت است:

• اجرای کوئری توسط SQL Server
• تولید Execution Plan
• ثبت متن کوئری
• ذخیره اطلاعات آماری اجرا
• نگهداری پلن‌های مختلف همان کوئری
• به‌روزرسانی اطلاعات پس از هر اجرا

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

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

یکی از نقاط قوت Query Store، ثبت جزئیات کامل عملکرد کوئری‌ها است.

اطلاعات مهم عبارت‌اند از:

• متن کامل Query
• Execution Plan
• مدت زمان اجرا (Duration)
• میزان مصرف CPU
• تعداد Logical Read
• تعداد Physical Read
• تعداد دفعات اجرا
• زمان اولین اجرا
• زمان آخرین اجرا
• تعداد پلن‌های مختلف یک کوئری
• اطلاعات مربوط به Compile

این داده‌ها امکان تحلیل دقیق عملکرد سیستم را فراهم می‌کنند.

مزایای Query Store  در SQL Server

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

• بررسی تاریخچه عملکرد

  می‌توان مشاهده کرد که یک کوئری در هفته گذشته چگونه اجرا شده و آیا عملکرد آن تغییر کرده است یا خیر.

• تشخیص Regression

  گاهی پس از ایجاد ایندکس جدید یا به‌روزرسانی آمار، SQL Server پلن متفاوتی انتخاب می‌کند که باعث کاهش سرعت می‌شود.
  Query Store این تغییر را ثبت می‌کند.

• مقایسه Execution Plan

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

• اجبار استفاده از یک Plan

  یکی از مهم‌ترین امکانات Query Store قابلیت Plan Forcing است.
  اگر SQL Server پلن نامناسبی انتخاب کند، می‌توان دستور داد همیشه از بهترین پلن قبلی استفاده شود.

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

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

• CPU
• Memory
• Duration
• IO

را مصرف کرده است.

فعال‌سازی Query Store

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

نمونه دستور:

ALTER DATABASE SalesDB

SET QUERY_STORE = ON;

همچنین می‌توان تنظیمات آن را تغییر داد.

نمونه:

ALTER DATABASE SalesDB

SET QUERY_STORE (

OPERATION_MODE = READ_WRITE

);

در این حالت Query Store علاوه بر خواندن اطلاعات، داده‌های جدید را نیز ذخیره می‌کند.

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

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

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

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

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

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

حالت‌های عملکرد Query Store  در SQL Server

Query Store دارای چند حالت عملیاتی است.

• READ_WRITE

  اطلاعات جدید ذخیره می‌شود
  این حالت معمولاً گزینه پیش‌فرض است.

• READ_ONLY

  اطلاعات موجود فقط قابل مشاهده هستند.
  داده جدید ثبت نمی‌شود.

• OFF

  Query Store غیرفعال است.
  هیچ اطلاعاتی جمع‌آوری نخواهد شد.

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

در SQL Server Management Studio بخشی اختصاصی برای Query Store وجود دارد.

مسیر:

Database

→ Query Store

در این قسمت گزارش‌های مختلفی مانند موارد زیر وجود دارد:

• Top Resource Consuming Queries

• Overall Resource Consumption

• Regressed Queries

• Tracked Queries

• Query Wait Statistics

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

Dynamic Management Views مربوط به Query Store

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

برخی از مهم‌ترین آن‌ها عبارت‌اند از:

sys.query_store_query

sys.query_store_plan

sys.query_store_runtime_stats

sys.query_store_runtime_stats_interval

sys.query_store_query_text

این Viewها امکان تهیه گزارش‌های سفارشی را نیز فراهم می‌کنند.

Plan Forcing چیست؟

یکی از مهم‌ترین قابلیت‌های Query Store، تثبیت پلن اجرایی (Plan Forcing) است.

فرض کنید کوئری زیر قبلاً با Plan شماره 15 اجرا می‌شده و زمان اجرای آن کمتر از یک ثانیه بوده است.

بعد از تغییر آمار، SQL Server Plan شماره 32 را انتخاب می‌کند.

اکنون اجرای کوئری به 20 ثانیه افزایش یافته است.

در چنین شرایطی می‌توان Plan شماره 15 را به‌عنوان پلن اجباری انتخاب کرد.

در نتیجه SQL Server تا زمانی که امکان استفاده از آن وجود داشته باشد، همان پلن را اجرا خواهد کرد.

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

مدیریت فضای ذخیره‌سازی

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

برای کنترل این موضوع تنظیماتی مانند موارد زیر وجود دارد:

• حداکثر حجم ذخیره‌سازی

• مدت نگهداری اطلاعات

• پاک‌سازی خودکار داده‌های قدیمی

• فاصله زمانی ثبت آمار

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

بهترین سناریوهای استفاده Query Store در SQL Server

Query Store در سناریوهای مختلف بسیار کاربردی است.

برخی از مهم‌ترین آن‌ها عبارت‌اند از:

• بررسی کاهش سرعت بعد از انتشار نسخه جدید نرم‌افزار

• تحلیل کوئری‌های کند

• مقایسه عملکرد قبل و بعد از ایجاد ایندکس

• بررسی تأثیر تغییر Statistics

• یافتن پرس‌وجوهای پرمصرف

• تحلیل مشکلات Performance در ساعات پرترافیک

• انتخاب بهترین Execution Plan
  
  
  
  

محدودیت‌های Query Store

اگرچه Query Store ابزار بسیار قدرتمندی است، اما محدودیت‌هایی نیز دارد.

از جمله:

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

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

برای بهره‌گیری بهتر از Query Store رعایت چند نکته توصیه می‌شود:

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

جمع‌بندی

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

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