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. For more information about setting an alert, see Creating a SQL Server Database Alert.
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. |