Use SQL Server Objects
TOC
Collapse the table of content
Expand the table of content

Use SQL Server Objects

 

Updated: March 17, 2016

Applies To: SQL Server 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Microsoft SQL Server provides objects and counters that can be used by System Monitor to monitor activity in computers running an instance of SQL Server. An object is any SQL Server resource, such as a SQL Server 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 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. 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 SQL Server objects and counters that are monitored when System Monitor is started.

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 Create a SQL Server Database Alert.

System_CAPS_ICON_tip.jpg Tip


You can also return the performance counter values by querying the sys.dm_os_performance_counters (Transact-SQL) dynamic management view.

System_CAPS_ICON_note.jpg Note


SQL Server statistics are displayed only when an instance of SQL Server is installed. If you stop and restart an instance of SQL Server, the display of statistics is interrupted and resumes automatically. Also note that you will see SQL Server counters in the System Monitor snap-in even if SQL Server is not running. On a clustered instance, performance counters only function on the node where SQL Server is running.

This topic contains the following sections:

The following table lists the performance objects provided for SQL Server Agent:

Performance objectDescription
SQLAgent:AlertsProvides information about SQL Server Agent alerts.
SQLAgent:JobsProvides information about SQL Server Agent jobs.
SQLAgent:JobStepsProvides information about SQL Server Agent job steps.
SQLAgent:StatisticsProvides general information about SQL Server Agent.

The following table lists the performance objects provided for Service Broker.

Performance objectDescription
SQLServer:Broker ActivationProvides information about Service Broker-activated tasks.
SQLServer:Broker StatisticsProvides general Service Broker information.
SQLServer:Broker TransportProvides information on Service Broker networking.

The following table describes SQL Server objects.

Performance objectDescription
SQLServer:Access MethodsSearches 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).
SQLServer:Backup DeviceProvides information about backup devices used by backup and restore operations, such as the throughput of the backup device.
SQLServer:Buffer ManagerProvides information about the memory buffers used by SQL Server, such as freememory and buffer cache hit ratio.
SQL Server:Buffer NodeProvides information about how frequently SQL Server requests and accesses free pages.
SQLServer:CLRProvides information about the common language runtime (CLR).
SQLServer:ColumnstoreApplies to: SQL Server (SQL Server 2016 through SQL Server 2016).

Provides information about rowgroups and segments for columnstore indexes.
SQLServer:Cursor Manager by TypeProvides information about cursors.
SQLServer:Cursor Manager TotalProvides information about cursors.
SQLServer:Database MirroringProvides information about database mirroring.
SQLServer:DatabasesProvides 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:Deprecated FeaturesCounts the number of times that deprecated features are used.
SQLServer:Exec StatisticsProvides information about execution statistics.
SQL Server:External ScriptsApplies to: SQL Server (SQL Server 2016 through SQL Server 2016).

Provides information about external script execution.
SQLServer:General StatisticsProvides information about general server-wide activity, such as the number of users who are connected to an instance of SQL Server.
SQL Server:HADR Availability ReplicaProvides information about SQL ServerAlways On Availability Groups availability replicas.
SQL Server:HADR Database ReplicaProvides information about SQL ServerAlways On Availability Groups database replicas.
SQLServer:LatchesProvides information about the latches on internal resources, such as database pages, that are used by SQL Server.
SQLServer:LocksProvides 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.
SQLServer:Memory ManagerProvides information about SQL Server memory usage, such as the total number of lock structures currently allocated.
SQLServer:Plan CacheProvides information about the SQL Server cache used to store objects such as stored procedures, triggers, and query plans.
SQLServer: Query StoreProvides information about the Query Store.
SQLServer: Resource Pool StatsProvides information about Resource Governor resource pool statistics.
SQLServer:SQL ErrorsProvides information about SQL Server errors.
SQLServer:SQL StatisticsProvides information about aspects of Transact-SQL queries, such as the number of batches of Transact-SQL statements received by SQL Server.
SQLServer:TransactionsProvides information about the active transactions in SQL Server, such as the overall number of transactions and the number of snapshot transactions.
SQLServer:User SettablePerforms custom monitoring. Each counter can be a custom stored procedure or any Transact-SQL statement that returns a value to be monitored.
SQLServer: Wait StatisticsProvides information about waits.
SQLServer: Workload Group StatsProvides information about Resource Governor workload group statistics.

The following table lists the performance objects provided for SQL Server replication:

Performance objectDescription
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 SQL Server 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)

Community Additions

ADD
Show:
© 2016 Microsoft