MS SQL Server - SQL cache so GREEDY?

Author

SQL cache so GREEDY?

Marek Wierzbicki

2006-03-05, 8:23 pm

I think I have problem with so greedy cache. (SQL 2000, sp4)

Buffer manager shows, that:
Hit ratio is often over 96%
Page life expectacy is about 15-50 sec
Pages read/sec is very similar to readahead pages (often bigger)

And I have trouble with disk array, which have no power to transfer all data
asked by SQL (often they are disk queue, and SQL can't answer at once for
simple select)

So I think its better set cache to not read so much readahead pages.

But I don't know how to do it. Is it possible?

Marek

Tibor Karaszi

2006-03-05, 8:23 pm

I'd say that 96% is pretty bad. 4% of the pages needed to process your queries are physically read
from disk! This is all relative, of course, but I would start with tuning the queries, making sure
you have good indexes etc. It is all about minimizing resource usage...

Apart from that, read-ahead could be bad if you have lots of fragmentation. I don't know how smart
SQL Server currently is, if SQL Server can detect high level of fragmentation and do single page I/O
instead of read-ahead. If not, then with fragmentation SQL server will read extents and then
potentially need only one page from an extent and then move to the next extent. So in theory it
could fill the cache with a lot of rubbish pages that weren't needed to process that query in the
first place.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Marek Wierzbicki" <marek. wierzbickiiiii@azymu
ttttt.pl> wrote in message
news:du467m$2les$1@n
ews2.ipartners.pl...
>I think I have problem with so greedy cache. (SQL 2000, sp4)
>
> Buffer manager shows, that:
> Hit ratio is often over 96%
> Page life expectacy is about 15-50 sec
> Pages read/sec is very similar to readahead pages (often bigger)
>
> And I have trouble with disk array, which have no power to transfer all data asked by SQL (often
> they are disk queue, and SQL can't answer at once for simple select)
>
> So I think its better set cache to not read so much readahead pages.
>
> But I don't know how to do it. Is it possible?
>
> Marek
>

Marek Wierzbicki

2006-03-05, 8:23 pm

> I'd say that 96% is pretty bad.
> 4% of the pages needed to process
> your queries are physically read from disk!

After optymalization first of havy query (3 join on non indexed fields,
answer time over 10 secounds, asked every 20 sec.) hir ratio grow to
99.2%-99.8% (depends on time) and page life expectancy grows to 100-600
secounds

I think after create indexes on this tables its much better. I will be
looking for next havy queries and optimize it. BTW. do you hear sth about
"periscope" - tolls for searching worst queries?

Marek

Tibor Karaszi

2006-03-05, 8:24 pm

> BTW. do you hear sth about "periscope" - tolls for searching worst queries?

I'm afraid not...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Marek Wierzbicki" <marek. wierzbickiiiii@azymu
ttttt.pl> wrote in message
news:du76aq$191a$1@n
ews2.ipartners.pl...
>
> After optymalization first of havy query (3 join on non indexed fields, answer time over 10
> secounds, asked every 20 sec.) hir ratio grow to 99.2%-99.8% (depends on time) and page life
> expectancy grows to 100-600 secounds
>
> I think after create indexes on this tables its much better. I will be looking for next havy
> queries and optimize it. BTW. do you hear sth about "periscope" - tolls for searching worst
> queries?
>
> Marek
>
>

Sponsored Links