Query Store – Part 1

[BOL]
The SQL Server Query Store feature provides DBAs with insight on query plan choice and performance. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows, allowing you to see database usage patterns and understand when query plan changes happened on the server

Query Store is a Database Feature , so to enable or disable you need to change the database property [QUERY_STORE]

GO
USE [SQL2016DB]
GO
ALTER DATABASE [SQL2016DB] SET QUERY_STORE = ON
GO
By Default Query Store Properties are set as
  • Operation Mode (Requested) is set to Read Write
  • Monitoring
    • Data Flush Interval (Minutes) is set to 15 min
    • Statistics Collection Interval is set to 1 Hour
  • Query Store Retention
    • Max Size (MB) is set to 100 MB
    • Query Store Capture Mode is set to All
    • Size Based Cleanup Mode is set to Off
    • Stale Query Threshold (Days) is set to 365

 

image

Leave a Reply

Your email address will not be published. Required fields are marked *