| title | Use SQL Server Objects | Microsoft Docs | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ms.custom | ||||||||||||||||||||
| ms.date | 03/17/2016 | |||||||||||||||||||
| ms.prod | sql | |||||||||||||||||||
| ms.prod_service | database-engine | |||||||||||||||||||
| ms.reviewer | ||||||||||||||||||||
| ms.technology | performance | |||||||||||||||||||
| ms.topic | conceptual | |||||||||||||||||||
| helpviewer_keywords |
|
|||||||||||||||||||
| ms.assetid | bcd731b1-3c4e-4086-b58a-af7a3af904ad | |||||||||||||||||||
| author | julieMSFT | |||||||||||||||||||
| ms.author | jrasnick |
[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]
Microsoft [!INCLUDEssNoVersion] provides objects and counters that can be used by System Monitor to monitor activity in computers running an instance of [!INCLUDEssNoVersion]. An object is any [!INCLUDEssNoVersion] resource, such as a [!INCLUDEssNoVersion] lock or Windows 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 and 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 [!INCLUDEssNoVersion]. 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. Counters for the default instance appear in the format SQLServer:<object name>. Counters for named instances appear in the format MSSQL$<instance name>:<counter name> or SQLAgent$<instance name>:<counter name>.
By adding or removing counters to the chart and saving the chart settings, you can specify the [!INCLUDEssNoVersion] objects and counters that are monitored when System Monitor is started.
You can configure System Monitor to display statistics from any [!INCLUDEssNoVersion] counter. In addition, you can set a threshold value for any [!INCLUDEssNoVersion] counter and then generate an alert when a counter exceeds a threshold. For more information about setting an alert, see Create a SQL Server Database Alert.
Tip
You can also return the performance counter values by querying the sys.dm_os_performance_counters (Transact-SQL) dynamic management view.
Note
[!INCLUDEssNoVersion] statistics are displayed only when an instance of [!INCLUDEssNoVersion] is installed. If you stop and restart an instance of [!INCLUDEssNoVersion], the display of statistics is interrupted and resumes automatically. Also note that you will see [!INCLUDEssNoVersion] counters in the System Monitor snap-in even if [!INCLUDEssNoVersion] is not running. On a clustered instance, performance counters only function on the node where [!INCLUDEssNoVersion] is running.
This topic contains the following sections:
The following table lists the performance objects provided for [!INCLUDEssNoVersion] Agent:
| Performance object | Description |
|---|---|
| SQLAgent:Alerts | Provides information about [!INCLUDEssNoVersion] Agent alerts. |
| SQLAgent:Jobs | Provides information about [!INCLUDEssNoVersion] Agent jobs. |
| SQLAgent:JobSteps | Provides information about [!INCLUDEssNoVersion] Agent job steps. |
| SQLAgent:Statistics | Provides general information about [!INCLUDEssNoVersion] Agent. |
The following table lists the performance objects provided for [!INCLUDEssSB].
| Performance object | Description |
|---|---|
| SQLServer:Broker Activation | Provides information about [!INCLUDEssSB]-activated tasks. |
| SQLServer:Broker Statistics | Provides general [!INCLUDEssSB] information. |
| SQLServer:Broker Transport | Provides information on [!INCLUDEssSB] networking. |
The following table describes [!INCLUDEssNoVersion] objects.
| Performance object | Description |
|---|---|
| SQLServer:Access Methods | Searches through and measures allocation of [!INCLUDEssNoVersion] database objects (for example, the number of index searches or number of pages that are allocated to indexes and data). |
| SQLServer:Backup Device | Provides information about backup devices used by backup and restore operations, such as the throughput of the backup device. |
| SQLServer:Batch Resp Statistics | Counters to track SQL Batch Response times. |
| SQLServer:Buffer Manager | Provides information about the memory buffers used by [!INCLUDEssNoVersion], such as freememory and buffer cache hit ratio. |
| SQL Server:Buffer Node | Provides information about how frequently [!INCLUDEssNoVersion] requests and accesses free pages. |
| SQLServer:Catalog Metadata | This defines a catalog metadata manager object for SQL Server. |
| SQLServer:CLR | Provides information about the common language runtime (CLR). |
| SQLServer:Columnstore | Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL15] and later). Provides information about rowgroups and segments for columnstore indexes. |
| SQLServer:Cursor Manager by Type | Provides information about cursors. |
| SQLServer:Cursor Manager Total | Provides information about cursors. |
| SQLServer:Database Mirroring | Provides information about database mirroring. |
| SQLServer:Databases | Provides information about a [!INCLUDEssNoVersion] 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:Deprecated Features | Counts the number of times that deprecated features are used. |
| SQLServer:Exec Statistics | Provides information about execution statistics. |
| SQL Server:External Scripts | Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL15] and later). Provides information about external script execution. |
| SQLServer:FileTable | Statistics associated with FileTable and non-transacted access. |
| SQLServer:General Statistics | Provides information about general server-wide activity, such as the number of users who are connected to an instance of [!INCLUDEssNoVersion]. |
| SQL Server:HADR Availability Replica | Provides information about [!INCLUDEssNoVersion][!INCLUDEssHADR] availability replicas. |
| SQL Server:HADR Database Replica | Provides information about [!INCLUDEssNoVersion][!INCLUDEssHADR] database replicas. |
| SQL Server:HTTP Storage | Provides information to monitor a Microsoft Azure Storage account when using SQL Server Data Files in Microsoft Azure |
| SQLServer:Latches | Provides information about the latches on internal resources, such as database pages, that are used by [!INCLUDEssNoVersion]. |
| SQLServer:Locks | Provides information about the individual lock requests made by [!INCLUDEssNoVersion], such as lock time-outs and deadlocks. There can be multiple instances of this object. |
| SQLServer:LogPool FreePool | Describes statistics for the free pool inside the Log Pool. |
| SQLServer:Memory Broker Clerks | Statistics related to memory broker clerks. |
| SQLServer:Memory Manager | Provides information about [!INCLUDEssNoVersion] memory usage, such as the total number of lock structures currently allocated. |
| SQLServer:Plan Cache | Provides information about the [!INCLUDEssNoVersion] cache used to store objects such as stored procedures, triggers, and query plans. |
| SQLServer: Query Store | Provides information about the Query Store. |
| SQLServer: Resource Pool Stats | Provides information about Resource Governor resource pool statistics. |
| SQLServer:SQL Errors | Provides information about [!INCLUDEssNoVersion] errors. |
| SQLServer:SQL Statistics | Provides information about aspects of [!INCLUDEtsql] queries, such as the number of batches of [!INCLUDEtsql] statements received by [!INCLUDEssNoVersion]. |
| SQLServer:Transactions | Provides information about the active transactions in [!INCLUDEssNoVersion], such as the overall number of transactions and the number of snapshot transactions. |
| SQLServer:User Settable | Performs custom monitoring. Each counter can be a custom stored procedure or any [!INCLUDEtsql] statement that returns a value to be monitored. |
| SQLServer: Wait Statistics | Provides information about waits. |
| SQLServer: Workload Group Stats | Provides information about Resource Governor workload group statistics. |
The following table lists the performance objects provided for [!INCLUDEssNoVersion] replication:
| Performance object | Description |
|---|---|
| SQLServer:Replication Agents SQLServer:Replication Snapshot SQLServer:Replication Logreader SQLServer:Replication Dist. SQLServer:Replication Merge For more information, see Monitoring Replication with System Monitor. |
Provides information about replication agent activity. |
For the SSIS Pipeline counter, see Performance Counters.
Use of the [!INCLUDEssNoVersion] objects depends on Windows permissions, except SQLAgent:Alerts. Users must be a member of the sysadmin fixed server role to use SQLAgent:Alerts.
Use Performance Objects
sys.dm_os_performance_counters (Transact-SQL)