SQL Server: Introduction to Query Store

By Erin Stellato
One of the most highly anticipated new features in SQL Server 2016 is Query Store. It's been referred to as the "flight recorder" for SQL Server because it tracks query information, namely query plans and run-time statistics.
Play course overview
Course info
Rating
(62)
Level
Beginner
Updated
Jan 31, 2017
Duration
3h 2m
Table of contents
Course Overview
Course Overview 1m
Introduction
Introduction and Traditional Troubleshooting Tools 2m Baseline Data 2m Traditional Tools vs. Query Store 5m Course Objectives and Structure 3m
Defining Query Store
Introduction and Query Store Basics 3m Demo: Query Store in Action - Part 1 6m Demo: Query Store in Action - Part 2 14m Viewing Query Store and Module Summary 1m
Implementing Query Store
Introduction 0m Demo: Enabling Query Store for a Database 7m Activating and Collecting Query Store Data 2m Demo: Query Store Activation and Collection 5m Storing Query Store Data 3m Demo: Query Store Data Storage 6m Flushing and Aggregating Query Store Data 2m Demo: Query Store Data Flushing and Aggregation 11m How Query Store Integrates with Query Processing 3m Runtime Statistics and Module Summary 1m
Forcing Plans with Query Store
Introduction and Options for Addressing Poor-performing Queries 2m Demo: Using a Plan Guide to Force a Plan 10m Plan Guide Details 1m Demo: Using Query Store to Force a Plan 9m Query Store Details and Plan Guides vs. Forcing Plan 3m Demo: How to Determine if a Forced Plan is Being Used - Part 1 12m Demo: How to Determine if a Forced Plan is Being Used - Part 2 5m Common Reasons for Failed Plan Forcing and Module Summary 2m
Practical Uses of Query Store
Introduction and Understanding Performance with Query Store 4m Demo: Understanding Performance with Query Store 13m Upgrade Testing 2m Demo: Using Query Store to Test an Application Change 16m Testing Specific to the Cardinality Estimator 1m Demo: Using Query Store to Test Cardinality Estimator Changes 5m Module Summary and Course Summary 3m
Description
Course info
Rating
(62)
Level
Beginner
Updated
Jan 31, 2017
Duration
3h 2m
Description

The Query Store feature is a game changer for how SQL Server professionals troubleshoot query performance. This is the first time Microsoft has provided the ability to natively capture baseline data within SQL Server. In this course, SQL Server: Introduction to Query Store, you'll configure Query Store and understand how the settings affect the data stored. First, you'll review the different options for Query Store and will look at examples of what gets collected. Next, you'll dig through the included reports. Finally, you'll see how you can force SQL Server to use a specific plan for a query. By the end of this course, you'll be more confident with Query Store, as it will show you how to enable and configure Query Store.

About the author
Erin Stellato
About the author

Erin Stellato is a Principal Consultant with SQLskills and a SQL Server MVP. She has worked as a SQL Server professional since 2003 and her interests include Internals, Performance Tuning, High Availability and Disaster Recovery. Erin is an active member of the SQL Server community as a presenter and blogger.

More from the author
SQL Server: Automatic Tuning in SQL Server 2017 and Azure SQL Database
Intermediate
1h 27m
Jan 9, 2018
SQL Server: Analyzing Query Performance for Developers
Beginner
4h 3m
Sep 29, 2017
More courses by Erin Stellato
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hello, my name is Erin Stellato, and welcome to my course, SQL Server: Introduction to Query Store. I'm a SQL Server consultant with SQLskills, as well as a Microsoft Data Platform MVP. And when SQL Server 2016 was released, the new feature I was most excited about was Query Store. Query Store is a feature that helps anyone who works with SQL Server, and for the first time, capturing baseline data is built into SQL Server. Once you have that data you can use it to compare performance before and after upgrades or hardware changes. It's also invaluable when it comes to troubleshooting critical and/or transient performance issues. Very often, by the time we are involved in a problem, it's disappeared, or someone has restarted the instance to fix it, losing all diagnostic data as a result. Query Store preserves query information so we can better understand exactly what happened and then determine how to resolve it based on quantitative data rather than assumptions. In this course, we will configure Query Store and understand how the settings affect the data stored. We will look at how to find regressions in query performance, and see how you can stabilize query performance by forcing a specific plan to be used for a query. Finally, we'll wrap up with methods for testing upgrades and code changes so that you'll no longer have to cross your fingers and hold your breath when they go live in production. Experience with investigating query performance is helpful, along with the ability to write basic T-SQL queries. I hope you'll join me as we explore all the capabilities of this new feature available in all editions in this course, SQL Server: Introduction to Query Store.

Implementing Query Store
Hi, this is Erin Stellato from SQLskills. com, and I'm recording this course for Pluralsight. This is course is an introduction to Query Store in SQL Server, and this is module 3, Implementing Query Store. In this module we are going to walk through enabling Query Store for a database, and we will also discuss the various Query Store options so you can understand why you might tweak them and the impact of doing so. And we aren't even going to go through any more slides, we are going to just jump right into a demo.

You're the smartest person in the room

PROVE IT

Your team won't just close the skills gap

THEY'LL HURDLE IT