How do I tell what compression level my tables/indexes are?


November 17, 2020 by Kenneth Fisher

It’s been a while since I worked with compression and the other day I needed to check which of my indexes were compressed and which weren’t. Now, I knew the information wasn’t going to be in sys.tables and I couldn’t find it in sys.indexes or INDEXPROPERTY(). I’ll be honest it had me stumped for a little bit. Until I remembered something!

Compression isn’t done at the table or even the index level. It’s done at the partition level. Something important to remember is that every table has at least one entry in sys.indexes, although in the case of a heap it’s just the unindexed table. So in a way you could say that every table has an index. Well every index has at least one partition. If you haven’t deliberately partitioned the index it’s just the whole index.

Why would you want to compress just certain partitions? Well, remember that one reason for partitioning is to separate out older, less used, data from newer, more frequently accessed data. You might decide to use page compression, the strongest but slowest compression, on your oldest data to conserve space on data you aren’t going to have to de-compress very often. You might then have a group of data that you access a bit more often where you use row compression, which saves some space and takes a bit less CPU to undo. And on your current/more frequently accessed data you don’t compress the data at all.

Anyway, back on point. The system view sys.partitions has the information we are looking for. This query has the table and index names along with the partition information.

SELECT,, p.*
FROM sys.partitions p
JOIN sys.indexes i
	ON i.object_id = p.object_id
	AND i.index_id = p.index_id
JOIN sys.objects o
	ON i.object_id = o.object_id

One thought on “How do I tell what compression level my tables/indexes are?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: