Optimizing Query Plans Part 1
The Optimizing Query Plans module provides you with the instructions and devices to develop your hands-on skills in the following topics: Working with SQL Server Profiler, Working with Query Store, Working with Extended Events.
Already have an account? Sign In »
Introduction
The Optimizing Query Plans module provides you with the instructions and devices to develop your hands-on skills in the following topics.
- Working with SQL Server Profiler
- Working with Query Store
- Working with Extended Events
Lab time: It will take approximately 45 minutes to complete this lab.
Exam Objectives
Three exam objectives are covered in this lab.
- Demonstrate how to capture the actual execution plans using extended events
- Demonstrate how to capture and display the actual execution plan from the trace
- Demonstrate how to optimize the queries using query store
Exercise 1 - Working with SQL Server Profiler
The SQL Server database engine delivers a collection of data and events, which is known as trace. SQL Server Profiler is a graphical user interface that you can use to create and manage traces. You can save the data and events in a trace file that can be used for troubleshooting database problems arising out of slow running queries or inefficient stored procedures.
In this exercise, you will learn to work with SQL Server Profiler.
Exercise 2 - Working with Query Store
SQL Server Query Store feature helps in diagnosing and troubleshooting query performance related issues. Query Store can hold multiple execution plans for a single query. You can use the Query Store feature to perform the following tasks:
- Audit the query history.
- Analyze the usage of resources such as memory, CPU, and input/output for the database.
- Determine the usage of memory and CPU time by the Transact-SQL queries.
- Monitor the database performance.
In this exercise, you will enable the Query Store feature and learn about the different types of Query Store views.
Exercise 3 - Working with Extended Events
Extended Events help to understand the innermost operations of the database engine. It is a monitoring system that helps to analyze, create, modify, and view session data.
In this exercise, you will learn to work with Extended Events in SQL Server Management Studio.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.