This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects. You 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
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?