Description
Course info
Rating
(16)
Level
Intermediate
Updated
Jan 9, 2018
Duration
1h 27m
Description

There's a whole new world of automated features in SQL Server, based on data, that are designed to make life easier for the data professional, and it's time to start using them! Microsoft has invested a lot of effort in these tools that can automate areas of performance tuning and give you time to work on more interesting and creative tasks that can't be automated. In this course, SQL Server: Automatic Tuning in SQL Server 2017 and Azure SQL Database, you'll learn how Query Store has been upgraded to also collect wait statistics from queries, to allow you to identify performance bottlenecks at the query level and dig deeper to resolve them. Next, you'll explore Automatic Plan Forcing, which allows SQL Server to identify queries where performance has regressed and automatically force a prior query plan that is more efficient. Finally, you'll take a dive into Automatic Index Management, which gives SQL Server the capability to automatically create missing indexes and remove unused or duplicate indexes. By the end of this course, you'll have the skills and knowledge to start enabling these features in appropriate parts of your SQL Server environment and taking back some of your precious time!

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
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hello. My name is Erin Stellato, and welcome to my course, Automatic Tuning in SQL Server 2017 and Azure SQL Database. I'm a SQL Server consultant with SQLskills, as well as a Microsoft Data Platform MVP, and I admit I'm horrible at delegating. I prefer to do things myself. However, there's a whole new world of automated features in SQL Server based on data that are designed to make life easier for the data professional, and it's time to start using them. In this course, we'll begin by talking about a new addition to Query Store, wait statistics. We'll review what data is collected and how to view the waits for a given query plan over time. Then we'll move onto the automatic tuning features. Don't let the word automatic scare you. Remember that the SQL Server team captures telemetry, which it then uses in a myriad of ways including making intelligent database decisions. With automatic plan correction, you'll see how a sudden regression in query performance can be fixed without any intervention from you. Just imagine, no calls from users because a query is suddenly slow. We'll end with a discussion of automatic index management, which can create indexes that will benefit your workload and drop indexes that are duplicates or unused. Is Microsoft trying to code you out of a job? Not at all. It's trying to give you time to work on the more interesting and creative tasks that can't be automated. As usual, I've got demos for each of these features so you can see them in action and understand how they work. Whether you're a long-time DBA, a new administrator, or a developer just looking for some help, this course will provide what you need to start using each of the features we cover here in Automatic Tuning in SQL Server 2017 and Azure SQL Database.

Finding and Using Wait Statistics in Query Store
Hi. This is Erin Stellato from SQLskills. com. This course is about Automatic Tuning in SQL Server 2017 and Azure SQL Database, and in this module, we'll be talking about wait statistics in Query Store. We're going to start with a review of how database professionals have historically used wait statistics in SQL Server. I want to make sure you are comfortable with what wait stats are and talk about how we are used to using them. Then we'll discuss what wait statistics information exists within Query Store, and how you can view that data both through the UI and with T-SQL. I'm guessing that most of you are familiar with wait statistics in SQL Server, but I still like to talk through the basics of wait stats to make sure we're all on the same page. Waits are a normal part of SQL Server, and these occur when a thread needs a specific resource, but it's not available. For example, let's say that you execute a SELECT query against a database, and the pages needed to satisfy that request are not in memory, so they have to be read from disk. SQL Server has to wait for those pages to be read from disk into memory. The resource here is the data page, and the wait is categorized as a PAGEIOLATCH wait. This wait is then tracked in SQL Server, and this goes on all the time, and every SQL Server instance will have waits, even one that is well tuned. With wait statistics, you want to understand what your normal waits are, whether that's for an instance or a query, so that when you're having a performance issue you can compare the waits at that time to what's normal and then take your next steps accordingly.

Using Automatic Plan Correction
Hi. This is Erin Stellato from SQLskills. com. This course is about Automatic Tuning in SQL Server 2017 and Azure SQL Database, and in this module, we'll be talking about automatic plan correction. We will start this module with an explanation of what automatic plan correction is as it's a very new feature, and then we'll step through how this feature works, what's happening behind the scenes, as well as examine the data captured as part of the plan correction process. In order to talk about automatic plan correction, we need to revisit plan forcing in Query Store. Remember that within Query Store you can find queries that have multiple plans and queries that have regressed, that is, they have suddenly stopped performing well because the plan changed, and you can force a specific plan to be used for the query. Many people equate plan forcing with plan guides, but they are two separate entities, and one big difference between them is that plan forcing is not schema-bound. Meaning, if you have a forced plan that uses a specific index, you can drop or change that index. If you had a plan guide, which used a specific index, you would not be able to change or drop that index without first removing the plan guide. This gives plan forcing a bit more flexibility, but just as with guides, if a forced plan is no longer optimal for a query, it will continue to be used by the optimizer unless you specifically unforce that plan or the forcing fails for some reason, for example, you dropped an index. But plan forcing in Query Store was initially a manual effort. You had to find the queries that were problematic, look at the plans, decide which plan to force, and then force it in the UI or with the sp_query_store_force_plan stored procedure.