آموزش Query Store در SQL Server

یکی از بزرگ‌ترین چالش‌های مدیران پایگاه داده (DBA) و توسعه‌دهندگان SQL Server، شناسایی علت افت ناگهانی عملکرد Queryها است. ممکن است یک Query که تا دیروز در کمتر از یک ثانیه اجرا می‌شد، امروز چندین ثانیه یا حتی چند دقیقه زمان ببرد؛ بدون اینکه تغییری در کد برنامه ایجاد شده باشد.

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

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

به همین دلیل امروزه Query Store یکی از مهم‌ترین ابزارهای Performance Tuning در SQL Server محسوب می‌شود و تقریباً در تمامی پروژه‌های حرفه‌ای توصیه می‌شود فعال باشد.

Query Store چیست؟

Query Store یک قابلیت داخلی در SQL Server است که تاریخچه اجرای Queryها را ثبت و نگهداری می‌کند. این ویژگی باعث می‌شود بتوانید عملکرد Queryها را در طول زمان تحلیل کنید، تغییرات Execution Plan را مشاهده نمایید و در صورت نیاز، بهترین Execution Plan را مجدداً به SQL Server تحمیل (Force) کنید.

برخلاف DMVها که اطلاعات آن‌ها معمولاً پس از Restart شدن SQL Server یا پاک شدن حافظه Cache از بین می‌رود، اطلاعات Query Store در خود پایگاه داده ذخیره می‌شود و تا زمانی که حذف نشود، قابل استفاده خواهد بود.

به عبارت ساده، Query Store مانند یک جعبه سیاه (Black Box) برای SQL Server عمل می‌کند؛ هر اتفاق مهمی که برای اجرای Queryها رخ دهد، در آن ثبت خواهد شد.

چرا با وجود ابزارهای قدیمی به Query Store نیاز داریم؟

قبل از معرفی Query Store، تحلیل مشکلات Performance در پایگاه‌های داده و Data Warehouse فرآیندی دشوار و زمان‌بر بود. بسیاری از مشکلات تنها در ساعات خاصی از روز یا تحت بار کاری مشخص رخ می‌دادند و زمانی که DBA برای بررسی وارد عمل می‌شد، دیگر اثری از مشکل وجود نداشت.

به عنوان مثال ممکن است کاربران گزارش دهند که ساعت ۱۰ صبح سیستم بسیار کند شده است، اما زمانی که مدیر پایگاه داده ساعت ۱۲ ظهر وضعیت را بررسی می‌کند، تمامی Queryها به حالت عادی بازگشته‌اند و اطلاعات مربوط به آن افت عملکرد نیز از حافظه SQL Server حذف شده است.

Query Store دقیقاً برای حل همین مشکل طراحی شد؛ یعنی ذخیره تاریخچه اجرای Queryها و امکان بازگشت به گذشته برای بررسی دقیق عملکرد سیستم.

معماری و اجزای اصلی Query Store

Query Store از چند بخش اصلی تشکیل شده است که هر کدام وظیفه مشخصی دارند.

Query Text

متن کامل Queryهای اجرا شده در این قسمت ذخیره می‌شود. اگر یک Query هزار بار اجرا شود، متن آن تنها یک بار ثبت خواهد شد.

متن کوئری و Query ID

هر Query دارای شناسه (Query ID) اختصاصی است و ارتباط بین متن Query و Execution Planها از طریق این بخش برقرار می‌شود.

نقش Execution Plan در تشخیص کندی

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

آمار زمان اجرا (Runtime Statistics)

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

• مدت زمان اجرا (Duration)
• میزان مصرف CPU
• تعداد Logical Read
• تعداد Physical Read
• تعداد دفعات اجرا
• میزان Memory مصرف‌شده
• تعداد سطرهای برگشتی

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

آمار انتظار (Wait Statistics) و تشخیص گلوگاه‌ها

از SQL Server 2017 به بعد، Query Store قادر است اطلاعات مربوط به Wait Statistics را نیز ذخیره کند. برای مثال مشخص می‌شود که یک Query بیشتر زمان خود را صرف چه نوع انتظاری کرده است:

• PAGEIOLATCH
• CXPACKET
• LCK_M_X
• WRITELOG
• SOS_SCHEDULER_YIELD

این اطلاعات نقش بسیار مهمی در تحلیل مشکلات Performance دارند.

 

Query Store چه اطلاعاتی را در پایگاه داده ذخیره می‌کند؟

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

• متن کامل Query
• Execution Plan
• زمان اجرای Query
• تعداد دفعات اجرا
• CPU مصرفی
• Logical Read
• Physical Read
• Memory Grant
• Wait Statistics
• زمان ایجاد Plan
• تغییرات Plan
• Query Hash
• Plan Hash

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

مزایای استفاده از Query Store

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

تحلیل تاریخچه عملکرد کوئری‌ها در طول زمان

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

شناسایی و مقایسه تغییرات Execution Plan

گاهی اوقات تنها دلیل کند شدن یک Query، تغییر Execution Plan است. Query Store این تغییرات را ثبت می‌کند و به شما اجازه می‌دهد Planهای مختلف یک Query را با یکدیگر مقایسه کنید.

امکان Force کردن بهترین Plan

اگر SQL Server به هر دلیلی Execution Plan نامناسبی انتخاب کند، می‌توانید از طریق Query Store بهترین Plan قبلی را مجدداً اعمال (Force) کنید تا عملکرد Query به وضعیت مطلوب بازگردد.

عیب‌یابی مشکلات گذشته (حتی بعد از ری‌استارت سرور)

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

کمک به Performance Tuning

با استفاده از اطلاعاتی مانند مصرف CPU، مدت زمان اجرا، تعداد دفعات اجرا و Wait Statistics می‌توان Queryهای پرهزینه را شناسایی و برای بهینه‌سازی آن‌ها اقدام کرد.

فعال کردن Query Store

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

با روشن کردن Query Store در دیتابیس، امکان تحلیل افت سرعت (Performance Regression) و تثبیت بهترین پلان اجرایی بدون نیاز به ابزارهای جانبی فراهم می‌شود؛ اقدامی منحصربه‌فرد که پایداری، سرعت و امنیت پایگاه‌داده شما را در بالاترین سطح ممکن تضمین می‌کند.

تنظیمات مهم Query Store

پیکربندی صحیح تنظیمات Query Store کلید اصلی دستیابی به بالاترین کارایی و پایش هوشمند دیتابیس بدون تحمیل بار اضافی بر سیستم است. تنظیم دقیق پارامترهای حیاتی مانند Operation Mode (روی حالت Read Write)، Data Flush Interval برای مدیریت زمان ذخیره‌سازی داده‌ها روی دیسک، و Max Size جهت جلوگیری از پر شدن حجم پایگاه داده، عملکرد بی‌نقص این ابزار را تضمین می‌کند. همچنین با سفارشی‌سازی Query Store Capture Mode (روی حالت Auto یا Custom) و تنظیم Stale Query Threshold، تنها اطلاعات کوئری‌های مهم و پرمصرف ثبت شده و از هدر رفت منابع سرور جلوگیری می‌شود؛ پیکربندی هوشمندی که نقش بسزایی در عیب‌یابی سریع، تحلیل رفتارهای زمانی SQL Server و ارتقای پایداری دیتابیس ایفا می‌کند.

سخن پایانی

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