May 17, 2011 at 11:32 am
Hey guys. We are in the process of moving our metrics from performance counters to DVM's. I'm just wondering, are there any equivalents for the following windows performance counters in DMV's
Category Counter
-----------------------------------------------------
LogicalDisk Avg. Disk Sec/Transfer
Network Interface Bytes Total/Sec
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
May 18, 2011 at 5:17 am
Yes, some of the counters are available from Dynamic Management Objects.
Take a look at sys.dm_os_performance_counters.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 18, 2011 at 5:49 am
I think sys.dm_os_performance_counters DMV returns performance counters related SQL Server only.
Non-SQL Server performance counter such as LogicalDisk Avg. Disk Sec/Transfer, Network Interface Bytes Total/Sec are not returned by this DMV
May 18, 2011 at 6:15 am
sys.os_exec_performance_counters DMV
The "sys.os_exec_performance_counters" DMV, lets you use simple TSQL to obtain different SQL Server performance counters. This view contains both instance level and database specific counters. Some counters provided valuable information by themselves, while other counters require you to compare the difference between multiple counters, to obtain a meaningful counter value. Below is list of the different SQL Server objects available within this DMV. For each of these objects multiple counters exist:
SQLServer:Buffer Partition
SQLServer:User Settable
SQLServer:Databases
SQLServer:CLR
SQLServer:Cursor Manager by Type
SQLServer:Exec Statistics
SQLServer:Transactions
SQLServer:Memory Manager
SQLServer:SQL Errors
SQLServer:Buffer Node
SQLServer:Plan Cache
SQLServer:Access Methods
SQLServer:Cursor Manager Total
SQLServer:Broker Activation
SQLServer:Latches
SQLServer:Wait Statistics
SQLServer:Broker/DBM Transport
SQLServer:General Statistics
SQLServer:SQL Statistics
SQLServer:Catalog Metadata
SQLServer:Broker Statistics
SQLServer:Locks
SQLServer:Buffer Manage
As you can see, there are quite a few SQL Server objects that contain performance counters, which are exposed by this DMV. For more information about each of these objects, please refer to the Books Online topic titled "Using SQL Server Object".
These performance counters were available in SQL Server 2000 by retrieving information from the "master.dbo.sysperfinfo" table. A view that represents this table has been provided in SQL Server 2005, for backwards compatibility with SQL Server 2000. This view allows your old SQL Server 2000 code to still work and retrieve information about performance counters. But as with any backwards compatible feature you should consider re-writing your code to use the new "sys.os_exec_performance_counters" DMV.
May 18, 2011 at 9:48 am
I found this link
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
but were you able to query sys.os_exec_performance_counters DMV?
M&M
May 18, 2011 at 11:18 am
I've been rummaging all the DMV's actually, for the 2 counters but I just couldn't find any equivalent. I just want to verify and confirm it from sql server savvy folks like you guys.
Thanks so much for the info! I highly appreciate it!
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
May 18, 2011 at 12:16 pm
For disk transfer rates, look at the DMF sys.dm_io_virtual_file_stats. The values are cumulative, so you need to sample it over a time range a diff the values.
As for network transfer rates, nothing comes to mind. I would be mostly concerned with the following wait types:
ASYNC_NETWORK_IO (stalls related to pushing data to clients)
OLEDB (stalls related to getting data back from a remote query; note that running DMVs also shows waits of this type, as many of them use OLEDB to communicate with the instance)
If those aren't popping out as problems, then I generally won't bother with looking at raw network rates.
-Eddie
Eddie Wuerch
MCM: SQL
May 19, 2011 at 12:20 pm
Eddie Wuerch (5/18/2011)
For disk transfer rates, look at the DMF sys.dm_io_virtual_file_stats. The values are cumulative, so you need to sample it over a time range a diff the values.As for network transfer rates, nothing comes to mind. I would be mostly concerned with the following wait types:
ASYNC_NETWORK_IO (stalls related to pushing data to clients)
OLEDB (stalls related to getting data back from a remote query; note that running DMVs also shows waits of this type, as many of them use OLEDB to communicate with the instance)
If those aren't popping out as problems, then I generally won't bother with looking at raw network rates.
-Eddie
Unfortunately, there is much work involved with sampling the sys.dm_io_virtual_file_stats because the MB read depends on how much activity there is on the file. If none or very very few and the time gap is significant, the computed speed is very less and this is misleading. We can however couple this with synthetic transaction but again that's another effort altogehter.
Thanks so much for the insight.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply