By: Svetlana Golovko | Last Updated: 2015-09-23 | Comments (4) | SQL Server 2016
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.
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:
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.
- 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.
Last Updated: 2015-09-23
About the author
View all my tips