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.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.