Introduction to Query Store

Many a time we come across a scenario where suddenly in production without any release or changes some query which was working perfectly alright till yesterday is taking too long to execute or consuming lot of resources or timing out.

Most of the times such issue are related to execution plan change (commonly referred as Plan Regression). Till yesterday the query was running fine as it was running with good cached execution plan and today a bad plan is generated and cached. Because of this bad cached plan the query which was executing perfectly alright suddenly starts misbehaving.

To identify and fix such performance problems due to the execution plan change the Query Store feature introduced in Sql Server 2016 will be very handy.

Query Store basically captures and stores the history of query execution plans and its performance data. And provides the facility to force the old execution plan if the new execution plan generated was not performing well.

To understand Query Store with an example let us create a demo database SqlhintsQSDemo and a Customer table having sample data as shown in the following image by executing the following statement:

Script:

--------Create Demo database--------
CREATE DATABASE SqlhintsQSDemo
GO
USE SqlhintsQSDemo
GO
--------Create a Customer Table------
CREATE TABLE dbo.Customer( 
 Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Populate 100,000 customers with unique FirstName 
INSERT INTO dbo.Customer (FirstName, LastName)
SELECT TOP 100000 NEWID(), NEWID()
FROM SYS.all_columns SC1 
	CROSS JOIN SYS.all_columns SC2
GO 
--Populate 15000 customers with FirstName as Basavaraj
INSERT INTO dbo.Customer (FirstName, LastName)
SELECT TOP 15000 'Basavaraj', NEWID()
FROM SYS.all_columns SC1
        CROSS JOIN SYS.all_columns SC2

Execute the following statement to create a non-clustered index on the FirstName column of the customer table and the stored procedure GetCustomersByFirstName to get the customer details by FirstName

-- Create non-clustered index on the FirstName column
CREATE INDEX IX_Customer_FirstName on dbo.Customer (FirstName)
GO
-- Create stored procedure to get customer details by FirstName
CREATE PROCEDURE dbo.GetCustomersByFirstName
(@FirstName AS NVARCHAR(50))
AS
BEGIN
	SELECT * FROM dbo.Customer 
	WHERE FirstName = @FirstName
END

Enabling Query Store

Query store is a database level feature, which is disabled by default. Follow the following steps to enable it by using Management Studio

Step 1: Right click on the demo database SqlHintsQSDemo and select the Properties menu option as shown in the below image

Step 2: Step 1 Pops-up the properties window. In the properties window from the left navigation options select the new option Query Store. Below is the view of the properties window after selecting the Query Store option.

Step 3: From the Query Store option window of Step 2 we can see that the Operation Mode (Requested) setting has three different options. Selecting the ReadWrite option will enable the Query Store and starts capturing the query execution data such as execution plan and it’s performance stats. Selecting Read option will only allow the data to read from the query store but no new query data is captured.
Below image shows the various query store options after selecting the ReadWrite Operation Mode. To know each of these options select the option it will display the details about it below.

Query store captured data is stored in the respective query store enabled database. As we can see from the above image 100 MB is reserved for query store data in the SqlhintsQSDemo database. This value can be changed by changing the Max Size (MB) option value.

Click OK after selecting the desired query store options, it will enable the query store. And if we refresh the SqlhintsQSDemo database in the object explorer we can see the Query Store folder as shown in the below image

Now query store feature is enabled let us understand how it comes handy in resolving performance issues

Example Usage of Query Store feature to resolve Performance issue

This example explains the scenario where suddenly in production without any release or changes some query which was working perfectly alright, starts taking too long to execute or consuming lot of resources or timing out. And how the query store comes handy in such situation

To explain this we will use the stored procedure GetCustomersByFirstName to fetch the records from the Customer table based on FirstName. And the Customer table has unique FirstName for all the customers except for the FirstName ‘Basavaraj’. There are 15,000 customers with FirstName as ‘Basavaraj’, so fetching a record where CustomerName is ‘Basavaraj’ will be better with table scan (i.e. clustered index scan) than using the non-clustered index on FirstName. But fetching the Customer records where FirstName value is anything other than ‘Basavaraj’, it makes sense to use the non-clustered index on FirstName. In this case where the majority queries will be better of executing by using the Non-clustered index on FirstName

Let us execute the following statement to get the details of the Customer whose FirstName is Xyz. While executing the below statement select the “Include Actual Execution Plan” option.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Xyz'

Result:

From the execution plan we can see that the Stored Procedure is using the Non-clustered index seek on the FirstName column to fetch the Customer details, which is the optimal option too.

From the object explorer select the “Top Resource Consuming Queries” option as shown in the below image to view the query plan and the performance stats capture for the above SP execution in the Query Store

Below is the Query Store view of the “Top Resource Consuming Queries”. Here we can see that Plan Id 6 is corresponding to the above stored procedure execution which was using Non-Clustered Index Seek. Mouse over on the bar corresponding to the query which got executed by the SP GetCustomersByFirstName shows that it is executed ones, has one execution plan, it took 16129 micro seconds and query id is 6.

Now the execution of the GetCustomersByFirstName will always use the Non-Clustered index on the FirstName column, as the first time execution of it cached this Non-Clustered Index Seek plan. So as long as this plan is cached the query where we need to get the Customer data by FirstName will always execute optimally. The only way this cached query plan is removed from the cache if the server is re-started or the memory pressure on the server etc. We can also execute the following DBCC statement to remove all the cached plans from the database server

DBCC FREEPROCCACHE

After executing the above DBCC statement all the cached plans are removed from the cache. Now try executing the same stored procedure i.e. GetCustomersByFirstName but this time pass the @FirstName parameter value as Basavaraj instead of Xyz. While executing the following statement select the “Include Actual Execution Plan” option.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Basavaraj'

Result:

From the result we can see that instead of using Non-Clustered Index on FirstName, it is doing the Clustered Index Scan. The reason it is doing clustered index scan is, there are 15,000 records whose FirstName is ‘Basavaraj’ and there are no previously cached plans. It makes sense for the Sql Server to use Clustered Index scan, instead of using a Non-Clustered index seek which uses bookmark look-up for each of the record. Now this plan is cached and each time the SP: GetCustomersByFirstName is executed it will use the clustered index scan

Below is the view of the Query Store after executing the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Basavaraj’. You need to refresh the query store if it is already open. Here we can see a new plan with plan id 11 is generated which corresponds to the clustered index scan. So we can see that now we have two query plans corresponding to the query executed by the stored procedure GetCustomersByFirstName.

Let us again execute the following statement to get the details of the Customer whose FirstName is Xyz. While executing the below statement select the “Include Actual Execution Plan” option.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Xyz'

Result:

From the result we can see that this time the stored procedure execution is using the Clustered index scan instead of the non-clustered index scan which it was doing previously. To get the customer details whose FirstName is ‘Xyz’, from the result we can see it is using the cached plan which is optimized/compiled for the @FirstName parameter value ‘Basavaraj’. This is how a perfectly working query starts misbehaving without any release or changes.

Below is the view of the Query Store after executing the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Xyz’. You need to refresh the query store if it is already open. Mouse over on the bar corresponding to the query which got executed by the SP GetCustomersByFirstName shows that it is executed 3 times and has two execution plans i.e. 6 and 11 and plan 11 which is a clustered index scan is the current plan. In the below image we can clearly see than plan 11 is taking more time to execute compared to plan 6. So, in this case it is optimal to execute the query with plan 6 compared to plan 11.

From the above query store view it is clear that plan 6 is the optimal plan for the execution of the stored procedure GetCustomersByFirstName compared to plan 11 as majority of the time the stored procedure is executed with a parameter value other than ‘Basavaraj’.

As shown in the below image query store gives an option to force the execution plan for the future execution of the queery. To force the plan select the plan 6 (i.e. light blue color circle) and then click on the Force Plan option.

After forcing the plan 6 as shown in the above image. Now execute the GetCustomersByFirstName one more time with @FirstName parameter value as ‘Xyz’.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Xyz'

Result:

From the above result we can see that execution of the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Xyz’ is doing a non-clustered index scan compared to clustered index which it was doing previously.

Below is the view of the Query Store after forcing the execution plan 6 and executing the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Xyz’. We can see that plan 6 circle has a right tick mark, which means this is the force plan which now will not change even after server re-start. As shown in the below image adjacent to Force Plan option we have an Un-force Plan which an be used to un-force this forced plan.