Tools for monitoring performance

SQL Server's toolset is very rich and, when monitoring, we need to know which tool or tools we have to use to address performance issues and how to interpret the results measured by any certain tool. In this section, we will go through all native SQL Server tools that help us monitor the performance of SQL Server.

Activity Monitor

Activity Monitor is a fast and relatively simple tool incorporated within Management Studio. It could be used for a fast overview of current activities running on the instance. As seen in the following screenshot, Activity Monitor is accessible from the popup menu called by right-clicking in Object Explorer at instance level:

The Activity Monitor window

In the preceding image, the Activity Monitor is already opened and divided into six sections:

• Overview
• Processes
• Resource Waits
• Data File I/O
• Recent Expensive Queries
• Active Expensive Queries

Overview

The Overview section provides quick information about the current CPU effort consumed by the...