Overview

The Query Store feature was introduced in SQL Server 2016 and it’s a game-changer for data professionals.  Have you ever…

• …had that emergency where you’re informed “The database is slow! It was fine yesterday! Nothing changed!”?
• …wished for a query’s baseline metrics so that you could quickly compare pre- and post-upgrade performance?
• …flailed about querying DMV after DMV to figure out the *real* problem with performance?

If so, then this is the course where you learn how to stop reacting and start making Query Store work for you.

Query Store allows you to proactively monitor query performance and find those queries that are currently causing problems, or have the potential to cause problems. In addition, you can stabilize query performance use plan forcing, whether that’s something you do manually or automatically with Automatic Plan Correction. Beyond the basics, you’ll see demos that teach you how to interrogate the data to find common patterns in query performance using T-SQL and visualization techniques, and how to take advantage of the wait statistics information captured. After this class you will have the skills to find and leverage important information in Query Store to make query tuning easier.

Instructor: Erin Stellato

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Ready to buy? Please see our Immersion Event Offerings for options, bundles, pricing. and more.

Select Quotes From Past Attendees of Erin’s SQLskills Classes

• “Extremely pleased with the course. FAR exceeded my expectations.”
• “Well worth the time and expense to attend. Would highly recommend this to others.”
• “I though this was extremely worthwhile. I have been a DBA for a few years and this was a great refresher for a lot of things I don’t do very often but should.”
• “Found class extremely helpful and plan to take the next one.”
• “Great course – very informative – very great instructors – I am sure to be back!”
• “I was familiar with all of the topics in this course, but barely understood most. I feel I will be able to begin to do all of the things I need to take simpler tasks from our DBAs to allow them to focus on the more advanced tasks. That was exactly my goal for taking this class.”
• “Great course. Good new info for me, plus refresher on other info. Thanks!”
• “Both Erin and Jon have a vast knowledge of not only SQL Server & tools, but also effective presentation.”
• “Thanks for taking the time to better my knowledge of SQL and allow me to better my career.”

Curriculum

Module 1: Query Store Fundamentals

In the first module we will create the foundation on which the rest of the class will build, as we cover requirements, configuration, and data captured. We’ll discuss methods traditionally used to capture query performance data, and how we find that same information within Query Store. Topics covered include:

• What’s in Query Store (and what isn’t)
• Query Store settings
• How it works

Module 2: Understanding the Query Store Data

If you want to make decisions about query performance, it’s essential to understand the data used to make such decisions. We will look at the data in detail to understand what the data represents and how it compares to the data you’ve used historically. Topics covered include:

• Understanding runtime statistics
• Understanding wait statistics
• The relationship between statistics and plans

Module 3: Workload Characteristics and Query Store Performance

Once data professionals realize the value of Query Store, a typical follow-up question is “How will it affect performance?” In this module we’ll look at how different workload types affect the data stored in Query Store, and the workload characteristics to monitor to understand how they affect Query Store performance. Topics covered include:

• Typical workloads
• How to identify what type of workload you have
• Performance with Query Store enabled

Module 4: Finding Performance Issues

Query Store is designed to help you quickly identify “what happened”, but you need to know where to find that information and how to leverage the built-in reports to quickly diagnose poor-performing and regressed queries. The reports may not always provide what you need, or want, but you have the ability to write your own queries against the data to return the information you need. Topics covered include:

• Using reports
• Internal schema
• Writing your own Query Store queries

Module 5: Forcing Plans

DBAs and developers have a long history of coming up with creative ways to stabilize query performance without refactoring code. Query Store provides another option with plan forcing, a very easy (but still temporary) solution to ensure a query consistently uses the same plan. But there are many details to be aware of with forcing, which we’ll review in depth in this module. Topics covered include:

• When and how to force a plan
• What causes forcing to fail
• Monitoring and addressing failures

Module 6: Automatic Tuning

Microsoft continues to move forward the concept of the intelligent database, with Automatic Tuning forming one small part of that automation. Comprised of Automatic Plan Correction and Automatic Index Management, we’ll review both features to see how they work and how they rely on Query Store data. Topics covered:

• Automatic Plan Correction
• Automatic Index Management

Module 7: Other Uses of Query Store

While Query Store is frequently marketed as a flight-recorder for your database that will help you identify performance issues, it can be used for so much more. In this module we’ll explore how you can leverage the data in Query Store to mitigate upgrade issues, find patterns in code, and potential problems based on plans. Topics covered include:

• Upgrade testing
• Cardinality Estimator testing
• Mining Query Store data

Module 8: Visualizing Query Store Data

SQL Server’s Management Studio comes with multiple built-in reports for reviewing Query Store data, but sometimes those reports aren’t enough. While you can’t customize what’s in SSMS, you can create your own reports using other tools such as Power BI, R, and SQL Server Reporting Services (SSRS). In this module we’ll pull together our knowledge of the schema and data to create customized solutions that you (and your managers) can use to look at solution performance.

Ready to buy? Please see our Immersion Event Offerings for options, bundles, pricing. and more.

Questions?

If you have any questions not answered by our Immersion Events F.A.Q., please contact us.