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?
Best Answer
To see whether or not data pages are indeed currently "PAGE" compressed, you can use the undocumented DMF
sys.dm_db_database_page_allocations()
. Theis_page_compressed
field contains the info you are looking for. You will need to use theDETAILED
mode (i.e. 5th parameter) or else the values in that field will all beNULL
.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:
The following shows that the data pages are not initially page compressed, but they are after the
REBUILD
operation: