Poor database performance is a widespread problem, and as data volumes increase it's only going to get worse. Up until now, identifying the poorly performing queries was challenging and difficult. SQL Server 2016 introduced the Query Store, which automatically tracks query performance. In this course, Solving Real World Problems with SQL Server 2016 Query Store, you'll learn how to solve difficult query performance problems in an easier, more efficient way. This course will show how to identify query performance regressions using the Query Store, how to identify queries using the most resources, and how to track the overall resource consumption at the database level. It'll also show some advanced uses of Query Store, such as upgrade tests and load tests. By the end this course, you’ll be able to use Query Store to identify and resolve what were previously very difficult performance problems.
Gail Shaw is a Data Platform MVP and holds the MCM certification for SQL Server. Her specialties are in performance tuning and database recovery for SQL Server. She is a frequent poster on the SQL Server Central forums, writes articles for both SQLServerCentral.com and Simple-Talk.com, and often speaks at SQLBits and the PASS Community Summit.
Course Overview Hello everyone. My name's Gail Shaw. Welcome to my course, Solving Real World Problems with the SQL Server 2016 Query Store. I'm a technical lead at Entelect Software based in South Africa. Query performance is an important area of SQL Server, and there are always badly performing queries around. Query Store is the newest tool for tracking and resolving performance problems in SQL Server. In this course, we're going to have a look at some practical uses of the SQL Server Query Store, including identifying the causes of performance regressions, determining just how much CPU that database is really consuming, and how to ensure the performance problems caused by updates are caught be testing and not by the end users. By the end of this course, you'll be able to use Query Store to identify and resolve what were previously very difficult to attract performance problems. Before beginning this course, you should be familiar with T-SQL and have some experience with SQL Server Query performance tuning. I hope you'll join me on this journey to learn the ins and outs of the best new feature in SQL Server 2016, the Query Store, with this Solving Real World Problems with SQL Server Query Store course at Pluralsight.
Solving Query Performance Regressions Hello, and welcome to the second module of this Pluralsight course on Solving Real World Problems with the SQL Server Query Store. In the last module, I mentioned that the Query Store has made a number of previously difficult problems easier to identify and resolve. In this module, we'll look at one of those problems, that of the query performance regression. We'll start this module by looking at what regressions are and some of the things that can cause them. We'll look at how we would've identified regressions in earlier versions of SQL Server, and then we'll see how much easier it is to identify them we're using the Query Store. And finally, we'll look at some details of how the Query Store defines a performance regression. So what precisely is a performance regression? A dictionary doesn't help much here. The Oxford Dictionary defines regression as returning to an earlier state, which is definitely not relevant here. A performance regression occurs when a query has degraded in performance over time. This degradation may be sudden, or it may be gradual. It's probably more common for regressions to be sudden, a behavior as sometimes refer to as falling off a cliff. The degradation may be permanent, the query remains at that poor performance level no matter what happens, or it may at a later point return to its previously acceptable behavior.