Tip: Know the Monitoring Tools and Resources Available in SQL Server 2008

The primary monitoring tools you will use for SQL Server are the Reliability and Performance Monitor and the SQL Server Profiler. But there are other resources also available for monitoring SQL Server. These include:
Activity Monitor This monitor provides information on current users, processes, and locks, as discussed in “Managing Server Activity” on page 134. To display Activity Monitor, use the Object Explorer view to access an instance of the Database Engine. Right-click the Database Engine instance and then select Activity Monitor.
Replication Monitor This monitor provides details on the status of SQL Server replication and allows you to configure replication alerts. To display Replication Monitor, use the Object Explorer view to access an instance of the Database Engine. Right-click the Replication node and then select Launch Replication Monitor.
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. To access the server logs, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the Management node. Under the Management node, expand the SQL Server Logs node and then double-click the log you want to examine.
Job Activity Monitor This monitor provides details on the status of SQL Server Agent jobs. To display Job Activity Monitor, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the SQL Server Agent node, and then double-click Job Activity Monitor.
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. To access agent logs, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the SQL Server Agent node. Under the SQL Server Agent node, expand the Error Logs node and then double-click the log you want to examine.
Note that 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 Microsoft 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 systemwide problems, including SQL Server and SQL Server Agent problems. To access event logs, click Start, click Administrative Tools, and then select Event Viewer.
DBCC statements This set of commands allows you to check SQL Server statistics, to trace activity, and to check database integrity.
sp_helpdb This stored procedure displays information about databases.
sp_helpindex This stored procedure reports information about indexes on a table or view.
sp_helpserver This stored procedure provides information in SQL Server instances configured for remote access or replication.
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, indexed view, or Service Broker queue in the current database.
sp_who This stored procedure shows a snapshot of current SQL Server users and processes.
sys.dm_tran_locks This dynamic management view shows information about object locks.
Note that the sys.dm_tran_locks view replaces the sp_lock stored procedure.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant by William R. Stanek.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.