Database administrators, software developers and system administrators all can be challenged with ensuring their applications are running at peak performance. A key part of this is ensuring the database is running with optimal performance. A very useful tool for monitoring and trouble shooting Micrsoft’s SQL Server performance is the Query Store. This was added in the 2016 version.
The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and run time statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server.
SQL Server creates query execution plans to execute a query optimally. The CPU, IO, and memory are some of the parameters SQL Server uses in recognizing the cost and finally generating the execution plan. You can see the plan visually and see how SQL server executes a query and then take action to optimize. Below is an example query plan.
You can configure query store using the following query:
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
You can also enable it from SQL Management Studio by right clicking on the database, go to Properties, go to Query Store tab and enable it.
This is a very powerful tool our team has been able to utilize to identify slow performing queries and optimize them. It also suggests missing indexes, but you have to be careful about implementing those suggestions as it might slow down other queries operating on the same database. So, ideally you will make those changes in development and use load testing tools to try and determine impact.
To analyze data collected by query store, follow the steps below:
- Go to SQL Management Studio
- Expand (+) the database you had enabled the query store on
- Expand (+) the folder called “Query Store”
Access query store reports - Under the query store you will see sub-options:
- Regressed Queries – It shows queries plans that have deteriorated over time.
- Top Resource Consuming Queries – As the name suggests, it tracks queries having top resource consumption. It also suggests missing indexes which can be useful.
- Query Wait Statistics – It shows wait time statistics for all queries.
- Queries With High Variations – It shows queries that deviate a lot in their execution time. For example, a query may sometimes take a second but other times it takes 10 seconds. This can help you to look for potential causes of bottlenecks in your query performance.
- Tracked Queries – This is used when you need to track execution of a specific query. You will need to use process ID of the query to track it. Go to Top Resource Consuming Queries (prior screenshot). From there you can get the query (process) ID. See screenshot below.
- Overall Resource Consumption – It shows resources used by queries during a specific time range. Below are the parameters that are used. When you go to that option it will show how resources are being used. By default, 4 resources are shown which you can change. See screenshots below.
1. CPU
2. Logical Reads
3. Logical Writes
4. Physical Reads
5. Execution Count
6. CLR Time
7. Memory Consumption
8. Row Count
9. Wait Time
10. Temp DB Memory Used
Default view:
You can customize the view. Select resources, timeframe etc.
Keeping your SQL server database optimized and running at peak performance is critical. The Query Store tools can be incredibly useful in monitoring and troubleshooting performance bottlenecks. Regularly checking the Query Store can help to head off performance troubles.