Increasing free space for compressed data
You can provide free space to avoid the potential problem of more getpage and lock requests for compressed data.
About this task
In some cases, using compressed data results in an increase in the number of getpages, lock requests, and synchronous read I/Os. Sometimes, updated compressed rows cannot fit in the home page, and they must be stored in the overflow page. This can cause additional getpage and lock requests. If a page contains compressed fixed-length rows with no free space, an updated row probably has to be stored in the overflow page.
Procedure
To avoid the potential problem of more getpage and lock requests:
Add more free space within the page. Start with a PCTFREE
10 value to create 10% additional free space and adjust further, as
needed. If, for example, 10% free space was used without
compression, start with 20% free space with compression for most cases. A sufficient amount of free space is especially important for
data that is heavily updated.