Solving Real World Problems with SQL Server 2016 Query Store
Poor database performance is a widespread problem. This course will show you how SQL Server 2016's Query Store makes solving many query performance problems much easier than before.
What you'll learn
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.
Table of contents
- Introduction 2m
- Demo - Performance Regression in Action 1m
- What Causes Regressions? 5m
- Tracking Regressions Before Query Store 2m
- Demo - Tracking Regressions with DMVs 4m
- Demo - Tracking Regressions with Extended Events 6m
- Demo - Tracking Regressions with Query Store 6m
- Configuring the Regressed Query Report 7m
- Demo - Forced Plans 6m
- Summary 1m
- Query Store Architecture 3m
- Demo - Adding Queries and Plans to Query Store 4m
- Default Configuration Settings 5m
- Demo - Effects of Aggregation Interval and Query Capture Mode 5m
- Configuring Sized-based Cleanup 1m
- Demo - Effects of Size-based Cleanup and Running Query Store out of Space 3m
- Conclusion 1m
- Using Query Store for Upgrade Tests 4m
- Demo - Obtaining a Replay Trace and Preparing it for Replay 6m
- Demo - Setting Database Compatibility Levels and Running Replays 3m
- Demo - Analysing the Test Results with Query Store 2m
- Using Query Store for Load Tests 3m
- Demo - Running Load Tests and Analysing Results with Query Store 5m
- Using Query Store to Help Identify Optimal Configuration Values 2m
- Demo - Using Query Store to Obtain a Good Value for Cost Threshold for Parallelism 3m
- Summary 0m
About the author
Gail is a Database Specialist focusing on database performance tuning and database recovery, with a particular interest in topics such as indexing strategies, execution plans, and writing T-SQL code that performs well and scales gracefully. Gail holds a Microsoft Certified Master certification for SQL Server 2008 and is a Data Platform MVP. She is a frequent poster on the SQL Server Central forums, writes articles for both SQLServerCentral.com and Simple-Talk.com, and has spoken at SQL Saturday... more