The Query Store feature was introduced in SQL Server 2016 and it’s a game-changer for data professionals. Have you ever…
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.
This class is delivered live via online streaming.
Instructor: Erin Stellato
Ready to register? Please see our Immersion Events Schedule for class dates.
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:
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:
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:
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:
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:
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:
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:
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 register? Please go to the class page for details on the class cost and logistics, and the registration link.
If you have any questions not answered by our F.A.Q., please contact us.