7

I have a lot of large tables (around 10 million wide rows) which need to be regularly loaded into SQL Server 2016 for read-only reporting. I would like these tables to be as small as possible on disk, and this matters more than performance improvements in either loading or querying.

Here is what I have been doing for the tables which require no further indexing:

  1. Create the table with DATA_COMPRESSION=PAGE.
  2. Use bcp to bulk insert the data from a flat file into the new table.

Column types in the tables are varchar (never more than 512, not max), float, tinyint, or date (not datetime). All columns are created as nullable and no primary or foreign keys are defined -- they don't matter for the querying and the tables are never updated directly. Default collation on everything is SQL_Latin1_General_CP1_CI_AS.

When I do this, I can see in sys.allocation_units that page data compression has been applied to the heap and I can see in sys.partitions that the fill factor is correctly 0 (100%). Since the tables are much smaller than uncompressed tables would be, I thought the compression was accomplished.

However, if I then rebuild with the same option DATA_COMPRESSION=PAGE, the supposedly-already-compressed table gets about 30% smaller! It looks like it's going from about 17 rows per data page to 25 rows per page. (Only once, though. Rebuilding again after that doesn't make it any smaller than the first rebuild did.)

The questions

So my questions are: (a) what is going on here? and (b) is there a way to get this extra-small compressed size directly as I load the table without having to rebuild after the data is loaded?

7

@HandyD is entirely correct, I only want to highlight some other methods to get compression while inserting into a heap.

From the same document

When a heap is configured for page-level compression, pages receive page-level compression only in the following ways:

  • Data is bulk imported with bulk optimizations enabled.
  • Data is inserted using INSERT INTO ... WITH (TABLOCK) syntax and the table does not have a nonclustered index.
  • A table is rebuilt by executing the ALTER TABLE ... REBUILD statement with the PAGE compression option.

According to this, you could leverage minimally logged bulk inserts or use INSERT INTO ... WITH (TABLOCK) to get PAGE compression without having to do rebuilds.


(a) what is going on here? and (b) is there a way to get this extra-small compressed size directly as I load the table without having to rebuild after the data is loaded?

There are rules to get PAGE compression when inserting into a heap, add -h "TABLOCK" to your bcp command to get compression.

ROW compression works without these prerequisites and is the least amount of compression used in below examples, thanks @DenisRubashkin for pointing that out!


Testing

Example start data & BCP out command


--Tested on SQL Server 2014 SP2

CREATE TABLE dbo.CompressedHeap_Source( Val varchar(512), 
                                 Datefield Date, 
                                 Tinyfield TinyINT,
                                 Floatfield float) 
WITH (DATA_COMPRESSION = PAGE);

INSERT INTO dbo.CompressedHeap_Source
(
Val,Datefield,Tinyfield,Floatfield)

SELECT 'Bla',cast(getdate() as date),1,1.2412
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;

--bcp TEST.dbo.CompressedHeap_Source out E:\Data\HeapData.bcp -c -T

The ROW compressed and Uncompressed size

The data size is at 132272 KB when doing a standard insert into the heap, this is ROW compressed but not PAGE compressed.

The data size without any compression is ~ 176216 KB for our test.

exec sp_spaceused 'dbo.CompressedHeap_Source'

name                    rows                    reserved    data      index_size    unused
CompressedHeap_Source   6365530                 132296 KB   132272 KB   8 KB    16 KB

INSERT INTO ... WITH TABLOCK

Inserting WITH TABLOCK gives us the PAGE compressed data size, 69480 KB.

INSERT INTO dbo.CompressedHeap_Source2  WITH(TABLOCK)
(
Val,Datefield,Tinyfield,Floatfield)

SELECT 'Bla',cast(getdate() as date),1,1.2412
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2

BULK INSERT

Now when we create a destination heap table that is also page compressed, and do a bulk insert with tablock:

CREATE TABLE dbo.CompressedHeap_Destination( Val varchar(512), 
                                 Datefield Date, 
                                 Tinyfield TinyINT,
                                 Floatfield float) 
WITH (DATA_COMPRESSION = PAGE);

bulk insert dbo.CompressedHeap_Destination

from 'E:\Data\HeapData.bcp'  with (TABLOCK)

The data gets page compressed and is also at 69480 KB:

name    rows    reserved    data    index_size  unused
CompressedHeap_Destination  6365530                 69512 KB    69480 KB    8 KB    24 KB

BCP IN WITH TABLOCK

You can get the same results as the BULK INSERT WITH TABLOCK by using BCP IN with the -h "TABLOCK" hint. This makes sense, they do the same internally

--bcp TEST.dbo.CompressedHeap_Destination2 IN E:\Data\HeapData.bcp -c -T -h "TABLOCK"

With the resulting size being 69480 KB

BCP IN WITHOUT TABLOCK

Using BCP to load data from the same file in a copy of the destination table

And a standard bcp command results into non compressed data:

--bcp TEST.dbo.CompressedHeap_Destination2 IN E:\Data\HeapData.bcp -c -T 

With the data size at 132272 KB (row compressed).

  • 2
    I think that rebuilding the "compressed" table will decrease its size. It seems the table is ROW compressed and has to be rebuilt to get PAGE compressed. – Denis Rubashkin Aug 13 at 10:08
  • @DenisRubashkin Thanks a lot! You are correct, these are not uncompressed but in fact row compressed. My bad – Randi Vertongen Aug 13 at 12:48
  • I updated the answer, thanks again! – Randi Vertongen Aug 13 at 12:54
  • You're right! Adding the -h TABLOCK to the bcp command made the difference! It seems so obvious now that you've pointed it out, but I missed it entirely before. (Actually, follow-on question: is there any way to tell that there's only-row-compressed data in a table with an allocation for page compression?) – Caitlin M. Shaw Aug 13 at 14:23
  • 2
    Randi: nice answer :-). @CaitlinM.Shaw (and Randi): That is a great question. Yes, there is a way to tell. If you post that question, and then post a link to it in a reply comment here (please be sure to use my @ name so that I get notified), I will post the answer. – Solomon Rutzky Aug 13 at 16:49
5

According to the Docs article on compression:

New pages allocated in a heap as part of DML operations do not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying compression, or by creating and removing a clustered index.

This would seem to align with what you're seeing. It seems like you're not actually getting compression on the table until you rebuild it. You could try loading the data on an uncompressed table and see if you still average 17 rows per page or if this decreases. If it remains the same, then you're not getting compression and the rebuild is necessary.

You could also add a clustered index to your table and that should prevent your table from being uncompressed/low-compressed after bulk loading your data.

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.