Batch Requests per second and Disk IO

davesnb
davesnb used Ask the Experts™
on
Hello EE,

What is the relationship between Batch requests and disk io ? I understand the two metrics separately, however, if the disks become latent or there is disk queueing , does this affect batch requsts/sec . Or is the batch requests just a sheer number of the amount of work requested to do, independent of hardware?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Operational DBA
Commented:
batch requests are how many batches/sql statements are executing at a given moment.  In most cases, you correlate this to system resources.  As you have more batch requests, you have more strain on the hardware.  Generally speaking, when you have a lot of batch requests coming in, you should see CPU start increasing and, depending on the profile of the statements and the data they access, potentially disk io.  The name of the game is increasing the amount of batch requests/sec you can achieve at pegged resource utilization.  Another way to look at it is to maintain the same batch requests/sec with decreased resource utilization.

There isn't going to necessarily be a hard and fast correlation between disk io and batch requests, however.  For example, the majority of the pages that are needed could already be in memory and so you'd be looking at very little disk activity (unless there are updates/inserts/deletes, in which case you would see disk activity when the lazy writer flushes the dirty pages to disk).  Or you could have the exact opposite where none of the data is in memory and needs to be moved there first, which would result in higher disk pressure.  Disk queues are just never good and you could indeed see them if your required IOPs for your data access needs exceed what your storage can handle.  They will make batches take longer to complete execution (again, dependent on whether or not they require disk access for the desired pages).

In most cases I use batches/sec merely to see if there's a justifiable increase in system resource utilization.  Generally speaking, as batches/sec increase, so does resource utilization (including disk io).

Author

Commented:
Perfect, thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial