SQL Server 2016Live Query Statistics& Query Store

Pre

sen

ter

Info

1976 Born. The world is now a safer place to be :P

1987 My first computer was a Sinclair ZX Spectrum

1995 I started my professional career in computers

industry as an all-around-all-weather IT-guy while

studying.

Later that year I got introduced to SQL Server 6.5

2000

.

.

.

Microsoft Certified Professional and MCT on 2005.

Staff happening…

I got older and wiser while still educating myself on

SQL Server and other staff that made me what I am

today

2007 I became father to a wonderful boy!

.

.

.

Staff happening…

2016 Here we are now looking at me saying about

myself (recursion-alert)

Vassilis IOANNIDIS a.k.a. “Bull”

SQL Server Geek MCT, MCSD, MCDBA, MCSA, MCTS, MCPS

SQ

Lsch

ool.g

rTe

am

Antonios ChatzipavlisSQL Server Evangelist • Trainer

Vassilis IoannidisSQL Server Expert • Trainer

Fivi PanopoulouSystem Engineer • Speaker

Sotiris KarrasSystem Engineer • Speaker

Follo

w u

sin

soci

al m

edia

@sqltattoo / @sqlschool

fb/sqlschoolgr

yt/c/SqlschoolGr

SQL School Greece group

Help

needed?

help@sqlschool.gr

Pre

senta

tion

Conte

nt

Live Query Statistics

Query Store

Live Query Statistics

Let’s lay the basis Say hello world to Query Optimizer! High-level bits of the engine running, under the hood of SQL Server Procedure Cache Buffer Pool Execution Plans

Live Query Statistics (LQS) Definition and aim When and why Meanwhile, behind the scenes…

Limitations and permissions Demo Summary

Say hello world to Query Optimizer!The Query Optimizer of SQL Server is a cost-based optimizer. Its work is to

analyze candidate execution plans, and based on the cost each one produce, to

choose the one with lowest estimated cost. It is part of the Query Processor which

one of the two main components of the Relational Engine of SQL Server, with the

other one being the Storage Engine.

LQS: Let’s lay the basis

Say hello world to Query Optimizer! (cont.)

….This means that this tiny part of the engine holds a gigantic affect on the

performance of your instance!

So keep it happy and feed it regularly! With what??

Statistics of course!

LQS: Let’s lay the basis

When the Jr. DBA tells me he fixed the parameter sniffing issue by setting up a

job to update statistics every 2 hours. (HT @SQLsoldier)

Bonus slide!

High-level bits of the engine running under the hood Procedure cache

A place in memory, where all your execution plans reside. Well, at least one for

each query.

To clear the plan cache use: DBCC FREEPROCCACHE

Governed by the Query Processor.

Buffer pool

A place in memory, also, where the cached data resides, and is first checked before

hitting on the disk and producing physical I/O.

To clear the buffer pool use: DBCC DROPCLEANBUFFERS

Governed by the Storage Engine.

LQS: Let’s lay the basis

High-level bits of the engine running under the hood (cont.)

Execution Plans

Two kinds of executions plans: Estimated and Actual

Even in Actual plans, exists estimate numbers based on …statistics

Tip: Try not to have the following iterator: lookup, hash, sort, spool, nested (serial) loop and

scans …maybe.

LQS: Let’s lay the basis

Live Query Statistics, are a brand new feature of the latest

SQL Server Management Studio namely “2016”, which

allows you to witness the live execution of the plan chosen

by the Query Optimizer for the running query!

Sounds awesome, right? …right!

LQS: Definition and aim

Warning! This feature is primarily intended for troubleshooting purposes. Using this feature can moderately slow the overall query performance.

You’ve been warned!

So, LQS is great for troubleshooting and debugging mainly long running queries or queries

that are never ending or overflowing the poor old-tempdb! Timeout issues could be also a

thingy to investigate with QLS.

…or it you could just run it because you like to see data flow around from operator to

operator and fancy yourself as the almighty “data whisperer”.

LQS: When, Why, How

LQS: When, Why, How (cont.)

Can be activated in one of 3 ways:

Tada…. enters sys.dm_exec_query_profiles DMV!

So, what we see as Live Query Statistics is a very nice and visual interpretation of the

sys.dm_exec_query_profiles DMV

Nonetheless you gotta love the easiness it provides for quick execution insight!

Just love it! Debug-debug-debug! New tools under your belt! Use them wisely!

LQS: Meanwhile, behind the scenes…

LQS: Meanwhile, behind the scenes…

Limitations The live windows will not show up if the query:

is using column store indexes

Is using memory optimized tables

Native Stored Procedures are not supported

The good thing is that you can utilize it from SQL Server 2014 SP1 and onwards by

installing the latest SSMS, which now is not included in the initial setup of SQL

Server.

Permissions Requires the database level SHOWPLAN permission to populate the Live Query

Statistics results page, the server level VIEW SERVER STATE permission to see the

live statistics, and requires any permissions necessary to execute the query.

Live Query Statistics in Action

Live Query Statistics

View CPU/memory usage, execution time, query progress, and more

Enables rapid identification of potential bottlenecks for troubleshooting

query performance issues

Allows drill down to live

operator level statistics

Number of generated rows

Elapsed time

Operator progress

Live warnings

LQS: Summary

Query Store

Definition and aim

Architecture-wise where does it stand?

How QS collects data

Upgrading to SQL 2016

Ways to manage and monitor QS

Best practices

Demo

Summary

“The SQL Server Query Store feature provides you with insight on query plan

choice and performance.

It simplifies performance troubleshooting by helping you quickly find performance

differences caused by query plan changes.

Query Store automatically captures a history of queries, plans, and runtime

statistics, and retains these for your review.

It separates data by time windows so you can see database usage patterns and

understand when query plan changes happened on the server.”

Query Store: Definition and aim

With the power vested to you by the Query Store you, the

DBA, are in control now!

Dedicated store for query workload performance data Captures the history of plans for each query

Captures the performance of each plan over time

Persists the data to disk (works across restarts, upgrades, and recompiles)

Significantly reduces TTD/TTM Find regressions and other issues in seconds

Allows you to force previous plans from history

Query Store: Definition and aim (cont.)

Remember this?

Query Store: Architecture-wise where does it stand?

…now Query Store enabled!

Query Store: Architecture-wise where does it stand?

Query Store works as a flight data recorder. The information is stored in tables and the

following views are used to display the info to the users:

Query Store: How QS collects data (Part 1/5)

Exposed views

Query

textQuery Plan

Runtime

stats

Context

settings

Runtime

stats

interval

One row per query text per plan

affecting option

(example: ANSI NULLS on/off)

One row per

plan (for

each query)

One row per plan

per time interval

(example: 5 min)

1 - n

Compile stats:

query_store_query_text

query_context_settings

query_store_query

query_store_plan

Runtime stats:

query_store_runtime_stats_interval

query_store_runtime_stats

sys.

Internal tables

1 - n

.

Query Store: How QS collects data (Part 2/5)

What happens after you hit “F5”… or you know what I mean.

Nuts and bolts of QS continues…

Query Store: How QS collects data (Part 3/5)

Nuts and bolts of QS continues…

Query Store write architecture

Query Store: How QS collects data (Part 4/5)

Query Store

Query

Execution

Internal

tables

Query and

Plan Store

Runtime stats

storeQuery exec. stats

Compile

Execute

async

Query text and plan

Nuts and bolts of QS continues…

Query Store read architecture

Query Store: How QS collects data (Part 5/5)

Query StoreQuery Execution

Internal

tables

Query and

Plan Store

Runtime stats

storeQuery exec. stats

Compile

Execute

async

Query text and plan

Query Store views

Keeping stability while upgrading to SQL Sever 2016

Query Store: Upgrading to SQL 2016

Install bits

Keep existing

compat. level

Run Query Store

(create a

baseline)

Move to vNext

CompatLevel

Fix

regressions

with plan

forcing

Enable/disable Query StoreALTER DATABASE <database_name> SET QUERY_STORE = {ON|OFF};

Is Query Store currently active?SELECT actual_state, actual_state_desc, readonly_reason,

current_storage_size_mb, max_storage_size_mbFROM sys.database_query_store_options;

Get Query Store optionsSELECT * FROM sys.database_query_store_options;

Set all Query Store optionsALTER DATABASE <database name>

SET QUERY_STORE (

OPERATION_MODE = READ_WRITE,

CLEANUP_POLICY =

(STALE_QUERY_THRESHOLD_DAYS = 30),

DATA_FLUSH_INTERVAL_SECONDS = 3000,

MAX_STORAGE_SIZE_MB = 500,

INTERVAL_LENGTH_MINUTES = 15,

SIZE_BASED_CLEANUP_MODE = AUTO,

QUERY_CAPTURE_MODE = AUTO,

MAX_PLANS_PER_QUERY = 1000

);

LQS: Ways to manage and monitor QS

Use the latest SSMS

Use Query Performance Insight in Azure SQL Databases

Using Query Store with Elastic Pool Databases

Keep Query Store adjusted to your workload

How to start with query performance troubleshooting

Verify Query Store is collecting data continuously

Set the optimal Query Capture Mode

Check the status of Forced Plans regularly

Avoid renaming databases if you have plans forced

Keep the Most Relevant Data in Query Store

Query Store: Best practices

Query Store in Action

Capability

Query Store helps customers quickly find and fix query performance issues

Query Store is a ‘flight data recorder’ for database workloads

Benefits

Greatly simplifies query performance troubleshooting

Provides performance stability across SQL Server upgrades

Allows deeper insight into workload performance

Query Store: Summary

Apart from my passion for SQL, other sources were used to make this presentation a reality:

MSDN

TechNet

BOL

Many blogs from around the web

…and of course our very own sqlschool.gr

S E L E C T K N O W L E D G E F R O M S Q L S E R V E R

Copyright © 2015 SQLschool.gr. All right reserved. PRESENTER MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION