PAGE compression in SQL Server effect on running queries

Question

I am planning to apply PAGE compression to some of the big tables in my database (DW to be precise). These tables are fairly big with over 15 Billion rows. When I applied the compression in test environment, the whole process took around 22 hours.These tables are accessed daily with quite long running queries. My questions are

1. While the compression is being applied would there be any affect on the queries that are running? Any lock etc I should be aware of?
2. Is there a staggered approach to applying compression?

3.Any other input/feedback you might have?

many thanks

Sean

Answer 1

Offline ALTER ... REBUILD takes a big fat schema modification lock on the table with absolutely 0 concurrency (not even dirty reads can scan the table). Online ALTER ... REBUILD, as the name suggests, allows for any concurrent query or DML operation. The MSDN article How Online Index Operations Work describes the three phases (prepare, build and finalization) and the object locks required in each phase (IS, IS, SCH-M). The builder operates in batches and acquires data locks as it makes progress but it will back off on conflict, and there is some special sauce on handling deadlocks with the builder:

Deadlocks between the index builder transaction that is holding the batch transaction locks and DML statements are possible, but are handled internally so that neither the DML operation nor the index builder transaction should terminate during the build phase due to a deadlock.

More details are in the Online Indexing Operations in SQL Server 2005 whitepaper, including how antimatter works.

Now that being said, for a 15B rows DW table the best option may be Columnstore Indexes.

Answer 2

I tested the scenario by running

ALTER TABLE test_tbl REBUILD WITH (DATA_COMPRESSION=PAGE,ONLINE=ON)

In a Transaction, it takes exclusive locks on the pages, so yes there is potential for blocking, however it will be very less for a big table since it compresses page-by-page, so only large full-table-scan selects can get blocked, not inserts or updates, so the answers are:

1. Yes it can block large queries, the process will take an exclusive lock on the page that is being compressed
2. A staggered approach is possible if you use partitions, however that won't help you much if you use Aggregate queries without a filter on the partitioned column, if you are anyway selecting the entire table, it matters not which partition you compress
3. In addition to disk space, you will also reap performance benefits because you can fit more of the table in memory, you can test this by observing the Page Life expectancy before and after the compression