Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Query Store Example


By:   |   Read Comments (2)   |   Related Tips: More > SQL Server 2016

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:

Open Pane

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

Open Pane and change view

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

Test 1 - table scan

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:

Plan #2

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:

Plan #3

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:

Grid View

You can see now all three plans with detailed statistics:

Grid View

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:

Force Plan

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):

Forced Plan in chart view

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:

New plan after un-forcing plan

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


Last Update:






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

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, July 27, 2016 - 9:38:04 AM - Svetlana Back To Top

Thank you.

 

Yes, the plan ID is assigned by SQL Server and could be different.


Wednesday, July 27, 2016 - 5:33:35 AM - Dev Back To Top

Thank you for such a great post. Made the learning so easy. Just a note, the plan id number can appear different.


Learn more about SQL Server tools