The Profiler is a graphical user interface to SQL Trace for monitoring an instance of the SQL Server Database Engine or Analysis Services. SQL Profiler enables you to monitor server and database activity (for example, number of deadlocks, fatal errors, traces of stored procedures and Transact-SQL statements, or login activity). You can capture SQL Profiler data to a SQL Server table or a file for later analysis, and you can replay the events captured on SQL Server, step by step, to see exactly what happened. SQL Profiler tracks engine process events, such as the start of a batch or a transaction.
The System Monitor lets you monitor server performance and activity using collections of predefined objects and counters-or user-defined counters-to monitor events. The System Monitor collects process counts as opposed to the data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). The monitor allows you to set thresholds on specific counters to generate alerts that notify operators as described earlier. System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use.
The System Monitor works only on Windows 2000 and Windows Server 2003. It can also be used to remotely or locally monitor an instance of SQL Server installed on another server.
The Current Activity window in Management Studio graphically displays information about the processes running currently on an instance of SQL Server. It displays information about blocked processes, locks, and user activity. The Current Activity folder is useful for ad hoc assessment of current activity.
The error logs contain additional information about events in SQL Server. They contain information about errors that go beyond what is available anywhere else. You can use the information in the error log to troubleshoot SQL Server-related problems. Each error has an error ID you can use for research or to address a technical support team at Microsoft.
The Windows application event log contains all the events occurring on the system as a whole. However, it also picks up events generated by SQL Server, SQL Server Agent, and full-text search. As demonstrated in Chapter 10, you can code a T-SQL error handler using RAISERROR with the WITH LOG option to place an error message and other parameters into the error log.
The sp_who stored procedure reports snapshot information about current SQL Server users and processes. It also includes the currently executing statement and whether the statement is blocked. This facility provides an alternative, through T-SQL code, to viewing user activity in the current activity window in Management Studio.
The sp_lock stored procedure (legacy) and the sys.dm_tran_locks view reports snapshot information about locks, including the object ID, index ID, type of lock, and the type of resource to which the lock applies. These facilities provide an alternative, through T-SQL code, to viewing user activity in the current activity window in Management Studio.
The sp_spaceused stored procedure displays an estimate of the current amount of disk space used by a table (or a whole database). This facility provides an alternative, through T-SQL code, to viewing user activity in the current activity window in Management Studio.
The sp_monitor stored procedure displays statistics. It includes the CPU usage, I/O usage, and the amount of time idle since sp_monitor was last executed.
DBCC statements enable you to check performance statistics and the logical and physical consistency of a database. They also allow you to troubleshoot database problems. The DBCC statements are discussed later in Appendix.
You also have built-in functions that display snapshot statistics about SQL Server activity since the server was started. These statistics are stored in predefined SQL Server counters. You have, for example, @@CPU_BUSY, which contains the amount of time the CPU has been executing SQL Server code. Then there is @@CONNECTIONS, which contains the number of SQL Server connections or attempted connections; and @@PACKET_ERRORS, which contains the number of network packets occurring on SQL Server connections.
The SQL Profiler uses stored procedures and functions to gather SQL Profiler statistics.
Trace flags can display information about a specific activity within the server. They are also used to diagnose problems or performance issues (for example, deadlock chains).
The old favorite Simple Network Management Protocol (SNMP) is a member of the maintenance team offering network management services. With SNMP, you can monitor an instance of SQL Server across different platforms. With SQL Server and the Microsoft SQL Server Management Information Base (MSSQL-MIB), an SNMP application can monitor the status of SQL Server installations. The SNMP services lets you monitor performance information, access databases, and view server and database configuration parameters.
The main difference between these two monitoring tools-SQL Profiler and System Monitor-is that the SQL Profiler monitors engine events, while System Monitor monitors resource usage associated with server processes. In other words, SQL Profiler can be used to monitor deadlock events, including the users and objects involved in the deadlock (see Chapter 17). System Monitor, on the hand, can be used to monitor the total number of deadlocks occurring in a database or on a specific object.
Windows 2000 Server and Windows Server 2003 platforms also provide the following monitoring tools:
Task Manager This tool provides a synopsis of the processes and applications running on the system. It is useful for quick analysis, or to kill a process.
Network Monitor Agent This tool helps monitor network traffic.
The System Monitor is a tool that can be used to monitor resource usage on a computer running Microsoft Windows 2000 Server or Windows Server 2003. It lets you set up charts that present resource usage data in graphical form. The Windows System Monitor provides access to many different counters, each of which measures some resource on the computer.
The Windows System Monitor is also extensible. In other words, server applications can add their own performance counters that System Monitor can access. SQL Server 2005 adds counters to Windows System Monitor to track items such as
SQL Server I/O
SQL Server memory usage
SQL Server user connections
SQL Server locking
Replication activity
The monitoring and performance tools on the OS include the following:
System Monitor
Task Manager
Event Viewer
Quality of Service
Windows Management Interface
SNMP