Chapter 8 - Monitoring Server Performance and Activity

Microsoft SQL Server version 7.0 provides a variety of tools that can be used to monitor the performance of a computer running SQL Server and the user activity that occurs in databases. Monitoring is an important task that allows you to determine if your database application is working efficiently and as expected. As your application, database, and environment change, regular monitoring allows you to check that these elements continue to work efficiently and to identify any areas that need modifying. For example, as more concurrent users use a database application, the load on SQL Server can increase. By monitoring you determine if the current SQL Server or system configuration must be changed to handle the increased workload, or if the increased load is having no significant effect on performance and therefore does not require any configuration change.

Monitoring SQL Server or the system environment requires:

•

Determining the monitoring goals you want to achieve.

Choosing the most appropriate tool for the type of monitoring you will perform. The most flexible tools that can be used to monitor SQL Server are:

•	SQL Server Profiler.
•	Windows NT Performance Monitor.

•

Using the tool to monitor SQL Server or the system environment and analyze the data captured.

See Also

In Other Volumes

"Optimizing Database Performance" in Microsoft SQL Server Diagnostics

Goals of Monitoring

After Microsoft SQL Server version 7.0 is installed and running, you can monitor the server to:

•	Determine whether it is possible to improve performance. For example, by monitoring the response times for frequently used queries, you can determine if changes to the query or indexes on the tables are needed.
•	Determine user activity. For example, by monitoring users attempting to connect to SQL Server, you can determine if security is adequately set up.
•	Troubleshoot any problems or debug application components, such as stored procedures.
•	Test applications and development systems. For example, by monitoring SQL queries as they are executed, you can determine if they are written correctly and producing the expected results.

See Also

In Other Volumes

"Optimizing Database Performance" in Microsoft SQL Server Diagnostics

Monitoring to Improve Performance

Optimal performance is minimal response time and maximal throughput as a result of minimizing network traffic, disk I/O, and CPU time. This goal is achieved by thoroughly analyzing the application requirements, understanding the logical and physical structure of the data, and assessing and negotiating tradeoffs between conflicting uses of the database, such as online transaction processing (OLTP) versus decision support.

Response Time vs. Throughput

Response time measures the length of time required for the first row of the result set to be returned. Response time refers to the amount of time for the user to receive visual affirmation that a query is being processed.

Throughput measures the total number of queries that can be handled by the server during a given time.

As the number of users increases, contention between users increases, which in turn causes response time to increase and overall throughput to decrease.

Factors That Affect Performance

Periodically monitor Microsoft SQL Server to ensure that it is performing at acceptable levels, or to determine if performance can be further improved. These areas affect the performance of SQL Server:

•	System resources (hardware)
•	Microsoft Windows NT operating system
•	Database applications
•	Client applications
•	Network

However, before these areas can be monitored, you must know what level of performance is reasonable given normal working conditions. This is done by establishing a server performance baseline.

Establishing a Performance Baseline

To determine whether your Microsoft SQL Server system is performing optimally, you should establish a server performance baseline by taking performance measurements over time. Each set of measurements should be compared against the same measurements taken earlier.

After establishing a server performance baseline, compare the baseline statistics to current server performance. Numbers far above or far below your baseline are candidates for further investigation. They may indicate areas where the server needs to be tuned or reconfigured to improve performance.

For example, if the amount of time to execute a set of queries increases, you may want to examine the queries to determine if they can be rewritten or if column statistics or new indexes need to be added.

At a minimum, measurements should be taken to determine:

•	Peak and off-peak hours of operation.
•	Production query or batch command response times.
•	Database backup and restore completion times.

See Also

In Other Volumes

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

Performance Monitoring Example: Identifying Bottlenecks

A bottleneck is a component of the system that restricts the performance of the entire system, for example, due to excessive demand on a system resource. Every system will have bottlenecks. By monitoring the Microsoft SQL Server system for bottlenecks, you can determine if changes can be made to the limiting component to make it perform at an optimum level.

Demand on a resource can become extreme, causing a bottleneck for the following reasons:

•	Insufficient resources requiring additional or upgraded components.
•	Resources of the same type are not sharing workloads evenly and need to be balanced. For example, one disk is being monopolized compared to others.
•	Malfunctioning resource requiring replacement.
•	Incorrectly configured resource requiring reconfiguration.

Decide What to Examine

Low numbers can be just as meaningful as high numbers. If a number is lower than expected, it may be a clue to a problem in another area. For example:

•	Some other component is preventing the load from reaching this component.
•	Network congestion is preventing client requests from reaching the server.
•	A bottleneck is preventing client computers from accessing the server as frequently as expected.
•	Windows NT Performance Monitor is being used incorrectly (for example, user is neglecting to turn on disk counters, looking at the wrong instance or the wrong counters, or even looking at the wrong computer).

A low number could also mean that the system is performing better than expected in meeting user expectations.

These are five key areas you should monitor to track server performance and identify bottlenecks.

Bottleneck candidate	Effects on the server
Memory usage	An inadequate amount of memory allocated or available to SQL Server will degrade performance. Data must be continually read from the disk rather than residing in the data cache. Microsoft Windows NT performs excessive paging by swapping data to and from the disk as the pages are needed.
CPU processor utilization	A constantly high CPU rate may indicate the need for a CPU upgrade (or the addition of multiple processors).
Disk I/O performance	A slow disk I/O (disk reads and writes) will cause transaction throughput to degrade.
User connections	An improperly configured number of users can cause your system to run too slowly or restrict the amount of memory that could otherwise be made available to SQL Server.
Blocking locks	A process may be forcing another process to wait, thereby stopping the blocking process.

See Also

In This Volume

Monitoring Memory Usage

Monitoring CPU Use

Monitoring Disk Activity

SQL Server: Locks Object

SQL Server: General Statistics Object

Monitoring to Determine User Activity

You can monitor individual user activity to pinpoint transactions that may be blocking other transactions or causing the performance of Microsoft SQL Server to be slower than expected.

Monitoring user activity helps identify trends such as the types of transactions run by certain users, if any users are executing inefficient ad hoc queries, and the types of transactions that require the most resources.

To collect statistical information about users, use either SQL Server Profiler or Windows NT Performance Monitor. Use the SQL Server Enterprise Manager Current Activity window to perform ad hoc monitoring of SQL Server, which allows you to determine user activity on the system.

See Also

In This Volume

Monitoring with SQL Server Enterprise Manager

Sessions Event Category

SQL Server: General Statistics Object

Monitoring to Troubleshoot Problems

You can monitor the following areas to troubleshoot problems:

•	Microsoft SQL Server stored procedures or batches of SQL statements submitted by user applications
•	User activity, such as blocking locks or deadlocks
•	Hardware activity, such as disk usage

Problems can include:

•	Application development errors involving incorrectly written Transact-SQL.
•	Hardware errors, such as disk or network-related errors.
•	Excessive blocking due to an incorrectly designed database.

SQL Server Profiler can be used to monitor and troubleshoot Transact-SQL and application-related problems. Windows NT Performance Monitor can be used to monitor hardware and other system-related problems.

Monitoring to Test Applications

During the development phase of an application, any SQL scripts or stored procedures written will need to be tested to ensure they work as expected. By single-stepping through each line of SQL as it is executed by Microsoft SQL Server, Transact-SQL statements and stored procedures can be tested and debugged as necessary. This ensures more robust systems when the application is delivered and made available to users. SQL Server Profiler allows SQL statements or stored procedures executed by an application to be traced, line by line, as they are executed by SQL Server.

See Also

In This Volume

Single-Stepping Traces

Replaying Traces

Top of page

Components of Monitoring

Monitoring an application, Microsoft SQL Server, or the operating system environment (hardware and software), involves:

•

Identifying the events that must be monitored.

The events determine the activities that are monitored and captured. These events depend on what is being monitored and why. For example, when monitoring disk activity, it is not necessary to monitor SQL Server locks.

•

Determining the event data to capture.

The event data describes each instance of an event as it occurred. For example, when monitoring lock events, it is useful to capture data that describes the tables, users, and connections affected by the lock event.

•	Applying filters to limit the event data collected. Limiting the event data allows the system to focus on the specific types of events pertinent to the monitoring scenario. For example, when the slow queries for a database are monitored, a filter can be applied to monitor only the queries issued by the application against a particular database that take more than 30 seconds to execute.
•	Monitoring (capturing) events. This is the process of actively monitoring the application, SQL Server, and so on, to see what is occurring. For example, when disk activity is monitored using Windows NT Performance Monitor, it captures and displays the event data, such as disk reads and writes, to the screen.
•	Saving captured event data. This allows the data to be analyzed at a later time or even replayed (when using SQL Server Profiler). Captured event data is saved to a file that can be loaded back into the tool that originally created the file for analysis by a user. SQL Server Profiler also allows event data to be saved to a SQL Server table. Saving captured event data is vital when creating a performance baseline. The performance baseline data is saved and used when comparing recently captured event data to determine if performance is optimal.
•	Creating definition files that contain the settings specified to capture the events. This includes the events themselves, event data, and filters that are used to capture data. These files can be used to monitor a specific set of events at a later time without redefining the events, event data, and filters. For example, if you frequently want to monitor the number of deadlocks and the users involved in those deadlocks, you can create a file defining those events, event data, and event filters; save the definition; and reapply the filter the next time you want to monitor deadlocks. SQL Server Profiler uses trace definition files for this purpose.
•	Analyzing captured event data. The saved, captured event data is loaded into the application that captured the data. For example, a captured trace from SQL Server Profiler can be reloaded into SQL Server Profiler for viewing and analysis. Analyzing event data involves determining what is happening and why. Using this information allows you to make changes that can improve performance, such as adding more memory, correcting coding problems with Transact-SQL statements or stored procedures, changing indexes, and so on, depending on the type of analysis performed. For example, using the Index Tuning Wizard, a captured trace from SQL Server Profiler can be analyzed automatically and index recommendations can be suggested and created.
•	Replaying captured event data. This allows you to establish a test copy of the database environment from which the data was captured and replay the captured events as they originally occurred on the real system. You can replay them at the same speed as they originally occurred, as fast as possible (to stress the system), or more likely, replay them one step at a time, which allows you to analyze the system after each event has occurred. To determine the effects of the events, this allows you to analyze the exact events that occur on a production system in a test environment, thereby preventing any effect on the production system. Because the captured events can be replayed, testing and analysis can be repeated until the problem is fully understood. Only SQL Server Profiler allows you to replay captured events.

See Also

In Other Volumes

"Index Tuning Wizard" in Microsoft SQL Server Database Developer's Companion

Top of page

Choosing a Tool to Monitor Server Performance and Activity

Microsoft SQL Server provides a comprehensive set of tools for monitoring events in SQL Server. The choice of tool depends on the events to be monitored and the type of monitoring. For example, ad hoc monitoring to determine the number of users who are currently connected to a computer running SQL Server can be accomplished by using the sp_who system stored procedure, rather than creating a trace and using SQL Server Profiler.

Tool	Description
SQL Server Profiler	Provides the ability to monitor server and database activity (for example, number of deadlocks, fatal errors, tracing stored procedures and Transact-SQL statements, or login activity). You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis, and also replay the events captured on SQL Server, step by step, to see exactly what happened. SQL Server Profiler tracks engine process events, such as the start of a batch or a transaction.
Windows NT Performance Monitor	Provides the ability to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. Windows NT Performance Monitor collects counts of the events rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). Thresholds can be set on specific counters to generate alerts that notify operators. Windows NT Performance Monitor mainly tracks resource usage, such as the number of buffer manager page requests that are in use. Windows NT Performance Monitor only works on Microsoft Windows NT and can monitor (remotely or locally) SQL Server running only on Windows NT.
Current activity window (SQL Server Enterprise Manager)	Provides the ability to graphically display information about currently running processes in SQL Server, blocked processes, locks, and user activity. This is useful for ad hoc views of current activity.
Error logs	Contains more complete information about events in SQL Server. You can use the information in the error log to troubleshoot problems that are known to be SQL Server-related. The Microsoft Windows NT application log provides an overall picture of events occurring on the Windows NT system as a whole, as well as events in SQL Server and SQL Server Agent.
sp_who	Reports snapshot information about current SQL Server users and processes, including the currently executing statement and if the statement is blocked. This is a Transact-SQL alternative to viewing user activity in the current activity window in SQL Server Enterprise Manager.
sp_lock	Reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies. This is a Transact-SQL alternative to viewing lock activity in the current activity window in SQL Server Enterprise Manager.
sp_spaceused	Displays an estimate of the current amount of disk space used by a table (or a whole database). This is a Transact-SQL alternative to viewing database usage in SQL Server Enterprise Manager.
sp_monitor	Displays snapshot statistics, including CPU usage, I/O usage, and amount of time idle, which indicate how busy SQL Server has been since sp_monitor was last executed.
DBCC statements	Checks performance statistics and the logical and physical consistency of a database. For more information, see "DBCC" in Microsoft SQL Server Transact-SQL and Utilities Reference.
Built-in functions	Displays snapshot statistics about SQL Server activity since the server was started that are stored in predefined SQL Server counters. For example, @@CPU_BUSY contains the amount of time the CPU has been executing SQL Server code; @@CONNECTIONS contains the number of SQL Server connections or attempted connections made; @@PACKET_ERRORS (T-SQL) returns the number of network packet errors that have occurred on Microsoft® SQL Server™ connections since the last time SQL Server was started. For more information, see "FUNCTIONS" in Microsoft SQL Server Transact-SQL and Utilities Reference.
SQL Server Profiler extended stored procedures	Gathers SQL Server Profiler statistics by executing Transact-SQL extended stored procedures. For more information, see "System Stored Procedures" in Microsoft SQL Server Transact-SQL and Utilities Reference.
Trace flags	Displays information about a specific activity within the server that is used to diagnose detailed problems or performance issues (for example, deadlock chains). For more information, see "Trace Flags" in Microsoft SQL Server Transact-SQL and Utilities Reference.
Simple Network Management Protocol (SNMP)	Monitors SQL Server from applications that run on operating systems that support SNMP (for example, UNIX) by generating alerts when specific events occur within the server. For more information, see "SNMP" in this volume.

Comparing Tool Features and Functions

The choice of a monitoring tool depends on the type of events and activity to be monitored.

Event or activity	SQL ServerProfiler	Windows NTPerformance Monitor	Currentactivitywindow	Transact-SQL	Errorlogs
Trend analysis	Yes	Yes
Replaying captured events	Yes
Ad hoc monitoring	Yes		Yes	Yes	Yes
Generating alerts		Yes
Graphical interface	Yes	Yes	Yes		Yes
Using within custom application	Yes1			Yes

1 Using SQL Server Profiler extended stored procedures.

When deciding between SQL Server Profiler and Windows NT Performance Monitor (the two main monitoring tools), the key difference is that SQL Server Profiler monitors engine events while Windows NT Performance Monitor monitors resource usage associated with server processes. For example, SQL Server Profiler could be used to monitor deadlocks events, including the users and objects involved in the deadlock. Windows NT Performance Monitor could be used to monitor the total number of deadlocks occurring in a database or on a specific object.

Microsoft Windows NT also provides tools that generate an accurate understanding of what is happening in the system:

•

Task Manager

Shows a synopsis of the processes and applications that are running on the system.

•

Network Monitor Agent

Assists in monitoring network traffic.

For more information about Windows NT tools, see your Windows NT documentation.

Top of page

Monitoring with SQL Server Profiler

SQL Server Profiler is a graphical tool that allows system administrators to monitor engine events in computers running Microsoft SQL Server. Examples of engine events include:

•	The login connects, fails, and disconnects.
•	The Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
•	The remote procedure call (RPC) batch status.
•	The start or end of a stored procedure.
•	The start or end of statements within stored procedures.
•	The start or end of an SQL batch.
•	An error written to the SQL Server error log.
•	A lock acquired or released on a database object.
•	An opened cursor.

Data about each event can be captured and saved to a file or SQL Server table for later analysis. Data about the engine events is collected by creating traces. Examples of the event data captured within a trace include:

•	The type (class) of event, such as SQL:BatchCompleted, which indicates the completion of an SQL batch.
•	The name of the computer on which the client is running.
•	The ID of the object affected by the event, such as a table name.
•	The SQL Server name of the user issuing the statement.
•	The text of the Transact-SQL statement or stored procedure being executed.
•	The time the event started and ended.

Event data can be filtered so that only a subset of the event data is collected. This allows you to collect only the event data in which you are interested. For example, only the events that affect a specific database, or those for a particular user, can be collected, and all others ignored. Alternatively, data could be collected about only those queries that take longer than five seconds to execute.

Additionally, SQL Server Profiler allows captured event data to be replayed against SQL Server, thereby effectively reexecuting the saved events as they originally occurred.

SQL Server Profiler can be used to:

•	Monitor the performance of SQL Server.
•	Debug Transact-SQL statements and stored procedures.
•	Identify slow-executing queries.
•	Test SQL statements and stored procedures in the development phase of a project by single-stepping through statements to confirm that the code works as expected.
•	Troubleshoot problems in SQL Server by capturing events on a production system, and replay those captured events on a test system, thereby re-creating what happened in the production environment for testing or debugging purposes. Replaying captured events on a separate system allows the users to continue using the production system without interference.

SQL Server Profiler provides a graphical user interface to a set of extended stored procedures. You can also use these extended stored procedures directly. For example, it is possible to create your own application that uses SQL Server Profiler extended stored procedures to monitor SQL Server.

See Also

In Other Volumes

"System Stored Procedures" in Microsoft SQL Server Transact-SQL and Utilities Reference

Starting SQL Server Profiler

SQL Server Profiler is started from either the Microsoft Windows NT or Windows 95/98 Start menu or SQL Server Enterprise Manager. When SQL Server Profiler is started, it does not automatically monitor any events until a trace definition is created (or imported from a trace definition file) and started.

Permissions Needed to Connect to SQL Server

SQL Server Profiler can connect to Microsoft SQL Server using either Windows NT Authentication Mode or SQL Server Authentication. When Windows NT Authentication Mode is used to connect to SQL Server, the user account that is used to run SQL Server Profiler must be granted permission to connect to SQL Server. The login account must also be granted permissions to execute SQL Server Profiler extended stored procedures. For more information, see "System Stored Procedures" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Important When using a shared memory network library, SQL Server does not support impersonation unless SQL Server Profiler is connected using Windows NT Authentication.

Events Monitored by SQL Server Profiler

Microsoft SQL Server provides a set of events (event classes) that can be used by SQL Server Profiler to monitor activity in SQL Server. An event class is an event generated within the SQL Server engine, such as the start of the execution of a stored procedure, a successful or failed connection to SQL Server, a transaction, or a lock time out. Within SQL Server Profiler, event classes are grouped into event categories (collections) that describe the type of event class. For example, all lock event classes are grouped within the Locks event category.

Note Grouping event classes into categories aids usability and understanding within the SQL Server Profiler interface. There is no corresponding engine event that maps to the event category.

These are the SQL Server event categories.

Event category	Description
Cursors	Collection of event classes that are produced by cursor operations.
Error and Warning	Collection of event classes that are produced when a SQL Server error or warning occurs. For example, an error during the compilation of a stored procedure or an exception in SQL Server.
Locks	Collection of database object locking event classes.
Misc.	Collection of miscellaneous event classes that do not fit into any of the other event categories.
Objects	Collection of event classes that are produced when database objects are created, opened, closed, dropped, or deleted.
Scans	Collection of database object scan event classes. Database objects that can be scanned include tables and indexes.
Sessions	Collection of event classes that are produced by clients connecting to and disconnecting from SQL Server.
SQL Operators	Collection of event classes that are produced from the execution of SQL data manipulation language (DML) operators.
Stored Procedures	Collection of event classes that are produced by the execution of stored procedures.
Transactions	Collection of event classes that are produced by the execution of Microsoft Distributed Transaction Coordinator (MS DTC) or SQL transactions, or by writing to the transaction log.
TSQL	Collection of event classes that are produced by the execution of Transact-SQL passed to SQL Server from the client.
User Configurable	Collection of user-configurable event classes.

Data Columns and Defaults

The data columns describe the data that is collected for each of the event classes captured in the trace. Because the event class determines the type of data that is collected in the trace, not all data columns are applicable to all event classes. For example, the Binary Data data column, when captured for the Lock:Acquired event class, contains the value of the locked page ID or row but has no value for the Disconnect event class. Default data columns are automatically populated for all event classes.

The data displayed in SQL Server Profiler can either be displayed in the order the events occur or grouped based on one or a combination of data columns. This is similar to the GROUP BY clause in Transact-SQL. Grouping events allows you to view events by the type of data displayed. For example, grouping events by SQL User Name and Duration allows you to view all monitored events grouped by user, and then subgrouped by duration. This allows you to easily view which user events are taking the longest amount of time to execute.

Data column	Description
Application Name1	Name of the client application that created the connection to SQL Server. This column is populated with the values passed by the application rather than the displayed name of the program.
Binary Data	Binary value dependent on the event class captured in the trace.
Connection ID1	ID assigned by SQL Server to the connection that is established by the client application. Events produced by system processes may not have a connection ID.
CPU1	Amount of CPU time (in milliseconds) that is used by the event.
Database ID1	ID of the database specified by the USE database statement, or the default database if no USE database statement has been issued for a given connection. SQL Server Profiler displays the name of the database if the Server Name data column is captured in the trace and the server is available. The value for a database can be determined by using the DB_ID function.
Duration1	Amount of elapsed time (in milliseconds) taken by the event.
End Time1	Time at which the event ended. This column is not populated for starting event classes, such as SQL:BatchStarting or SP:Starting.
Event Class1	Type of event class that is captured.
Event Sub Class	Type of event subclass. This data column is not populated for all event classes.
Host Name	Name of the computer on which the client is running. This data column is populated if the host name is provided by the client. To determine the host name, use the HOST_NAME function.
Host Process ID	ID assigned by the host computer to the process in which the client application is running. This data column is populated if the host process ID is provided by the client. To determine the host ID, use the HOST_ID function.
Index ID	ID for the index on the object that is affected by the event. To determine the index ID for an object, use the indid column of the sysindexes system table.
Integer Data	Integer value dependent on the event class that is captured in the trace.
NT Domain Name1	Microsoft Windows NT domain to which the user belongs.
NT User Name1	Windows NT username.
Object ID	System-assigned ID of the object.
Reads	Number of logical disk reads that are performed by the server on behalf of the event.
Server Name1	Name of the SQL Server that is traced.
Severity	Severity level of an exception.
SPID1	Server Process ID assigned by SQL Server to the process associated with the client.
SQL User Name1	SQL Server username of the client.
Start Time1	Time at which the event started, when available.
Text	Text value dependent on the event class that is captured in the trace.
Transaction ID	System-assigned ID of the transaction.
Writes	Number of physical disk writes performed by the server on behalf of the event.

¹ Default data column, which is automatically populated for all event classes.

When viewing the captured data in a trace, if SQL Server Profiler can connect to the computer running SQL Server where the trace data was captured from, it will try to populate the Database ID, Object ID, and Index ID data columns with the names of the database, object, and index respectively. Otherwise, identification numbers (IDs) will be displayed instead.

Cursors Event Category

The Cursors event classes can be used to monitor cursor operations.

Event class	Description
CursorClose	A cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is closed.
CursorExecute	A cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is executed. For more information, see "How to prepare and execute a statement (ODBC)" in SQL Server Books Online.
CursorImplicitConversion	A cursor on a Transact-SQL statement is converted by SQL Server from one type to another. Triggered for ANSI and non-ANSI cursors.
CursorOpen	A cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library.
CursorPrepare	A cursor on a Transact-SQL statement is prepared for use by ODBC, OLE DB, or DB-Library. For more information, see "How to prepare and execute a statement (ODBC)" in SQL Server Books Online.
CursorRecompile	A cursor that is opened on a Transact-SQL statement by ODBC or DB-Library has been recompiled either directly or due to a schema change. Triggered for ANSI and non-ANSI cursors.
CursorUnprepare	A prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODBC, OLE DB, or DB-Library.

By monitoring the CursorOpen, CursorExecute, and CursorImplicitConversion event classes, you can determine when a cursor is executed and what type of cursor is used. These event classes are useful to determine the actual cursor type used for an operation by SQL Server, rather than the cursor type specified by the application.

These are the event-specific data columns for the Cursors event category.

Event class	Data column	Description
CursorClose	Event Sub Class	Handle of the cursor.
CursorExecute	Event Sub Class	Handle of the cursor.
	Integer Data	Cursor type. Values are: 1 = Keyset 2 = Dynamic 4 = Forward only 8 = Static 16 = Fast forward
	Binary Data	Handle of the prepared cursor.
CursorImplicitConversion	Integer Data	Requested cursor type. Values are: 1 = Keyset 2 = Dynamic 4 = Forward only 8 = Static 16 = Fast forward
	Binary Data	Resulting cursor type. Values are: 1 = Keyset 2 = Dynamic 4 = Forward only 8 = Static 16 = Fast forward
CursorOpen	Event Sub Class	Handle of the cursor.
	Integer Data	Cursor type. Values are: 1 = Keyset 2 = Dynamic 4 = Forward only 8 = Static 16 = Fast forward
CursorPrepare	Event Sub Class	Handle of the prepared cursor.
CursorUnprepare	Event Sub Class	Handle of the cursor created by CursorPrepare.

See Also

In Other Volumes

"Cursors" in Microsoft SQL Server Database Developer's Companion

Error and Warning Event Category

The Error and Warning event classes can be used to monitor many of the errors and warnings that are raised by Microsoft SQL Server and components such as OLE DB.

Event class	Description
ErrorLog	Error events have been logged in the SQL Server error log.
EventLog	Events have been logged in the Microsoft Windows NT application log.
Exception	Exception has occurred in SQL Server.
Execution Warnings	Any warnings that occurred during the execution of a SQL Server statement or stored procedure.
Hash Warning	Hashing operation may have incurred a problem.
Missing Column Statistics	Column statistics that could have been useful for the optimizer are not available.
Missing Join Predicate	Query is being executed that has no join predicate. This could result in a long-running query.
OLEDB Errors	OLE DB error has occurred.
Sort Warnings	Sort operations do not fit into memory. This does not include sort operations involving the creation of indexes, only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).

By monitoring the Missing Column Statistics event class, you can determine if there are statistics missing for a column used by a query. This can cause the optimizer to choose a less-efficient query plan than otherwise expected. For more information about creating column statistics, see "Statistical Information" in Microsoft SQL Server Database Developer's Companion.

The Execution Warnings event class can be monitored to determine if and how long queries had to wait for resources before proceeding. This is important for determining if there are any contention issues in the system that can affect performance and therefore need investigating. Use the Locks event classes to determine the objects affected.

The Sort Warnings event class can be used to monitor query performance. If a query involving a sort operation generates a Sort Warnings event class with an Event Sub Class data column value of 2, the performance of the query can be affected because multiple passes over the data are required to sort the data. Investigate the query further to determine if the sort operation can be eliminated.

The Hash Warning event class can be used to monitor when a hash recursion or hash bail has occurred during a hashing operation. Hash recursion occurs when the build input does not fit into memory, resulting in the input being split into multiple partitions, which are processed separately. If any of these partitions still do not fit into memory, they are further split into sub-partitions, which are then processed separately. This process continues until each partition fits into memory or the maximum recursion level is reached (displayed in the Integer Data data column), thus causing hash bail to occur.

Hash bail occurs when a hashing operation reaches its maximum recursion depth and reverts to an alternate plan to process its remaining partitioned data. Hash bail usually occurs due to skewed data, trace flags, or bit counting. To eliminate or reduce the chance of hash bail occurring, verify that statistics exist on the columns being joined or grouped. For more information, see "Statistical Information" in Microsoft SQL Server Database Developer's Companion.

If hash bail continues to occur each time the query is executed, consider using an optimizer hint to force a different algorithm to be used by the query optimizer and then compare the performance of the query. For more information about Join Hints, see "FROM" in Microsoft SQL Server Transact-SQL and Utilities Reference.

These are the event-specific data columns for the Error and Warning event category.

Event class	Data column	Description
ErrorLog	Severity	Error severity.
	Event Sub Class	Error number.
	Text	Text of the error message.
EventLog	Binary Data	Supplied binary data, if available.
	Severity	Error severity.
	Event Sub Class	Error number, if available.
	Text	Text of the error message, if available.
Exception	Integer Data	Error number.
	Severity	Error severity.
	Event Sub Class	Server state.
Execution Warnings	Event Sub Class	Can have these values: 1 = Query wait. The query must wait for resources before it can execute. 2 = Query time-out. The query timed out while waiting for resources it required to execute.
	Integer Data	The time (in seconds) the query had to wait before continuing or timing out.
Hash Warning	Event Sub Class	The type of hash operation. Can have these values: 0 = Hash recursion. 1 = Hash bail.
	Integer Data	Recursion level (Hash recursion only).
	Object ID	Hash partition node ID.
Missing Column Statistics	Text	List of the columns with missing statistics.
OLEDB Errors	Text	Error message.
Sort Warnings	Event Sub Class	Can have these values: 1 = Single pass. When the sort table was written to disk, only a single additional pass over the data to be sorted was required to obtain sorted output. 2 = Multiple pass. When the sort table was written to disk, multiple passes over the data to be sorted were required to obtain sorted output.

See Also

In This Volume

Monitoring the Error Logs

In Other Volumes

"Error Messages" in Microsoft SQL Server Diagnostics

Locks Event Category

The Locks event classes can be used to monitor Microsoft SQL Server lock activity.

Event class	Description
Lock:Acquired	Acquisition of a lock on a resource, such as a data page, has been achieved. For more information about resources that can be locked, see "Understanding Locking in SQL Server" in Microsoft SQL Server Database Developer's Companion.
Lock:Cancel	Acquisition of a lock on a resource has been canceled (for example, due to a deadlock).
Lock:Deadlock	Two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources that the other transaction owns. For more information, see "Deadlocking" in Microsoft SQL Server Database Developer's Companion.
Lock:Deadlock Chain	Produced for each of the events leading up to the deadlock.
Lock:Escalation	A finer-grained lock has been converted to a coarser-grained lock (for example, a row lock that is converted to a page lock).
Lock:Released	A lock on a resource, such as a page, has been released.
Lock:Timeout	A request for a lock on a resource, such as a page, has timed out due to another transaction holding a blocking lock on the required resource. Time-out is determined by the @@LOCK_TIMEOUT system function and can be set with the SET LOCK_TIMEOUT statement. For more information, see "Customizing the Lock Time-out" in Microsoft SQL Server Database Developer's Companion.

By monitoring the Locks event classes, you can investigate contention issues caused by concurrent users and applications using a database. The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated. For example, an application can be acquiring locks on rows in a table, and then waiting for user input. Because the user input can take a long time to occur, the locks can block other users. In this instance, the application should be redesigned to make lock requests only when needed and not require user input when locks have been acquired.

The Lock:Deadlock, Lock:Deadlock Chain, and Lock:Timeout event classes can be used to monitor when deadlocks and time-out conditions occur, and which objects are involved. This information is useful to determine if deadlocks and time-outs are significantly affecting the performance of your application, and which objects are commonly involved. The application code that modifies these objects can then be examined to determine if changes to minimize deadlocks and time-outs can be made. For more information about reducing deadlocks, see "Avoiding Deadlocks" in Microsoft SQL Server Database Developer's Companion.

Because lock events are so prolific, capturing the lock event classes can incur significant overhead on the server being traced and result in very large trace files or trace tables.

These are the event-specific data columns for the Locks event category.

Event class	Data column	Description
Lock:Acquired	Binary Data	Resource ID.
	Duration	Wait between the time the lock request was issued and the time the lock was acquired.
	Object ID	ID of the object on which the lock was acquired.
	Event Sub Class	Lock mode, such as intent exclusive.
Lock:Cancel	Binary Data	Resource ID.
	Duration	Wait between the time the lock request was issued and the time the lock was canceled.
	Object ID	ID of the object on which the lock was canceled.
	Event Sub Class	Lock mode, such as intent exclusive.
Lock:Deadlock	Binary Data	Resource ID.
	Duration	Wait between the time the lock request was issued and the time the deadlock occurred.
	Integer Data	Deadlock number. Numbers are assigned beginning with 0 when the server is started and are incremented for each deadlock.
	Object ID	ID of the object in contention.
	Event Sub Class	Lock mode, such as intent exclusive.
Lock:Deadlock Chain	Binary Data	Resource ID.
	Integer Data	Deadlock number. Numbers are assigned beginning with 0 when the server is started, and incremented for each deadlock.
	Object ID	ID of the object that was locked.
	Event Sub Class	Lock mode, such as intent exclusive.
Lock:Escalation	Object ID	ID of the object on which the lock was escalated.
Lock:Released	Binary Data	Resource ID.
	Duration	Wait between the time the lock request was issued and the time the lock was released.
	Object ID	ID of the object on which the lock was released.
	Event Sub Class	Lock mode, such as intent exclusive.
Lock:Timeout	Binary Data	Resource ID.
	Duration	Wait time between the time the lock request was issued and the lock was timed out.
	Object ID	ID of the object on which the lock was timed out.
	Event Sub Class	Lock mode, such as intent exclusive.

See Also

In Other Volumes

"Locking" in Microsoft SQL Server Database Developer's Companion

Misc. Event Category

The Misc. event classes can be used to monitor a variety of event classes not found in the other event categories, such as failed logins and query plans.

Event class	Description
Attention	Attention event, such as client-interrupt requests or broken client connections, has occurred.
Auto-UpdateStats	Event associated with the automatic updating of index statistics has occurred.
Exec Prepared SQL	ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL statement or statements.
Execution Plan	Plan tree of the Transact-SQL statement being executed is displayed.
LoginFailed	Login attempt to SQL Server from a client has failed.
Prepare SQL	ODBC, OLE DB, or DB-Library has prepared a Transact-SQL statement or statements for use.
Server Memory Change	Microsoft SQL Server memory usage has increased or decreased by either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever is greater.
ServiceControl	Server control event, such as server paused or restart events, has occurred.
Unprepare SQL	ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared Transact-SQL statement or statements.

By monitoring the Attention event class, you can determine if attention events are occurring. If a high number of attention events is generated, this could indicate a network communication problem between the clients and SQL Server that needs further investigation.

Monitoring the LoginFailed event class is useful for monitoring security and performing logon auditing. By monitoring the Event Sub Class and the SQL User Name or NT User Name default data columns, you can determine which users are failing to connect to SQL Server and why.

These are the event-specific data columns for the Misc. event category.

Event class	Data column	Description
Auto-UpdateStats	Event Sub Class	Can have these values: 0 = error 1 = success
Exec Prepared SQL	Event Sub Class	Handle of the prepared Transact-SQL statement.
Execution Plan	Binary Data	Estimated cost.
	Integer Data	Estimated rows returned.
	Text	Execution plan tree. Only SQL statement trees are expressed. Transact-SQL constructs are not represented.
LoginFailed	Text	Login error message.
Prepare SQL	Event Sub Class	Handle of the prepared Transact-SQL statement.
Server Memory Change	Event Sub Class	Can have these values: 1 = Memory increase 2 = Memory decrease
	Integer Data	The new memory size.
ServiceControl	Event Sub Class	Can have these values: 0 = Exit 1 = Start 2 = Pause 3 = Continue 4 = Stop
Unprepare SQL	Event Sub Class	Handle of the prepared Transact-SQL statement.

Objects Event Category

The Objects event classes can be used to monitor when an object such as a database, table, index, view, or stored procedure is opened, created, deleted, or used.

Event class	Description
Object:Closed	Open object has been closed, such as at the end of a SELECT, INSERT, or DELETE statement.
Object:Created	Object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.
Object:Deleted	Object has been deleted, such as for DROP INDEX and DROP TABLE statements.
Object:Opened	Object has been accessed, such as for SELECT, INSERT, or DELETE statements.

By monitoring the Object:Opened event class, you can determine which objects are most commonly used. The Object:Created and Object:Deleted event classes can be used to determine if many ad hoc objects are being created or deleted, for example, by ODBC applications that often create temporary stored procedures. By monitoring the SQL User Name and NT User Name default data columns in addition to the Objects event classes, you can determine the name of the user who is creating, deleting, or accessing objects. This can be useful when monitoring to determine if your security policies are correctly implemented, for example, to confirm that users who are not allowed to create or delete objects are not doing so.

Because object events are so prolific, capturing the object event classes can incur significant overhead on the server being traced and result in large trace files or trace tables.

These are the event-specific data columns for the Objects event category.

Event class	Data column	Description
Object:Closed	Event Sub Class	Object type
	Object ID	Object that was closed
Object:Created	Event Sub Class	Object type
	Object ID	Object that was created
Object:Deleted	Event Sub Class	Object type
	Object ID	Object that was deleted
Object:Opened	Event Sub Class	Object type
	Object ID	Object that was opened

See Also

In Other Volumes

"Overview of Creating and Maintaining Databases" in Microsoft SQL Server Database Developer's Companion

Scans Event Category

The Scans event classes can be used to monitor when a table or index is being scanned during the execution of a query.

Event class	Description
Scan: Started	Table or index scan has started.
Scan: Stopped	Table or index scan has stopped.

These are the event-specific data columns for the Scans event category.

Event class	Data column	Description
Scan: Started	Event Sub Class	Scan mode represented as a bitmap
	Index ID	Index that is being scanned
	Object ID	Object that is being scanned
Scan: Stopped	Event Sub Class	Scan mode represented as a bitmap
	Index ID	Index that is being scanned
	Object ID	Object that is being scanned

Using the Scan:Started and Scan:Stopped event classes, it is possible to monitor the type of scans being performed by a query on a specific object.

These are the scan modes for the Event Sub Class data column.

Value	Scan mode
1	Normal
2	First
4	Back
8	Unordered
16	No data
32	Reserved
64	Exlatch
128	Index supplied
256	Marker

By monitoring the Index ID default data column, you can determine the identification number of the index being used by a specific query. The Index ID data column contains either:

•	The value 1 when the clustered index of the table is being scanned.
•	The value 2 through 255 when a nonclustered index is being scanned.

Sessions Event Category

The Sessions event classes can be used to monitor Microsoft SQL Server user connections.

Event class	Description
Connect	New connection event has occurred since the trace was started, such as a client requesting a connection to a server running SQL Server.
Disconnect	New disconnect event has occurred since the trace was started, such as a client issuing a disconnect command.
ExistingConnection	Activity by users connected to SQL Server before the trace was started has been detected. Otherwise, these existing connections would not be detected by the Connect event class.

Using the Disconnect and ExistingConnection event classes, it is possible to monitor the length of time each user connection was connected to SQL Server, and the amount of SQL Server processor time the queries submitted on the connection took to execute. This information can be useful for determining:

•	The amount of time and the volume of activity used by each SQL Server user. This can be useful for tracking database activity for different users and charging each user for the time and SQL Server CPU time (CPU data column) they used.
•	The security of the system, by checking the users who are connecting to and using SQL Server.

These are the event-specific data columns for the Sessions event category.

Event class	Data column	Description
Connect	Binary Data	Session level settings, including ANSI nulls, ANSI padding, cursor close on commit, null concatenation, and quoted identifiers. For more information, see "SET" in Microsoft SQL Server Transact-SQL and Utilities Reference.
Disconnect	CPU	CPU time used by the connection.
	Duration	Duration of the connection (time elapsed since the connection was opened).
ExistingConnection	Binary Data	Session level settings, including ANSI nulls, ANSI padding, cursor close on commit, null concatenation, and quoted identifiers. For more information, see "SET" in Microsoft SQL Server Transact-SQL and Utilities Reference.
	CPU	CPU time used by the connection until the time of the event.
	Duration	Duration of the connection until the time of the event.

By monitoring the SQL User Name and NT User Name default data columns, you can map the name of the user to each connection.

To determine failed logins, use the LoginFailed event class in the Misc. Event Category.

See Also

In This Volume

Events Monitored by SQL Server Profiler

SQL Operators Event Category

The SQL Operators event classes can be used to monitor when a DELETE, INSERT, SELECT, or UPDATE query occurs.

Event class	Description
Delete	Occurs before a DELETE statement is executed.
Insert	Occurs before an INSERT statement is executed.
Select	Occurs before a SELECT statement is executed.
Update	Occurs before an UPDATE statement is executed.

The Event Sub Class data column for each operator can be used for monitoring if the query optimizer generates a parallel query execution plan for the specific SQL operator.

Event class	Data column	Description
Delete	Event Sub Class	Degree of parallelism (number of CPUs used to perform the DELETE).
Insert	Event Sub Class	Degree of parallelism (number of CPUs used to perform the INSERT).
Select	Event Sub Class	Degree of parallelism (number of CPUs used to perform the SELECT).
Update	Event Sub Class	Degree of parallelism (number of CPUs used to perform the UPDATE).

The degree of parallelism chosen for each query plan execution is defined as follows.

Event Sub Class value	Description
0	No parallelism considered because Microsoft SQL Server could execute the query only on a single CPU. This occurs when: The computer has only one processor. The max degree of parallelism server configuration option is set to 1. For more information, see "max degree of parallelism Option" in this volume. The MAXDOP 1 query hint is specified. For more information, see "SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference. The Desktop edition of SQL Server is being used.
1	Parallel execution considered, but the query is executed using a serial plan because either the query is trivial, the cost of executing the query is less than the cost threshold for parallelism value, or there are not enough resources available to execute a parallel plan. For more information, see "cost threshold for parallelism Option" in this volume.
>1	For a DELETE, INSERT, SELECT, or UPDATE statement, the whole or a portion of the query is executed using a parallel execution plan with the shown degree of parallelism.

To achieve proper and efficient parallel query execution, setting configuration parameters is not required.

Every parallel plan contains either the Distribute Streams, Gather Streams, or Repartition Streams logical operators. For more information, see "Logical and Physical Operators" in Microsoft SQL Server Diagnostics.

Note Although system administrators can influence the query optimizer's generation of parallel query execution plans by changing the values for the cost threshold for parallelism and max degree of parallelism server configuration options using the sp_configure system stored procedure, it is not recommended. For more information, see "Setting Configuration Options" in this volume.

Stored Procedures Event Category

The Stored Procedures event classes can be used to monitor the execution of stored procedures.

Event class	Description
SP:CacheHit	Procedure is found in the cache.
SP:CacheInsert	Item is inserted into the procedure cache.
SP:CacheMiss	Stored procedure is not found in the procedure cache.
SP:CacheRemove	Item has been removed from the procedure cache.
SP:Completed	Stored procedure has completed.
SP:ExecContextHit	Execution version of a stored procedure has been found in the cache.
SP:Recompile	Stored procedure has been recompiled.
SP:Starting	Stored procedure has started.
SP:StmtCompleted	Statement within a stored procedure has completed.
SP:StmtStarting	Statement within a stored procedure has started.

By monitoring the SP:CacheHit and SP:CacheMiss event classes, you can determine how often stored procedures are found in the cache when executed. If, for example, the SP:CacheMiss event class occurs frequently, then it can indicate that more memory should be made available to Microsoft SQL Server, thereby increasing the size of the procedure cache. By monitoring the Object ID of the SP:CacheHit event class, you can determine which stored procedures reside in the cache.

The SP:CacheInsert, SP:CacheRemove, and SP:Recompile event classes can be used to determine which stored procedures are brought into cache (first executed), then later removed from the cache (aged out of the cache), and also when they get recompiled. For more information about recompiling stored procedures, see "Recompiling a Stored Procedure" in Microsoft SQL Server Database Developer's Companion. This information is useful to determine how stored procedures are being used by applications.

By monitoring the SP:Starting, SP:StmtStarting, SP:StmtCompleted, and SP:Completed event classes and all the TSQL event classes, the execution of a stored procedure can be monitored.

A stored procedure has a compiled version that has shared data and an execution context version that has session specific data. When a stored procedure is looked up in the cache, execution contexts are looked for first. If none are found, the cache is searched for compiled plans. Use the SP:ExecContextHit event class to monitor execution contexts. If the SP:ExecContextHit event class is not generated for a stored procedure, then the stored procedure has no execution time cachable queries.

These are the event-specific data columns for the Stored Procedures event category.

Event class	Data column	Description
SP:CacheHit	Event Sub Class	Nesting level of the stored procedure.
	Object ID	System-assigned ID of the stored procedure.
SP:CacheInsert	Event Sub Class	Nesting level of the stored procedure.
	Object ID	System-assigned ID of the stored procedure.
SP:CacheMiss	Event Sub Class	Nesting level of the stored procedure.
	Object ID	System-assigned ID of the stored procedure.
SP:CacheRemove	Event Sub Class	Nesting level of the stored procedure. A value of 0 indicates that the stored procedure was explicitly removed from the buffer pool.
	Object ID	System-assigned ID of the stored procedure.
SP:Completed	Event Sub Class	Nesting level of the stored procedure.
	Object ID	System-assigned ID of the stored procedure.
	Text	Text of the stored procedure.
SP:ExecContextHit	Event Sub Class	Nesting level of the stored procedure.
	Object ID	System-assigned ID of the stored procedure.
SP:Recompile	Event Sub Class	Nesting level of the stored procedure.
	Object ID	System-assigned ID of the stored procedure.
SP:Starting	Event Sub Class	Nesting level of the stored procedure.
	Object ID	System-assigned ID of the stored procedure.
	Text	Text of the stored procedure.
SP:StmtCompleted	Event Sub Class	Nesting level of the stored procedure.
	Integer Data	Actual rows returned by the statement.
	Object ID	System-assigned ID of the stored procedure.
	Text	Text of the statement in the stored procedure.
SP:StmtStarting	Event Sub Class	Nesting level of the stored procedure.
	Object ID	System-assigned ID of the stored procedure.
	Text	Text of the statement in the stored procedure.

See Also

In Other Volumes

"Stored Procedures" in Microsoft SQL Server Database Developer's Companion

Transactions Event Category

The Transactions event classes can be used to monitor the status of transactions.

Event class	Description
DTCTransaction	Tracks Microsoft Distributed Transaction Coordinator (MS DTC) coordinated transactions between two or more databases.
SQLTransaction	Tracks Transact-SQL BEGIN, COMMIT,