Cache HIT ratio problem

Hello

I am tring to figure out why our SQL server is a bit sluggish from
time to time.

It is running a dual XEON, with 2.5 GB RAM, and a fast SCSI I/O sub
system setup as follows.

OS, mirrored 2 drives
SQL DATA  16 HDD RAID 10
SQL LOG   4 HDD RAID 10
SQL tempdb 4 HDD RAID 10

OS = win 2003
SQL = sql2000 standard edition

dbcc showcontig shows me nothing special, it looks ok

I launch performacne monitor and add SQL server cache manager: hit
ratio

and it is constantly at 7% and never changes up or down, it is just
constant.

Can this be correct? and if so it sounds rather bad, we have a
handfull of large tables that are heavily used and enough RAM to hold
them all in RAM so I really do not understand why the cache hit ratio
is not higher.

Any hints would be great

rgds

Matt

It's not impossible - the Cache Manager cache hit ratio is the number
of hits on a cached query plan, not on a cached data page. If you want
to see the cached data page ratio, you need to look under Buffer
Manager - that should be as close to 100% as possible.

Still, 7% does sound rather low, unless users are executing very
different queries every time, so that a cached plan can't be reused.
You might want to use Profiler to trace activity on the server during a
sluggish period, and see exactly what is running slowly.

Simon

"Simon Hayes" <sql@hayes.ch> wrote in message news:<1114084817.050203.257840
@f14g2000cwb.googlegroups.com>...

> Still, 7% does sound rather low, unless users are executing very
> different queries every time, so that a cached plan can't be reused.
> You might want to use Profiler to trace activity on the server during a
> sluggish period, and see exactly what is running slowly.

ok thanks a lot for the insight, it turns out the buffer hits are very
high indeed I was looking at the wrong counter.

I also used profiler and found the code causing my problems, but I
really do not know how to write it more efficiantly, it does not use
cursors, it does not use temp tables.

any hints would be much apprecaited as to how I can speed this query
up.

CREATE procedure q_spr_ordvalmonitor

AS

declare  @maxpossible money,
@moneysofar money,
@moneysofar2 money

select  @maxpossible =
sum((orp.ordantal*orp.vb_pris)*(100-orp.rabatt1)/100)
from  orp
where  (orp.ordberlevdat = convert(varchar(8), getdate(), 112) or
orp.ordberlevdat = convert(varchar(8), dateadd(day, -1, getdate()),
112)) and
orp.ordradst < 50 and
orp.ftgnr <> '11000'

select  @moneysofar =
sum((orp.ordantal*orp.vb_pris)*(100-orp.rabatt1)/100)
from  orp
where  (orp.ordberlevdat = convert(varchar(8), getdate(), 112) or
orp.ordberlevdat = convert(varchar(8), dateadd(day, -1, getdate()),
112)) and
orp.ordradst = 50 and
orp.ftgnr <> '11000'

select  @moneysofar2 = sum(ft.faktradsumma)
from  ft
where  ft.faktdat = convert(varchar(8), getdate(), 112) and
ft.kundkategorikod <> 17

if @moneysofar IS NULL
set @moneysofar = 0

if @moneysofar2 IS NULL
set @moneysofar2 = 0

select   convert(numeric, @maxpossible) AS 'moneyremaining',
(convert(numeric, @moneysofar) + convert(numeric, @moneysofar2)) AS
'moneysofar'

rgds

It is used in some PHP code I wrote to display our current order
values.

Hi

To improve cache hits try starting with using qualified names. Adding owner
prefixes should help

http://msdn.microsoft.com/library/d...br />
4azp.asp

You may also want to look at:
http://support.microsoft.com/defaul...kb;en-us;325119

John

"Matt" <matt@fruitsalad.org> wrote in message
news:b609190f.0504241040.5cf4d561@posting.google.com...
> "Simon Hayes" <sql@hayes.ch> wrote in message
> news:<1114084817.050203.257840@f14g2000cwb.googlegroups.com>...
> 
>
> ok thanks a lot for the insight, it turns out the buffer hits are very
> high indeed I was looking at the wrong counter.
>
> I also used profiler and found the code causing my problems, but I
> really do not know how to write it more efficiantly, it does not use
> cursors, it does not use temp tables.
>
> any hints would be much apprecaited as to how I can speed this query
> up.
>
> CREATE procedure q_spr_ordvalmonitor
>
> AS
>
> declare  @maxpossible money,
>  @moneysofar money,
>  @moneysofar2 money
>
> select  @maxpossible =
> sum((orp.ordantal*orp.vb_pris)*(100-orp.rabatt1)/100)
> from  orp
> where  (orp.ordberlevdat = convert(varchar(8), getdate(), 112) or
>  orp.ordberlevdat = convert(varchar(8), dateadd(day, -1, getdate()),
> 112)) and
>  orp.ordradst < 50 and
>  orp.ftgnr <> '11000'
>
> select  @moneysofar =
> sum((orp.ordantal*orp.vb_pris)*(100-orp.rabatt1)/100)
> from  orp
> where  (orp.ordberlevdat = convert(varchar(8), getdate(), 112) or
>  orp.ordberlevdat = convert(varchar(8), dateadd(day, -1, getdate()),
> 112)) and
>  orp.ordradst = 50 and
>  orp.ftgnr <> '11000'
>
> select  @moneysofar2 = sum(ft.faktradsumma)
> from  ft
> where  ft.faktdat = convert(varchar(8), getdate(), 112) and
>  ft.kundkategorikod <> 17
>
> if @moneysofar IS NULL
> set @moneysofar = 0
>
> if @moneysofar2 IS NULL
> set @moneysofar2 = 0
>
> select   convert(numeric, @maxpossible) AS 'moneyremaining',
>  (convert(numeric, @moneysofar) + convert(numeric, @moneysofar2)) AS
> 'moneysofar'
>
> rgds
>
> It is used in some PHP code I wrote to display our current order
> values.

"John Bell" < jbellnewsposts@hotma
il.com> wrote in message news:< 426bf127$0$26335$db0
fefd9@n
ews.zen.co.uk>...
> Hi
>
> To improve cache hits try starting with using qualified names. Adding owne
r
> prefixes should help
>
> http://msdn.microsoft.com/library/d... />
a_4azp.asp
>
> You may also want to look at:
> http://support.microsoft.com/defaul...kb;en-us;325119


I read it and I added qualified names, once I got the correct counter
added, I am running an avg of 90% or more cache hits, the issue at
hand is that the below query seems to kill my I/O subsystem, as soon
as it runs my avg disk queue hits 100% and the entire SQL server gets
sluggish and slow for the duration of this query which lasts for about
20-30 seconds.

It seems to me like a fairly simple query and I just dont get why it
is so hard on the server, I have checked for table scans, indexes
troubles but it all seems ok, there are basically no table scans so I
am assuming the indexer are working as planned, but still the I/O goes
through the roof on this query.




>
 

Hi Matt

Look at profiler and see where the high reads/durations occur. You may
me missing suitable indexing.

Also

convert(varchar(8), ,..112) this can be char(8), but if you can change
(orp.ordberlevdat =3D convert(varchar(8), getdate(), 112) or
orp.ordberlevdat =3D convert(varchar(8), dateadd(day, -1, getdate()),
112))

to something like
orp.ordberlevdat > @date1
and orp.ordberlevdat < @date2

then you may improve the performance.

You may want to also try:

select  @moneysofar =3D
sum( CASE WHEN orp.ordradst =3D 50 THEN
(orp.ordantal*orp.vb_pris)=AD*(100-orp.rabatt1)/100 END)
@moneysofar =3D
sum( CASE WHEN orp.ordradst < 50 THEN
(orp.ordantal*orp.vb_pris)=AD*(100-orp.rabatt1)/100) END)
from  orp=20
WHERE  orp.ordradst <=3D 50

John

Matt (matt@fruitsalad.org)  writes:
> select  @maxpossible =
> sum((orp.ordantal*orp.vb_pris)*(100-orp.rabatt1)/100)
> from  orp
> where  (orp.ordberlevdat = convert(varchar(8), getdate(), 112) or
>   orp.ordberlevdat = convert(varchar(8), dateadd(day, -1, getdate()),
> 112)) and
>   orp.ordradst < 50 and
>   orp.ftgnr <> '11000'
>
> select  @moneysofar =
> sum((orp.ordantal*orp.vb_pris)*(100-orp.rabatt1)/100)
> from  orp
> where  (orp.ordberlevdat = convert(varchar(8), getdate(), 112) or
>   orp.ordberlevdat = convert(varchar(8), dateadd(day, -1, getdate()),
> 112)) and
>   orp.ordradst = 50 and
>   orp.ftgnr <> '11000'

I don't really see why this query would take so much power from
the machine, but then again I don't know how many rows you have
in your tables. Anyway, if the problem is with the access to orp,
then at least this should give you a 50% reduction.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

"John Bell" < jbellnewsposts@hotma
il.com> wrote in message news:<1114451201.445815.229010@l4
1g2000cwc.googlegroups.com>...
> Hi Matt
>
> Look at profiler and see where the high reads/durations occur. You may
> me missing suitable indexing.
>
> Also
>
> convert(varchar(8), ,..112) this can be char(8), but if you can change
> (orp.ordberlevdat =3D convert(varchar(8), getdate(), 112) or
>   orp.ordberlevdat =3D convert(varchar(8), dateadd(day, -1, getdate()),
> 112))
>
> to something like
> orp.ordberlevdat > @date1
> and orp.ordberlevdat < @date2
>
> then you may improve the performance.
>
> You may want to also try:
>
> select  @moneysofar =3D
> sum( CASE WHEN orp.ordradst =3D 50 THEN
> (orp.ordantal*orp.vb_pris)=AD*(100-orp.rabatt1)/100 END)
> @moneysofar =3D
> sum( CASE WHEN orp.ordradst < 50 THEN
> (orp.ordantal*orp.vb_pris)=AD*(100-orp.rabatt1)/100) END)
> from  orp=20
> WHERE

orp.ordradst <=3D 50

cooool that made a huge difference, thanks a lot, the query now runs
in sub 4 seconds.

rgds

Matt

Hi Matt

Did you do the date thing? That should also help.

Also remove the two if statements and do
select   convert(numeric,  ISNULL(@maxpossible,
0)) AS 'moneyremaining',
(convert(numeric,  ISNULL(@moneysofar))
 + convert(numeric,
 ISNULL(@moneysofar2)
)) AS
'moneysofar'

Look at changing your money data types to a decimial with fixed
precision.

John

Hi

Another thing to help would be to pass the values as output parameters!

John

John Bell  wrote:
> Hi Matt
>
> Did you do the date thing? That should also help.
>
> Also remove the two if statements and do
> select   convert(numeric,  ISNULL(@maxpossible,
0)) AS
 'moneyremaining',[co
lor=darkred]
>   (convert(numeric,  ISNULL(@moneysofar))
 + convert(numeric,
>  ISNULL(@moneysofar2)
)) AS
> 'moneysofar'
>
> Look at changing your money data types to a decimial with fixed
> precision.
>
> John[/color]