Big Buffer cache hit Ratio
in [Server Configuration]
|
Prev: SQL Server 2008 Standard Per Device CALs question
Next: SQL 2000 to SQL 2005 Upgrade. Upgrade advisor Errors
From: iv69 on 14 Jan 2010 10:50 I am using sys.dm_os_performance_counters and sys.sysperfinfo to obtain information for Buffer cache hit Ratio but I am very surpriced to se the values of 3000, 6000, 2888 and similar all greater than 1000 this is common behaviour on all my SQL 2005 servers - production and developement, On SQL server 2008 I see values that seems to be normal =55,66 As I know this value should be percentage so it sould be under 100. Any help will be apperiated. Ivan
From: Uri Dimant on 17 Jan 2010 04:37 Can you post the output of select objtype, count(*) as number_of_plans, sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs, avg(usecounts) as avg_use_count from sys.dm_exec_cached_plans group by objtype ----------------------------------- dbcc memorystatus "iv69" <ivan.tsatsarov(a)gmail.com> wrote in message news:d11bd0fb-ec58-44b4-bc2e-f09157ed90cd(a)u41g2000yqe.googlegroups.com... >I am using sys.dm_os_performance_counters and sys.sysperfinfo > to obtain information for Buffer cache hit Ratio > > but I am very surpriced to se the values of 3000, 6000, 2888 and > similar all greater than 1000 > this is common behaviour on all my SQL 2005 servers - production and > developement, > > On SQL server 2008 I see values that seems to be normal =55,66 > > As I know this value should be percentage so it sould be under 100. > > Any help will be apperiated. > Ivan
From: Roger Newton on 2 Feb 2010 14:57
I prefer using the Page Life Expectancy counter for monitoring buffer cache, the value of this counter should remain above 300 which simply means data will remain in the cache for at least 5 minutes if not used. "Uri Dimant" wrote: > Can you post the output of > select objtype, > > count(*) as number_of_plans, > > sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs, > > avg(usecounts) as avg_use_count > > from sys.dm_exec_cached_plans > > group by objtype > > ----------------------------------- > > dbcc memorystatus > > "iv69" <ivan.tsatsarov(a)gmail.com> wrote in message > news:d11bd0fb-ec58-44b4-bc2e-f09157ed90cd(a)u41g2000yqe.googlegroups.com... > >I am using sys.dm_os_performance_counters and sys.sysperfinfo > > to obtain information for Buffer cache hit Ratio > > > > but I am very surpriced to se the values of 3000, 6000, 2888 and > > similar all greater than 1000 > > this is common behaviour on all my SQL 2005 servers - production and > > developement, > > > > On SQL server 2008 I see values that seems to be normal =55,66 > > > > As I know this value should be percentage so it sould be under 100. > > > > Any help will be apperiated. > > Ivan > > > . > |