Database

Outside of configuring the server and the instance, how the database is configured has considerable effect on the overall performance of the system.

Database Logging Mode

There are two logging modes for a database

Buffered

Under buffered logging, the memory buffer is flushed to disk only when the buffer is full.

Unbuffered

When unbuffered is used then the data is still written to the memory buffer but once the transaction is committed then the buffer is flushed to disk. Because the buffer is flushed when the transaction completes, it means that the buffer could be being flushed even though it is nearly empty. Unbuffered logging increases the disk IO has the buffer is flushed every transaction and not when it is full. Also, because the buffer is flushed in it's entirety, more disk space is used when the logical logs.

There is a risk when using buffered logging that the database can potentially lose transactions if the system goes down before the log buffer is flushed to disk.

Within an engine all databases share the same logical log buffer. Therefore, if one database is unbuffered and all the others are buffered, the logical log buffer will be flushed after every COMMIT in the database with unbuffered logging. This the only time one database can directly effect the performance of another.