Performance Information using T-SQL

  • How can I get the amount of available memory, and the amount of memory SQL Server is using use T-SQL? Is there a DMV that is useful for this.

    Also wonder how I can convert he "Buffer cache hit ratio" in the sys.dm_os_performance_counters into an actual % value like 95%. When I run this the following command:

    select * from sys.dm_os_performance_counters

    where counter_name = 'Buffer cache hit ratio'

    I get a cntr_value = 1472 when I run this on my instance of sql server running on my laptop. What does this mean?

  • Download performance dashboard from Microsoft website which will give you some more information about memory. use performance counter Total & Traget Server memory. i am not sure if DMV will be helpfull, as in turn this DMV's gets those information from this counters.

    I hope this helps.

  • - Indeed performance_dashboard is a great resource.

    - http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=107424 has more info regarding the pct calculation.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • The performance dashboard is an excellent suggestion. But those cache hit ratios (Requests and Sessions) I think only account for the active sessions and requests, not the overall pictures. The dashboard uses the

    following formula to calculate the buffer cache ration:

    (logical reads - reads) / logicial reads using the sys.dm_exec_requests and sys.dm_exec_system DMVs. Does the "read" column in these views mean physical reads?

    What I would like to know is what does the cntr_value represent when you run the following:

    select * from sys.dm_os_performance_counters

    where counter_name = 'Buffer cache hit ratio'

    And how do you turn it into a percentage?

  • cntr_value is "Percentage of pages that were found in the buffer pool without having to incur a read from disk."

    the query that you are running "select * from sys.dm_os_performance_counters where counter_name = 'Buffer cache hit ratio' " which behind the scene is making windows calls for Performance counter, which is "SQLServer:Buffer Manager"

    MSDN = http://msdn2.microsoft.com/en-us/library/ms187743.aspx does n't give more information on this counter "cnter_value".

    I will rely on performance counter which gives me percentage like for e.g. 99 or 95 rather than i rely on the DMV's counter. I am not sure if this helps

  • I think I now see how this works. I think you have too divide the counter / base, like so:

    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 = 'Buffer cache hit ratio') a

    join

    (select * from sys.dm_os_performance_counters

    where counter_name = 'Buffer cache hit ratio base') b

    on a.object_name = b.object_name

  • This is the query I start with: 🙂

    SELECT dmv_cntr.[object_name]

    , dmv_cntr.counter_name

    , dmv_cntr.instance_name

    , dmv_cntr.cntr_value

    , dmv_cntr.cntr_type

    , CASE dmv_cntr.cntr_type

    WHEN 537003264 -- This counter is expressed as a ratio and requires calculation. (Sql2005)

    THEN CONVERT(FLOAT,dmv_cntr.cntr_value) /coalesce(RatioBaseValue.cntr_RatioBaseValue,-1)

    ELSE dmv_cntr.cntr_value -- The values of the other counter types are already calculated.

    END as Calculated_Counter_value

    , CASE dmv_cntr.cntr_type

    WHEN 537003264 -- This counter is expressed as a ratio and requires calculation. (Sql2005)

    THEN RatioBaseValue.cntr_RatioBaseValue

    ELSE 1 -- The values of the other counter types are already calculated.

    END as cntr_RatioBaseValue

    FROM sys.dm_os_performance_counters dmv_cntr

    left join ( SELECT CASE cntr_value

    WHEN 0 THEN 1

    ELSE cntr_value

    END as cntr_RatioBaseValue

    , counter_name

    , [object_name]

    , instance_name

    , cntr_type

    FROM sys.dm_os_performance_counters

    WHERE cntr_type = 1073939712

    ) RatioBaseValue

    on dmv_cntr.counter_name + ' '= SUBSTRING(RatioBaseValue.counter_name, 1, PATINDEX('% Base%', RatioBaseValue.counter_name))

    AND dmv_cntr.[object_name] = RatioBaseValue.[object_name]

    AND dmv_cntr.instance_name = RatioBaseValue.instance_name

    WHERE dmv_cntr.cntr_type <> 1073939712 -- Don't display the divisors. (RatioBaseValue)

    -- and dmv_cntr.instance_name like '%key%'

    order by 1,2,3

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • hehe, nice to see that the code html-tag does its work in the forums.:w00t:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I found a small issue with this report

    the procedure [MS_PerfDashboard].[usp_Main_GetSessionInfo] needs to be updated as the maximum time in milliseconds a datediff can process is 24 days, 20 hours, 31 minutes and 23.647 seconds.

    If you have people logged in longer then that time the report fails and you cannot get to anything. Here is the Fix, I use seconds instead and then multiply it by 1000 to turn it into milliseconds.... I figure after 24 days if you are still logged in you no longer care about the exact milliseconds you are logged in for. Seconds in datediff allows for 68 years of login time.

    USE [msdb]

    GO

    /****** Object: StoredProcedure [MS_PerfDashboard].[usp_Main_GetSessionInfo] Script Date: 07/17/2008 15:03:49 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER procedure [MS_PerfDashboard].[usp_Main_GetSessionInfo]

    as

    begin

    select count(*) as num_sessions,

    sum(convert(bigint, s.total_elapsed_time)) as total_elapsed_time,

    sum(convert(bigint, s.cpu_time)) as cpu_time,

    sum(convert(bigint, s.total_elapsed_time)) - sum(convert(bigint, s.cpu_time)) as wait_time,

    --sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

    sum(convert(bigint,

    case when datediff(ss, login_time,getDate()) >= 2145623

    then convert(bigint, datediff(ss, login_time,getDate())) * 1000

    else convert(bigint, datediff(ms, login_time,getDate()))

    end)

    ) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

    case when sum(s.logical_reads) > 0 then (sum(s.logical_reads) - isnull(sum(s.reads), 0)) / convert(float, sum(s.logical_reads))

    else NULL

    end as cache_hit_ratio

    from sys.dm_exec_sessions s

    where s.is_user_process = 0x1

    end


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • Indeed. The bug is known for a while now.

    It would be nice if MS fixed that at least for the new downloads !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • 1) You can use WMI scripting to easily access perf mon counters, IIRC.

    2) The undocumented (search online for information) dbcc memorystatus offers a wealth of information about sql memory usage/allocations.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 11 posts - 1 through 10 (of 10 total)

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