SQL Server 2016 Query Store

#SQLSAT454

SQL Server 2016 Query Store

Sergio Govoni

@segovoni

#SQLSAT454

#SQLSAT454

Speaker BIO

#SQLSAT454

Agenda

 Query plan change

 Can cause performance issues

 Why do plan changes happen?

 Problems you face for slow running queries

 What does the Query Store do for you?

#SQLSAT454

QP changes can cause performance issues

• Database is not working • How to fix it ASAP?

Web site is

down

• Impossible to predict the root cause

Temporary

perf. issues

• Performance regression caused by the new version

SQL Server

upgrade

#SQLSAT454

Fixing query plan regression is hard

 Problems you face for slow running queries

 Time of the day and compiled parameters

 Has the Data Collector been activated?

 Plan cache may not be suitable for troubleshooting

 You have to find out why it is show

 When you have the issue on you hands

 Can you modify the query text?

#SQLSAT454

Let’s do a quiz

 Who is the author of this painting?

A. Pablo Picasso

B. Vincent Van Gogh C. Claude Monet D. Pierre-Auguste Renoir E. Query Optimizer  P₁ P₂ P₃ P₄

#SQLSAT454

Why do plan changes happen?

 Query Optimizer

generates and evaluates many plans for the same query

 Data changes every hour, so the QO might select a different plan

 Generally, boundary plans have the same

#SQLSAT454

What does the Query Store do for you?

 It stores all the plan choices and related performance metrics for each query

 It identifies queries that have become slower recently

 It allows DBA to force an execution plan easily  It makes sure your changes work across server

restart, upgrades, failover and query recompiles

#SQLSAT454

#SQLSAT454

How the Query Store exposes data

sys.query_store_runtime_stats

On-Disk Data In-Memory

Data

 Each DMV joined In-Memory and On-Disk data

 When memory pressure occurs, some data

In-Memory will be flushed to the disk

#SQLSAT454

How the Query Store interprets the query text

 Query text starts from the first character of the first token (of the statement) and it ends at the last character of the last token

 Comments and spaces, before or after, don’t

change anything

 Comments and spaces inside count

 sys.query_store_query_text

 query_text_id is used to force a plan for a query

 statement_sql_handle contains the MD5 hash of the query text

#SQLSAT454

Query Store DMV overview

Query_Text Query_ANSI Query_Plan Runtime_Stats

1:N 1:N 1:N

Context_Settings _{Runtime_Stats_Interval} ANSI

Options

Execution plans Metrics: 1 row per plan per time interval

One row per unique combination of set options

One row per time interval

#SQLSAT454

Demo Environment

 A console application executes this query in a loop on a database where Query Store is

enabled

 Parameters are generated by a randomized function, values are between Zero and 100

 Plan cache is cleaned when parameters values are less than 2

#SQLSAT454

DEMO

#SQLSAT454

Query Store Tuning

 Because collecting data has always a cost, SQL Server allows you to tune the Query

Store with these options of ALTER DATABASE

statement

 INTERVAL_LENGTH_MINUTES

 MAX_STORAGE_SIZE_MB

 MAX_PLANS_PER_QUERY (200 by default)

 QUERY_CAPTURE_MODE (ALL, AUTO, NONE)

#SQLSAT454

Server Upgrades

 Are you worried about SQL Server upgrades? This is the way out

1. Restore a copy of the production DBs on a test server

2. Run a significative workload

3. Look at the Query Store for performance regressions

 Plan forced works across

 Recompilation

#SQLSAT454

Live Query Statistics

 Allows you to see the live execution plan of an

active query

 It provides a way to better understand the

query execution process and the control flow  It is extremely useful (and cool ) for

debugging query performance

 It is based on sys.dm_exec_query_profiles

#SQLSAT454

DEMO

#SQLSAT454

Live Query Statistics on SQL 2014 (SP1)

 At the server side

 Download and install the Service Pack 1 for SQL 2014 (if you have not already done)

 At the client side

 Download and install the latest version of SSMS (September 2015 preview)

 Enable the Live Query Statistics

 Play with it  on a test machine.. SQL Server will make some effort to produce the data for this tool!

#SQLSAT454

Summary: With Query Store you can..

Easily fix plan regressions Analyze your workload Find the most expensive queries De-risk SQL Server upgrades

#SQLSAT454

Resources

 [docs.com]

 SQL Server Query Store

 SQL Server 2016 Video Pills

#SQLSAT454

Q&A

#SQLSAT454

Evaluations

 Don’t forget to compile evaluations form here

 http://speakerscore.com/sqlsat454

#SQLSAT454

THANKS!

Thanks for attending this session. If you have additional questions, please post them on the MSDN SQL Server Forums (ITA | ENG)

#sqlsat454