Compressed Indexes And The Buffer Pool

Mail Drag


After my smash hit double diamond post about index tuning, I got a question questioning my assertion that compressed indexes are also compressed in the buffer pool.

Well, this should be quick. A quick question. Eighty hours later.

First, two indexes with no compression:

CREATE INDEX o
ON dbo.Posts
    (OwnerUserId);

CREATE INDEX l
ON dbo.Posts
    (LastEditorDisplayName);

Looking at what’s in memory:

jot’em

Now let’s create a couple indexes with compression:

CREATE INDEX o
ON dbo.Posts
    (OwnerUserId)
WITH(DATA_COMPRESSION = ROW);

CREATE INDEX l
ON dbo.Posts
    (LastEditorDisplayName)
WITH(DATA_COMPRESSION = PAGE);

I’m choosing compression based on what I think would be sensible for the datatypes involved.

For the integer column, I’m using row compression, and for the string column I’m using page compression.

got’em

Now in memory: way less stuff.

So there you go.

Thanks for reading!



8 thoughts on “Compressed Indexes And The Buffer Pool

      1. Hi Erik,

        Just need to know, do we get the same benefits if we have a 200GB size table?

        is there any drawback?

    1. hi Nirav,
      Compression can INCREASE the size of the tables on disk.
      This is why Eric choose to use Row compression on numbers and Page compression on strings.
      It is also why you must choose your compression from table to table.
      YMMV!

  1. Data is in compressed format even in CPU cache levels L3 and L2, so huge benefits come from those superfast always limited sized caches.
    Almost always compression makes your queries much faster, and one reason besides getting more datapages into buffer cache is that you get a lot more datapages also into those superfast, near cpu core caches L3 and L2

Leave a Reply

Your email address will not be published. Required fields are marked *