Post reply

Are DMVs deprecated in 2014 SQL Instance

  • June 28, 2014 at 12:47 am

    #309488

    I'm SysAdmin on a SQL Instance which is 2014 version.

    I'm not able to get the results for my DMVs. I'm trying to find the PLE and cache hit ratio using below queries. Its not erroring out but its not giving any results.

    --PLE

    SELECT [object_name], [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy'

    --Cache Hit Ratio

    SELECT (A.cntr_value * 1.0 / B.cntr_value) * 100.0 as BufferCacheHitRatio,A.counter_name FROM sys.dm_os_performance_counters A

    JOIN (SELECT cntr_value,OBJECT_NAME FROM sys.dm_os_performance_counters WHERE counter_name ='Buffer Cache hit ratio'

    AND OBJECT_NAME like '%Buffer Manager%') B ON A.OBJECT_NAME = B.OBJECT_NAME

    WHERE A.counter_name = 'Buffer Cache hit ratio' AND A.OBJECT_NAME like '%Buffer Manager%'

  • June 28, 2014 at 6:29 am

    #1725647

    Quick question, what is your server collation?

  • June 28, 2014 at 7:04 am

    #1725650

    Collation: SQL_Latin1_General_CP1_CI_AS

    Version: Microsoft SQL Server 2014 - 12.0.2000.8 (X64)

  • June 28, 2014 at 7:22 am

    #1725652

    Only suggestion I have then is run this and look through the result set:

    SELECT [object_name], [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%'

    If you don't see it, perhaps something happened during the installation of SQL Server 2014.

  • June 28, 2014 at 7:29 am

    #1725653

    It should still be there since I found this on MSDN:

    http://msdn.microsoft.com/en-us/library/ms189628.aspx

  • June 28, 2014 at 1:50 pm

    #1725688

    Stupid question, I know, but you didn't happen to accidently change the font color to White, did you? If that's not it, then I'd reinstall the client tools as a first step.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 30, 2014 at 2:06 am

    #1725764

    I was handed this SQL Instance 2 days ago so I have no clue who installed SQL on this box. Anyhow, the below blog solved my issue.

    http://blogs.technet.com/b/pfelatam/archive/2011/08/08/sql-performance-counters-are-missing.aspx

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

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