none
Compression Effect On Locking

    Question

  • Can anyone point me to any literature that talks about the effect of ROW or PAGE compression on latch or lock contention? 

    Specifically, I’m curious to understand if data compression will result in increasing or decreasing lock or latch contention. The idea is, one read to disk or memory results in a read to an 8 KB page, that same 8 KB page read will contain much more data in the case of a compressed data set. So, in the case of heaps, where it’s difficult to get a ROW lock and usually PAGE locks are used instead, I wonder if that page lock will result in a larger set of rows that could participate in blocks, or has Microsoft implemented compression in a way that won’t effect locking behaviors.

    Thanks in advance for any help.

    Wednesday, May 16, 2018 3:18 PM

All replies

  • The extra CPU involved in decompressing the data means many transactions take longer, though the worst effects are making short transactions longer, long transactions are already long even if they then get longer too.

    Page locks are always bad news, try to avoid them.

    Josh

    Wednesday, May 16, 2018 4:02 PM