Problem
I make no attempt to hide my dislike for Performance Monitor. Don’t get me wrong – it is a good product, but I’m a Transact-SQL kind of guy and I’m at the point after this many years as a SQL Server Professional where I expect to get all my information at the bottom of a white screen in tabular format. Queries, performance information, kids’ report cards, letters from my wife… Yes, I’m a SQL Server Management Studio Junkie and I will not change any time soon. What is a guy like me supposed to do when it comes to getting performance information – specifically performance counters that I would expect to see in Performance Monitor (aka PerfMon) – in SSMS?
Solution
This is simple with the DMVs, also known as Dynamic Management Views, in Microsoft SQL Server 2005 and later. By querying a single DMV, sys.dm_os_performance_counters to be precise, you can collect counter information that you would receive from PerfMon for the various SQL Server counters. Let me stress that: this is for the various SQL Server counters. You will not be able to receive counter information from outside the SQL Server stack from this DMV. However for a quick and simple query to return information about your SQL Server instance’s performance this is a great tool.
Firstly though, let me show you how to get a quick listing of the various objects we can query counters for via sys.dm_os_performance_counters DMV. What follows is the complete list of the 27 objects returned by the following query:
SELECT DISTINCT [object_name]
FROM sys.[dm_os_performance_counters]
ORDER BY[object_name]
Now with that listing in hand, you can isolate the full list of counters that are available for querying from sys.dm_os_performance_counters as well. Due to the sheer number of them, I’ll only provide a screenshot of a partial list here. There are a couple of options when it comes to returning the listing of individual counters. I prefer to use the following approach in which I use a templating format to pass in the object name to return the full list of counters available for the object.
“Tim, I’m not familiar with templates”, you say?
Well you should be and if you follow this link you will be! The important thing to know immediately though is the keystroke combination of Ctl+Shift+M. This will allow you to replace any parameters (identified in the format of <variable_name, data type, default value>) with a value of your chosing.
Let’s examine by looking at the following code and a list of counters it returns for the SQLServer:Buffer Manager object:
SELECT [object_name], [counter_name],
[instance_name], [cntr_value]
FROM sys.[dm_os_performance_counters]
WHERE [object_name] = ‘<var_object_name, varchar(100), SQLServer:Wait Statistics>’I’ll click Ctl+Shift+M and the following window is displayed – typical behavior for a T/SQL batch with an identified template parameter syntax embedded:
I can replace the default value with SQLServer:Buffer Manager and I get the following results once I click save and execute the query:
SELECT [object_name], [counter_name],
[instance_name], [cntr_value]
FROM sys.[dm_os_performance_counters]
WHERE [object_name] = ‘SQLServer:Buffer Manager’
Next Steps
- More tips from the author are available via this link.
- The template tip mentioned in this article is available here.
- Stay tuned for the next tip on sys.dm_os_performance_counters when we examine how to persist and return information on counter values over time.
- Want to learn more about SQL Server…check out SQLCruise.com
