SYS.SYSPERFINFO and sys.dm_os_performance_counters

Question

Post reply

SYS.SYSPERFINFO and sys.dm_os_performance_counters

akarczewska

Mr or Mrs. 500

Points: 535
More actions
July 13, 2010 at 10:07 am

#221210

Posted - 07/12/2010 : 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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

I just ran both queries in SQL Server 2008 R2 Express and they worked fine. What edition are you using?

_________________________________
seth delconte
http://sqlkeys.com

akarczewska Mr or Mrs. 500 Points: 535 More actions · Answer 2

akarczewska

Mr or Mrs. 500

Points: 535

July 13, 2010 at 12:02 pm

#1192259

Thank you for your reply. I am using SQL 2008.

akarczewska Mr or Mrs. 500 Points: 535 More actions · Answer 3

When I run the queries on SQL 2008 they complete all right but don’t return any results. I just read something in Book online about performance counters being disabled. I don’t know where to look for that. Any ideas?

seth delconte SSCertifiable Points: 6388 More actions · Answer 4

What edition and version of SQL Server 2008 are you using? R2 Developer, Standard, Express, etc...

_________________________________
seth delconte
http://sqlkeys.com

akarczewska Mr or Mrs. 500 Points: 535 More actions · Answer 5

akarczewska

Mr or Mrs. 500

Points: 535

July 13, 2010 at 12:27 pm

#1192272

Standard

seth delconte SSCertifiable Points: 6388 More actions · Answer 6

I ran both queries on SQL Server 2008 R2 Express AND SQL Server 2008 R2 Enterprise Evaluation editions. The queries worked fine, and returned results. Is Performance Monitor running while you are running these queries? I found this on a forum:

"If Perfmon (local or remote), or some other process who is reading the SQL Server counters is running while the SQL Server service account is being changed, the shared memory section which contains the counter values and which is created under the old service account is kept alive. The new service account doesn't have access to it. You should see an approriate error message in SQL Server errorlog. In order to fix this you need to close Perfmon (or whatever process is keeping a ref on the shared memory section) and restart SQL Server.

Thanks,

-Ivan"

From: http://www.bigresource.com/Tracker/Track-ms_sql-alb3IU6e/

_________________________________
seth delconte
http://sqlkeys.com