I rely on Query Store every day—it lets me uncover performance issues in minutes, even without knowing the app code. If you want real insight into plan choices, regressions, and control over execution plans, you need to turn it on. Learn how to configure it, use its DMVs, spot plan changes fast, and finally answer, “Why was my query slow?”
Turn It On: The Power of
Query Store for Rapid
Performance Tuning
Monica Morehouse (Rathbun)
Consultant, Denny Cherry and Associates Consulting
1. Drag & drop your photo onto
the slide.
2. Resize & crop so the key part
of the photo will show up
here. You will want the
picture to go to ALL THE WAY
to the top, bottom, and left
side. On the right, position it
so it only just barely covers
the red line.
IMPORTANT: Only resize photos
using the CORNER SQUARE to
maintain proportions (never have
distorted photos in your slides).
Use that to get the HEIGHT you
need (so it touches the top and
bottom of the slide. Use CROP
(double click the photo and it will
show up under “picture format”
tab) to get the photo to the
WIDTH you need.
Presentation Rules
Always Ask Questions
Interrupt me
This is a two-way conversation
let’s learn from each other’s
experiences
QUERY STORE
BAD REP
Bad Reputation Why? (In the beginning…….)
Query Store Capture Mode in SQL Server
2016 and 2017 the default for this AUTO
ALL which
which
reduced
overhead
your
created
capturecapture
overhead
on youron
server.
server.
“SIMPLY CHANGED
“DBA’s and Bosses
ALL to AUTO and
were SAD”
POOF GONE”
If it runs MORE THAN 3 TIMES it CAPTURES IT else doesn't
MILLIONS OF DATABASES
There is no reason NOT to TURN IT ON!
CONFIGURATION BASICS
Database Scoped
USE [master]
GO
ALTER DATABASE [AdventureWorks2019]
SET QUERY_STORE
(OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO)
CAUTION
ALL means capture the one
offs, and you don’t want this
AUTO – need to run multiple
times
WHAT IS
QUERY STORE
and
WHY DO I CARE?
QUERY STORE
FOR TUNING
REGRESSED QUERIES
Plans that have gotten WORSE over a time period
COMPARING PLANS
THINGS TO LOOK
AT
What’s
different?
Use to see
tuning
improvements
Compare after
upgrades
COMPARING PLANS EXAMPLE
DIVE INTO THE PROPERTIES
QUERIES WITH FORCED PLANS
FORCE LAST GOOD
PLAN
SADLY,
NOT ON IN FABRIC
ALTER DATABASE
SET
AUTOMATIC_TUNING (
FORCE_LAST_GOOD_PLAN =
ON );
CPU Gain > 10 seconds
Number of errors in new
plan > than recommend
Plan
Verified Force Plan is Better
than Current
sys.dm_db_tuning_recommendations (Transact-SQL)
FORCED PLAN
RECOMMENDATIONS GAINS
UNFORCED WHY?
Reason
Description
ONLINE_INDEX_BUILD
query tries to modify data while target table has an index that is being
built online
INVALID_STARJOIN
plan contains invalid StarJoin specification
TIME_OUT
Optimizer exceeded number of allowed operations while searching for
plan specified by forced plan
NO_DB
A database specified in the plan does not exist
HINT_CONFLICT
Query cannot be compiled because plan conflicts with a query hint
DQ_NO_FORCING_SUPPORTED
plan conflicts with use of distributed query or full-text operations.
NO_PLAN
forced plan could not be verified to be valid for the query
NO_INDEX
Index specified in plan no longer exists
VIEW_COMPILE_FAILED
problem in an indexed view referenced in the plan
GENERAL_FAILURE:
problem in an indexed view referenced in the plan
REASONS FOR UNFORCE
FIND ANOMALIES QUICKLY
OVERALL RESOURCE CONSUMPTION
OVERALL RESOURCE CONSUMPTION
FIND PERFORMANCE WINS QUICKLY
OVERALL RESOURCE CONSUMPTION
QUERY STORE
HINT
SQL 2022
Hint by query id
EXEC sp_query_store_set_hints
@query_id=6, @value =
N'OPTION(RECOMPILE)'; GO
QUERY STORE HINTS
QUERY STORE
IN THE CLOUD
Understanding where the insights
come from
QUERY PERFORMANCE INSIGHTS
QUERY DETAILS
Note the Query ID
this links directing
to Query Store
See RunTime Stats
per hour
Pay Attention to
CPU/Data IO
Execution Counts
can be HUGE
indicator of death
by 1000 cuts
PERFORMANCE
DASHBOARD
FABRIC
READ ONLY
REPLICAS
Things to watch out for when you
offload your workload and need to
performance tune.
QUERY STORE IS REPLICATED
Data is
Primary
Workload
This is of
NO USE TO YOU!
Sad, I
know
NOW LIVE - CAUTION – PREVIEW
ON SQL 2022 (with Trace Flag) 2025 DEFAULTED
Shared Channel that
Keeps Secondary Up to
Date
Larger Query Store
Delayed Durability on
Overload
QUERY STORE ON Primary STORES Secondary
ALTER DATABASE
[Database_Name]
FOR SECONDARY
SET QUERY_STORE = ON
(OPERATION_MODE =
READ_WRITE );
DEMO
How was
the session?
Complete Session Surveys in
for your chance to WIN
PRIZES!
Monica Rathbun MRathbun@sqlespresso.com
@SQLEspresso sqlespresso.com
/in/sqlespresso