Microsoft SQL Server

Monitoring Server Performance and Activity

Microsoft® SQL Server™ 2000 provides a variety of tools that can be used to monitor the performance of an instance of SQL Server and the user activity that occurs in databases. Monitoring allows you to determine whether your database application is working efficiently and as expected, even as your application, database, and environment change. For example, as more concurrent users use a database application, the load on SQL Server can increase. By monitoring, you can determine whether the current instance of SQL Server or system configuration must be changed to handle the increased workload, or whether the increased load is having no significant effect on performance.

To monitor an application, an instance of SQL Server, or the operating system environment (hardware and software):

The events determine which activities are monitored and captured. Your selection of events to monitor will depend on what is being monitored and why. For example, when monitoring disk activity, it is not necessary to monitor SQL Server locks.

The event data describes each instance of an event as it occurs. For example, when monitoring lock events, you can capture data describing the tables, users, and connections affected by the lock event. The following explains the process involved in capturing event data and putting it to use.

Limiting the event data allows the system to focus on the events pertinent to the monitoring scenario. For example, if you want to monitor slow queries, you can use a filter to monitor only those queries issued by the application that take more than 30 seconds to execute against a particular database.

Once enabled, active monitoring captures data from the specified application, instance of SQL Server, or operating system. For example, when disk activity is monitored using System Monitor (Performance Monitor in Microsoft Windows NT® 4.0), monitoring captures event data such as disk reads and writes and displays it to the screen.

Saving captured data allows you to analyze it at a later time or even replay it using SQL Profiler. Captured event data is saved to a file that can be loaded back into the tool that originally created the file for analysis. SQL Profiler 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 whether performance is optimal.

Definition files include specifications about 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 want to monitor frequently 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 Profiler uses trace definition files for this purpose.

In order to be analyzed, the captured, saved event data is loaded into the application that captured the data. For example, a captured trace from SQL Profiler can be reloaded into SQL Profiler for viewing and analysis. Analyzing event data involves determining what is happening and why. This information allows you to make changes that can improve performance, such as adding more memory, changing indexes, correcting coding problems with Transact-SQL statements or stored procedures, and so on, depending on the type of analysis performed. For example, you can use the Index Tuning Wizard to analyze a captured trace from SQL Profiler automatically and make index recommendations based on the results.

Only available in SQL Profiler, event replay allows you to establish a test copy of the database environment from which the data was captured and repeat the captured events as they occurred originally 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, one step at a time (to analyze the system after each event has occurred). By analyzing the exact events in a test environment, you can prevent detrimental effects on the production system.

Monitoring SQL Server allows you to:

 

Evaluating Performance

Optimal performance comes from minimal response times and maximum throughput as a result of efficient network traffic, disk I/O, and CPU time. This goal is achieved by analyzing thoroughly 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 is measured as the length of time required for the first row of the result set to be returned to the user in the form of visual confirmation that a query is being processed.

Throughput is a measure of the total number of queries handled by the server during a given time.

As the number of users increases, so does the competition for a server's resources, which in turn causes response time to increase and overall throughput to decrease.

Factors That Affect Performance

The following areas affect the performance of SQL Server:

Before these areas can be monitored, you must know what level of performance is reasonable given normal working conditions. To do this, establish a server performance baseline by monitoring Microsoft® SQL Server™ performance at regular intervals, even when no problems occur.

Troubleshooting Problems

You can monitor the following areas to troubleshoot problems:

Problems can include:

SQL Profiler can be used to monitor and troubleshoot Transact-SQL and application-related problems. System Monitor (Performance Monitor in Windows NT 4.0) can be used to monitor hardware and other system-related problems.

 

Establishing a Performance Baseline

To determine whether your Microsoft® SQL Server™ system is performing optimally, take performance measurements over time and establish a server performance baseline. Compare each new set of measurements with those taken earlier.

After you establish 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 in need of tuning or reconfiguration. For example, if the amount of time to execute a set of queries increases, examine the queries to determine if they can be rewritten or if column statistics or new indexes must be added.

At a minimum, use baseline measurements to determine:

Identifying Bottlenecks

Bottlenecks are caused by excessive demand on a system resource, and they are present in every system, to varying degrees. By monitoring the Microsoft® SQL Server™ system for bottlenecks, you can determine whether changes can be made to the limiting component to make it perform at an optimal level.

Reasons that bottlenecks occur include:

Analyzing Bottlenecks

When analyzing event data, low numbers can be just as meaningful as high numbers. If a number is lower than expected, it may indicate a problem in another area. For example:

A low number also can mean that the system is performing better than expected.

These are five key areas to monitor when tracking server performance and identifying bottlenecks.

Bottleneck candidate

Effects on the server

Memory usage

Insufficient memory allocated or available to SQL Server will degrade performance. Data must be read from the disk continually rather than residing in the data cache. Windows NT 4.0 and Microsoft Windows® 2000 perform 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 the system to run slowly or restrict the amount of memory otherwise available to SQL Server.

Blocking locks

A process may be forcing another process to wait, thereby slowing down or stopping the blocking process.


Determining 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, the number of inefficient ad hoc queries being run, and the types of transactions requiring the most resources.

To collect statistical information about users, use either SQL Profiler or System Monitor (Windows NT Performance Monitor in Windows NT® 4.0). 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.

 

Choosing a Monitoring Tool

Microsoft® SQL Server™ provides a comprehensive set of tools for monitoring events in SQL Server. Your choice of tool will depend on the type of monitoring and the events to be monitored. For example, ad hoc monitoring to determine the number of users currently connected to an instance of SQL Server can be accomplished by using the sp_who system stored procedure, rather than creating a trace and using SQL Profiler.

SQL Profiler

Enables you 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 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 Profiler tracks engine process events, such as the start of a batch or a transaction.

System Monitor

Enables you to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. System Monitor (Performance Monitor in Microsoft Windows NT® 4.0) collects counts rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). You can set thresholds on specific counters to generate alerts that notify operators. System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use.

System Monitor works only on Microsoft Windows® 2000 and can monitor (remotely or locally) an instance of SQL Server on Windows NT 4.0 or Windows 2000 only.

Current activity window (SQL Server Enterprise Manager)

Graphically displays information about processes running currently on an instance of SQL Server, blocked processes, locks, and user activity. This is useful for ad hoc views of current activity.

Error Logs

Contain additional information about events in SQL Server than is available elsewhere. You can use the information in the error log to troubleshoot SQL Server-related problems. The Windows application event log provides an overall picture of events occurring on the Windows NT 4.0 and Windows 2000 system as a whole, as well as events in SQL Server, SQL Server Agent, and full-text search.

sp_who

Reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether 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 statistics, including CPU usage, I/O usage, and the amount of time idle since sp_monitor was last executed.

DBCC statements

Enables you to check performance statistics and the logical and physical consistency of a database.

Built-in functions

Display snapshot statistics about SQL Server activity since the server was started; these statistics 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; and @@PACKET_ERRORS contains the number of network packets occurring on SQL Server connections.

SQL Profiler stored procedures and functions

Use Transact-SQL stored procedures to gather SQL Profiler statistics.

Trace flags

Display information about a specific activity within the server and are used to diagnose problems or performance issues (for example, deadlock chains).

Simple Network Management Protocol (SNMP)

Simple Network Management Protocol (SNMP) is an application protocol that offers network management services. Using SNMP, you can monitor an instance of SQL Server across different platforms (for example, Windows NT 4.0, Windows 98, and UNIX). With SQL Server and the Microsoft SQL Server Management Information Base (MSSQL-MIB), you can use SNMP applications to monitor the status of SQL Server installations. You can monitor performance information, access databases, and view server and database configuration parameters.

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



Event or activity


SQL Profiler


System Monitor

Current activity window


Transact-SQL


Error logs

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

Yes 1

 

 

Yes

 

 

1 Using SQL Profiler system stored procedures.

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

Windows NT 4.0 and Windows 2000 also provides these monitoring tools:

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

Assists in monitoring network traffic.

For more information about Windows NT 4.0 or Windows 2000 tools, see the Windows NT 4.0 or Windows 2000 documentation.

Monitoring with SQL Profiler

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft® SQL Server™. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

After you have traced events, SQL Profiler allows captured event data to be replayed against an instance of SQL Server, thereby effectively reexecuting the saved events as they occurred originally.

Use SQL Profiler to:

SQL Profiler provides a graphical user interface to a set of stored procedures that can be used to monitor an instance of SQL Server. For example, it is possible to create your own application that uses SQL Profiler stored procedures to monitor SQL Server.

You must have at least 10 megabytes (MB) of free space to run SQL Profiler. If free space drops below 10 MB while you are using SQL Profiler, all SQL Profiler functions will stop.

Starting SQL Profiler

SQL Profiler is started from the Microsoft® Windows NT® 4.0, Microsoft Windows® 2000 or Microsoft Windows 98 Start menu, or from SQL Server Enterprise Manager.

With Windows Authentication mode, the user account that runs SQL Profiler must be granted permission to connect to an instance of SQL Server. The login account also must be granted permissions to execute SQL Profiler stored procedures.

 

SQL Profiler Keyboard Shortcuts

The following table shows the keyboard shortcuts available in SQL Profiler.

CTRL+Shift+Delete

Clear a trace window

CTRL+F4

Close a trace window

-

Collapse a trace grouping

CTRL+C

Copy

ALT+Delete

Delete a trace

+

Expand a trace grouping

CTRL+F

Find

F3

Find the next item

Shift+F3

Find the previous item

F1

Display available help

CTRL+N

Open a new trace

ALT+F7

Replay the settings

CTRL+F10

Run to cursor

F5

Start a replay

F11

Step

Shift+F5

Stop a replay

F9

Toggle a breakpoint


SQL Profiler Terminology

To use SQL Profiler, you need to understand the terminology that describes the way the tool functions. For example, you create a template that defines the data you want to collect. You collect this data by running a trace on the events defined in the template. While the trace is running, the event classes and data columns that describe the event data are displayed in SQL Profiler.

Template

A template defines the criteria for each event you want to monitor with SQL Profiler. For example, you can create a template, specifying which events, data columns, and filters to use. Then you can save the template and launch a trace with the current template settings. The trace data captured is based upon the options specified in the template. A template is not executed, and must be saved to a file with the .tdf extension.

Trace

A trace captures data based upon the selected events, data columns, and filters. For example, you can create a template to monitor exception errors. To do this, you would select to trace the Exception event class, and the Error, State, and Severity data columns, which need to be collected for the trace results to provide meaningful data. After you save the template, you can then run it as a trace, and collect data on any Exception events that occur in the server. This trace data can be saved and then replayed at a later date, or used immediately for analysis.

Filter

Event Category

An event category defines the way events are grouped. For example, all lock events classes are grouped within the Locks event category. However, event categories only exist within SQL Profiler. This term does not reflect the way engine events are grouped.

Event

An event is an action generated within the Microsoft SQL Server™ engine. For example:

All of the data that is generated as a result of an event is displayed in the trace in a single row. This row contains columns of data called event classes that describe the event in detail.

Event Class

An event class is the column that describes the event that was produced by the server. The event class determines the type of data collected, and not all data columns are applicable to all event classes. Examples of event classes include:

Data Column

The data columns describe the data collected for each of the event classes captured in the trace. Because the event class determines the type of data collected, 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 Integer Data event class. Default data columns are populated automatically for all event classes.

 

SQL Profiler Scenarios

Typically, you use SQL Profiler to:

For example, you can create a trace that captures events relating to TSQL and Stored Procedure event classes, specifically RPC:Completed and SQL:BatchCompleted. Include all data columns in the trace, group by Duration, and specify event criteria. For example, if you specify that the Duration of the event must be at least 1,000 milliseconds, you can eliminate short-running events from the trace. The Duration minimum value can be increased as required. If you want to monitor only one database at a time, specify a value for the Database ID event criteria.

For example, you can create a trace that captures events relating to TSQL and Stored Procedure event classes (RPC:Starting and SQL:BatchStarting) and Locks event classes (Lock:Deadlock and Lock:Deadlock Chain). Include all data columns in the trace and group by Event Class. If you want to monitor only one database at a time, specify a value for the Database ID event criteria.

To view the connections involved in a deadlock, do one of the following:

For example, you can create a trace that captures events relating to Stored Procedures event classes (SP:Completed, SP:Starting, SP:StmtCompleted and SP:StmtStarting), and TSQL event classes (SQL:BatchStarting and SQL:BatchCompleted). Include all data columns in the trace and group by ClientProcessID. If you want to monitor only one database at a time, specify a value for the Database ID event criteria. Similarly, if you want to monitor only one stored procedure at a time, specify a value for the Object ID event criteria.

You can audit activity in SQL Server using SQL Profiler. For example, if the security administrator always needs to know who is logged in to the server, you can create a SQL Profiler trace that provides a complete view of users who have logged in or out of the server. This information can then be used for legal purposes to document activity and for technical purposes to track security policy violations.

To set up a SQL Profiler trace that tracks users who have logged in or out of the server, do the following:

  1. Create a trace, selecting Audit Login Event.
  2. To return the appropriate information, specify the following data columns:

EventClass (selected by default)

EventSubClass

LoginSID

LoginName

You can create a trace that captures events relating to the Sessions event class, ExistingConnection, and TSQL event classes. Include all data columns in the trace, do not specify any event criteria, and group the captured events by DBUserName.

Monitoring with SQL Profiler Event Categories

In SQL Profiler, use event categories to monitor events in Microsoft® SQL Server™. Event categories contain event classes that have been grouped together within the SQL Profiler user interface. The following table describes the SQL Profiler event categories and their associated event classes.

Event category

Description

Cursors

Collection of event classes produced by cursor operations.

Database

Collection of event classes produced when data or log files grow or shrink automatically.

Errors and Warnings

Collection of event classes 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 event classes produced when a lock is acquired, cancelled, released, etc.

Objects

Collection of event classes produced when database objects are created, opened, closed, dropped, or deleted.

Performance

Collection of event classes produced when SQL data manipulation (DML) operators execute.

Scans

Collection tables and indexes are scanned.

Security Audit

Collection of event classes used to audit server activity.

Sessions

Collection of event classes produced by clients connecting to and disconnecting from an instance of SQL Server.

Stored Procedures

Collection of event classes produced by the execution of stored procedures.

Transactions

Collection of event classes produced by the execution of Microsoft Distributed Transaction Coordinator (MS DTC) transactions or by writing to the transaction log.

TSQL

Collection of event classes produced by the execution of Transact-SQL statements passed to an instance of SQL Server from the client.

User Configurable

Collection of user-configurable event classes.

 

Monitoring with System Monitor

If you are running the Microsoft® Windows® 2000 operating system, use System Monitor (Performance Monitor in Microsoft Windows NT® 4.0) to measure the performance of Microsoft SQL Server™. You can view SQL Server objects and performance counters as well as the behavior of other objects, such as processors, memory, cache, threads, and processes. Each of these objects has an associated set of counters that measure device usage, queue lengths, delays, and other indicators of throughput and internal congestion.

System Monitor makes it possible to obtain up-to-the-second SQL Server activity and performance statistics. With this graphical tool, you can:

Note  You can use either the System Monitor or Performance Monitor to do these tasks.

For information about Windows NT 4.0 and Windows 2000 objects and counters, see the Windows NT 4.0 and Windows 2000 documentation.

 

Monitoring Disk Activity

Microsoft® SQL Server™ uses Microsoft Windows NT® 4.0 or Windows® 2000 I/O calls to perform disk reads and writes. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. The I/O subsystem includes the system bus, disk controller cards, disks, tape drives, CD-ROM drive, and many other I/O devices. Disk I/O is frequently the cause of bottlenecks in a system.

Monitoring Disk I/O and Detecting Excess Paging

Two of the counters that can be monitored to determine disk activity include:

In System Monitor (Performance Monitor in Windows NT 4.0), the PhysicalDisk: % Disk Time counter monitors the percentage of time that the disk is busy with read/write activity. If the PhysicalDisk: % Disk Time counter is high (more than 90 percent), check the Physical Disk: Current Disk Queue Length counter to see how many system requests are waiting for disk access. The number of waiting I/O requests should be sustained at no more than 1.5 to 2 times the number of spindles making up the physical disk. Most disks have one spindle, although redundant array of inexpensive disks (RAID) devices usually have more. A hardware RAID device appears as one physical disk in System Monitor; RAID devices created through software appear as multiple instances.

Use the values of the Current Disk Queue Length and % Disk Time counters to detect bottlenecks within the disk subsystem. If Current Disk Queue Length and % Disk Time counter values are consistently high, consider:

If you are using a RAID device, the % Disk Time counter can indicate a value greater than 100 percent. If it does, use the PhysicalDisk: Avg. Disk Queue Length counter to determine how many system requests, on average, are waiting for disk access.

Applications and systems that are I/O-bound may keep the disk constantly active.

Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. In Windows NT 4.0 or Windows 2000, paging is caused by:

If you have more than one logical partition on the same hard disk, use the Logical Disk counters instead of the Physical Disk counters. Looking at the logical disk counters will help you determine which files are heavily accessed. After you have found the disks with high levels of read/write activity, look at the read-specific and write-specific counters (for example, Logical Disk: Disk Write Bytes/sec) for the type of disk activity that is causing the load on each logical volume.

Isolating Disk Activity Created by SQL Server

To determine the amount of I/O generated by SQL Server components, examine the following performance areas:

The number of page reads and writes that SQL Server performs can be monitored using the SQL Server: Buffer Manager Page Reads/sec and Page Writes/sec counters. If these values start to approach the capacity of the hardware I/O subsystem, try to reduce the values by tuning your application or database to reduce I/O operations (such as index coverage, better indexes, or normalization), increasing the I/O capacity of the hardware, or by adding memory.

 

Monitoring CPU Usage

Monitor an instance of Microsoft® SQL Server™ periodically to determine if CPU usage rates are within normal ranges. A continually high CPU usage rate may indicate the need for a CPU upgrade or the addition of multiple processors. Alternately, a high CPU usage rate may indicate a poorly tuned or designed application. Optimizing the application can lower CPU utilization.

A good way to determine this is to use the Processor:% Processor Time counter in System Monitor (Performance Monitor in Microsoft Windows NT® 4.0). This counter monitors the amount of time the CPU spends processing a nonidle thread. A consistent state of 80 to 90 percent may indicate the need for a CPU upgrade or the addition of more processors. For multiprocessor systems, a separate instance of this counter should be monitored for each processor. This value represents the sum of processor time on a specific processor. To determine the average for all processors, use the System: %Total Processor Time counter instead.

Optionally, you can also monitor:

This counter corresponds to the percentage of time the processor is spending executing Windows NT 4.0 or Microsoft Windows® 2000 kernel commands such as processing SQL Server I/O requests. If this counter is consistently high when the Physical Disk counters is high, consider a faster or more efficient disk subsystem.

Note  Different disk controllers and drivers use different amounts of kernel processing time. Efficient controllers and drivers use less privileged time, leaving more processing time available for user applications, increasing overall throughput.

This counter corresponds to the percentage of time the processor is spending executing user processes such as SQL Server.

This counter corresponds to the number of threads waiting for processor time. A processor bottleneck develops when threads of a process require more processor cycles than are available. If more than a few processes are trying to utilize the processor's time, you might need to install a faster processor or an additional processor if you are using a multiprocessor system.

When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor's time can be used. If this causes the performance of other applications to suffer, try changing the workload (for example, by dedicating the computer to running the instance of SQL Server).

Values around 100 percent, where many client requests are executing, may indicate that processes are queuing up, waiting for processor time, and causing a bottleneck. Resolve the problem by adding more powerful processors.

 

Monitoring Memory Usage

Monitor an instance of Microsoft® SQL Server™ periodically to confirm that memory usage is within typical ranges and that no processes, including SQL Server, are lacking or consuming too much memory.

To monitor for a low-memory condition, start with the following object counters:

The Available Bytes counter indicates how many bytes of memory are currently available for use by processes. The Pages/sec counter indicates the number of pages that either were retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults.

Low values for the Available Bytes counter can indicate that there is an overall shortage of memory on the computer or that an application is not releasing memory. A high rate for the Pages/sec counter could indicate excessive paging. Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging.

A low rate of paging (and hence page faults) is typical, even if the computer has plenty of available memory. The Microsoft Windows NT® Virtual Memory Manager (VMM) steals pages from SQL Server and other processes as it trims the working-set sizes of those processes, causing page faults. To determine whether SQL Server rather than another process is causing excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance.

For more information about resolving excessive paging, see the Windows NT 4.0 or Microsoft Windows® 2000 documentation.

Isolating Memory Used by SQL Server

By default, SQL Server changes its memory requirements dynamically, based on available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and uses the available memory. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system. However, the option to dynamically use memory can be overridden using the min server memory, max server memory, and set working set size server configuration options. To monitor the amount of memory being used by SQL Server, examine the following performance counters:

The Working Set counter shows the amount of memory used by a process. If this number is consistently below the amount of memory SQL Server is configured to use (set by the min server memory and max server memory server options), SQL Server is configured for more memory than it needs. Otherwise, fix the size of the working set using the set working set size server option. The Buffer Cache Hit Ratio counter is application specific; however, a rate of 90 percent or higher is desirable. Add more memory until the value is consistently greater than 90 percent, indicating that more than 90 percent of all requests for data were satisfied from the data cache.

If the Total Server Memory (KB) counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.

System Monitor Scenarios

When monitoring Microsoft® SQL Server™ and the operating system to investigate performance-related issues, there are three main areas on which to concentrate your initial efforts:

It can be useful to monitor Microsoft Windows NT® 4.0 or Microsoft Windows® 2000 and SQL Server counters at the same time to determine any correlation between the performance of SQL Server and Windows NT 4.0 or Windows 2000. For example, monitoring the Windows NT 4.0 or Windows 2000 disk I/O counters and the SQL Server Buffer Manager counters at the same time can show how the whole system is behaving.

Monitoring a computer using System Monitor (Performance Monitor in Windows NT 4.0) can slightly impact the performance of the computer. Therefore, either log the System Monitor data to another disk (or computer) so that it reduces the effect on the computer being monitored, or run System Monitor remotely. Monitor only the counters in which you are interested. Monitoring too many counters adds overhead to the monitoring process and will impact the computer being monitored, possibly affecting the results.

Running System Monitor

System Monitor (Performance Monitor in Microsoft® Windows NT® 4.0) collects information from Microsoft SQL Server™ using remote procedure calls (RPC). Any user who has Microsoft Windows® 2000 permissions to run System Monitor can use it to monitor SQL Server.

Note  When using either System Monitor or Performance Monitor, you cannot connect to an instance of SQL Server running on Microsoft Windows 98.

As with all performance monitoring tools, expect some performance overhead when monitoring SQL Server with System Monitor. The actual overhead in any specific instance will depend on the hardware platform, the number of counters, and the selected update interval. However, the integration of System Monitor with SQL Server is designed to minimize the impact.

 

Creating Charts, Alerts, Logs, and Reports

System Monitor (Performance Monitor in Microsoft® Windows NT® 4.0) allows you to create charts, alerts, logs, and reports to monitor an instance of Microsoft SQL Server™.

Charts

Charts can monitor the current performance of selected objects and counters (for example, the CPU usage or disk I/O). You can add to a chart various combinations of System Monitor objects and counters, as well as Windows NT 4.0 or Microsoft Windows® 2000 objects and counters.

Each chart represents a subset of information you want to monitor. For example, one chart can track memory usage statistics and a second chart can track disk I/O statistics.

Using a chart can be useful for:

Charts are useful for short-term, real-time monitoring of a local or remote computer (for example, when you want to monitor an event as it occurs).

Alerts

Using alerts, System Monitor can track specific events and notify you of these events as requested. An alert log can monitor the current performance of selected counters and instances for objects in SQL Server. When a counter exceeds a given value, the log records the date and time of the event. An event can also generate a network alert. You can have a specified program run the first time or every time an event occurs. For example, an alert can send a network message to all system administrators that the instance of SQL Server is getting low on disk space.

Logs

Logs allow you to record information on the current activity of selected objects and computers for later viewing and analysis. You can collect data from multiple systems into a single log file. For example, you can create various logs to accumulate information on the performance of selected objects on various computers for future analysis. You can save these selections under a file name and reuse them when you want to create another log of similar information for comparison.

Log files provide a wealth of information for troubleshooting or planning. Whereas charts, alerts, and reports on current activity provide instant feedback, log files enable you to track counters over a long period of time, thereby allowing you to examine information more thoroughly and to document system performance.

Reports

Reports allow you to display constantly changing counter and instance values for selected objects. Values appear in columns for each instance. You can adjust report intervals, print snapshots, and export data. Use reports when you need to display the raw numbers.

For more information about charts, alerts, logs, and reports, or about Windows NT 4.0 or Windows 2000 objects and counters, see the Windows 4.0 or Windows 2000 documentation.

 

Using SQL Server Objects

Microsoft® SQL Server™ provides objects and counters that can be used by System Monitor (Performance Monitor in Microsoft Windows NT® 4.0) to monitor activity in computers running an instance of SQL Server. An object is any Windows NT 4.0, Microsoft Windows® 2000 or SQL Server resource, such as a SQL Server lock or Windows NT 4.0 or Windows 2000 process. Each object contains one or more counters that determine various aspects of the objects to monitor. For example, the SQL Server Locks object contains counters called Number of Deadlocks/sec or Lock Timeouts/sec.

Some objects have several instances if multiple resources of a given type exist on the computer. For example, the Processor object type will have multiple instances if a system has multiple processors. The Databases object type has one instance for each database on SQL Server. Some object types (for example, the Memory Manager object) have only one instance. If an object type has multiple instances, you can add counters to track statistics for each instance, or in many cases, all instances at once.

Note  Performance condition alerts are only available for the first 99 databases. Any databases created after the first 99 databases will not be included in the sysperfinfo system table, and using the sp_add_alert procedure will return an error.

By adding or removing counters to the chart and saving the chart settings, you can specify the SQL Server objects and counters monitored when System Monitor is started.

SQL Server object

Counter

SQL Server: Buffer Manager

Buffer Cache Hit Ratio

SQL Server: General Statistics

User Connections

SQL Server: Memory Manager

Total Server Memory (KB)

SQL Server: SQL Statistics

SQL Compilations/sec

SQL Server: Buffer Manager

Page Reads/sec

SQL Server: Buffer Manager

Page Writes/sec

 

You can configure System Monitor to display statistics from any SQL Server counter. In addition, you can set a threshold value for any SQL Server counter and then generate an alert when a counter exceeds a threshold.

Note  SQL Server statistics are displayed only when an instance of SQL Server is running. If you stop and restart an instance of SQL Server, the display of statistics is interrupted and then resumed automatically.

These are the SQL Server objects.

SQL Server object

Description

SQL Server: Access Methods

Searches through and measures allocation of SQL Server database objects (for example, the number of index searches or number of pages that are allocated to indexes and data).

SQL Server: Backup Device

Provides information about backup devices used by backup and restore operations, such as the throughput of the backup device.

SQL Server: Buffer Manager

Provides information about the memory buffers used by SQL Server, such as free memory and buffer cache hit ratio.

SQL Server: Cache Manager

Provides information about the SQL Server cache used to store objects such as stored procedures, triggers, and query plans.

SQL Server: Databases

Provides information about a SQL Server database, such as the amount of free log space available or the number of active transactions in the database. There can be multiple instances of this object.

SQL Server: General Statistics

Provides information about general server-wide activity, such as the number of users who are connected to an instance of SQL Server.

SQL Server: Latches

Provides information about the latches on internal resources, such as database pages, that are used by SQL Server.

SQL Server: Locks

Provides information about the individual lock requests made by SQL Server, such as lock time-outs and deadlocks. There can be multiple instances of this object.

SQL Server: Memory Manager

Provides information about SQL Server memory usage, such as the total number of lock structures currently allocated.

SQL Server: Replication Agents

Provides information about the SQL Server replication agents currently running.

SQL Server: Replication Dist.

Measures the number of commands and transactions read from the distribution database and delivered to the Subscriber databases by the Distribution Agent.

SQL Server: Replication Logreader

Measures the number of commands and transactions read from the published databases and delivered to the distribution database by the Log Reader Agent.

SQL Server: Replication Merge

Provides information about SQL Server merge replication, such as errors generated or the number of replicated rows that are merged from the Subscriber to the Publisher.

SQL Server: Replication Snapshot

Provides information about SQL Server snapshot replication, such as the number of rows that are bulk copied from the publishing database.

SQL Server: SQL Statistics

Provides information about aspects of SQL queries, such as the number of batches of Transact-SQL statements received by SQL Server.

SQL Server: User Settable Object

Performs custom monitoring. Each counter can be a custom stored procedure or any Transact-SQL statement that returns a value to be monitored.


Monitoring with SQL Server Enterprise Manager

Use SQL Server Enterprise Manager to view the following information about current Microsoft® SQL Server™ activity:

If you are a system administrator, you can view additional information about a selected process, send a message to a user who is connected currently to an instance of SQL Server, or terminate a selected process.

Use the current activity window in SQL Server Enterprise Manager to perform ad hoc monitoring of an instance of SQL Server. This allows you to determine, at a glance, the volume and general types of activity on the system, for example:

SQL Server activity can be monitored using the sp_who and sp_lock system stored procedures.

Here are icons and descriptions of the icons in the current activity window.

 

Here are descriptions of the process information in the Current Activity window.

Item

Description

Process ID

SQL Server Process ID.

Context ID

Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.

User

ID of the user who executed the command.

Database

Database currently being used by the process.

Status

Status of the process (for example, running, sleeping, runnable, and background).

Open Transactions

Number of open transactions for the process.

Command

Command currently being executed.

Application

Name of the application program being used by the process.

Wait Time

Current wait time in milliseconds. When the process is not waiting, the wait time is zero.

Wait Type

Indicates the name of the last or current wait type.

Wait Resources

Textual representation of a lock resource.

CPU

Cumulative CPU time for the process. The entry is updated only for processes performed on behalf of Transact-SQL statements executed when SET STATISTICS TIME ON has been activated in the same session. The CPU column is updated when a query has been executed with SET STATISTICS TIME ON. When zero is returned, SET STATISTICS TIME is OFF.

Physical IO

Cumulative disk reads and writes for the process.

Memory Usage

Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.

Login Time

Time at which a client process logged into the server. For system processes, the time at which SQL Server startup occurred is displayed.

Last Batch

Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the time at which SQL Server startup occurred is displayed.

Host

Name of the workstation.

Network Library

Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that allows them to make the connection.

Network Address

Assigned unique identifier for the network interface card on each user's workstation. When the user logs in, this identifier is inserted in the Network Address column.

Blocked By

Process ID (SPID) of a blocking process.

Blocking

Process ID (SPID) of processes that are blocked.

 

Here are descriptions of the lock information in the Current Activity window.

Item

Type

Description

spid

spid

Server process ID of the current user process.

ecid

ecid

Execution context ID. Represents the ID of a given thread associated with a specific spid.

Lock type

RID

Row identifier. Used to lock a single row individually within a table.

 

KEY

Key; a row lock within an index. Used to protect key ranges in serializable transactions.

 

PAG

Data or index page.

 

EXT

Contiguous group of eight data pages or index pages.

 

TAB

Entire table, including all data and indexes.

 

DB

Database.

Lock mode

Shared (S)

Used for operations that do not change or update data (read-only operations), such as a SELECT statement.

 

Update (U)

Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and then potentially updating resources later.

 

Exclusive (X)

Used for data modification operations, such as UPDATE, INSERT, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

 

Intent

Used to establish a lock hierarchy.

 

Schema

Used when an operation dependent on the schema of a table is executing. There are two types of schema locks: schema stability (Sch-S) and schema modification (Sch-M).

 

Bulk update (BU)

Used when bulk copying data into a table and the TABLOCK hint is specified.

 

RangeS_S

Shared range, shared resource lock; serializable range scan.

 

RangeS_U

Shared range, update resource lock; serializable update scan.

 

RangeI_N

Insert range, null resource lock. Used to test ranges before inserting a new key into an index.

 

RangeX_X

Exclusive range, exclusive resource lock. Used when updating a key in a range.

Status

GRANT

Lock was obtained.

 

WAIT

Lock is blocked by another process.

 

CNVT

Lock is being converted to another lock. A lock being converted to another lock is held in one mode but is waiting to acquire a stronger lock mode (for example, update to exclusive). When diagnosing blocking issues, a CNVT can be considered similar to a WAIT.

Owner

Owner

The lock owner: xact (transaction), sess (session), or curs (cursor).

Index

Index

The index associated with the resource. If the index is clustered, you see the table name instead.

Resource

RID

Row identifier of the locked row within the table. The row is identified by a fileid:page:rid combination, where rid is the row identifier on the page.

 

KEY

Hexadecimal number used internally by SQL Server.

 

PAG

Page number. The page is identified by a fileid:page combination, where fileid is the fileid in the sysfiles table, and page is the logical page number within that file.

 

EXT

First page number in the extent being locked. The page is identified by a fileid:page combination.

 

TAB

No information is provided because the ObjId column already contains the object ID of the table.

 

DB

No information is provided because the dbid column already contains the database ID of the database.


sp_who

Provides information about current Microsoft® SQL Server™ users and processes. The information returned can be filtered to return only those processes that are not idle.

Syntax

sp_who [[@login_name =] 'login']

Arguments

[@login_name =] 'login'

Is a user login name on SQL Server. login is sysname, with a default of NULL. If no name is specified, the procedure reports all active users of SQL Server. login can also be a specific process identification number (SPID). To return information on active processes, specify ACTIVE. ACTIVE excludes from the report processes that are waiting for the next command from the user.

Return Code Values

0 (success) or 1 (failure)

Result Sets

sp_who returns a result set with the following information.

Column

Data type

Description

spid

smallint

The system process ID.

ecid

smallint

The execution context ID of a given thread associated with a specific SPID.

ECID = {0, 1, 2, 3, ...n}, where 0 always represents the main or parent thread, and {1, 2, 3, ...n} represent the sub-threads.

status

nchar(30)

The process status.

loginame

nchar(128)

The login name associated with the particular process.

hostname

nchar(128)

The host or computer name for each process.

blk

char(5)

The system process ID for the blocking process, if one exists. Otherwise, this column is zero.

When a transaction associated with a given spid is blocked by an orphan distributed transaction, this column will return a '-2' for the blocking orphan transaction.

dbname

nchar(128)

The database used by the process.

cmd

nchar(16)

The SQL Server command (Transact-SQL statement, SQL Server internal engine process, and so on) executing for the process.

 

The sp_who result set will be sorted in ascending order according to the spid values. In case of parallel processing, sub-threads are created for the specific spid. The main thread is indicated as spid =xxx and ecid =0. The other sub-threads have the same spid = xxx, but with ecid > 0. Thus, multiple rows for that spid number will be returned -- grouped together within that spid's placement in the overall list. The sub-threads will be listed in random order, except for the parent thread (ecid = 0), which will be listed first for that spid.

Remarks

A blocking process (which may have an exclusive lock) is one that is holding resources that another process needs.

In SQL Server 2000, all orphaned DTC transactions are assigned the SPID value of '-2'. Orphaned DTC transactions are distributed transactions that are not associated with any SPID. Thus, when an orphaned transaction is blocking another process, this orphaned distributed transaction can be identified by its distinctive '-2' SPID value. For more information, see KILL.

SQL Server 2000 reserves SPID values from 1 through 50 for internal use, while SPID values 51 or higher represent user sessions.

Permissions

Execute permissions default to the public role.

Examples
A. List all current processes

This example uses sp_who without parameters to report all current users. 

USE master
EXEC sp_who

Here is the result set:

spid ecid status     loginame       hostname  blk dbname cmd                  
---- ---- ------     ------------   --------  --- ------ -----                
1    0    background sa                       0   pubs   LAZY WRITER          
2    0    sleeping   sa                       0   pubs   LOG WRITER           
3    0    background sa                       0   master SIGNAL HANDLER       
4    0    background sa                       0   pubs   RA MANAGER           
5    0    background sa                       0   master TASK MANAGER         
6    0    sleeping   sa                       0   pubs   CHECKPOINT SLEEP     
7    0    background sa                       0   master TASK MANAGER         
8    0    background sa                       0   master TASK MANAGER         
9    0    background sa                       0   master TASK MANAGER         
10   0    background sa                       0   master TASK MANAGER         
11   0    background sa                       0   master TASK MANAGER         
51   0    runnable   DOMAIN\loginX  serverX   0   Nwind  BACKUP DATABASE      
51   2    runnable   DOMAIN\loginX  serverX   0   Nwind  BACKUP DATABASE      
51   1    runnable   DOMAIN\loginX  serverX   0   Nwind  BACKUP DATABASE      
52   0    sleeping   DOMAIN\loginX  serverX   0   master AWAITING COMMAND     
53   0    runnable   DOMAIN\loginX  serverX   0   pubs   SELECT               
(16 row(s) affected)
B. List a specific user's process

This example shows how to view information about a single current user by login name.

USE master
EXEC sp_who 'janetl'
C. Display all active processes
USE master
EXEC sp_who 'active'
D. Display a specific process with process ID
USE master
EXEC sp_who '10' --specifies the process_id

 

Monitoring the Error Logs

Microsoft® SQL Server™ logs events (although only certain system events and user-defined events) to the SQL Server error log and the Microsoft Windows® application log. Use the information in the error log to troubleshoot problems related to SQL Server.

The Windows application logs provide an overall picture of events that occur on the Windows NT® 4.0 and Windows 2000 systems, as well as events in SQL Server and SQL Server Agent. Use Event Viewer to view the Windows application log and to filter the information. For example, you can filter events, such as information, warning, error, success audit, and failure audit.

Both logs automatically timestamp all recorded events.

Comparing Error and Application Log Output

You can use both the SQL Server error log and the Windows application log to identify the cause of problems. For example, while monitoring the SQL Server error log, you may detect a certain set of messages for which you do not know the cause. By comparing the dates and times for events between these logs, you can narrow the list of probable causes.

Viewing the SQL Server Error Log

View the Microsoft® SQL Server™ error log to ensure that processes have completed successfully (for example, backup and restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, and so on.

View the SQL Server error log by using SQL Server Enterprise Manager or any text editor. By default, the error log is located at Program Files\Microsoft SQL Server\Mssql\Log\Errorlog.

A new error log is created each time an instance of SQL Server is started, although the sp_cycle_errorlog system stored procedure can be used to cycle the error log files without having to restart the instance of SQL Server. Typically, SQL Server retains backups of the previous six logs and gives the most recent log backup the extension .1, the second most recent the extension .2, and so on. The current error log has no extension.

 

Viewing the Windows Application Log

When Microsoft® SQL Server™ is configured to use the Microsoft Windows® application log, each SQL Server session writes new events to that log. Unlike the SQL Server error log, a new application log is not created each time you start an instance of SQL Server.

View and manage the Windows application log by using Event Viewer in Microsoft Windows NT® 4.0 or Windows 2000.

There are three logs that can be viewed with Event Viewer.

Windows log type

Description

System log

Records events logged by the Windows NT 4.0 or Windows 2000 system components. For example, the failure of a driver or other system component to load during startup is recorded in the system log.

Security log

Records security events, such as failed login attempts. This helps track changes to the security system and identify possible breaches to security. For example, attempts to log on to the system may be recorded in the security log, depending on the audit settings in the User Manager.

Only members of the sysadmin fixed server role can view the security log.

Application log

Records events that are logged by applications. For example, a database application might record a file error in the application log.

 

For more information about using Event Viewer, managing the application log, and understanding the information it presents, see the Windows NT 4.0 or Windows 2000 documentation.

To view the Windows application log

Monitoring with Transact-SQL Statements

Microsoft® SQL Server™ provides several Transact-SQL statements and system stored procedures that allow ad hoc monitoring of an instance of SQL Server. Use these statements when you want to gather, at a glance, information about server performance and activity. For example:

Most of this information can also be monitored using SQL Server Enterprise Manager, SQL-DMO, or System Monitor (Performance Monitor in Microsoft Windows NT® 4.0).

 

1