October 21, 2007 at 8:03 pm
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?
October 21, 2007 at 10:06 pm
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.
October 22, 2007 at 12:11 am
- 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
October 22, 2007 at 8:45 am
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?
October 22, 2007 at 11:58 am
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
October 22, 2007 at 9:03 pm
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
October 23, 2007 at 12:12 am
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
October 23, 2007 at 12:17 am
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
July 17, 2008 at 3:05 pm
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
July 18, 2008 at 12:17 am
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
July 18, 2008 at 8:23 am
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