SQL Server 2016 Query Store Example

By: Svetlana Golovko   |   Comments (4)   |   Related: > Query Store

Problem

One of the new features in SQL Server 2016 is the Query Store. In our last tip, we reviewed how to configure and access the Query Store with SQL Server Management Studio (SSMS). We also provided a detailed overview of one Query Store reports in SSMS. In this tip we will show you an example on how to use the Query Store, how to track multiple execution plans and how to force an efficient plan.

Solution

We will create a database for this demo with a table and a stored procedure. The table will be populated with enough demo data to show the differences in execution plans.

Create Demo Query Store Database

CREATE DATABASE [qstore_demo]
 ON  PRIMARY 
( NAME = N'qs_demo', FILENAME = N'C:\DATA\qs_demo.mdf' , SIZE = 102400KB , 
		MAXSIZE = 1024000KB , FILEGROWTH = 20480KB )
 LOG ON 
( NAME = N'qs_demo_log', FILENAME = N'D:\DATA\qs_demo_log.ldf' , SIZE = 20480KB , 
		MAXSIZE = 1024000KB , FILEGROWTH = 20480KB )
GO
ALTER DATABASE [qstore_demo] SET AUTO_UPDATE_STATISTICS OFF 
GO
ALTER DATABASE [qstore_demo] SET AUTO_CREATE_STATISTICS OFF 
GO
ALTER DATABASE [qstore_demo] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [qstore_demo] SET QUERY_STORE = OFF
GO

We have created a database with AUTO_UPDATE_STATISTICS off and AUTO_CREATE_STATISTICS off to have as little optimizations as possible. This is for the demonstration purpose only.

We have not enabled the Query Store for now. The table will be populated with data and we do not want expensive INSERT queries to be in the Query Store.

Now we will create the table and the procedure. We will populate the table and we will enable the Query Store on the database:

USE qstore_demo
GO
-- create a table
CREATE TABLE dbo.db_store (c1 CHAR(3) NOT NULL, c2 CHAR(3) NOT NULL, c3 SMALLINT NULL)
GO
-- create a stored procedure
CREATE PROC dbo.proc_1 @par1 SMALLINT
AS 
SET NOCOUNT ON
SELECT c1, c2 FROM dbo.db_store
WHERE c3 = @par1
GO
-- populate the table (this may take a couple of minutes)
SET NOCOUNT ON
INSERT INTO [dbo].db_store (c1,c2,c3) SELECT '18','2f',2
go 20000
INSERT INTO [dbo].db_store (c1,c2) SELECT '171','1ff'
go 4000  
INSERT INTO [dbo].db_store (c1,c2,c3) SELECT '172','1ff',0
go 10
INSERT INTO [dbo].db_store (c1,c2,c3)   SELECT '172','1ff',4 
go 15000
-- enable Query Store on the database
ALTER DATABASE [qstore_demo] SET QUERY_STORE = ON
GO

SQL Server 2016 Query Store Performance Tests

Test 1 - No Indexes on the Table

In our first test we will execute the stored procedure on the table without any indexes. Then we will review the results in the SSMS Query Store reports. We will run the procedure 20 times just to generate enough executions and I/Os to easily find it among other queries in the Query Store:

EXEC dbo.proc_1 0
GO 20

In SSMS under "Query Store" database's container open "Top Resource Consuming Queries" pane:

Change the vertical axis to use "exec count" and change the "Metric" drop down from "Duration" to "Logical Reads":

Here is our query (Query #1) with a single table scan plan (Plan #1):

Test 2 - Testing with a Non Clustered Index

In this test we will create a non-clustered index and execute the stored procedure again:

CREATE NONCLUSTERED INDEX NCI_1
ON dbo.db_store (c3)
GO
EXEC dbo.proc_1 0
GO 20

Note, that we will be creating several indexes (which are duplicate indexes) that are created for the demonstration purposes only in order to generate multiple execution plans.

Go back to the Query Store pane in SSMS and refresh the results:

Note, that the new plan (Plan #13) was created and used. This plan has less logical reads now.

Test 3 - Create Another Non Clustered Index

Now we will create the new index with an additional column:

CREATE NONCLUSTERED INDEX NCI_2
ON dbo.db_store (c3, c1)
GO
EXEC dbo.proc_1 0
GO 20

Review the results in SSMS and note that another plan (Plan #20) was created:

Force Plan

Let's run some table updates and execute the procedure again:

UPDATE  dbo.db_store SET c1  ='1' WHERE c3 = '0'
UPDATE  dbo.db_store SET c2  ='3ff' WHERE c3 = '1'
DELETE FROM dbo.db_store  WHERE c3 = 3
INSERT INTO  dbo.db_store (c1,c2,c3) SELECT '173','1fa',0
GO 5

EXEC dbo.proc_1 0
GO 20

Go back to the Query Store pane in SSMS and click "View plan summary in a grid format" button:

You can see now all three plans with detailed statistics:

Note, that the latest plan #20 has more logical reads than the old plan #13.

Click on the "Force Plan" button under the "Plan Summary" or "Execution Plan" section of the Query Store report. You can also right click on plan #13 and force the plan as well. Confirm that you want to force this plan for the query:

Another way to force the plan is by using sp_query_store_force_plan stored procedure:

EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 13;
 

Run the stored procedure again. Then go back to the Query Store pane in SSMS. Refresh the Plan Summary and note that the "plan forced" column value is now set to 1 for the "plan id" #13. The "exec count" for this plan is increased as well which means that the forced plan #13 was used this time instead of plan #20:

Here is how forced plan looks in the chart format (checked bubble):

Create the final, optimal index for the stored procedure:

CREATE NONCLUSTERED INDEX NCI_3
ON dbo.db_store (c3)
INCLUDE (c1,c2)

If you run the procedure again you will notice that forced plan will be used again instead of creating the new plan with the new index.

Now we will un-force plan #13 and run the stored procedure again:

EXEC sp_query_store_unforce_plan @query_id = 1, @plan_id = 13
GO
EXEC dbo.proc_1 0
GO

Here are the results:

The new plan #23 has been created and used. It has the least number of logical reads.

Note: This tip was written based on SQL Server 2016 CTP 2.2. Some screens and behaviors may change in the final release of SQL Server 2016.

In our next tip we will provide a couple of useful queries that you can run against the Query Store.

Next Steps

• Come back soon to read the next tip about the Query Store.
• Read more about Maintaining Query Performance Stability here.
• Download the latest evaluation version of SQL Server 2016.
• Read SQL Server 2016 Books Online documentation.
• Read how to Monitor Performance By Using the Query Store.
• Read other tips on SQL Server 2016.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips