This is a follow-on question to a question which I asked yesterday: Can I bulk insert into an empty page-compressed table and get full compression? The answer to that question (paraphrased from Randi Vertongen's excellent answer) is yes, but it requires that the bulk insert take a table-level lock; otherwise, the bulk insert takes a row-level lock and performs only row data compression. This raises the question: how can I tell afterward what compression is applied?

Here are the steps to create row-compressed data in a theoretically-page-compressed table:
1. Create a table with DATA_COMPRESSION=PAGE and do not use sp_tableoption to turn on the "table lock on bulk load" option for this table.
2. Use bcp to bulk insert the data from a flat file into the new table, but without specifying the -h TABLOCK option to lock the table.

The result is a table in which the data is compressed on the row level (smaller than an uncompressed table but larger than a page-compressed table), but inspecting the sys.allocation_units catalog table shows the data compression as page.

The question

When the table's data allocation is for page compression as in this scenario, what can I do to find out if the data in that table is page-compressed?

  • Also for SQL 2016+ you should consider a clustered columnstore index for scenarios involving bulk loading and compression. – David Browne - Microsoft Aug 13 '19 at 20:17

To see whether or not data pages are indeed currently "PAGE" compressed, you can use the undocumented DMF sys.dm_db_database_page_allocations(). The is_page_compressed field contains the info you are looking for. You will need to use the DETAILED mode (i.e. 5th parameter) or else the values in that field will all be NULL.

To be clear (based on the wording of the question being, "what can I do to find out if the data in that table is page-compressed?"), this is not an all-or-none issue: page compression is applied per each data page, hence you can have none that are compressed, all being compressed, or any combination in between. So, you need to look at all of the pages. And no, you cannot necessarily assume that a single non-page compressed page indicates that you need to REBUILD because a non-filled page will not compress.

For example:

SELECT [is_page_compressed]
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED');

The following shows that the data pages are not initially page compressed, but they are after the REBUILD operation:

USE [tempdb];

-- DROP TABLE dbo.CompressedHeap;
CREATE TABLE dbo.CompressedHeap
  String sysname,
  [MaxLength] SMALLINT,
  [Type] VARCHAR(5)

INSERT INTO dbo.CompressedHeap ([String], [MaxLength], [Type])
  SELECT col.[name], col.[max_length], obj.[type]
  FROM   master.sys.columns col
  CROSS JOIN master.sys.objects obj;

SELECT [is_page_compressed], *
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED')
WHERE  [is_iam_page] = 0
AND    [is_allocated] = 1;
-- 394 pages

ALTER TABLE dbo.CompressedHeap REBUILD;

SELECT [is_page_compressed], *
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED')
WHERE  [is_iam_page] = 0
AND    [is_allocated] = 1;
-- 179 pages
| improve this answer | |
  • 1
    That is exactly what I need, thank you so much! I checked some of my actual tables and the ones loaded without the table lock have 0% of pages compressed while the new ones that I loaded with the table lock have 99.8% of the pages compressed (and far fewer of them), just as you describe. – Caitlin M. Shaw Aug 13 '19 at 21:45

You can find the compression level in the sys.partitions dmv

SELECT t.name AS tablename,
       i.name AS indexname,
FROM sys.tables AS t
INNER JOIN sys.indexes AS i
     ON t.object_id = i.object_id
INNER JOIN sys.partitions AS p
     ON i.object_id = p.object_id
        AND i.index_id = p.index_id;
| improve this answer | |
  • 1
    Sadly, this is a scenario in which the actual compression on the data pages is not what's listed in sys.partitions. – Caitlin M. Shaw Aug 13 '19 at 21:57
  • 1
    Hi Bob (and @CaitlinM.Shaw ): the value in sys.partitions should be the requested / ideal data compression type. But since that value is just at the partition level, it can't show the per-page current value, nor the per-row current value, both of which can vary within each partition. – Solomon Rutzky Aug 14 '19 at 5:52
  • that's good to know, thanks – Bob Klimes Aug 15 '19 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.