Blog - comments

Hi goog article can we install avdf firewall with flat network if it's possible please let me know ?

Thilina
First, thank you for your interrest in this blog.Yes, the byte code will be interpreted each time bu...
BIEHLER Stephane
Pretty sure this is wrong:> already said that JVMs interprets the generated byte code - that's true...
Gs
Michael, great article, however, I would disagree on DRS/Host Affinity. You are legally only requir...
David Bradshaw
Hi lauri, db_file_multiblock_read_count is still used in exadata smartscan because it defines the si...
Blog Stephane Haby SQL Server 2012: new perfmon counters

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
Posted by on in Technology Survey

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

b2ap3_thumbnail_Perfmon-counter.jpg

 

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.Wink

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!Cool

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
Rate this blog entry:
1

Stéphane Haby is Delivery Manager and Senior Consultant at dbi Services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. Stéphane Haby is Microsoft Certified Solutions Associate MCSA) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. He is also ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking / Financial Services, and many other industries.


MCSA  MCSE  mvp

Comments

  • Guest
    Andy Joiner Friday, 07 March 2014

    Undocumented counters - if you tick the "Show Description" box when adding counters, some useful documentation is shown, e.g.:
    External benefit of memory: "The external value of memory, in ms per page per ms, multiplied by 10 billion and truncated to an integer"

Leave your comment

Guest Saturday, 25 October 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter