Compressed Indexes And The Buffer Pool In SQL Server

Posted on by Erik Darling

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!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts

8 thoughts on “Compressed Indexes And The Buffer Pool In SQL Server”

      1. Hi Erik,

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

        is there any drawback?

    2. 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!

  2. 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

  3. Pingback: Index Compression and the Buffer Pool – Curated SQL

Comments are closed.