Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 3 down vote accepted

The difference between sys.sysperfinfo and sys.dm_os_performance_counters are not that major meaning that sys.sysperfinfo is a compatibility view and is depreciated (for backward compatibility). So, sys.dm_os_performance_counters just a replacement for sys.sysperfinfo for newer versions of SQL Server.

So its a good idea for transitioning from compatibility view to DMV's (as you mentioned that the script won't be used for any SQL 2000 servers).

DMV's can be run for any databases if you have required permissions e.g. VIEW SERVER STATE permission to run sys.dm_os_performance_counters

Reference : QUERYING PERFORMANCE COUNTERS IN SQL SERVER for an excellent script to make sense out of the performance counters.

share|improve this answer

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.