SQL Server 2012: new perfmon counters

I have read some blog postings concerning the new perfmon counters, but I have not seen really extensive overviews yet. In this post, I would like to present not only what is new, but also what has changed (or not) and how to find it. The first step is to select all objects in SQL2008R2 and SQL2012 from the view sys.dm_os_performance_counters.

Remarks: All object or counter definitions from this post come from msdn.

Objects comparison

The query to do a comparison is simple:

select disctinct object_name from sys.dm_os_performance_counters

The result is 27 objects for SQL Server 2008 R2 and 33 for SQL Server 2012.
Additionally, I found that 1 object disappeared (Buffer Partition) and 7 appeared (Query Execution, FileTable, Batch Resp Statistics, Memory Broker Clerks, Memory Node, Database Replica and Availability Replica).

The Buffer Partition object provides counters to monitor how SQL Server uses free pages (Free list empty/sec, Free list requests/sec and Free page)

Objects without changes between 2008R2 and 2012

In SQL Server 2012, 19 objects out of the 27 from SQL Server 2008R2 have not changed (counter added or deleted):

• MSSQL$…:Transactions
• MSSQL$…:Wait Statistics
• MSSQL$…:User Settable
• MSSQL$…:Workload Group Stats
• MSSQL$…:Cursor Manager by
• MSSQL$…:Resource Pool Stats
• MSSQL$…:Broker/DBM Transport
• MSSQL$…:Latches
• MSSQL$…:SQL Statistics
• MSSQL$…:Exec Statistics
• MSSQL$…:CLR
• MSSQL$…:General Statistics
• MSSQL$…:Cursor Manager Total
• MSSQL$…:SQL Errors
• MSSQL$…:Broker Statistics
• MSSQL$…:Plan Cache
• MSSQL$…:Broker TO Statistics
• MSSQL$…:Broker Activation
• MSSQL$…:Catalog Metadata

I have used this query to find them:

select distinct 'select COUNT(*) as '''+RTRIM(object_name)+'''from sys.dm_os_performance_counters where object_name='''+ RTRIM(object_name)+'''' from sys.dm_os_performance_counters

Objects with changes

There are 19 objects without changes and 7 new in SQL Server 2012, so I have calculated that 7 objects have changed

• from SQL Server 2012: 33-19-7=7
• from SQL Server 2008R2: 27-19-1(deleted object)=7

I have created a query to get all counters by object for these 7 objects with changes:

select distinct 'select counter_name, instance_name from sys.dm_os_performance_counters where object_name='''+ RTRIM(object_name)+''' ORDER BY instance_name DESC, counter_name DESC' from sys.dm_os_performance_counters

MSSQL$…:Buffer Node

This provides counters that complement counters provided by the Buffer Manager object.

Is deleted:

• Target pages: Ideal number of pages in the buffer pool on this node
• Stolen pages: Number of pages used for miscellaneous server purposes (stolen from the buffer pool) on this node
• Free pages: Total number of free pages on this node
• Foreign pages: Number of pages that come from a different NUMA node

Is added:

• Nothing

MSSQL$…:Buffer Manager

The Buffer Manager object provides counters to monitor how SQL Server uses

• Memory to store data pages, internal data structures, and the procedure cache
• Counters to monitor the physical I/O as SQL Server reads and writes database pages

Is deleted:

• Total pages: Number of pages in the buffer pool (includes database, free, and stolen pages)
• Stolen pages: Number of pages used for miscellaneous server purposes (including procedure cache)
• Reserved pages: Number of buffer pool reserved pages
• Free pages: Total number of pages on all free lists
• AWE write maps/sec: Number of times per second that it is necessary to map in a dirty buffer so it can be written to disk
• AWE unmap pages/sec: Number of SQL Server buffers that are unmapped per second
• AWE unmap calls/sec: Number of calls to unmap buffers per second
• AWE stolen maps/sec: Number of times per second that a buffer was taken from the free list and mapped
• AWE lookup maps/sec: Number of times per second that a database page was requested by the server, found in the buffer pool,and mapped

Is added:

• Integral Controller Slope: Not documented!
• Background writer pages/sec: Not documented!

MSSQL$…:Locks

The Locks object in Microsoft SQL Server provides information about SQL Server locks on individual resource types.

Is deleted:

• Nothing

Is added:

• A new instance: OibTrackTbl Not documented !

MSSQL$…:Memory Manager

The Memory Manager object in Microsoft SQL Server provides counters to monitor overall server memory usage.

Is deleted:

• Nothing

Is added:

• Stolen Server Memory (KB): Specifies the amount of memory the server is using for purposes other than database pages
• Reserved Server Memory (KB): Indicates the amount of memory the server has reserved for future usage
• Log Pool Memory (KB): Not documented!
• Free Memory (KB): Specifies the amount of committed memory currently not used by the server
• External benefit of memory: Not documented!
• Database Cache Memory (KB):Specifies the amount of memory the server is currently using for the database pages cache

MSSQL$…:Access Methods

The Access Methods object in SQL Server provides counters to monitor how the logical data within the database is accessed

Is deleted:

• Nothing

Is added:

• InSysXact waits/sec: Not documented!

MSSQL$…:Databases

The Databases object in SQL Server provides counters to monitor bulk copy operations, backup and restore throughput, and transaction log activities

Is deleted:

• Nothing

Is added:

• Log Pool Requests/sec: The number of log-block requests processed by the log pool
• Log Pool Disk Reads/sec: Number of disk reads that the log pool issued to fetch log blocks
• Log Pool Cache Misses/sec: Number of requests for which the log block was not available in the log pool
• Log Flush Write Time (ms): Time in milliseconds for performing writes of log flushes that were completed in the last second

MSSQL$…:Deprecated Features

The Deprecated Features object in SQL Server provides a counter to monitor the features designated as deprecated.

Is deleted:

• sp_dropalias
• sp_dboption
• SET DISABLE_DEF_CNST_CHK
• RESTORE DATABASE or LOG WITH DBO_ONLY
• Oldstyle RAISERROR
• Non-ANSI *= or =* outer join operators
• FASTFIRSTROW
• database_principal_aliases
• CREATE TRIGGER WITH APPEND
• COMPUTE [BY]
• BACKUP DATABASE or LOG WITH
• BACKUP DATABASE or LOG WITH MEDIAPASSWORD

Is added:

• sp_trace_setstatus
• sp_trace_setfilter
• sp_trace_setevent
• sp_trace_getdata
• sp_trace_create
• sp_dropsrvrolemember
• sp_droprolemember
• sp_db_increased_partitions
• sp_configure ‘default trace’
• sp_configure ‘c2 audit mode’
• sp_configure ‘affinity64 mask’
• sp_configure ‘affinity mask’
• sp_changedbowner
• sp_addsrvrolemember
• sp_addrolemember
• SET ERRLVL
• SET FMTONLY ON
• Old NEAR Syntax
• objidupdate
• fn_trace_gettable
• fn_trace_getinfo
• fn_trace_getfilterinfo
• fn_trace_geteventinfo
• DBCC_IND
• DBCC_EXTENTINFO
• Database Mirroring

The deleted counters are for SQL 2000 compatibility and in SQL Server 2012, SQL 2000 is not supported anymore as compatibility mode. The added counters are the deprecated features for SQL server 2008 and 2008R2.

And to finish, the 7 new objects…

New Objects

MSSQL$…:Query Execution

Not documented!
New counters:

• Remote resend requests/sec
• Remote requests/sec
• Remote activations/sec
• Rem Req Cache Hit Ratio Base
• Rem Req Cache Hit Ratio
• Local data access/sec

MSSQL$…:FileTable

Not documented! But easy to find that is for measuring performance of the new feature FileTable.:roll:
New counters:

• Time update FileTable item BASE
• Time to get FileTable item BASE
• Time rename FileTable item BASE
• Time per file I/O response BASE
• Time per file I/O request BASE
• Time move FileTable item BASE
• Time FileTable handle kill BASE
• Time FileTable enumeration BASE
• Time delete FileTable item BASE
• FileTable table operations/sec
• FileTable kill handle ops/sec
• FileTable item update reqs/sec
• FileTable item rename reqs/sec
• FileTable item move reqs/sec
• FileTable item get requests/sec
• FileTable item delete reqs/sec
• FileTable file I/O response/sec
• FileTable file I/O requests/sec
• FileTable enumeration reqs/sec
• FileTable db operations/sec
• Avg time update FileTable item
• Avg time to get FileTable item
• Avg time rename FileTable item
• Avg time per file I/O response
• Avg time per file I/O request
• Avg time move FileTable item
• Avg time FileTable handle kill
• Avg time FileTable enumeration
• Avg time delete FileTable item

MSSQL$…:Memory Node

The Memory Node object in Microsoft SQL Server provides counters to monitor server memory usage on NUMA nodes.
New counters:

• Database Node Memory (KB): Specifies the amount of memory the server is currently using on this node for database pages
• Free Node Memory (KB): Specifies the amount of memory the server is not using on this node
• Foreign Node Memory (KB): Specifies the amount of non NUMA-local memory on this node
• Stolen Memory Node (KB): Specifies the amount of memory the server is using on this node for purposes other than database pages
• Target Node Memory: Specifies the ideal amount of memory for this node
• Total Node Memory: Indicates the total amount of memory the server has committed on this node

MSSQL$…:Database Replica

The Database Replica performance object contains performance counters that report information about the secondary databases of an AlwaysOn availability group in SQL Server 2012.
New counters:

• File Bytes Received/sec: Amount of FILESTREAM data received by the secondary replica for the secondary database in the last second
• Log Bytes Received/sec: Amount of log records received by the secondary replica for the database in the last second
• Log remaining for undo: The amount of log in kilobytes remaining to complete the undo phase
• Log Send Queue: Amount of log records in the log files of the primary database, in kilobytes, that has not yet been sent to the secondary replica
• Mirrored Write Transaction/sec: Number of transactions that wrote to the mirrored database and waited for the log to be sent to the mirror in order to commit, in the last second
• Recovery Queue: Amount of log records in the log files of the secondary replica that has not yet been redone
• Redo Bytes Remaining: The amount of log in kilobytes remaining to be redone to finish the reverting phase
• Redone Bytes/sec: Amount of log records redone on the secondary database in the last second
• Total Log requiring undo: Total kilobytes of log that must be undone
• Transaction Delay: Delay in waiting for unterminated commit acknowledgement, in milliseconds

MSSQL$…:Availability Replica

The SQLServer:Availability Replica performance object contains performance counters that report information about the availability replicas in AlwaysOn availability groups in SQL Server 2012.
New counters:

• Bytes Received from Replica/sec: Number of bytes received from the availability replica per second
• Bytes Sent to Replica/sec: Number of bytes sent to the remote availability replica per second
• Bytes Sent to Transport/sec: Actual number of bytes sent per second over the network to the remote availability replica
• Flow Control Time (ms/sec): Time in milliseconds that log stream messages waited for send flow control, in the last second
• Flow Control/sec: Number of times flow-control initiated in the last second
• Receives from Replica/sec: Number of AlwaysOn messages received from the replica per second
• Resent Messages/sec: Number of AlwaysOn messages resent in the last second
• Sends to Replica/sec: Number of AlwaysOn messages sent to this availability replica per second
• Sends to Transport/sec: Actual number of AlwaysOn messages sent per second over the network to the remote availability replica

MSSQL$…:Memory Broker Clerks

Not documented!

2 instances: Column store object pool and Buffer Pool

Counters per instance:

• Simulation size
• Simulation benefit
• Pressure evictions (pages/sec)
• Periodic evictions (pages)
• Memory broker clerk size
• Internal benefit

MSSQL$…:Batch Resp Statistics

Not documented!

4 instances:Elapsed Time:Total(ms), Elapsed Time:Requests, CPU Time:Total(ms) and CPU Time:Requests

Counters per instance:

• Batches >=100000ms
• Batches >=”050000ms” &
• Batches >=”020000ms” &
• Batches >=”010000ms” &
• Batches >=”005000ms” &
• Batches >=”002000ms” &
• Batches >=”001000ms” &
• Batches >=”000500ms” &
• Batches >=”000200ms” &
• Batches >=”000100ms” &
• Batches >=”000050ms” &
• Batches >=”000020ms” &
• Batches >=”000010ms” &
• Batches >=”000005ms” &
• Batches >=”000002ms” &
• Batches >=”000001ms” &
• Batches >=”000000ms” &

Conclusion

This overview demonstrates all performance counters available for SQL Server 2012.
It may be noticed that the majority of new metrics are not documented…
I hope that will change in an nearly future!:-D

The lastest version of Performance Analysis of Logs (PAL) Tool to analyse the result file of a monitoring, haven’t included these new counters for SQL Server 2012 yet.

Some references:

• msdn reference for performance monitor for SQL Server here
• Performance Analysis of Logs (PAL) Tool web site here