Both Oracle and SQL Server perform writes that are asynchronous to the user sessions (not to be confused with asynchronous I/O, which is something else). This means that when a user session modifies rows in a table, the modified pages will be physically written to the table at a later stage and will not add additional time to the DML operations.
So, we might say that we don’t care much about storage write performance in Oracle and SQL. Correct?
In short – the answer is no.
In fact, physical data modifications are done in the background and can happen a long time after the modified transaction/session is completed. However, to ensure data consistency, enough data to describe the changes must be written to the log, and these writes are done synchronous to the session –the first reason why write performance is important. The good news is that, for most transactions, the log block I/O sizes are small and, more important, these I/Os are sequential. In addition, most advanced storage systems will satisfy log I/O requests from cache, providing excellent performance.
The real problem starts when the database server writes the modified pages to the files. These writes can happen for several reasons, such as checkpoint or due to exceeding lazy write thresholds. These writes are random by nature. A description of what happens with many storage systems follows:
- These writes affect the performance of the log writes of other transactions. Recall the cache writes – in many cases, the dirty-pages writes will cause contention on the cache, and as a result the writes to the transaction logs are much slower. Transactions commit takes a long time and the performance hit can be substantial.
- In addition, the dirty-pages writes affect the performance of reads. This is simple to understand. Your reads are performing well, but once write I/Os are added to the picture, the performance is bad.
I have seen cases where transactions per second (TPS) and disk reads were high over time with an acceptable performance. Suddenly, a checkpoint occured and the disk reads (and obviously the TPS) dropped down until the writes bottleneck was reduced.
To summarize, while you can imagine a workload in which bad write performance of the storage subsystem will not affect the performance of the users, for continuous I/O-demanding workload, the performance of the writes (especially random write) is critical.