Sql-server – PAGE compression in SQL Server effect on running queries


I am planning to apply PAGE compression to some of the big tables in my database (a data warehouse). 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.

  1. While the compression is being applied would there be any effect 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?

Best Answer

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.

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