Getting Perfmon data from inside of SSMS

Did you know that the SQL Server related performance counters can be accessed from a DMV using T-SQL?
Let us Explore the sys.dm_os_performance_counters DMV to retrieve the Page Life Expectancy counter value from the Buffer Manager object.
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE
object_name = ‘SQLServer:Buffer Manager’
AND counter_name = ‘page life expectancy’
Note that the objects & counters available through this DMV are limited to those exposed to PerfMon by SQL Server, so counters such as Avg. Disk Sec/Read are not available using this technique because they originate in Windows, not SQL Server.

For the complete list of available counters, try a simple select against the DMV such as this:
SELECT *
FROM sys.dm_os_performance_counters

This technique is a great alternative to PerfMon, especially for obtaining a quick overview of SQL Serverrelated
performance information in real time by issuing simple T-SQL commands within SQL Server
Management Studio (SSMS).

Leave a Reply

Your email address will not be published. Required fields are marked *