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.
- 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?
- Is there a staggered approach to applying compression?
- Any other input/feedback you might have?
-
1Is this SQL Server, or SSAS? If the former, why not SSAS?podiluska– podiluska2012年10月31日 10:17:04 +00:00Commented Oct 31, 2012 at 10:17
-
There is an SSAS layer on the top. However this is mostly to do with saving disk space and hopefully speeding up the SSAS processing. Thanks for flagging to dba.se though.swanand kant– swanand kant2012年10月31日 10:23:23 +00:00Commented Oct 31, 2012 at 10:23
-
1Is the table partitioned?brian– brian2012年10月31日 17:20:19 +00:00Commented Oct 31, 2012 at 17:20
2 Answers 2
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.
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:
- Yes it can block large queries, the process will take an exclusive lock on the page that is being compressed
- 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
- 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