آشنایی با Query Store – بخش سوم

مقدمه

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

همان طور که در قسمت قبلی عنوان شد، توصیه نویسندگان این کتاب بدین شکل بوده است که ابتدا از همین تنظیمات پیش فرض که برای این قابلیت در نظر گرفته شده است استفاده شود. سپس با توجه به سربار عملیاتی که در مجموعه خود دارید باید تغییراتی در پارامتر‌ها ایجاد کنید. در ورژن‌های بالاتر نظیر SQL SERVER 2019 تغییراتی در تنظیم این پارامتر‌ها شاهد بودیم که به محض فعال شدن Query Store این تنظیمات به صورت پیش فرض اعمال می‌شود. با توجه به این که در این مدت بیزینس‌های مختلف از این قابلیت استفاده می‌کردند و بازخورد‌هایی که مایکروسافت دریافت کرده بود، متوجه شدند که اعمال تغییرات در بعضی از این پارامتر‌ها به مراتب دقت گزارشات و عملکرد Query Store را بهبود می‌بخشد. در ادامه بعضی از این پارامتر‌ها معرفی می‌شود. در صورتی که تمایل داشتید مقاله‌های مختلف را در این خصوص مطالعه کنید می‌توانید با عبارت Query Store Configuration Best Practices نکات مرتبط با تنظیمات صحیح این پارامتر‌ها را دنبال کنید .

پارامتر MAX_STORAGE_SIZE_MB

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

نکته: کلیه اطلاعات مرتبط با Query Store در PRIMARY filegroup دیتابیس مورد نظر ذخیره می‌شود. لذا باید به این نکته دقت داشت که تخصیص فضای بیشتر برای ذخیره‌سازی اطلاعات، ممکن است مدت زمان بیشتری را نیز صرف بازگرداندن اطلاعات Query Store نماید و حجم دیتابیس نیز افزایش یابد. پس حتما عدد مورد نظر را با دقت انتخاب نمایید و بر روی اعداد بزرگ این تنظیمات را انجام ندهید. پیشنهاد این کتاب بر روی عدد ۱۰۲۴ هست.

پارامتر QUERY_STORE_CAPTURE_MODE

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

پارامتر INTERVAL_LENGTH_MINUTES

همان طور که توضیح داده شد این پارامتر برای تقسیم کردن اطلاعات در بازه زمانی که برای آن در نظر گرفته شده است را نمایش می‌دهد. مقدار پیش فرض این پارامتر بر روی عدد ۶۰ تنظیم شده بود. نکته ایی که باید در نظر بگیرید، این هست که در صورتی که نیاز به دقت بیشتری در تحلیل اطلاعات جمع آوری شده توسط Query Store دارید می‌توانید بر روی عدد ۱۵ در نظر بگیرید. در صورتی که حجم تراکنش‌های Ad-Hoc یا Store proc ‌های زیادی بر روی سرور دارید و میخواهید اطلاعات بیشتری را در بازی‌های زمانی بلند مدت‌تری پردازش و تحلیل کنید می‌توانید از اعداد بزرگتر نیز استفاده کنید. این پارامتر برای خارج کردن اطلاعات از حالت تجمیع شده به کار گرفته می‌شد که در چه بازه‌های زمانی و با چه دقتی اطلاعات را تحلیل و ارزیابی کنیم.

پارامتر WAIT_STATISTICS_CAPTURE_MODE

یکی از قابلیت‌های ارزشمندی که از نسخه ۲۰۱۷ به Query Store اضافه شد بحث WAIT_STATISTICS‌ها بود. حتما این قابلیت در سرور‌های عملیاتی فعال باشد تا متوجه شوید هر Query دقیقا با چه Wait ‌هایی درگیر بوده و چه مشکلاتی دارد. مبحث طبقه‌بندی Wait ‌های در مقالات بعدی مورد بررسی قرار خواهد گرفت و متوجه خواهید شد که شناخت دقیق آن‌ها تا چه اندازه می‌تواند به بهبود عملکرد Query ‌ها کمک کند

تاثیرALTER /Drop and Create بر عملکرد Query Store

زمانی که بر روی تریگر، فانکشن و Store Proc ‌هایی که ایجاد شده است عملیات Drop And Create اجرا شود، object_id آن‌ها تغییر خواهد کرد. با توجه به مکانیزمی که در این قسمت داریم، Query Store ذخیره و انالیز این اطلاعات را بر اساس همین object_id انجام می‌دهد. لذا عیب‌یابی این دست Query ‌هایی که در هر یک از روش‌های بالا نوشته شود بسیار سخت می‌شود. برای این کار کافی هست به جای استفاده از Drop And Create از CREATE OR ALTER برای اعمال تغییرات بر روی Query ‌های استفاده کنید

تنظیمات پیشنهادی برای Query Store در نسخه های مختلف

به صورت کلی، مایکروسافت تنظیمات مختص به Query Store را با توجه به هر نسخه ایی که معرفی کرده است و پارامتر‌های جدید به آن اضافه شده است بدین شکل توصیه کرده که استفاده شود.

در صورتی که از SQL Server 2016 استفاده می‌کنید می‌توانید از تنظیمات زیر استفاده کنید:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
DATA_FLUSH_INTERVAL_SECONDS = 900,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 1000,
INTERVAL_LENGTH_MINUTES = 60
);

در صورتی که از نسخه SQL Server 2017 استفاده می کنید می توانید از تنظیمات زیر استفاده کنید:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
DATA_FLUSH_INTERVAL_SECONDS = 900,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 1000,
INTERVAL_LENGTH_MINUTES = 60,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200,
WAIT_STATS_CAPTURE_MODE = ON
);

در صورتی که از نسخه SQL Server 2019استفاده می کنید می توانید از تنظیمات زیر استفاده کنید :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 1000,
INTERVAL_LENGTH_MINUTES = 60,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200,
WAIT_STATS_CAPTURE_MODE = ON,
QUERY_CAPTURE_MODE = CUSTOM,
QUERY_CAPTURE_POLICY = (
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
EXECUTION_COUNT = 30,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100
)
);

همچنین در نسخه SQL SERVER 2016 می توانیم بر روی متریک های زیر تحلیل داشته باشیم:

• CPU time
• Duration
• Execution count
• Logical reads
• Logical writes
• Memory consumption
• Physical reads
• CLR time
• Degree of parallelism (DOP)
• Row count

و در SQL SERVER 2017 می توان بر روی چندین عامل دیگر نیز تحلیل هایی انجام داد .

• CPU time
• Duration
• Execution count
• Logical reads
• Logical writes
• Memory consumption
• Physical reads
• CLR time
• Degree of parallelism
• Row count
• Log memory
• TempDB memory
• and Wait times

همچنین طبقه بندی و مشاهده تحلیل کلیه این متریک ها بر حسب متد های زیر طبقه بندی انجام می شود:

• Average
• Maximum
• Minimum
• Standard Deviation
• Total

بررسی فضای استفاده شده توسط Query Store

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

SELECT current_storage_size_mb,
max_storage_size_mb,
FROM sys.database_query_store_options
WHERE CAST(CAST(current_storage_size_mb AS
DECIMAL(21, 2)) / CAST(max_storage_size_mb AS
DECIMAL(21, 2)) * 100 AS DECIMAL(4, 2)) >= 90

AND size_based_cleanup_mode_desc = ‘OFF’;

پاک کردن اطلاعات Query Store

با استفاده از کوئری زیر می توانید کلیه اطلاعات ذخیره شده توسط Query Store را حذف کنید:

ALTER DATABASE [<Database Name>] SET QUERY_STORE CLEAR ALL;

همچنین با استفاده از کوئری زیر می‌توانید کلیه اطلاعات خارج از Query Store را حذف کنید. به عبارتی کلیه اطلاعات موجود در حافظه را سریعا به دیسک منتقل کنید.

USE [<Database>];
GO

EXEC sys.sp_query_store_flush_db;

در قسمتی از کتاب بحث مهمی اشاره شد که ذکر آن خالی از لطف نیست. از زمانی که Query Store در نسخه ۲۰۱۷ با تغییراتی همراه بود و پارامتر‌هایی به ان اضافه گردید ممکن است که با حالتی رو به رو شویم که به آن Error State گفته شده. برای برطرف شدن این مشکل پیشنهاد شده است که از کوئری زیر استفاده کنید. این کوئری بر روی هر دیتابیسی که این قابلیت بر روی فعال هست مشکل مورد نظر را برطرف می‌کند.

DECLARE @SQL AS NVARCHAR(MAX) = N'';
SELECT @SQL += REPLACE(
N'USE [{{DBName}}]
--Try Changing to READ_WRITE
IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3)
BEGIN
BEGIN TRY
ALTER DATABASE [{{DBName}}] SET QUERY_STORE = OFF
ALTER DATABASE [{{DBName}}] SET QUERY_STORE = READ_WRITE
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
END
--Run sys.sp_query_store_consistency_check
IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3)
BEGIN
BEGIN TRY
EXEC [{{DBName}}].sys.sp_query_store_consistency_check
ALTER DATABASE [{{DBName}}] SET QUERY_STORE = ON
ALTER DATABASE [{{DBName}}] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
END
--Run purge Query Store
IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3)
BEGIN
BEGIN TRY
ALTER DATABASE [{{DBName}}] SET QUERY_STORE CLEAR
ALTER DATABASE [{{DBName}}] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
END CATCH;
END
',
'{{DBName}}',
[name]
)
FROM sys.databases
WHERE is_query_store_on = 1;
EXEC (@SQL);

گزارشات و داشبوردهای تحلیلی Query Store

زمانی که قابلیت Query Store بر روی هر دیتابیس فعال گردد. فولدر مجزایی در آن دیتابیس ساخته شده که شامل چندین گزارش مختلف هست. این گزارشات هر یک به تفکیک در این بخش توضیح داده خواهد شد که چه اطلاعاتی را می‌توانیم برای افزایش عملکرد سیستم از آن‌ها دریافت کنیم. همانند شکل زیر می‌توانید گزارشات مرتبط را مشاهده نمایید:

همان طور که در تصویر بالا ملاحظه می کنید این گزارشات به شرح زیر هستند:

• Regressed Queries
• Overall Resource Consumption
• Top Resource Consuming Queries
• Queries With Forced Plans
• Queries With High Variation
• Query Wait Statistics

گزارش Regressed Queries

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

در سمت چپ، نمودارهایی را مشاهده می‌کنید به بسته به ماهیت کوئری‌ها اجرا شده توسط فانکشن‌ها، رویه‌ها یا Store proc‌ها و تریگرها و… را شامل می‌شود. در قسمت سمت راست نقشه ایی از پسرفت هر کوئری را مشاهده می‌کنید. در واقع با کلیک بر روی هر نموداری که در سمت چپ مشاهده می‌کنید اطلاعات تکمیلی از پسرفت پلن را در سمت چپ و پایین صفحه با جزییات بیشتری مشاهده خواهید کرد. همچنین می‌توانید در قسمت Configuration در قسمت سمت راست بالای صفحه همانند شکل زیر مشخص کنید که در چه بازه ایی میخواهید این پسرفت Query ‌ها را مشاهده کنید. همانند شکل زیر می‌توانید تنظیمات مختلف این قسمت را در بازه‌های زمانی مختلف و بر اساس تعداد پلن‌های اجرایی مشخص کنید:

می توانید جزییات هر اپراتور را نیز در محیط Query Store بدین شکل مشاهده نمایید:

گزارش Overall Resource Consumption

این گزارش به صورت کلی منابعی که Query ‌ها جهت اجرا استفاده کرده‌اند را نمایش میدهد. در این گزارش به عنوان پیش فرض چهار عامل نمایش داده می‌شود. متریک Duration بر حسب میلی ثانیه، دفعات اجرا کوئری، زمان پردازش CPU بر حسب میلی ثانیه و Logical Read بر حسب کیلوبایت در این گزارش نشان داده می‌شود. همان طور که در تصویر زیر مشاهده می‌کنید پیش فرض‌ها نشان داده شده است:

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

همچنین می توانید کلیه این اطلاعات اماری را به شکل جدول و Row Base مشاهده نمایید.

برای سایر بخش ها نیز اطلاعات مختلف با توجه به متریک های مرتبط با آن گزارش به شما نمایش داده می شود.

همچنین می‌توانید چارت‌های مرتبط با پیش فرض این گزارش را به دلخواه حذف یا اضافه و کم کنید و از متریک‌های مرتبط استفاده نمایید:

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

گزارش Top Resource Consuming Queries

در این گزارش به صورت پیش فرض ۲۵ کوئری ایی که بیشترین زمان اجرا را داشتند نمایش داده می‌شود. همچنین کلیه Option ‌هایی که در قسمت‌های قبل‌تر معرفی شد نیز در این گزارش وجود دارد. شمایی از این گزارش را در عکس زیر مشاهده می‌کنید:

متریک هایی که در این گزارش می توانیم ببینیم شامل موارد زیر هستند:

• Execution count
• Duration (ms) (default)
• CPU time (ms)
• Logical reads (KB)
• Logical writes (KB)
• Physical reads (KB)
• CLR time (ms)
• DOP
• Memory consumption (KB)
• Row count
• Log memory used (KB)
• Tempdb memory usage (KB)
• Wait time (ms)

همچنین عملیات هایی که می توان بر روی این گزارش انجام داد با استفاده از متد های زیر قابل انجام هست:

• Avg
• Max
• Min
• Std dev
• Total (default)

همچنین در این گزارش نیز می‌توانیم هم در حالت Chart و هم در حالت Grid View خروجی و امار گزارش را بررسی و تحلیل کنیم. عکس زیر نمونه ایی از این حالت را نمایش می‌دهد:

در این گزارش قابلیت مقایسه دو پلن اجرایی را نیز داریم و می‌توانیم مشخص کنیم که چه پلنی به عنوان Force plan در نظر گرفته شود. شکل زیر نمونه ایی از این حالت را نمایش میدهد.

گزارش Query Wait Statistics

در این گزارش امار مرتبط با Wait ‌هایی را مشاهده می‌کنیم که Query ‌های اجرا شده ثبت کرده‌اند. این Wait ‌های در ۲۳ دسته مختلف طبقه‌بندی شده است که شامل موارد مرتبط با CPU, memory, buffer IO و غیره هستند. لازم به ذکر هست که از نسخه ۲۰۱۷ شاهد اضافه این گزارش هستیم و در نسخه ۲۰۱۶ این گزارش وجود ندارد. در شکل زیر نمونه ایی از این گزارش را مشاهده می‌کنید:

همانند سایر گزارش ها می توانید امار مرتبط با این Wait ها را به صورت Grid View نیز مشاهده کنید. شکل زیر نمونه ایی از این گزارش هست:

به عنوان مثال می‌خواهیم Query ‌هایی را پیدا کنیم که CPU سیستم را به شدت درگیر کرده‌اند. با کلیک بر روی Wait مرتبط با CPU در این قسمت می‌توانید لیست این Query را مشاهده نمایید. نمونه ایی زیر تحیلی بر روی این گزارش هست که بتوانیم Query‌های مشکل دار را پیدا کنیم.

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

بررسی دقیق تر Query Store Catalog Views

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

• database_query_store_options (Transact-SQL)
• query_context_settings (Transact-SQL)
• query_store_plan (Transact-SQL)
• query_store_query (Transact-SQL)
• query_store_query_text (Transact-SQL)
• query_store_wait_stats (Transact-SQL)
• query_store_runtime_stats (Transact-SQL)
• query_store_runtime_stats_interval (Transact-SQL)
• query_store_query_hints (Transact-SQL)

بررسی sys.database_query_store_options

در این View شما تنظیمات کلی Query Store را مشاهده خواهید کرد. گاهی اوقات نیاز هست که کانفیگ‌های انجام شده را به ازای یک یا چند دیتابیس مورد بررسی قرارد دهید یا حتی در صورت لزوم از Best Practice ‌های گفته شده برای این کار استفاده کنید و تغییراتی در پارامتر‌ها اعمال کنید.

برای این کار می توانید از اسکریپت زیر استفاده کنید:

DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL += REPLACE(REPLACE('
USE [{{DBName}}];
SELECT "{{DBName}}",
*
FROM sys.database_query_store_options; '
,'{{DBName}}', [name])
,'" ','''')
FROM sys.databases
WHERE is_query_store_on = 1;
EXEC (@SQL);

در اسکریپت بالا به ازای هر دیتابیسی که قابلیت Query Store در آن فعال هست می‌توانید تنظیمات کلی آن را مشاهده نمایید. نکته ایی که در این قسمت بسیار اهمیت دارد این هست که ستونی در خروجی این گزارش مشاهده می‌کنید به اسم actual_state_desc که باید به صورت مرتبط بررسی کنید که از به حالت Read-only تغییر وضعیت نداده باشد. در این رابطه در قسمت دوم به تفصیل توضیحات لازم داده شد لذا نیاز هست که نسبت به کلیه این پارامتر‌ها و وضعیت آن‌ها اطلاعات دقیقی داشته باشید. در این کتاب از دو جدول استفاده شده است که می‌توانید اطلاعات تکمیلی در خصوص این قابلیت‌ها را از لینک زیر دنبال کنید:

https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/relational-databases/performance/monitoring-performance-by-using-the-query-store.md

بررسی sys.query_context_settings

در این Catalog Views در واقع context‌هایی که یک Query با آن اجرا شده است را مشاهده می‌کنید. به عنوان مثال زمانی که یک Store Procedure را اجرا می‌کنیم ممکن است از عباراتی مانند ANSI_NULLS و یا QUOTED_IDENTIFIER استفاده کنیم. اطلاعات ستون مرتبط با set_options در این قسمت به شما نمایش داده می‌شود که از چهContext ایی در کوئری موورد نظر استفاده شده است. نکته ایی که باید در نظر داشته باشیداین هست که در صورتی که کوئری خود را با context‌های متفاوت ایجاد کردید، رکورد‌های جدید در این View به ازای کوئری جدید نیز مشاهده خواهید کرد.

از اسکریپت زیر می توانید برای اطلاع از Contenx های استفاده شده استفاده نمایید:

SELECT q.query_id,
qt.query_sql_text,
qs.plan_handle,
q.context_settings_id
FROM sys.query_store_query q
INNER JOIN sys.dm_exec_query_stats qs
ON q.last_compile_batch_sql_handle = qs.sql_handle
INNER JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_context_settings cs
ON cs.context_settings_id = q.context_settings_id
WHERE qt.query_sql_text LIKE '%select%'
ORDER BY

q.query_id

همچنین می توانید با استفاده از Query پایین نیز این مورد را بررسی کنید:

SELECT *
FROM sys.dm_exec_plan_attributes(<plan_handle >)
WHERE attribute = 'set_options'

همچنین می توانید با استفاده از تابع زیر لیستی از context های مختلف را درست کرده و بر اساس ان خروجی Query هایی موجود را بررسی کنید:

CREATE FUNCTION dbo.fn_QueryStoreSetOptions
(
@SetOptions AS INT
)
RETURNS VARCHAR
(
MAX
)
AS
BEGIN
--Variables:
DECLARE @Result VARCHAR(MAX) = '',
@SetOptionFound INT

DECLARE @SetOptionsList TABLE
([Value] INT, [Option] VARCHAR(60))

INSERT INTO @SetOptionsList
VALUES
(
۱,
'ANSI_PADDING'
),
(۲, 'Parallel Plan'),
(۴, 'FORCEPLAN'),
(۸, 'CONCAT_NULL_YIELDS_NULL'),
(۱۶, 'ANSI_WARNINGS'),
(۳۲, 'ANSI_NULLS'),
(۶۴, 'QUOTED_IDENTIFIER'),
(۱۲۸, 'ANSI_NULL_DFLT_ON'),
(۲۵۶, 'ANSI_NULL_DFLT_OFF'),
(۵۱۲, 'NoBrowseTable'),
(۱۰۲۴, 'TriggerOneRow'),
(۲۰۴۸, 'ResyncQuery'),
(۴۰۹۶, 'ARITH_ABORT'),
(۸۱۹۲, 'NUMERIC_ROUNDABORT'),
(۱۶۳۸۴, 'DATEFIRST'),
(۳۲۷۶۸, 'DATEFORMAT'),
(۶۵۵۳۶, 'LanguageID'),
(۱۳۱۰۷۲, 'UPON'),
(۲۶۲۱۴۴, 'ROWCOUNT')

SELECT TOP 1
@SetOptionFound = ISNULL([Value], -1),
@Result = ISNULL([Option], '') + ' (' + CAST(@SetOptionFound AS VARCHAR) + ')' + '; '
FROM @SetOptionsList
WHERE [Value] <= @SetOptions
ORDER BY
[Value] DESC

RETURN @Result +
CASE
WHEN @SetOptionFound > -1 THEN dbo.fn_QueryStoreSetOptions(@SetOptions - @SetOptionFound)
ELSE ''
END
END
GO

سپس با استفاده از اسکریپت زیر می توانید بر روی اطلاعات Query Store تحلیل های لازم را انجام دهید

SELECT dbo.fn_QueryStoreSetOptions(CAST(set_options AS INT))
FROM sys.query_context_settings

همان طور که در تصویر پایین نیز مشاهده می کنید جزییات مرتبط با هر کدام از این ستونها را که مرتبط با sys.query_context_settings هست ، در این لیست مشاهده می کنید.

بررسی sys.query_store_plan

همان طور که از نام این Catalog View مشخص هست اطلاعات مرتبط با پلن های اجرایی را در نمایش می دهد. در sys.query_store_plan ستون های مهمی وجود دارند. به عنوان مثال compatibility_level که یکی از مهمترین ستون های این View هست نشان دهنده این هست که با چه نسخه ایی از SQL SERVER کوئری ما اجرا شده است . با توجه به بحث تغییر الگوریتم های در نسخه های بالاتر نیاز هست که عنایت ویژه ایی بر روی این پارامتر داشته باشید !

اطلاعاتی که در این View می توانیم مشاهده کنیم را در شکل زیر مشخص شده است:

در ادامه مقاله سایر Catalog view های مرتبط با Query Store را بررسی خواهیم کرد و به سایر جزییات مرتبط با هر یک از ان ها خواهیم پرداخت.