Monitoring server performance, tracking user activity, and troubleshooting errors are essential parts of database administration, and Microsoft SQL Server has several tools that you can use to perform these tasks. Performance Monitor, the standard Microsoft Windows tool for monitoring servers, has updated counters for SQL Server. These counters allow you to track many different server resources and activities. SQL Server Profiler, an analysis and profiling tool, allows you to trace server events. Other tools and resources are available, such as stored procedures and the SQL Server logs.
Monitoring SQL Server is not something you should do haphazardly. You need to have a plan—a set of goals that you hope to achieve. Let’s look at some reasons you may want to monitor SQL Server and the tools you can use to do this.
One of the main reasons you monitor SQL Server performance is to troubleshoot problems. For example, if users are having problems connecting to the server, you will want to monitor the server to find out more about what is causing these problems. Your goal is to track down the problem using the available monitoring resources and then solve the problem effectively.
Another common reason to monitor SQL Server is to improve server performance. To achieve optimal performance, you need to minimize the time it takes for users to see the results of queries and maximize the total number of queries that the server can handle simultaneously. You do this by using the following techniques:
Resolve hardware issues that may be causing problems. For example, if disk read/write activity is slower than expected, work on improving disk input/output (I/O).
Monitor memory and CPU usage and take appropriate steps to reduce the load on the server. For example, other processes running on the server may be using memory and CPU resources needed by SQL Server.
Cut down the network traffic load on the server. With replication, for example, you can configure remote stored procedure execution rather than transmit large data changes individually.
Unfortunately, you often have to make tradeoffs in resource usage. For example, as the number of users accessing SQL Server grows, you may not be able to reduce the network traffic load, but you may be able to improve server performance by optimizing queries or indexing.
Before you start monitoring SQL Server, it is a good idea to establish baseline performance metrics for your server. To do this, you measure server performance at various times and under different load conditions. You can then compare the baseline performance with subsequent performance to determine how SQL Server is performing. Performance metrics that are well above the baseline measurements may indicate areas in which the server needs to be optimized or reconfigured.
After you establish the baseline metrics, prepare a monitoring plan. A comprehensive monitoring plan involves the following steps:
Determine which server events should be monitored to help you accomplish your goal.
Set filters to preferentially select the amount of information that is collected.
Configure monitors and alerts to watch the events.
Log the event data so that it can be analyzed.
Analyze the event data and replay the data to find a solution.
These procedures will be examined later in this chapter in the section entitled "Monitoring SQL Server Performance." Although you should develop a monitoring plan in most cases, sometimes you may not want to go through all these steps to monitor SQL Server. For example, if you only want to check current user activity levels, you may not want to use Performance Monitor and can run the stored procedure sp_whoinstead. Or you can examine this information in the Current Activity window in SQL Server Management Studio.
Note
The stored procedure sp_who reports on current users and processes. When you execute sp_who, you can pass a login name as an argument. If you do not specify a login name, NULL is passed in this argument, so all logins are returned. If you use the keyword active as the login name, you will see only active processes; any processes waiting for the next command from a user will be excluded. Instead of a specific login name, such as sa, you can use the numeric value for a system process ID as well.
The primary monitoring tools you will use are Windows Performance Monitor and SQL Server Profiler. Other resources for monitoring SQL Server include:
Activity Monitor. This monitor provides information on current users, processes, and locks as discussed in the section titled "Managing Server Activity" in Chapter 5.
Replication Monitor. This monitor provides details on the status of SQL Server replication and allows you to configure replication alerts.
SQL Server logs. The information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server problems.
SQL Server Agent logs. The information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server Agent problems.
Note
SQL Server documentation refers to the SQL Server and SQL Server Agent logs as error logs. In their current implementation, however, the logs are more accurately called event logs, which is the terminology used in this chapter. Similar to event logs in Windows, these logs in SQL Server contain informational and security messages as well as error messages.
Event logs. The information in the event logs allows you to troubleshoot system-wide problems, including SQL Server and SQL Server Agent problems.
sp_helpdb. This stored procedure displays information about databases.
sp_helpindex. This stored procedure reports information about indexes on a table.
sp_helpserver. This stored procedure provides information in SQL Server instances configured for remote access or replication.
sp_lock. This stored procedure shows information about object locks.
sp_monitor. This stored procedure shows key SQL Server usage statistics, such as CPU idle time and CPU usage.
sp_spaceused. This stored procedure shows an estimate of disk space used by a table or database.
sp_who. This stored procedure shows a snapshot of current SQL Server users and processes.
DBCC statements. This set of commands allows you to check SQL Server statistics, to trace activity, and to check database integrity.
In addition to log files and Transact-SQL statements, you will find a set of built-in functions that return system information. Table 13-1 provides a summary of key functions and their usages. The values returned by these functions are cumulative from the time SQL Server was last started.
Table 13-1. Built-In Functions for Monitoring SQL Server Performance and Activity
Description | Example | |
|---|---|---|
@@connections | Returns the number of connections or attempted connections. | SELECT @@connections AS ‘Total Login Attempts’ |
@@cpu_busy | Returns CPU processing time in milliseconds for SQL Server activity. | SELECT @@cpu_busy AS ‘ CPU Busy’, GETDATE() AS ‘Since’ |
@@idle | Returns SQL Server idle time in milliseconds. | SELECT @@idle AS ‘Idle Time’, GETDATE() AS ‘Since’ |
@@io_busy | Returns I/O processing time in milliseconds. | SELECT @@io_busy AS ‘IO Time’, GETDATE() AS ‘Since’ for SQL Server |
@@pack_received | Returns the number of input packets read from the network by SQL Server. | SELECT @@pack_received AS ‘Packets Received’ |
@@pack_sent | Returns the number of output packets written to the network by SQL Server. | SELECT @@pack_sent AS ‘Packets Sent’ |
@@packet_errors | Returns the number of network packet errors for SQL Server connections. | SELECT @@packet_errors AS ‘Packet Errors’ |
@@timeticks | Returns the number of milliseconds per CPU clock tick. | SELECT @@timeticks AS ‘Clock Ticks’ |
@@total_errors | Returns the number of disk read/write errors encountered by SQL Server. | SELECT @@total_errors AS ‘Total Errors’, GETDATE() AS ‘Since’ |
@@total_read | Returns the number of disk reads by SQL Server. | SELECT @@total_read AS ‘Reads’, GETDATE() AS ‘Since’ |
@@total_write | Returns the number of disk writes by SQL Server. | SELECT @@total_write AS ‘Writes’, GETDATE() AS ‘Since’ |