When the Wash Area Is Too Small

If the wash area is too small for the usage in a buffer pool, operations that need a clean buffer may have to wait for I/O to complete on the dirty buffer at the LRU end of the pool or at the victim marker. This is called a dirty buffer grab, and it can seriously impact performance.

This figure shows a dirty buffer grab on a strict replacement policy cache.

Graphic describing what happens when a wash area is too small. The buffer pool is described as a bar that is divided into pages. Because the wash area is too small, the dirty page must be written to disk before the next clean page comes along.

Use sp_sysmon to determine whether dirty buffer grabs are taking place in your memory pools. Run sp_sysmon while the cache is experiencing a heavy period of I/O and heavy update activity, since it is the combination of many dirty pages and high cache replacement rates that usually causes dirty buffer grabs.

If the “Buffers Grabbed Dirty” output in the cache summary section shows a nonzero value in the “Count” column, check the “Grabbed Dirty” row for each pool to determine where the problem lies. Increase the size of the wash area for the affected pool.

This example sets the wash area of the 8K memory pool to 720K:

sp_poolconfig pubs_cache, "8K", "wash=720K"

If the pool is very small, you may also want to increase its size, especially if sp_sysmon output shows that the pool is experiencing high turnover rates.

See the Performance and Tuning Series: Monitoring SAP Adaptive Server with sp_sysmon.