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]

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]

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<11**********************@f14g2000cwb.googleg roups.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.

[John Bell]

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

John

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6**************************@posting.google.c om...

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:<11**********************@f14g2000cwb.googleg roups.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.

[Matt]

"John Bell" <jb************@hotmail.com> wrote in message news:<42***********************@news.zen.co.uk>...

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

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.

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]

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 = convert(varchar(8), getdate(), 112) or
orp.ordberlevdat = 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 =
sum( CASE WHEN orp.ordradst = 50 THEN
(orp.ordantal*orp.vb_pris)**(100-orp.rabatt1)/100 END)
@moneysofar =
sum( CASE WHEN orp.ordradst < 50 THEN
(orp.ordantal*orp.vb_pris)**(100-orp.rabatt1)/100) END)
from orp
WHERE orp.ordradst <= 50

John

[Erland Sommarskog]

Matt (ma**@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, es****@sommarskog.se

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

[Matt]

"John Bell" <jb************@hotmail.com> wrote in message news:<11**********************@l41g2000cwc.googleg roups.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

[John Bell]

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

[John Bell]

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', (convert(numeric, ISNULL(@moneysofar)) + convert(numeric,
ISNULL(@moneysofar2))) AS
'moneysofar'

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

John