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:
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.
Now in memory: way less stuff.
So there you go.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.