Loading a PDW Region in APS - SQL Server Integration Services Design Patterns - page 249

Let's take another look at the CREATE DATABASE code in Listing 8-5 .
REPLICATED_SIZE specifies space allocation for replicated tables on each Compute
node , whereas DISTRIBUTED_SIZE specifies space allocation for distributed tables
across the appliance . That means StageDB_Example actually has 16GB of space
allocated: 10GB for replicated tables (10 Compute nodes with 1GB each), 5GB for dis-
tributed tables, and 1GB for the log.
All data is automatically compressed using page-level compression during the
PDW load process. This is not optional, and the amount of compression will vary
greatly from customer to customer and table to table. If you have SQL Server Enter-
prise or Developer Editions, you can execute the statement in Listing 8-6 to estimate
compression results.
Listing 8-6 . Code to Estimate Compression Savings
/* Estimate compression ratio */
EXECUTE sp_estimate_data_compression_savings
'dbo', 'FactSales', NULL, NULL, 'PAGE';
Example results of sp_estimate_data_compression_savings are
shown in Figure 8-5 .
Figure 8-5 . Example compression savings
You can generally use 2:1 as a rough estimate. With a 2:1 compression ratio, the
5GB of distributed data specified in Listing 8-5 actually stores 10GB of uncompressed
SQL Server data.
You still need a place in PDW to store the data you're importing. Execute the code
in Listing 8-7 in SSDT to create the destination database and table for FactSales .
Listing 8-7 . PDW Code to Create the Destination Database and Table