Lesson 2: Choosing Among Monitoring Tools

3 4

SQL Server 2000 and Windows 2000 provide a number of tools the database administrator can use to monitor SQL Server performance and activity. In this lesson, you are introduced to each of the tools and its use. The two primary tools are Windows 2000 System Monitor, which is used to monitor overall system resource use, and SQL Profiler, which is used to monitor selected details regarding selected SQL Server events.

After this lesson, you will be able to

  • Describe and use each SQL Server 2000 monitoring tool
  • Determine the appropriate tools for each monitoring task

Estimated lesson time: 15 minutes

Using System Monitor

Windows 2000 System Monitor (called Performance Monitor in Windows NT 4.0) is used to monitor resource usage on either the local computer or a remote computer. Use System Monitor to detect inadequate resources or resource use levels that warrant additional investigation, such as an excessive number of blocking locks or a significant increase in memory usage. Because System Monitor can impact performance, administrators frequently perform monitoring from a remote computer. If you do run System Monitor from the local computer, you can log the System Monitor data to another disk or computer to reduce impact on performance.

System Monitor is generally used either to view real-time performance data or to record data to disk for later review and analysis. System Monitor includes performance objects, counters, and instances.

  • Performance objects generally correspond to hardware resources (such as memory, physical disk, or processor) or Windows services (such as server work queue or print queue).
  • Counters are data items regarding aspects of each performance object (such as pages/sec for the memory performance object or writes/sec for the physical disk performance object).
  • Instances are multiple performance objects of the same type (such as for multiple processors or hard disks).

System Monitor is extensible, enabling server applications (such as SQL Server 2000) to add performance objects, counters, and instances specific to the particular server application. Table 14.1 describes the performance objects (other than replication performance objects) added to System Monitor by SQL Server 2000 to track activity that is specific to SQL Server.

Table 14.1 Performance Objects for Tracking SQL Server Activity

SQL Server Performance Object

This Counter Measures. . .

Used to Monitor. . .

SQL Server: Access Methods

Access to and allocation of logical SQL Server database objects (such as data and index pages).

Index and query efficiency based on types of pages accessed, page splits, and page allocations.

SQL Server: Backup Device

Backup and restore performance information on a per-device basis.

Throughput or progress of backup and restore operations on a per-device basis.

SQL Server: Buffer Manager

Memory buffer use, including free buffer pages and buffer cache hit ratio.

Lack of physical memory, frequency of disk reads, and efficiency of query performance.

SQL Server: Cache Manager

Memory used for caching stored procedures, Transact-SQL statements, and triggers.

Efficiency of plan caching and reuse.

SQL Server: Databases

Database activity, including active transactions, bulk copy throughput, backup and restore throughput, and transaction log activities.

Level of user activity in a database, autogrowth and autoshrink operations, fullness of the transaction log, and performance levels for bulk copy, backup, and restore operations.

SQL Server: General Statistics

General server-wide activity, including user connections and logins.

Overall connection activity.

SQL Server: Latches

Internal SQL Server resource locks (called latches).

Performance bottlenecks based on the number and length of waits for internal resource locks to be granted.

SQL Server: Locks

Individual lock requests made by SQL Server, including number of lock timeouts and number of deadlocks.

Overall number and types of locks. Minimizing locks improves concurrency and performance.

SQL Server: Memory Manager

Overall memory usage, including memory used for connections and locks, available memory, and granted memory.

Overall memory usage for various objects, to determine whether a memory shortage exists.

SQL Server: SQL Statistics

Transact-SQL queries, including T-SQL compilations, T-SQL recompilations, and number of batches received.

Query compilation speed and overall efficiency of the query optimizer. Minimizing compilation time and re-compilation frequency improves performance.

SQL Server: User Settable Object

Custom counters based on stored procedures or Transact-SQL statements.

Custom information, such as product inventory or number of orders.

Using Task Manager

Windows 2000 (and Windows NT 4.0) Task Manager is used to provide a snapshot in real time of the amount of memory and processor resources used by each process and application running on a computer. This snapshot shows the relative server loads of competing server applications on the same computer. It also provides an overview of the total level of memory and processor usage on the computer. You can use this overview to quickly determine whether inadequate hardware resources are a problem.


Task Manager is not available with Windows Me and Windows 98.

Using SQL Profiler

SQL Profiler is a graphical SQL Server 2000 tool used to monitor (trace) selected SQL Server events, and save the information to a table or file with a .TRC filename extension for later analysis. For example, you can monitor slowly executing stored procedures or events immediately preceding deadlocks. You can create traces and then replay them (in real time or step by step) on another computer running SQL Server (a test server) to debug performance and coding problems with Transact-SQL statements or stored procedures.


Certain data columns are required to replay a trace. Use the SQLProfilerTSQL_Replay preconfigured trace template as a starting point for capturing selected data for replay.

A SQL Server event is any action generated within the SQL Server engine. Events include logins, Transact-SQL statements, stored procedures, batches, errors, cursors, and security permission checks. For each event, you can choose to monitor selected information, including computer name, object affected (such as table name), user name, text of the Transact-SQL statement or stored procedure, and time started and stopped. This trace definition information is stored in a template with a .TDF filename extension that defines the information that will be captured into a file or table. The result of this capture of information is called a trace. Using the same definitions over time is useful for detecting performance and usage trends.

You should take care to avoid monitoring too many events, which can affect SQL Server performance. The default maximum size of a trace file is 5 MB. By default, SQL Profiler creates a new trace file when the current trace file reaches the maximum size. The new trace filename is the original .TRC filename with a number appended to it. Limit trace size by limiting the type of events and data collected. Filters such as like and not like, equals and not equals, and greater than or equal and less than or equal should also be used to limit event data (such as by database, application, or user).

Events are grouped into event categories. Within each event category are event classes for capturing data about selected SQL Server events. Table 14.2 describes the event categories that can be monitored.

Table 14.2 Event Categories That Can Be Monitored with SQL Profiler

Event Category

Event Classes in This Event Category Monitor...

Used to Monitor. . .


Cursor creation, use, and deletion events.

The actual types of cursors being used, which is not necessarily the type specified by the calling application.


Automatic data and transaction log file growth and shrinkage events.

Automatic growth of data and transaction log files, to properly size these files for maximum performance.

Errors and Warnings

Error and warning events, such as stored procedure compilation errors or missing column statistics warnings.

The length of waits for resources, which can indicate contention issues. Also, the efficiency of query optimizer execution plans.


Lock acquired, canceled, escalated, and released events.

Contention issues based on type and length of locks. Also, deadlocks and timeout events. Can generate large files.


Object creating, opening, closing, dropping, and deleting events.

Ad hoc creation of objects by applications and users. Can generate particularly large files.


Query optimizer showplan information and the execution of SQL data manipulation language (DML) operators.

Query execution and query optimizer efficiency by capturing the plan tree, the query plan cost estimates, the query execution statistics, and the query plan tree.


Tables or indexes scanned.

Types of scans being performed on an object.

Security audit

Audit events.

Logins, logouts, security and permission changes, password changes, and backup and restore events.


Memory change events.

Changes in SQL Server memory usage greater than 1 MB or 5% of the maximum server memory, whichever is greater.


Length of time per user connection and amount of SQL Server processor time used by queries submitted using each connection.

Connected users, databaseactivity, and CPU time used,for charging for usage and activity

Stored procedures

Stored procedure execution information, including cache hits and misses, order of execution, when aged out of cache, and when recompiled.

Memory to determine additional memory needs. Also, use of stored procedures by applications.


Transaction execution information.

Types of logging activity by applications. Also, transaction commits and rollbacks, and distributed transactions.


Execution of SQL Server statements and batch events.

Accuracy of application results compared to expected results during application testing. Also, events that take a long time to run, including the users who submit these queries.

User configurable

Custom events.

User-defined events, such as application progress reports at specified points during application testing.

With SQL Profiler, you can use the preconfigured trace definitions (called templates) either as is or as a basis for custom templates. These templates define the types of event information that SQL Profiler will trace and capture. Table 14.3 describes the preconfigured trace templates that ship with SQL Server 2000.


SQL Profiler supports C2-level security auditing for C2-certified systems (C2 is a government-defined security level). To enable C2 Audit Mode, use the sp_configure system stored procedure. Refer to the topic "C2 Audit Mode Option" in SQL Server Books Online for further information.

Using SQL Query Analyzer

You can use SQL Query Analyzer to view and analyze the execution plan for a query. You use this plan to determine how efficiently queries are being processed and whether indexes are being used effectively. To view the execution plan for a query, enter the query in the query pane and then click Show Execution Plan on the Query menu. The estimated execution plan displays in the results pane. Refer to the topic "Graphically Displaying the Execution Plan Using SQL Query Analyzer" in SQL Server Books Online for assistance in interpreting the results displayed. The details of analyzing query plans are beyond the scope of this book.

Table 14.3 PreconFigured Trace Templates in SQL Profiler

Template Name

A Trace Using This Definition Captures. . .


The number of stored procedures that run; groups the results by stored procedure name and includes the number of times the procedure has executed.


General information regarding SQL batches and stored procedures executed and their connections, in execution order.


Each Transact-SQL statement issued in execution order including the time each statement was issued.


Each Transact-SQL statement issued; groups the results by duration (in milliseconds).


Each Transact-SQL statement issued; groups the results by user submitting them.


Details about each Transact-SQL statement issued, in sufficient detail to be used for replay in SQL Query Analyzer. Use this preconfigured template as a starting point for capturing data for replay.


Details in execution order about each stored procedure that executes, including the Transact-SQL commands within each stored procedure.


Duration information and binary data about each stored procedure issued and SQL batch executed. Binary data includes information such as session level settings, type of cursor issued, and lock type.

Using the SQL Server Enterprise Manager Current Activity Window

The SQL Server Enterprise Manager Current Activity window displays a snapshot of information regarding processes, user activity, locks held by processes, and locks held on objects. The current activity window is used to perform ad hoc monitoring to determine blocked and blocking transactions. As a system administrator, you can kill a selected process or send a message to the user who is executing a transaction that is causing a blocking lock or a deadlock. You can also view currently connected users and their last executed statement. Finally, you can view all locks currently in effect on the system based on the database object.

Using Transact-SQL

There are a number of types of Transact-SQL statements that you can issue to monitor SQL Server 2000 activity and performance. These statements can display either current resource information or performance over a period of time.

System Stored Procedures

The system stored procedures you can use to monitor SQL Server 2000 activity and performance are described in Table 14.4.

Table 14.4 System Stored Procedures for Monitoring Performance

System Stored Procedure

The Procedure Reports. . .

Used to Monitor. . .


Snapshot of current users and processes, including the currently executing command

Active users and their processes


Snapshot of current users and processes with additional columns (also more readable)

Active users and their processes


Snapshot of current locks

Blocking locks and deadlocks, and the process causing them


Estimate of current disk space reserved and used by a table or the entire database

Database or object space usage


Statistics, including CPU use, I/O use, and idle time since last execution of sp_monitor

Volume of work performed during period of time

In addition, all of the functions of SQL Profiler can be executed using stored procedures. These include the sp_trace_create, sp_trace_generateevent, sp_trace_setevent, sp_trace_setfilter, and sp_trace_setstatus system stored procedures.


Transact-SQL provides several types of Database Console Commands (DBCCs) for SQL Server 2000. You use DBCC commands to check physical and logical database consistency, as well as monitor SQL Server. Most inconsistencies detected can also be repaired by specifying the repair option. The DBCC statements most commonly used to check database consistency and monitor performance statistics are described in Table 14.5.

Built-in Functions

Transact-SQL provides a number of built-in functions (also called T-SQL globals) that keep track of specific information regarding SQL Server activity, such as performance statistics about activity since SQL Server was last started. This information is stored in predefined SQL Server counters and accessed using the SELECT statement. Table 14.6 describes the most commonly used T-SQL global counters.

Table 14.5 DBCC Statements Used for Monitoring

DBCC Statement

Statement Activity


Verifies that every data type in the syscolumns table also has an entry in the systypes table, and that every table and view in the sysobjects table has at least one column in the syscolumns table.


Checks the allocation and structural integrity of all objects in a specified database. Use the repair option to correct minor inconsistencies. Includes the functionality of the DBCC CHECKALLOC and DBCC CHECKTABLE statements.


Verifies foreign key and check constraints on a table.


Performs the same function as DBCC CHECKDB, but limited to a single specified filegroup and required tables.


Checks how many times more than five batches were executed concurrently on the SQL Server 2000 Personal Edition or the SQL Server 2000 Desktop Engine. Performance of the database engine is limited when users execute more than five batches concurrently on these editions.


Removes all clean buffers from the buffer pool. You use this statement to test queries with an empty buffer cache without shutting down and restarting the server.


Displays information regarding the oldest active transaction and oldest distributed and nondistributed replicated transactions within a specified database.


Displays information regarding the contents of the procedure cache, including number of stored procedures in cache, the number currently executing, and the size of the procedure cache.


Displays fragmentation information for the data and indexes of a table.


Displays the current distribution statistics for an index or statistics collection on a table.


Displays statistics about transaction log space size and percent used in all databases for a SQL Server instance.


Checks and corrects inaccuracies in space usage reports by the sp_spaceused system stored procedure for the sysindexes table.

Table 14.6 Commonly Used T-SQL Global Counters


Count Since SQL Server Started


Number of connections (including attempted connections)


Time in milliseconds that the processor has spent working


Time in milliseconds that SQL Server has been idle


Time in milliseconds that SQL Server has spent performing input and output operations


Number of input packets read from the network


Number of output packets written to the network


Number of network packet errors that have occurred on connections


Number of disk read/write errors encountered


Number of disk reads (not cache reads)


Number of disk writes

Trace Flags

Trace flags are an unsupported feature of SQL Server 2000 and might not be supported in future releases. You can use them to temporarily enable specified server characteristics or turn off certain behavior. They leave a record of their activity in the SQL Server error log, and are therefore useful for debugging. They are generally used by developers. However, they are also referenced in Knowledge Base articles. Use DBCC TRACEON to enable a specified trace flag. For example, enabling trace flag 3205 disables hardware compression on tape drives. For more information on this topic, refer to Kalen Delaney's book, Inside Microsoft SQL Server 2000 (Microsoft Press, 2000).

Using SNMP

You can use Simple Network Management Protocol (SNMP) to send management information across different operating system platforms. Management information can include performance statistics and configuration information. SNMP can only monitor the default instance. SQL Server 2000 support for SNMP is enabled automatically on Windows 2000 and Windows NT 4.0 computers supporting SNMP.

Lesson Summary

System Monitor is used to monitor resources used on the local computer or on remote computers by various server processes. Task Manager provides a quick snapshot of per-process usage or overall resource usage. SQL Profiler is used to monitor events and processes that are specific to SQL Server. SQL Query Analyzer is used to display estimated execution plans for query efficiency analysis. The SQL Server Enterprise Manager Current Activity window provides a snapshot of current user and locking activity, and can be used to terminate a process. Transact-SQL system stored procedures and built-in functions can be used to provide snapshots of current activity, or to provide statistics regarding resource usage over a period of time. DBCC statements can be used to check database consistency and monitor SQL Server. SNMP can be used for centralized reporting across various operating systems.

MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
Year: 2001
Pages: 126

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