|
Home > Archive > Microsoft SQL Server forum > April 2005 > Cache HIT ratio problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| Simon Hayes 2005-04-21, 7:23 am |
| 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.
| |
| John Bell 2005-04-24, 8:23 pm |
| Hi
To improve cache hits try starting with using qualified names. Adding owner
prefixes should help
http://msdn.microsoft.com/library/d..._ar_sa_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@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/d..._ar_sa_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.
>
[color=darkred]
| |
| John Bell 2005-04-25, 1:23 pm |
| 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
| |
| Erland Sommarskog 2005-04-25, 8:24 pm |
| 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@l41g2000cwc.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
| |
| John Bell 2005-04-27, 3:23 am |
| 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 2005-04-27, 7:23 am |
| 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]
|
|
|
|
|