Description
Course info
Rating
(25)
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
SQL Server: Introduction to Query Store
Beginner
3h 3m
Jan 31, 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, 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.