Re: Can anyone explain this huge increase in logical I./O....?

> In the "slow" database, you have many more waits for the 'cache buffer
> chains' latch, plus you have buffer busy waits. This tells me that in
> this system, you have much more concurrency for these resources. Is it
> possible that in the first, you were the only user accessing this data,
> but in the second database, there were multiple users accessing (and
> maybe modifying) the same data blocks?
>

You hit the nail on the head - the fast DB has no concurrency (DEV) and the slow DB (PROD) has additional workload thanks to the users :).

However the application is a reporting system in which users cannot update this table. So I can understand that the concurrency would cause a slowdown but I can't understand why the logical I/O count has jumped up to over 20 times more. I could accept it if it was rebuilding a pre-image of changed blocks via the rollback segments but this table cannot be modified by users of the application.

Would 'delayad block cleanout' cause this behaviour..?

Matt Received on Mon Oct 23 2006 - 13:55:04 CDT