For an instructor lead, in-depth look at learning SQL click below.
SQL Server Query Store is a performance monitoring tool introduced in SQL Server 2016. It acts as flight recorder or ‘black box’, capturing the history of executed SQL queries. The Query Store feature provides DBAs with insight on query plan choice, and performance. It simplifies performance troubleshooting by helping you quickly find the longest running and most resource-intensive queries.
Enabling Query Store
The first step to using Query Store is to enable it, as it is not turned on by default. This can be done with the following SQL command:
|
1 2 3 |
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON; |
Configuring the Query Store
Once Query Store is enabled, you can configure its settings to suit your environment. Use the following command to set the size of the Query Store in MB:
|
1 2 3 4 5 6 7 8 9 10 |
ALTER DATABASE [YourDatabase] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, MAX_STORAGE_SIZE_MB = 100, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200) |
Viewing Query Store Data
You can use the following queries to view some of the Query Store runtime and wait statistics:
|
1 2 3 4 5 6 7 8 |
-- Get top 10 total duration queries SELECT TOP 10 qsrs.query_id, qsrs.plan_id, qsrs.avg_duration FROM sys.query_store_runtime_stats qsrs JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id ORDER BY qsrs.avg_duration DESC; |
Clearing Query Store Data
To clear out Query Store data, use the following command:
|
1 2 3 |
ALTER DATABASE [YourDatabase] SET QUERY_STORE CLEAR; |
Conclusion
The SQL Server Query Store is a powerful tool for tracking and optimizing your SQL queries. It’s easy to setup, simple to use, and provides valuable insights into your database’s performance. It’s definitely a tool worth integrating into your SQL Server toolkit.