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.

Note

---

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.

Note

---

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. . .
Cursors	Cursor creation, use, and deletion events.	The actual types of cursors being used, which is not necessarily the type specified by the calling application.
Database	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.
Locks	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.
Objects	Object creating, opening, closing, dropping, and deleting events.	Ad hoc creation of objects by applications and users. Can generate particularly large files.
Performance	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.
Scans	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.
Server	Memory change events.	Changes in SQL Server memory usage greater than 1 MB or 5% of the maximum server memory, whichever is greater.
Sessions	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.
Transactions	Transaction execution information.	Types of logging activity by applications. Also, transaction commits and rollbacks, and distributed transactions.
TSQL	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.

Note

---

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. . .
SQLProfilerSP_Counts	The number of stored procedures that run; groups the results by stored procedure name and includes the number of times the procedure has executed.
SQLProfilerStandard	General information regarding SQL batches and stored procedures executed and their connections, in execution order.
SQLProfilerTSQL	Each Transact-SQL statement issued in execution order including the time each statement was issued.
SQLProfilerTSQL_Duration	Each Transact-SQL statement issued; groups the results by duration (in milliseconds).
SQLProfilerTSQL_Grouped	Each Transact-SQL statement issued; groups the results by user submitting them.
SQLProfilerTSQL_Replay	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.
SQLProfilerTSQL_SPs	Details in execution order about each stored procedure that executes, including the Transact-SQL commands within each stored procedure.
SQLProfilerTuning	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. . .
sp_who	Snapshot of current users and processes, including the currently executing command	Active users and their processes
sp_who2	Snapshot of current users and processes with additional columns (also more readable)	Active users and their processes
sp_lock	Snapshot of current locks	Blocking locks and deadlocks, and the process causing them
sp_spaceused	Estimate of current disk space reserved and used by a table or the entire database	Database or object space usage
sp_monitor	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.

DBCC

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
DBCC CHECKCATALOG	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.
DBCC CHECKDB	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.
DBCC CHECKCONSTRAINTS	Verifies foreign key and check constraints on a table.
DBCC CHECKFILEGROUP	Performs the same function as DBCC CHECKDB, but limited to a single specified filegroup and required tables.
DBCC CONCURRENCYVIOLATION	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.
DBCC DROPCLEANBUFFERS	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.
DBCC OPENTRAN	Displays information regarding the oldest active transaction and oldest distributed and nondistributed replicated transactions within a specified database.
DBCC PROCCACHE	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.
DBCC SHOWCONTIG	Displays fragmentation information for the data and indexes of a table.
DBCC SHOW_STATISTICS	Displays the current distribution statistics for an index or statistics collection on a table.
DBCC SQLPERF (LOGSPACE)	Displays statistics about transaction log space size and percent used in all databases for a SQL Server instance.
DBCC UPDATEUSAGE	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

Counter	Count Since SQL Server Started
@@CONNECTIONS	Number of connections (including attempted connections)
@@CPU_BUSY	Time in milliseconds that the processor has spent working
@@IDLE	Time in milliseconds that SQL Server has been idle
@@IO_BUSY	Time in milliseconds that SQL Server has spent performing input and output operations
@@PACK_RECEIVED	Number of input packets read from the network
@@PACK_SENT	Number of output packets written to the network
@@PACKET_ERRORS	Number of network packet errors that have occurred on connections
@@TOTAL_ERRORS	Number of disk read/write errors encountered
@@TOTAL_READ	Number of disk reads (not cache reads)
@@TOTAL_WRITE	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.