Vinod Kumar (Blog home)

In performance tuning exercises I am used to querying and using Performance counters at large. This is one source of high-level troubleshooting that we advocate to our customers for a quick effective start. Once we know where the problem is, we get into detailed analysis using other tools. Several memory counters that were present in the Buffer Pool objects – for example, Buffer Manager, Buffer Partition and Buffer Node are removed in SQL Server 2012. New counters that reflect status of memory manager components are added to the system monitor objects: Memory Manager, Memory Node. It is key to know what was added because these help us in future troubleshooting.

Counters removed in SQL Server 2012

SQLServer:Buffer Manager

1. Free pages
2. Total pages
3. Target pages
4. Reserved pages
5. Stolen pages
6. AWE lookup maps/sec
7. AWE stolen maps/sec
8. AWE write maps/sec
9. AWE unmap calls/sec
10. AWE unmap pages/sec

SQLServer:Buffer Partition

1. Free pages
2. Free list requests/sec
3. Free list empty/sec

SQLServer:Buffer Node

1. Free pages
2. Total pages
3. Foreign pages
4. Stolen pages
5. Target pages

The following memory related counters have been added in SQL Server 2012:

SQLServer:Memory Manager

1. Database Cache Memory (KB)
2. Free Memory (KB)
3. Reserved Server Memory (KB)
4. Stolen Server Memory (KB)

SQLServer:Memory Node

1. Database Node Memory (KB)
2. Free Node Memory (KB)
3. Foreign Node Memory (KB)
4. Stolen  Memory Node (KB)
5. Target Node Memory
6. Total Node Memory

SQLServer:Buffer Node

1. Local Node page lookups/sec
2. Remote  Node page lookups/sec

These were important to know because during performance tuning, I have a tendency to use perfmon templates. In a recent testing, I found that some of these counters were different and were missing in my analysis. On closer debugging is when I understood, some of these counters have been removed because of the memory changes made to SQL Server.

Tags: Counters, InterOp, ITPro, Management, Misc, Performance, SQL Server, Technology, Tips and Tricks

This entry was posted on Tuesday, January 15th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.