:::: MENU ::::

Posts Categorized / Buffer

  • Oct 27 / 2008
  • 0
Buffer, dbDigger, Performance Tunning and Optimization, SQL Server IO

SQL Server I/O operations in buffer

SQL Server deals the log operations for read and write requests in an entirely different way. Here is a comparison for both types handling in buffer.

As a read request is submitted to SQL Server, first it is looked in buffer cache of SQL Server. If data is found in buffer cache then it is retrieved from there and if not found in buffer cache then it is retrieved from storage engine to buffer cache and provided to requester from buffer cache.
If requested data is found in buffer cache then it is called buffer hit.
If data is not found in buffer cache and storage engine is concerned then it is called buffer miss.
The ratio of buffer hits to total buffer requests is called buffer cache hit ratio.
As much as buffer cache hit ratio is close to 100 %, you have got as good performance.

In case of write operation for SQL Server database, data is always first written to buffer cache. If data is modified first it will be modified in buffer cache and if not present in buffer then it will be retrieved from disk for modification in buffer. When change is complete and COMMIT is performed a LOG WRITE process will write changes in log file and COMMIT is complete now. Till now the changes are not written to disk. Changes will be applied to data on disk at a later time either by LAZY WRITER process or CHECKPOINT process. Unlike read operation during all this process to write/modify data on disk user session will not wait.

So read performance is more important to consider than write performance.

Click here to read all posts related to SQL Server storage systems and I/O operations

Consult us to explore the Databases. Contact us