Buffer Cache Hit Ratio vs Memory Manager Cache Hit ratio

We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.
Hi
You would need Enterprise Edition of Windows and SQL Server if you want to
use all 8GB.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" wrote:

> We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
> trying ascertain whether or not upgrading to a OS that will see the full
> monty will help or redploying the assets would be a better option
> The Buffer Cache Hit Ratio - 99%
> The Memory Manager Cache Hit Ratio = 77%
> The Target and actual values for Total Memory and Buffer Pages are the same
> The memory pages/sec swings wildly, generally below 1 it goes up into the
> thousands.
> What is the difference between the two Cache Hit Ratio objects
> Note: Vendor supplied DB is horrendous in design.
|||Yes, we have EE of SQL running on 2k standard. We are contemplaing going to
a 2003 version that can support the 8GB.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> You would need Enterprise Edition of Windows and SQL Server if you want to
> use all 8GB.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Jeffrey K. Ericson" wrote:
|||Those are good questions; however, you will find that using Win2K AS or
Win2K3 EE not much different, but although AWE will momentarily ease memory
pressures, the added headaches it introduces will make the upgrades
short-lived. By going that route, you could extend the life of your system
for a year or two at best, but then you will be quickly running into the
limitations of the 32-bit architecture.
Since the OS and SQL Server licenses can be redeployed, and upgraded to
their 64-bit counterparts without charge, you could go ahead and upgrade
immediately but consider 64-bit solutions long term, IA64, not that silly
wanna-be, x64, EMT64 garbage.
It kind of depends on the usage. I would look at the SQL Server:Databases,
Transactions per second for each database, or the _Total instance, and the
SQL Server:General Statistics, Batch Requests per second to get an idea of
how busy your box is.
As for the two Cach hit Ratios: the Buffer Manager actually has five (5)
seperate memory managers associated with it...that is, the Buffer Pool. The
Data Buffer, which is just one of the memory managers, stores and manages
the data page cache and is what this counter is referring to. Whenever data
is requested to satisfy a query, if the data pages supporting that request
is currently resident in memory, you have a Data Buffer Cache Hit, otherwise
it is a miss and Disk I/O must be performed to load the requested pages.
You want a high cache hit, anything below 90% and you would be pretty
sluggish. Even at 98%, you are 10 times as inefficient than at 99%.
Now, the other cached items are the procedure plans in the PROCCACHE. The
Cache Manager, Cache Hit Ratio measures this and, actually, has multiple
instances, the _Total and one for each type of execution object: Procedures,
Ad-Hoc, Execution Contexts, Triggers, Cursors, Misc. Normalized Trees, etc.,
etc., etc. You should look at all of the instances as well as the _Total,
which you are quoting a figure of 77%. The instances can clue you in on
whether you have a memory limitation issue or just bad coding.
Now, let this be known, if you go the AWE route, it will be mostly for
additional Data Page buffering, not the Proc Cache. Welcome to the
limitations of the 32-bit arcitecture. No matter what you do, you are
limited to the 2 GB/3 GB lower memory region for Proc Cache storage. Now,
it is possible to offload some of the Data Buffer to the AWE space thereby
leaving more lower memory region for a larger than currently allocated Proc
Cache, but once you go AWE, SQL Server looses some of its flexibility and
dynamicity. Chances are your Proc Cache will not be reallocated much. In
fact, whenever there are memory pressures, the proc cache is usually the
first thing that is flushed or paged out to the swap file.
So, long term, you have two choices: go multi-instanced, either on the same
server, or by installing multiple servers, or took a real good look at IA64,
64-bit platforms. Dell has two low-entry versions currently, their 3250 and
7250 series.
Spend some time with the DBCC MEMORYSTATUS and DBCC PROCCACHE. These will
give you a lot of insight into how your installations is partitioning it
various memory allocations.
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;271624
Hope this helps.
Sincerely,
Anthony Thomas

"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:CAB56205-326F-41A1-8E42-20985CBE4CFF@.microsoft.com...
We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.
|||Do u have the PAE switch and AWE switch enabled ?
If you have done the required configuration, I dont see how the OS is not
able to see
beyond 3GB.
Thanks,
Gopi
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...[vbcol=seagreen]
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
|||Found this in another Post. Have you applied SP4 ?
See the link below for the KB article on this issue. Microsoft are still
working on the problem.
Nick Colebourn (MCDBA)
DBA
United Coop Ltd
"Dimitar Dimitrov" wrote:

> The configuration is:
> OS: Windows 2003 Enterprise Edition
> RAM: 6GB
> boot.ini switches : /fastdetect /PAE /3GB /USERVA=3030
> SQL 2000 Enterprise Edition SP3a (awe enabled; min server memory (MB) =
> max server memory (MB) = 5120)
> Before installing SP4 SQL server uses configured amount of memory
> (5120MB).
> After installing SP4 it doesn't utilize more than 3GB of memory the SQL
> Server Logs reports "Warning: unable to allocate 'min server memory' of
> 5120MB."
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...[vbcol=seagreen]
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
|||Those are good questions; however, you will find that using Win2K AS or
Win2K3 EE not much different, but although AWE will momentarily ease memory
pressures, the added headaches it introduces will make the upgrades
short-lived. By going that route, you could extend the life of your system
for a year or two at best, but then you will be quickly running into the
limitations of the 32-bit architecture.
Since the OS and SQL Server licenses can be redeployed, and upgraded to
their 64-bit counterparts without charge, you could go ahead and upgrade
immediately but consider 64-bit solutions long term, IA64, not that silly
wanna-be, x64, EMT64 garbage.
It kind of depends on the usage. I would look at the SQL Server:Databases,
Transactions per second for each database, or the _Total instance, and the
SQL Server:General Statistics, Batch Requests per second to get an idea of
how busy your box is.
As for the two Cach hit Ratios: the Buffer Manager actually has five (5)
seperate memory managers associated with it...that is, the Buffer Pool. The
Data Buffer, which is just one of the memory managers, stores and manages
the data page cache and is what this counter is referring to. Whenever data
is requested to satisfy a query, if the data pages supporting that request
is currently resident in memory, you have a Data Buffer Cache Hit, otherwise
it is a miss and Disk I/O must be performed to load the requested pages.
You want a high cache hit, anything below 90% and you would be pretty
sluggish. Even at 98%, you are 10 times as inefficient than at 99%.
Now, the other cached items are the procedure plans in the PROCCACHE. The
Cache Manager, Cache Hit Ratio measures this and, actually, has multiple
instances, the _Total and one for each type of execution object: Procedures,
Ad-Hoc, Execution Contexts, Triggers, Cursors, Misc. Normalized Trees, etc.,
etc., etc. You should look at all of the instances as well as the _Total,
which you are quoting a figure of 77%. The instances can clue you in on
whether you have a memory limitation issue or just bad coding.
Now, let this be known, if you go the AWE route, it will be mostly for
additional Data Page buffering, not the Proc Cache. Welcome to the
limitations of the 32-bit arcitecture. No matter what you do, you are
limited to the 2 GB/3 GB lower memory region for Proc Cache storage. Now,
it is possible to offload some of the Data Buffer to the AWE space thereby
leaving more lower memory region for a larger than currently allocated Proc
Cache, but once you go AWE, SQL Server looses some of its flexibility and
dynamicity. Chances are your Proc Cache will not be reallocated much. In
fact, whenever there are memory pressures, the proc cache is usually the
first thing that is flushed or paged out to the swap file.
So, long term, you have two choices: go multi-instanced, either on the same
server, or by installing multiple servers, or took a real good look at IA64,
64-bit platforms. Dell has two low-entry versions currently, their 3250 and
7250 series.
Spend some time with the DBCC MEMORYSTATUS and DBCC PROCCACHE. These will
give you a lot of insight into how your installations is partitioning it
various memory allocations.
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;271624
Hope this helps.
Sincerely,
Anthony Thomas

"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:CAB56205-326F-41A1-8E42-20985CBE4CFF@.microsoft.com...
We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.
|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek
|||How many transactions per second and/or batch requests per second does your
installation produce?
Ours pushes nearly 1500 hundred of both/either quite often. This coupled
with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
space quite heavily.
We've been piloting a 64-bit system for a while now and are getting ready to
make the conversion. Dropping AWE altogether and going back to Dynamic
Memory management for all of the memory managers including the PROC CACHE
was probably the most compelling reason for the push to convert.
Glad to hear that you haven't begun to hit those limits yet and that it is
still working out for your installation(s).
Sincerely,
Anthony Thomas

"Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek
|||The profile of our system is very different indeed. We have quite a lot of
data, 200+GB, biggest table has about 80mln records, and it is mostly
static. In this scenario, AWE, /pae and /3gb seem to work just fine. But now
I do see what you mean. AFAIK, AWE memory can be used only by data cache, so
it's pretty much limited indeed. Thanks for the info!
Regards
Wojtek
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23Wl4GcIaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> How many transactions per second and/or batch requests per second does
> your
> installation produce?
> Ours pushes nearly 1500 hundred of both/either quite often. This coupled
> with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
> space quite heavily.
> We've been piloting a 64-bit system for a while now and are getting ready
> to
> make the conversion. Dropping AWE altogether and going back to Dynamic
> Memory management for all of the memory managers including the PROC CACHE
> was probably the most compelling reason for the push to convert.
> Glad to hear that you haven't begun to hit those limits yet and that it is
> still working out for your installation(s).
> Sincerely,
>
> Anthony Thomas
>
> --
> "Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
> news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> What headaches do you mean? We're using AWE for a few years, and I have
> not
> had any headaches because of that The only headache could be hardware
> and/or drivers not compatible with AWE.
> Best regards
> Wojtek
>