Usually, the following steps are used to find performance problems:
- Review the server state to make sure that it has enough resources to ensure normal database operation;
- Find DB sessions consuming excessive resources;
- Find DB sessions waiting for any database events like various types of locks, server resources, etc.;
- Investigate actions produced by the problematical sessions in the database;
- Look for a solution to fix the problem.
This article is to discuss how to efficiently monitor SQL Server instances using the database Activity Monitor tool.
Activity Monitor is a built-in tool which allows an administrator to perform a quick analysis of the database state. The tool is a part of SQL Server Management Studio, so it doesn’t require any additional installations.
To launch Activity Monitor, you can right click on the database instance name under Object Explorer and select Activity Monitor. You can do the same by clicking on the Activity Monitor icon on the toolbar or use Ctrl+Alt+A key combination.
Activity Monitor has the following categories:
- Overview
- Processes
- Resource Waits
- Data File I/O
- Recent Expensive Queries
In the Overview category you can find server state summary metrics, namely Percent of Processor Time, Waiting Tasks, Database I/O and Batch Requests:
These metrics enable the database administrator to quickly understand the status of the database server and find problems related to server resources.
In the Processes section, you can find a list of processes running in the database. You can quickly see what machine the session is connected from, what the state of the session is, what SQL is being executed and the information about wait events. If there is a session waiting for another one, the information on this blocking session will be available as well. You can kill the process by right-clicking on it and select the Kill Process option. Likewise, you can launch SQL Server Profile to trace the process.
The Resource Waits section gives details of database wait events. For each database event, you can find various statistics of time during which the database is waiting for this event.
Data File I/O is to provide information about disk usage operations. You can find the speed of read and write operations for each data file together with disk response time.
The Recent Expensive Queries section contains a list of top queries sorted by resource usage. Under this section, you can determine the most problematical SQL in the database. You can find various useful statistics like the number of executions per minute, CPU rate, physical reads, logical reads and writes and the average duration of the SQL. In addition, you can see a number of execution plans that were built for the query.
You can view the execution plan of the SQL by right-clicking on the query and selecting the Show Execution Plan. Choosing Edit Query Text opens this SQL in new query windows.
Eventually, Activity Monitor is a simple but powerful tool that allows the database administrator to quickly determine and solve most of the typical database problems. Being a part of SQL Server Management makes it easy to use.
For additional database monitoring, 3rd party products can be used. A good example is DataSunrise that combines database protection mechanism with powerful performance monitoring. It acts as a database traffic analyzer and collects various performance and security metrics to be used for any further analysis. Notifications to database administrators of any performance and security problems bring extra control of the database state.