Microsoft® SQL Server™ provides objects and counters that can be used by Windows NT Performance Monitor to monitor activity in computers running SQL Server. An object is any Microsoft Windows NT® or SQL Server resource, such as a SQL Server lock or Windows NT 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.
When Windows NT Performance Monitor is initially started, it starts monitoring the SQL Server objects and counters found in the Sqlctrs.pmc file. By default, the Sqlctrs.pmc file contains a subset of the available SQL Server counters. SQL Server (and Windows NT) counters can be added or removed from the file as necessary. By adding or removing counters to the chart and saving the chart settings to the Sqlctrs.pmc file, you can change the default objects and counters monitored when Windows NT Performance Monitor is initially started. When Windows NT Performance Monitor is next started, the new settings take effect. The default objects and counters monitored are:
| 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 Windows NT Performance 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. For more information about setting an alert, see Setting Up an SQL Server Database Alert.
Note SQL Server statistics are displayed only when SQL Server is running. If you stop and restart SQL Server, the display of SQL Server statistics is interrupted and then automatically resumed.
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 that are currently connected to 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 the 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. |