Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Performance Counter DMV sys.dm_os_performance_counters


By:   |   Read Comments (1)   |   Related Tips: More > Dynamic Management Views and Functions

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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];

 you can collect counter information that you would receive from PerfMon for the various SQL Server counters

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:

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';

replace the default value with SQLServer:Buffer Manager and I get the following results once I click save and execute the query
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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, June 29, 2010 - 3:08:48 PM - russ960 Back To Top

Tim:

It looks like the Ctrl+Shift+M does not work in SSMS 2008. 

 http://stackoverflow.com/questions/1250120/ctrl-shift-m-no-longer-working-in-sql-server-management-studio

Alt+Q+S will work.  Thank you.


Learn more about SQL Server tools