SQL Server: Introduction to Query Store

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.
Course info
Rating
(31)
Level
Beginner
Updated
January 31, 2017
Duration
3h 2m
Table of contents
Introduction
13m 18s
Description
Course info
Rating
(31)
Level
Beginner
Updated
January 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
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
More courses by Erin Stellato
Transcript
Transcript

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 and 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 problems is helpful, along with the ability to write basic TSQL 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.