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.
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:
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).