February 11, 2015 at 4:04 pm
Currently this database is not compressed, and I'm considering compression. I'd appreciate any thoughts about setting DATA_COMPRESSION = PAGE for all tables and indexes in a database. For example, are there circumstances where compression is not advised for specific tables or usage patterns?
This database is about 500 GB in size, and contains basically a star-schema type data model. There are simple dimension tables (Integer key + several varchar lookup values), some of which are large with several million rows, and then there are a handful of narrow fact tables, each containing 2 - 5 keys and anywhere from several hundred million to several billion rows.
Based on my analysis of performance monitoring metrics, this server is not pressed for CPU (this is consistently low and never above 70%) or memory (high page life expectancy). Instead my concern is increasing row volume, disk storage, and query performance.
I ran a script to execute sp_estimate_data_compression_savings across each table, and it's estimating 60% - 80% compression for most of the larger tables and ~ 20% compression for the remainder.
Also, the fact tables are mostly batch inserted (almost never updated or deleted), but they do tend to accumulate fragmentation, so while I'm in the process of re-building indexes, any thoughts about default FILLFACTOR and PAD_INDEX for this type of large-ish star-schema database?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 9, 2015 at 7:31 pm
consider partitioning in a way that inserts or updates happen to the latest partition only and doing page compression to the partitions that do not get any inserts or updates. And also, dont go full hog, one table at a time, and test it.
March 10, 2015 at 4:29 am
Here you have a nice doc on compression https://technet.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply