Cache HIT ratio problem

[Matt]

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

[Simon Hayes]

Re: Cache HIT ratio problem

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

[Matt]

Re: Cache HIT ratio problem

"Simon Hayes" <sql@hayes.ch > wrote in message news:<111408481 7.050203.257840 @f14g2000cwb.go oglegroups.com> ...
[color=blue]
> 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.[/color]

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_ordvalmon itor

AS

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

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

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

select @moneysofar2 = sum(ft.faktrads umma)
from ft
where ft.faktdat = convert(varchar (8), getdate(), 112) and
ft.kundkategori kod <> 17

if @moneysofar IS NULL
set @moneysofar = 0

if @moneysofar2 IS NULL
set @moneysofar2 = 0

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

rgds

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

[John Bell]

Re: Cache HIT ratio problem

Hi

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

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_4azp.asp

You may also want to look at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;325119

John

"Matt" <matt@fruitsala d.org> wrote in message
news:b609190f.0 504241040.5cf4d 561@posting.goo gle.com...[color=blue]
> "Simon Hayes" <sql@hayes.ch > wrote in message
> news:<111408481 7.050203.257840 @f14g2000cwb.go oglegroups.com> ...
>[color=green]
>> 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.[/color]
>
> 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_ordvalmon itor
>
> AS
>
> declare @maxpossible money,
> @moneysofar money,
> @moneysofar2 money
>
> select @maxpossible =
> sum((orp.ordant al*orp.vb_pris) *(100-orp.rabatt1)/100)
> from orp
> where (orp.ordberlevd at = convert(varchar (8), getdate(), 112) or
> orp.ordberlevda t = convert(varchar (8), dateadd(day, -1, getdate()),
> 112)) and
> orp.ordradst < 50 and
> orp.ftgnr <> '11000'
>
> select @moneysofar =
> sum((orp.ordant al*orp.vb_pris) *(100-orp.rabatt1)/100)
> from orp
> where (orp.ordberlevd at = convert(varchar (8), getdate(), 112) or
> orp.ordberlevda t = convert(varchar (8), dateadd(day, -1, getdate()),
> 112)) and
> orp.ordradst = 50 and
> orp.ftgnr <> '11000'
>
> select @moneysofar2 = sum(ft.faktrads umma)
> from ft
> where ft.faktdat = convert(varchar (8), getdate(), 112) and
> ft.kundkategori kod <> 17
>
> if @moneysofar IS NULL
> set @moneysofar = 0
>
> if @moneysofar2 IS NULL
> set @moneysofar2 = 0
>
> select convert(numeric , @maxpossible) AS 'moneyremaining ',
> (convert(numeri c, @moneysofar) + convert(numeric , @moneysofar2)) AS
> 'moneysofar'
>
> rgds
>
> It is used in some PHP code I wrote to display our current order
> values.[/color]

[Matt]

Re: Cache HIT ratio problem

"John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<426bf127$ 0$26335$db0fefd 9@news.zen.co.u k>...[color=blue]
> Hi
>
> To improve cache hits try starting with using qualified names. Adding owner
> prefixes should help
>
> http://msdn.microsoft.com/library/de...ar_sa_4azp.asp
>
> You may also want to look at:
> http://support.microsoft.com/default...b;en-us;325119[/color]


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.



[color=blue]
>[/color]
[color=blue][color=green]
> > CREATE procedure q_spr_ordvalmon itor
> >
> > AS
> >
> > declare @maxpossible money,
> > @moneysofar money,
> > @moneysofar2 money
> >
> > select @maxpossible =
> > sum((orp.ordant al*orp.vb_pris) *(100-orp.rabatt1)/100)
> > from orp
> > where (orp.ordberlevd at = convert(varchar (8), getdate(), 112) or
> > orp.ordberlevda t = convert(varchar (8), dateadd(day, -1, getdate()),
> > 112)) and
> > orp.ordradst < 50 and
> > orp.ftgnr <> '11000'
> >
> > select @moneysofar =
> > sum((orp.ordant al*orp.vb_pris) *(100-orp.rabatt1)/100)
> > from orp
> > where (orp.ordberlevd at = convert(varchar (8), getdate(), 112) or
> > orp.ordberlevda t = convert(varchar (8), dateadd(day, -1, getdate()),
> > 112)) and
> > orp.ordradst = 50 and
> > orp.ftgnr <> '11000'
> >
> > select @moneysofar2 = sum(ft.faktrads umma)
> > from ft
> > where ft.faktdat = convert(varchar (8), getdate(), 112) and
> > ft.kundkategori kod <> 17
> >
> > if @moneysofar IS NULL
> > set @moneysofar = 0
> >
> > if @moneysofar2 IS NULL
> > set @moneysofar2 = 0
> >
> > select convert(numeric , @maxpossible) AS 'moneyremaining ',
> > (convert(numeri c, @moneysofar) + convert(numeric , @moneysofar2)) AS
> > 'moneysofar'
> >
> > rgds
> >
> > It is used in some PHP code I wrote to display our current order
> > values.[/color][/color]

[John Bell]

Re: Cache HIT ratio problem

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.ordberlevd at = convert(varchar (8), getdate(), 112) or
orp.ordberlevda t = convert(varchar (8), dateadd(day, -1, getdate()),
112))

to something like
orp.ordberlevda t > @date1
and orp.ordberlevda t < @date2

then you may improve the performance.

You may want to also try:

select @moneysofar =
sum( CASE WHEN orp.ordradst = 50 THEN
(orp.ordantal*o rp.vb_pris)*(1 00-orp.rabatt1)/100 END)
@moneysofar =
sum( CASE WHEN orp.ordradst < 50 THEN
(orp.ordantal*o rp.vb_pris)*(1 00-orp.rabatt1)/100) END)
from orp
WHERE orp.ordradst <= 50

John

[Erland Sommarskog]

Re: Cache HIT ratio problem

Matt (matt@fruitsala d.org) writes:[color=blue]
> select @maxpossible =
> sum((orp.ordant al*orp.vb_pris) *(100-orp.rabatt1)/100)
> from orp
> where (orp.ordberlevd at = convert(varchar (8), getdate(), 112) or
> orp.ordberlevda t = convert(varchar (8), dateadd(day, -1, getdate()),
> 112)) and
> orp.ordradst < 50 and
> orp.ftgnr <> '11000'
>
> select @moneysofar =
> sum((orp.ordant al*orp.vb_pris) *(100-orp.rabatt1)/100)
> from orp
> where (orp.ordberlevd at = convert(varchar (8), getdate(), 112) or
> orp.ordberlevda t = convert(varchar (8), dateadd(day, -1, getdate()),
> 112)) and
> orp.ordradst = 50 and
> orp.ftgnr <> '11000'[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Matt]

Re: Cache HIT ratio problem

"John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<111445120 1.445815.229010 @l41g2000cwc.go oglegroups.com> ...[color=blue]
> 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.ordberlevd at =3D convert(varchar (8), getdate(), 112) or
> orp.ordberlevda t =3D convert(varchar (8), dateadd(day, -1, getdate()),
> 112))
>
> to something like
> orp.ordberlevda t > @date1
> and orp.ordberlevda t < @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*o rp.vb_pris)=AD* (100-orp.rabatt1)/100 END)
> @moneysofar =3D
> sum( CASE WHEN orp.ordradst < 50 THEN
> (orp.ordantal*o rp.vb_pris)=AD* (100-orp.rabatt1)/100) END)
> from orp=20
> WHERE[/color]

orp.ordradst <=3D 50

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

rgds

Matt

[John Bell]

Re: Cache HIT ratio problem

Hi Matt

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

Also remove the two if statements and do
select convert(numeric , ISNULL(@maxposs ible,0)) AS 'moneyremaining ',
(convert(numeri c, ISNULL(@moneyso far)) + convert(numeric ,
ISNULL(@moneyso far2))) AS
'moneysofar'

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

John

[John Bell]

Re: Cache HIT ratio problem

Hi

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

John

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