http://www.sqlservercentral.com/blogs/stratesql/2012/11/22/lost-in-translation-deprecated-system-tables-sysperfinfo/

Printed 2014/07/25 09:24PM

Lost in Translation – Deprecated System Tables – sysperfinfo

By StrateSQL, 2012/11/22

4957867646_2f2478fd69_m5 This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objectsYou can find a list of all of the deprecated views in the introduction post.

The compatibility view sysperfinfo returns rows for all of the internal SQL Server performance counters.  Theses are the same counters that are available through Windows System Monitor.

The catalog view sysperfinfo is replaced by the dynamic management view sys.dm_os_performance_counters.  The output for sys.dm_os_performance_counters is the same as that of sysperfinfo.

Query Via sysperfinfo

At it’s basic level, sysperfinfo is fairly simple compatibility view.  There is a column for the name of the counters and the object it is associated with.  It also includes the counter value and a type for the counter.  A query to retrieve this information would be similar to the one in Listing 1.


--Listing 1 – Query for sys.sysperfinfo

SELECT object_name
, counter_name
, instance_name
, cntr_value
, cntr_type
FROM sysperfinfo

Query via sys.dm_os_performance_counters

When it comes to things changing and staying the same, the differences between sysperfinfo and sys.dm_os_performance_counters are pretty much the poster child.  Transitioning between the compatibility view and the dynamic management view is just the names of the objects.  Everything else is the same, resulting in a query for the dynamic management view that is similar to the one in Listing 2


--Listing 2 – Query for sys.dm_os_performance_counters

SELECT  object_name
, counter_name
, instance_name
, cntr_value
, cntr_type
FROM sys.dm_os_performance_counters

Summary

In this post, we compared the compatibility view sysperfinfo with the dynamic management view sys.dm_os_performance_counters.  The difference in this case is only the name of the base object, a change to the name of the view transitions any application from deprecated to supported code..  After reading all of this, do you see any reason to continue using sysperfinfo?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

  1. Lost in Translation – Deprecated System Tables – sysmembers
  2. Lost in Translation – Deprecated System Tables – sysopentapes
  3. Lost in Translation – Deprecated System Tables – sysmessages


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.