sys.dm_os_performance_counters (Transact-SQL)
Applies to: SQL Server (all supported versions)
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Parallel Data Warehouse
Returns a row per performance counter maintained by the server. For information about each performance counter, see Use SQL Server Objects.
Note
To call this from Azure Synapse Analytics or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_performance_counters.
Column name | Data type | Description |
---|---|---|
object_name | nchar(128) | Category to which this counter belongs. |
counter_name | nchar(128) | Name of the counter. To get more information about a counter, this is the name of the topic to select from the list of counters in Use SQL Server Objects. |
instance_name | nchar(128) | Name of the specific instance of the counter. Often contains the database name. |
cntr_value | bigint | Current value of the counter. Note: For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used. |
cntr_type | int | Type of counter as defined by the Windows performance architecture. See WMI Performance Counter Types on Docs or your Windows Server documentation for more information on performance counter types. |
pdw_node_id | int | Applies to: Azure Synapse Analytics, Parallel Data Warehouse The identifier for the node that this distribution is on. |
Remarks
If the installation instance of SQL Server fails to display the performance counters of the Windows operating system, use the following Transact-SQL query to confirm that performance counters have been disabled.
SELECT COUNT(*) FROM sys.dm_os_performance_counters;
If the return value is 0 rows, this means that the performance counters have been disabled. You should then look at the setup log and search for error 3409, Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.
This denotes that performance counters were not enabled. The errors immediately before the 3409 error should indicate the root cause for the failure of performance counter enabling. For more information about setup log files, see View and Read SQL Server Setup Log Files.
Performance counters where the cntr_type
column value is 65792, 272696320, and 537003264 display an instant snapshot counter value.
Performance counters where the cntr_type
column value is 272696576, 1073874176, and 1073939712 display cumulative counter values instead of an instant snapshot. As such, to get a snapshot-like reading, you must compare the delta between two collection points.
Permission
On SQL Server, requires VIEW SERVER STATE
permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the Server admin
or an Azure Active Directory admin
account is required. On all other SQL Database service objectives, the VIEW DATABASE STATE
permission is required in the database.
Examples
The following example returns all performance counters that display snapshot counter values.
SELECT object_name, counter_name, instance_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE cntr_type = 65792 OR cntr_type = 272696320 OR cntr_type = 537003264;
See Also
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
sys.sysperfinfo (Transact-SQL)