MyView24By7: Query Store in SQL 2016

Query Store

The SQL Server Query Store feature provides DBAs with insight on query plan choice and performance.
It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans.
The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.
It separates data by time windows, allowing you to see database usage patterns and understand when query plan changes happened on the server.

Step 2: Enable Query Store

alter database current set query_store(interval_length_minutes=1)

alter database current set query_store=on

Right Click and Go to Database Properties

Step 3: Create Test data load

· Create a database and enable query store with 1 minute interval

· Load 10000 unique records starting from 0

· Create sque data load by loading 100000 records with value “1″

CREATE TABLE MYTABLE (col1 int,col2 int, col3 binary(2000));

DECLARE @i int =0

WHILE @i <10000

BEGIN

INSERT INTO MYTABLE(col1,col2) values (@i,@i)

SET @i+=1

END

INSERT INTO MYTABLE (COL1,COL2) VALUES (1,1)

GO 100000

CREATE INDEX i1 on mytable(col1)

CREATE INDEX i2 on mytable(col2)

Step 4: Randomly Query Data

Open Power shell script and update Datasource & Initial and save it.

Now Execute PowerShell script. (QueryStoreDemo.ps1)

This will fetch the data in random order by using a random number generator and then frequently we will flush the plan so that it will re-compiled and new parameter will get sniff will cause plan change.

Now while the script is running let us see how the query store helps us in analyzing this problem.

If you go to Query Store folder and select “Top resource consuming queries” it will show all the queries running with duration (there are other options e.g. cpu, read, write) and all the plans with plan id’s graphical plan.

Step 5: Compare Plans

This feature also provides the facility to compare both the plans and find out what’s the actual issue. You can click on “Compare plan” option in plan summary above.