The SQL Server Profiler


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.

System Monitor

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 Activity Monitor Window in SQL Server Management Studio

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.

Error Logs

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.

sp_who

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.

sp_lock and sys.dm_tran_locks

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.

sp_spaceused

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.

sp_monitor

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

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.

Built-in Functions

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.

SQL Profiler Stored Procedures and Functions

The SQL Profiler uses stored procedures and functions to gather SQL Profiler statistics.

Trace Flags

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).

Simple Network Management Protocol (SNMP)

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.

SQL Profiler or System Monitor

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




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net