I'm testing using the publicly available AdventureworksDW2016CTP3 database.
If I insert the first 100k rows from FactResellerSalesXL_PageCompressed
into a page compressed heap without minimal logging I get a table size of 12552 KB. Doing a rebuild of the table brings down the size to 6024 KB. If I do the same insert with minimal logging I get a table size of 5832 KB. Why is the initial table size over 50% smaller when minimal logging is used?
Below is code to reproduce the issue. I'm using SQL Server 2016 SP1:
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 6.3 (Build 14393: )
No trace flags are enabled.
The actual execution plans are effectively the same. I've verified that minimal logging occurs for the second insert but not the first. Changing the order of inserts does not make a difference. Clearing the buffer cache and plan cache also does not make a difference.
Table creation script:
-- create page compressed heap copy of FactResellerSalesXL_PageCompressed
SELECT * INTO FactResellerSalesXL_PageCompressed_Heap
FROM FactResellerSalesXL_PageCompressed
WHERE 1 = 0;
ALTER TABLE FactResellerSalesXL_PageCompressed_Heap REBUILD WITH (DATA_COMPRESSION = PAGE);
Code for the first insert:
-- insert 100k rows without minimal logging
INSERT INTO FactResellerSalesXL_PageCompressed_Heap
SELECT TOP 100000 *
FROM FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);
-- check space
EXEC sp_spaceused FactResellerSalesXL_PageCompressed_Heap;
Result:
╔═════════════════════════════════════════╦════════╦══════════╦══════════╦════════════╦════════╗
║ name ║ rows ║ reserved ║ data ║ index_size ║ unused ║
╠═════════════════════════════════════════╬════════╬══════════╬══════════╬════════════╬════════╣
║ FactResellerSalesXL_PageCompressed_Heap ║ 100000 ║ 12808 KB ║ 12496 KB ║ 8 KB ║ 304 KB ║
╚═════════════════════════════════════════╩════════╩══════════╩══════════╩════════════╩════════╝
Code for the second insert:
TRUNCATE TABLE FactResellerSalesXL_PageCompressed_Heap;
-- insert 100k rows with minimal logging
INSERT INTO FactResellerSalesXL_PageCompressed_Heap WITH (TABLOCK)
SELECT TOP 100000 *
FROM FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);
-- check space
EXEC sp_spaceused FactResellerSalesXL_PageCompressed_Heap;
Result:
╔═════════════════════════════════════════╦════════╦══════════╦═════════╦════════════╦════════╗
║ name ║ rows ║ reserved ║ data ║ index_size ║ unused ║
╠═════════════════════════════════════════╬════════╬══════════╬═════════╬════════════╬════════╣
║ FactResellerSalesXL_PageCompressed_Heap ║ 100000 ║ 5832 KB ║ 5776 KB ║ 8 KB ║ 48 KB ║
╚═════════════════════════════════════════╩════════╩══════════╩═════════╩════════════╩════════╝