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 o.name, i.name, 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
The post How do I tell what compression level my tables/indexes are? appeared first on SQLServerCentral.
At Packt, we are always on the lookout for innovative startups that are not only…
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…