How can I see if the data in a SQL Server table is page-compressed?

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.

1. 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?

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
(
  ID INT IDENTITY(1, 1) NOT NULL,
  String sysname,
  [MaxLength] SMALLINT,
  [Type] VARCHAR(5)
) WITH (DATA_COMPRESSION = PAGE);

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

StackExchange Database Administrators Q#245254, answer score: 15