find % CPU from dm_os_performance_counters

  • Hi ,

    Iam collecting a baseline report for one of the sql server instance, where i have only instance level access, dont have access to run the perfmon. Hence i collected details from the DMV - but iam getting values like 1023,2010, some times 0 , 1. How to get the correct percentage of CPU utilised by sql server from this DMV.

    SQLServer:Resource Pools CPU usage %

    SELECT *

    FROM [master].[sys].[dm_os_performance_counters]

    WHERE

    ([object_name] = 'SQLServer:Resource Pool Stats' AND [counter_name] = 'CPU usage %')

    and instance_name ='default'

    Thanks

    GKRISH

  • Sorry this is a old post few months old. Thought I would post the answer in case anyone else ends up here due to a google search.

    This page has a good explanation of how to query this dmv : http://rtpsqlguy.wordpress.com/2009/08/11/sys-dm_os_performance_counters-explained/

    I would try something like:

    select perfCount.object_name, perfCount.counter_name,

    CASE WHEN perfBase.cntr_value = 0

    THEN 0

    ELSE (CAST(perfCount.cntr_value AS FLOAT) / perfBase.cntr_value) * 100

    END AS cntr_Value

    from

    (select * from sys.dm_os_performance_counters

    where object_Name = 'SQLServer:Resource Pool Stats'

    and counter_name = 'CPU usage %' ) perfCount

    inner join

    (select * from sys.dm_os_performance_counters

    where object_Name = 'SQLServer:Resource Pool Stats'

    and counter_name = 'CPU usage % base') perfBase

    on perfCount.Object_name = perfBase.object_name

    Blog: http://crazyemu.wordpress.com/
    Twit: @crazySQL

  • You can also go for a third party tool for the same purpose which will generate a comprehensive report of CPU usage disk space etc which will be helpful to improve the productivity of SQL server !!!!!

  • "You can also go for a third party tool for the same purpose"

    Why? SQL Server has some reporting built into it. 😎

    And can you be certain that the tool you've selected accurately reports on the problem?

    I like the other answer that gives me a script more than a tool because you get to dig into what's going on. As soon as the tool company raises prices to where you can't buy them or it goes out of business, you're out of business. Have to take the time to learn these internals. More I look around, the more I see folks dependent on tools and not understanding what goes on underneath. It's a big reason why I love this site. We dig in here.

Viewing 4 posts - 1 through 3 (of 3 total)

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