Monitoring Performance using SQL Trace and Extended Events

Introduction

The Monitoring Performance using SQL Trace and Extended Events module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Monitoring SQL Server database with Profiler tool
• Monitoring SQL Server database with Windows tools
• Monitoring SQL Server database with Microsoft Azure tool
• Monitoring SQL Server database with Extended Events

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Four lab objectives are covered in this lab.

• Demonstrate how to monitor SQL server performance with Windows tools
• Demonstrate how to perform monitoring tasks with SQL Trace
• Demonstrate how to monitor the performance of Azure SQL Database
• Demonstrate how to perform monitoring tasks with extended events

Exercise 1 - Monitoring SQL Server Database with Profiler Tool

Microsoft SQL Server Profiler is a GUI (Graphical User Interface) tool to monitor the performance of the Transact-SQL statements running on the SQL Server. In addition, you can also collect information about the duration of the query, number of reads and writes and the system on which the query is run. The performance metrics data is saved in a trace file that can be accessed as and when required. It is possible to monitor the tasks that need to be analyzed. Thus, with SQL Server Profiler, you can perform the following tasks:

• Create a trace.
• Watch the trace results as it runs.
• Store the trace results in a database table.
• Replay the trace results, whenever required.

In this exercise, you will learn to run the SQL Server Profiler to obtain required information about the query that is run on a database and about the associated events that take place, on running the query.

Exercise 2 - Monitoring SQL Server Database with Windows Tools

System Monitor is a graphical tool that can be used to measure the performance of SQL Server, running on Microsoft Windows server operating system. This tool allows you to view the objects of SQL Server, performance of other objects, such as processors, memory, threads and cache and counters to monitor events. It includes features such as data exporting and remote monitoring. System Monitor runs on Windows Server and all Windows operating systems.

Using the System Monitor tool, you can view data from multiple computers simultaneously, charts showing current task, and activity reports. You can also add system alerts to notify about the thresholds set on specific counters.

The System Monitor tool uses Remote Procedure Calls (RPCs) for gathering information from Microsoft SQL Server in order to monitor its performance.

In this exercise, you will learn to run the System Monitor tool to check the performance of the SQL Server.

Exercise 3 - Monitoring SQL Server Database with Extended Events

Extended Events in SQL Server is a general event-handling tool that records data related to the tasks occurring in the system. Although extended events work in a similar manner as the SQL Profiler or Trace, it is more beneficial to the users. It uses less of the system resources and is highly scalable and easier to use. It can be executed synchronously and asynchronously and provides in-depth information about the events.

The design of Extended Events is so flexible that a single event or multiple events can be triggered on any destination or target location that need not have the specific configuration to manage such events. Therefore, the triggering Extended Events can move the output data to the Event Tracing for Windows (ETW) or can be shown through Views in SMSS (Session Manager Subsystem) components. Extended Events sessions can join together any group of events and monitor the system performance.

In this exercise, you will learn to run the Extended Events tool in SQL Server to monitor the performance of the system.

Exercise 4 - Monitoring SQL Server Database with Microsoft Azure Tool

You will be using a different lab topology for this exercise.

Microsoft Azure SQL Database or SQL Azure is similar to Microsoft SQL Server in all aspects, except that the applications run in Azure cloud. These databases are easy to manage through the tools provided by Microsoft data centers. The organizations can scale up or down the size of their databases in the cloud as per the requirement.

One of the most important areas to monitor the health of a database is to identify factors affecting the performance of the system.

Three service tiers, Basic, Standard, and Premium can be implemented to monitor the performance of the database, depending upon its size.

• Basic: Can be used to monitor the performance of a small database when only a single user is working at a particular time. The performance can be monitored on an hourly basis to identify any bottlenecks and take corrective actions.
• Standard: Can be used to monitor the performance of a database when many users are working simultaneously at a given time. The performance can be monitored every minute by dividing the database into smaller groups. This helps identify any performance issues and take corrective actions.
• Premium: Can be used to monitor the performance of huge databases with high I/O operations and used by multiple users simultaneously. The performance can be monitored every second by dividing the database based on peak load to identify issues that can affect the performance.

The performance of the database can be monitored on the basis of the following metrics:

• Core Processing Unit (CPU) usage percentage
• Database Transaction Unit (DTU) usage percentage
• Database size percentage
• Data IO percentage

In this exercise, you will learn to run the Microsoft Azure tool to monitor the database performance in SQL Azure.