Cache Hit Ratio Problem

Sorry, this is a repost of a few weeks ago. The thread died without any
responses.
=====
I have a customer using SQL Server 2000 Enterprise Edition on two clustered
servers (Windows 2003 SP1) with the following spec (times two):
4 x Intel Xeon Processors @. 2.7GHz
3.5GB RAM
36GB Local Hard Drives
175GB of SAN space for Data and log files
The database is around 2.5 GB.
I have a Cache Hit Ratio that is static at 70. I've never seen a Cache Hit
Ratio this low on any other customers let alone one that doesn't change.
It doesn't fluctuate at all. It has a minimum of 70, an average of 70, and
a maximum of 70 over a 24 hour period with more than 240 user connections.
This seems strange to me. Is there a setting that caps the cache hit ratio?
I would expect it to be > 90.
Any comments or suggestions would be great.
Results of DBCC MEMORYSTATUS
Buffer Distribution Buffers
-- --
Stolen 5444
Free 936
Procedures 37946
Inram 0
Dirty 6306
Kept 0
I/O 0
Latched 700
Other 155172
Buffer Counts Buffers
-- --
Commited 206504
Target 206504
Hashed 162178
InternalReservation 1214
ExternalReservation 47
Min Free 536
Visible 206504
Procedure Cache Value
-- --
TotalProcs 10694
TotalPages 37946
InUsePages 14862
Dynamic Memory Manager Buffers
-- --
Stolen 43379
OS Reserved 976
OS Committed 954
OS In Use 948
General 4588
QueryPlan 37474
Optimizer 993
Utilities 17
Connection 995
Global Memory Objects Buffers
-- --
Resource 2398
Locks 135
XDES 73
SQLCache 1536
Replication 2
LockBytes 2
ServerGlobal 32
Query Memory Objects Value
-- --
Grants 1
Waiting 0
Available (Buffers) 139633
Maximum (Buffers) 139691
Optimization Queue Value
-- --
Optimizing 1
Waiting 0
Available 31
Maximum 32
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:OP2AJGYtGHA.4968@.TK2MSFTNGP03.phx.gbl...
> Sorry, this is a repost of a few weeks ago. The thread died without any
> responses.
> =====> I have a customer using SQL Server 2000 Enterprise Edition on two
> clustered
> servers (Windows 2003 SP1) with the following spec (times two):
> 4 x Intel Xeon Processors @. 2.7GHz
> 3.5GB RAM
> 36GB Local Hard Drives
> 175GB of SAN space for Data and log files
> The database is around 2.5 GB.
> I have a Cache Hit Ratio that is static at 70. I've never seen a Cache
> Hit
> Ratio this low on any other customers let alone one that doesn't change.
> It doesn't fluctuate at all. It has a minimum of 70, an average of 70,
> and
> a maximum of 70 over a 24 hour period with more than 240 user connections.
> This seems strange to me. Is there a setting that caps the cache hit
> ratio?
> I would expect it to be > 90.
> Any comments or suggestions would be great.
> Results of DBCC MEMORYSTATUS
> Buffer Distribution Buffers
> -- --
> Stolen 5444
> Free 936
> Procedures 37946
> Inram 0
> Dirty 6306
> Kept 0
> I/O 0
> Latched 700
> Other 155172
> Buffer Counts Buffers
> -- --
> Commited 206504
> Target 206504
> Hashed 162178
> InternalReservation 1214
> ExternalReservation 47
> Min Free 536
> Visible 206504
Your total buffer pool is 206504 pages (1613MB). Of that you have 162178
pages (1267MB) of data and indexes cached, this is about half the total size
of your database. And 37946 pages (296MB) of pocedure plans cached.
So off the bat a few observations:
Apparently you're not running with the 3GB switch. This would allow the
buffer pool to grow by an extra gigabyte, approaching the total size of your
database. This should help.
Your procedure cache is large, suggesting a high volume of non-shared SQL.
This wastes memory and burns CPU compiling plans. Address this in the
application by using static. parameterized SQL instead of Ad-Hoc SQL. Run
the profiler to see all the query traffic and identify common
non-parameterized queries.
Even then, your poor cache-hit ratio suggests that you are accessing all
parts of your database all the time. Perhaps you are causing a high number
of table scans of a very large table. Run profiler to find your most
expensive queries (most Reads) and see if you can add some indexes or
otherwise tweak the physical database design to make these queries cheaper.
David|||70 seems indeed low. The non-variation *could* be explained if you are prior to sp3. Prior to sp3,
cache hit ratio was since SQL Server was restarted. With sp3, it is average from about 2000-3000
samples.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:OP2AJGYtGHA.4968@.TK2MSFTNGP03.phx.gbl...
> Sorry, this is a repost of a few weeks ago. The thread died without any responses.
> =====> I have a customer using SQL Server 2000 Enterprise Edition on two clustered
> servers (Windows 2003 SP1) with the following spec (times two):
> 4 x Intel Xeon Processors @. 2.7GHz
> 3.5GB RAM
> 36GB Local Hard Drives
> 175GB of SAN space for Data and log files
> The database is around 2.5 GB.
> I have a Cache Hit Ratio that is static at 70. I've never seen a Cache Hit
> Ratio this low on any other customers let alone one that doesn't change.
> It doesn't fluctuate at all. It has a minimum of 70, an average of 70, and
> a maximum of 70 over a 24 hour period with more than 240 user connections.
> This seems strange to me. Is there a setting that caps the cache hit ratio?
> I would expect it to be > 90.
> Any comments or suggestions would be great.
> Results of DBCC MEMORYSTATUS
> Buffer Distribution Buffers
> -- --
> Stolen 5444
> Free 936
> Procedures 37946
> Inram 0
> Dirty 6306
> Kept 0
> I/O 0
> Latched 700
> Other 155172
> Buffer Counts Buffers
> -- --
> Commited 206504
> Target 206504
> Hashed 162178
> InternalReservation 1214
> ExternalReservation 47
> Min Free 536
> Visible 206504
> Procedure Cache Value
> -- --
> TotalProcs 10694
> TotalPages 37946
> InUsePages 14862
> Dynamic Memory Manager Buffers
> -- --
> Stolen 43379
> OS Reserved 976
> OS Committed 954
> OS In Use 948
> General 4588
> QueryPlan 37474
> Optimizer 993
> Utilities 17
> Connection 995
> Global Memory Objects Buffers
> -- --
> Resource 2398
> Locks 135
> XDES 73
> SQLCache 1536
> Replication 2
> LockBytes 2
> ServerGlobal 32
> Query Memory Objects Value
> -- --
> Grants 1
> Waiting 0
> Available (Buffers) 139633
> Maximum (Buffers) 139691
>
> Optimization Queue Value
> -- --
> Optimizing 1
> Waiting 0
> Available 31
> Maximum 32
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23ZvbPRZtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> 70 seems indeed low. The non-variation *could* be explained if you are
> prior to sp3. Prior to sp3, cache hit ratio was since SQL Server was
> restarted. With sp3, it is average from about 2000-3000 samples.
>
I just checked the @.@.version and it seems to be correct. Here are the
results...
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)|||>
> Your total buffer pool is 206504 pages (1613MB). Of that you have 162178
> pages (1267MB) of data and indexes cached, this is about half the total
> size of your database. And 37946 pages (296MB) of pocedure plans cached.
> So off the bat a few observations:
> Apparently you're not running with the 3GB switch. This would allow the
> buffer pool to grow by an extra gigabyte, approaching the total size of
> your database. This should help.
Thank you, that is very helpful. I will have them change it.
> Your procedure cache is large, suggesting a high volume of non-shared SQL.
> This wastes memory and burns CPU compiling plans. Address this in the
> application by using static. parameterized SQL instead of Ad-Hoc SQL. Run
> the profiler to see all the query traffic and identify common
> non-parameterized queries.
>
I assume that stored procedures would solve this problem, right? Also, how
long is a parameterized query kept before it's discarded?
> Even then, your poor cache-hit ratio suggests that you are accessing all
> parts of your database all the time. Perhaps you are causing a high
> number of table scans of a very large table. Run profiler to find your
> most expensive queries (most Reads) and see if you can add some indexes
> or otherwise tweak the physical database design to make these queries
> cheaper.
>
Again, thank you, that is very helpful.
> David
>|||"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:OPP5hBatGHA.3264@.TK2MSFTNGP03.phx.gbl...
> >
>> Your total buffer pool is 206504 pages (1613MB). Of that you have 162178
>> pages (1267MB) of data and indexes cached, this is about half the total
>> size of your database. And 37946 pages (296MB) of pocedure plans cached.
>> So off the bat a few observations:
>> Apparently you're not running with the 3GB switch. This would allow the
>> buffer pool to grow by an extra gigabyte, approaching the total size of
>> your database. This should help.
> Thank you, that is very helpful. I will have them change it.
>> Your procedure cache is large, suggesting a high volume of non-shared
>> SQL. This wastes memory and burns CPU compiling plans. Address this in
>> the application by using static. parameterized SQL instead of Ad-Hoc SQL.
>> Run the profiler to see all the query traffic and identify common
>> non-parameterized queries.
> I assume that stored procedures would solve this problem, right?
Usually, yes, unless the procedure used dynamic SQL.
>Also, how long is a parameterized query kept before it's discarded?
Until it's forced out by other plans, so you can keep a lot of plans cached
that never get reused.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23QiTVXatGHA.3552@.TK2MSFTNGP03.phx.gbl...
>> I assume that stored procedures would solve this problem, right?
> Usually, yes, unless the procedure used dynamic SQL.
>
Thanks.
>>Also, how long is a parameterized query kept before it's discarded?
> Until it's forced out by other plans, so you can keep a lot of plans
> cached that never get reused.
> David
>
Thanks.|||> I just checked the @.@.version and it seems to be correct. Here are the results...
Nope. That service pack number returned by @.@.version is the OS service pack. Your build number say
that you are on SQL Server service pack 4.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:%23zXOU8ZtGHA.3240@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23ZvbPRZtGHA.2260@.TK2MSFTNGP03.phx.gbl...
>> 70 seems indeed low. The non-variation *could* be explained if you are prior to sp3. Prior to
>> sp3, cache hit ratio was since SQL Server was restarted. With sp3, it is average from about
>> 2000-3000 samples.
> I just checked the @.@.version and it seems to be correct. Here are the results...
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O2cQGeltGHA.3964@.TK2MSFTNGP04.phx.gbl...
>> I just checked the @.@.version and it seems to be correct. Here are the
>> results...
> Nope. That service pack number returned by @.@.version is the OS service
> pack. Your build number say that you are on SQL Server service pack 4.
>
So can I assume that the Cache Hit Ratio fix is included in SP4 if it was
fixed in SP3?|||Yep. Note that the fix is only how the value is calculated...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:eEtTokxtGHA.5076@.TK2MSFTNGP04.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O2cQGeltGHA.3964@.TK2MSFTNGP04.phx.gbl...
>> I just checked the @.@.version and it seems to be correct. Here are the results...
>> Nope. That service pack number returned by @.@.version is the OS service pack. Your build number
>> say that you are on SQL Server service pack 4.
> So can I assume that the Cache Hit Ratio fix is included in SP4 if it was fixed in SP3?
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:um12pK0tGHA.3552@.TK2MSFTNGP03.phx.gbl...
> Yep. Note that the fix is only how the value is calculated...
>
Thanks