In a previous post about changed system objects in Denali, I talked about the changes to memory-related DMVs due to underlying changes in the memory manager. The SQLOS team has posted a great introduction to these changes, and they plan to post more details in future posts. In the meantime, and due to a question yesterday from Tom LaRock (blog | twitter) ...
... I thought I would tell you about some performance counters that have changed between SQL Server 2008 R2 and Denali - most of which involve these memory manager changes. In response to Tom, initially I just performed a count and compared, but this isn't very accurate; since many counters are database-specific, the counts depend greatly on how many databases you have installed (as well as edition and features - more on that below). So instead I wanted to query and see the actual differences ... on a 2008 R2 instance, I created a linked server pointing to a Denali instance, then I ran this query:
;WITH loc AS
(
SELECT obj, cn
FROM
(
SELECT
obj = SUBSTRING([object_name], CHARINDEX(':', [object_name]) + 1, 256),
cn = counter_name
FROM sys.dm_os_performance_counters
) AS x
GROUP BY obj, cn
),
rem AS
(
SELECT obj, cn
FROM
(
SELECT
obj = SUBSTRING([object_name], CHARINDEX(':', [object_name]) + 1, 256),
cn = counter_name
FROM [Denali_Linked_Server].[master].sys.dm_os_performance_counters
) AS x
GROUP BY obj, cn
)
SELECT
[2008R2_obj] = loc.obj,
[2008R2_ctr] = loc.cn,
[Denali_obj] = rem.obj,
[Denali_ctr] = rem.cn
FROM
loc
FULL OUTER JOIN
rem
ON loc.obj = rem.obj
AND loc.cn = rem.cn
WHERE
loc.obj IS NULL
OR rem.obj IS NULL
ORDER BY
[Denali_obj],
[Denali_ctr],
[2008R2_obj],
[2008R2_ctr];
|
The results:
| 2008 R2 Object |
2008 R2 Counter |
Denali Object |
Denali Counter |
| Buffer Manager |
AWE lookup maps/sec |
<removed in Denali> |
| Buffer Manager |
AWE stolen maps/sec |
<removed in Denali> |
| Buffer Manager |
AWE unmap calls/sec |
<removed in Denali> |
| Buffer Manager |
AWE unmap pages/sec |
<removed in Denali> |
| Buffer Manager |
AWE write maps/sec |
<removed in Denali> |
| Buffer Manager |
Free pages |
<removed in Denali> |
| Buffer Manager |
Reserved pages |
<removed in Denali> |
| Buffer Manager |
Stolen pages |
<removed in Denali> |
| Buffer Manager |
Target pages |
<removed in Denali> |
| Buffer Manager |
Total pages |
<removed in Denali> |
| Buffer Node |
Foreign pages |
<removed in Denali> |
| Buffer Node |
Free pages |
<removed in Denali> |
| Buffer Node |
Stolen pages |
<removed in Denali> |
| Buffer Node |
Target pages |
<removed in Denali> |
| Buffer Node |
Total pages |
<removed in Denali> |
| Buffer Partition |
Free list empty/sec |
<removed in Denali> |
| Buffer Partition |
Free list requests/sec |
<removed in Denali> |
| Buffer Partition |
Free pages |
<removed in Denali> |
| <new in Denali> |
Access Methods |
InSysXact waits/sec |
| <new in Denali> |
Availability Replica |
Bytes Received from Replica/sec |
| <new in Denali> |
Availability Replica |
Bytes Sent to Replica/sec |
| <new in Denali> |
Availability Replica |
Bytes Sent to Transport/sec |
| <new in Denali> |
Availability Replica |
Flow Control Time (ms/sec) |
| <new in Denali> |
Availability Replica |
Flow Control/sec |
| <new in Denali> |
Availability Replica |
Receives from Replica/sec |
| <new in Denali> |
Availability Replica |
Resent Messages/sec |
| <new in Denali> |
Availability Replica |
Sends to Replica/sec |
| <new in Denali> |
Availability Replica |
Sends to Transport/sec |
| <new in Denali> |
Database Replica |
File Bytes Received/sec |
| <new in Denali> |
Database Replica |
Log Bytes Received/sec |
| <new in Denali> |
Database Replica |
Log Send Queue |
| <new in Denali> |
Database Replica |
Recovery Queue |
| <new in Denali> |
Database Replica |
Redone Bytes/sec |
| <new in Denali> |
Databases |
Log Flush Write Time (ms) |
| <new in Denali> |
Memory Manager |
Database Cache Memory (KB) |
| <new in Denali> |
Memory Manager |
Free Memory (KB) |
| <new in Denali> |
Memory Manager |
Log Pool Memory (KB) |
| <new in Denali> |
Memory Manager |
Reserved Server Memory (KB) |
| <new in Denali> |
Memory Manager |
Stolen Server Memory (KB) |
| <new in Denali> |
Memory Node |
Database Node Memory (KB) |
| <new in Denali> |
Memory Node |
Foreign Node Memory (KB) |
| <new in Denali> |
Memory Node |
Free Node Memory (KB) |
| <new in Denali> |
Memory Node |
Stolen Node Memory (KB) |
| <new in Denali> |
Memory Node |
Target Node Memory (KB) |
| <new in Denali> |
Memory Node |
Total Node Memory (KB) |
You can see that several AWE-related counters have been removed, since AWE is no longer supported in Denali (this means if you are on 32-bit hardware and have > 4GB of RAM, you may as well start shopping for new hardware if you plan to be an early adopter). You'll also notice several Buffer Manager/Node/Partition objects are gone in Denali, replaced by Memory Manager/Node objects (and now measured in KB as opposed to pages). Some of these are described here. There are also some new replica counters based on the new availability and HA/DR features in Denali (described here and here). The new Log Flush Write Time (ms) counter is described in this BOL topic: SQL Server, Databases Object. I can't find any information about the InSysXact waits/sec counter; hopefully the documentation will catch up by RTM (or, ideally, by the next CTP).
So, if you're currently monitoring any of these performance counters in 2008 R2 or previous versions, take note that not only are they changing in Denali, but also that their values can mean something different.
Now keep in mind that this does not address changes to performance counters that are only present when certain features are enabled, such as database mirroring and replication. I will try to re-run these tests when I have a suitable environment with as many features enabled as possible.