Replacing master.dbo.sysperfinfo with sys.dm_os_performance_counters I'm working with some old code that pulls performance counters, and part of what I'm doing is making sure we're doing some processes efficiently and effectively.

Right now I have this code that I'm looking at:

SELECT REPLACE(RTRIM(OBJECT_NAME), 'SQLServer:', '') AS 'Object',
       RTRIM(counter_name)   AS 'Counter',
       RTRIM(instance_name)  AS 'Instance',
       cntr_value            AS VALUE
FROM   MASTER.dbo.sysperfinfo
WHERE  OBJECT_NAME &lt;&gt; 'SQLServer:User Settable'


I'm proposing to replace it with:

SELECT REPLACE(RTRIM(OBJECT_NAME), 'SQLServer:', '') AS 'Object',
       RTRIM(counter_name)   AS 'Counter',
       RTRIM(instance_name)  AS 'Instance',
       cntr_value            AS VALUE
FROM   sys.dm_os_performance_counters
WHERE  OBJECT_NAME &lt;&gt; 'SQLServer:User Settable'


Which is faster and more compatible with everything past SQL 2000. There are no environments this will be used in that run SQL 2000 anymore, the earliest version the above code would be run using is SQL 2008. I've checked that the returned values are congruent and it works in the context of the rest of the code.

My question is: What are the differences between dm_os_performance_counters and master.dbo.sysperfinfo? Do I need to pull dm_os_performance_counters from the Master context?
What are the differences between dm_os_performance_counters and master.dbo.sysperfinfo?


sys.dm_os_performance_counters is the replacement for sysperfinfo.  Please see this BOL reference on the mapping of system tables to system views/DMVs.  You should be using sys.dm_os_performance_counters.


  Do I need to pull dm_os_performance_counters from the Master context?


No, DMVs (or system catalog views) can be accessed from the context of any database, provided you have the appropriate permissions to view the contents.  The below example for illustration purposes would be just fine:

use NonMasterDatabase;
go

select *
from sys.dm_os_performance_counters;