Post reply

SYS.SYSPERFINFO and sys.dm_os_performance_counters

  • 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

  • July 13, 2010 at 12:00 pm

    #1192258

    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

  • July 13, 2010 at 12:02 pm

    #1192259

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

  • July 13, 2010 at 12:18 pm

    #1192265

    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?

  • July 13, 2010 at 12:22 pm

    #1192268

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

    _________________________________
    seth delconte
    http://sqlkeys.com

  • July 13, 2010 at 12:27 pm

    #1192272

    Standard

  • July 14, 2010 at 8:04 am

    #1192838

    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

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

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