Table Compression Discrepancy
-
Evening Guys,
I've enabled page compression for a few tables in the database, this seems to work fine. But when I query the system tables with the below query it shows compression type as being NONE. I've done an updateusage on the DB too.
I use the following to compress the table:
alter table dbo.LEDGERTRANS
rebuild partition = all with (data_compression = page)And then the following to identify if it is compressed:
SELECT
p.data_compression_desc as compression_description,
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
and p.data_compression_desc !='PAGE'
GROUP BY
t.Name, s.Name, p.Rows, p.data_compression_desc
ORDER BY
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) descAnd for *some* tables that have been compressed it still shows them as 'NONE'
When I check Table -> Storage Properties in Management Studio its shows the expected compression type!
So, whats wrong with my query?
Cheers
Alex - alex.sqldba - Monday, May 14, 2018 11:02 AM
You filtered out page compression using this in your query:
and p.data_compression_desc !='PAGE'Sue
-
Yes because I wanted to see tables that are not compressed with PAGE compression, yet the table that IS compressed by PAGE compression is still showing.
-
Okay, so weird plot twist.
If I comment out the portion of the where clause where it filters by compression_desc, then I need some of the tables that have page compression on listed as 'none'. Yet if I filter for ONLY tables with page compression on, I see those tables that were once listed as none in there with page compression.
- alex.sqldba - Monday, May 14, 2018 11:27 AM
That's different than your original post where you gave an example of using page compression and said:
And for *some* tables that have been compressed it still shows them as 'NONE'So you have tables with page compression showing up when it's filtered out? Did you try looking at just sys.partions to see if you get the same results?
SELECT
OBJECT_NAME(object_id) as ObjectName,
data_compression_desc as CompressionType
FROM sys.partitions
WHERE OBJECTPROPERTY(object_id,'IsMSShipped') = 0Sue
-
Perhaps screen shots will do a better job of explaining it than I am verbally:
Take a look at the first query that does not include a filter for compression_desc: I have highlighted a consistently offending row in the results. Please also take a look at the column UsedSpaceKB.
in this next query all I have done is ADD IN the filter for Page Compression. The rest of the query is identical:
Now somehow the LEDGERTRANS table is miraculously compressed and its UsedSpaceKB has changed.
Also yes, Sue that last query you sent returns the correct results. So I am not sure what in my above query is making it go awry!
Cheers
Alex - alex.sqldba - Monday, May 14, 2018 12:35 PM
Container_id in sys.allocation_units does not always equal partition_id in sys.partitions. It depends on the allocation unit type:
sys.allocation_units (Transact-SQL)Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply