Database Reference
In-Depth Information
CarrierTrackingNumber nvarchar(25) null,
CustomerPONumber nvarchar(25) null,
OrderDate datetime null,
DueDate datetime null,
ShipDate datetime null
)
Table
35-2
compares the on-disk size of all four tables.
Table 35-2.
On-disk data size for different compression methods
HEAP table
(no compression)
HEAP table
(page compression)
Columnstore compression
Archival compression
10,504MB
2,440MB
831MB
362MB
Obviously, different table schema and data lead to different compression results; however, in most cases, you will
achieve significantly greater space saving when archival compression is implemented.
Archival compression introduces additional CPU overhead at the compression and decompression stages.
Let's run a query that performs a
MAX()
aggregation on 20 columns in a table. The result of the query is meaningless;
however, it forces SQL Server to read data from 20 different column segments in each row group in the table.
Listing 35-6 shows the query.
Listing 35-6.
Test query
-- Clear buffer pool forcing SQL Server to read data from disk
dbcc dropcleanbuffers
go
select
max(ProductKey),max(OrderDateKey),max(DueDateKey)
,max(ShipDateKey),max(CustomerKey),max(PromotionKey)
,max(CurrencyKey),max(SalesTerritoryKey),max(SalesOrderLineNumber)
,max(RevisionNumber),max(OrderQuantity),max(UnitPrice)
,max(ExtendedAmount),max(UnitPriceDiscountPct),max(DiscountAmount)
,max(ProductStandardCost),max(TotalProductCost),max(SalesAmount)
,max(TaxAmt),max(Freight)
from dbo.FactSalesBig
Table
35-3
illustrates the execution time of the query against the tables with different columnstore compression
methods. Even though the data compressed using archival compression uses significantly less space on disk, it takes
longer for the query to complete due to the decompression overhead involved. Obviously, the results would vary
based on the CPU and I/O performance of the system.
Table 35-3.
Execution time for different compression methods
COLUMNSTORE compression
(Elapsed/CPU time)
COLUMNSTORE_ARCHIVE compression
(Elapsed/CPU time)
2,025ms / 6,573ms
2,464ms / 8,470ms
