SYS.SYSPERFINFO and sys.dm_os_performance_counters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

SYS.SYSPERFINFO and sys.dm_os_performance_counters

Author

Topic

AgaK
Yak Posting Veteran

86 Posts

Posted - 2010-07-12 : 16:50:37

Hi,

I am looking for the SQL 2008 version of the following 2 views: SYS.SYSPERFINFO and sys.dm_os_performance_counters

I have very usefull queries that work in SQL 2005 but not 2008.

Thanks
Ak

--Buffer Manager
SELECT object_name,counter_name,cntr_value,(cntr_value*8)/1024 as inMB
FROM SYS.SYSPERFINFO WHERE
OBJECT_NAME LIKE '%:BUFFER MANAGER%' AND
(COUNTER_NAME like '%TARGET PAGES%' OR
COUNTER_NAME like '%TOTAL PAGES%' OR
COUNTER_NAME like '%DATABASE PAGES%' OR
COUNTER_NAME like '%STOLEN PAGES%' OR
COUNTER_NAME like '%FREE PAGES%')
ORDER BY cntr_value DESC

select (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [Buffer Cache Hit Ratio]
from (select * from sys.dm_os_performance_counters
where counter_name like '%Buffer cache hit ratio%') a
join
(select * from sys.dm_os_performance_counters
where counter_name like '%Buffer cache hit ratio base%') b
on a.object_name = b.object_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources