August 27, 2010 at 9:41 am
SQL Server R2 RTM
Regrettably, I don't have test data available, nor can I spend a lot of time on this, but I wanted to let the community know that some counter-intuitive results may occur when adding data to compressed tables.
In some initial testing I've done, if I create a table with a clustered index, insert a million rows, and then REBUILD with page compression, it's faster and has a smaller result than creating a page compressed clustered index first.
The target table is dropped and re-created for each test. The clustered index is the primary key, and is thus unique.
Watching Resource Monitor shows that while the
ALTER INDEX <clustered PK> ON <table with lots of varchars> REBUILD WITH (ONLINE = OFF, DATA_COMPRESSION = PAGE)
does appear to be CPU bound,
INSERT INTO <table with lots of varchars> SELECT (fields, many with RTRIM()) FROM <almost identical table with lots of chars>
is not CPU bound, nor is it disk bound. Performance does not change significantly between this INSERT and one with an ORDER BY <clustered PK> clause (both tables have the same clustered PK. OPTION (MAXDOP n) has no visible effect. Wrapping the INSERT in an explicit transaction has no visible effect.
Basic timings and page_count of the clustered index from sys.dm_db_index_physical_stats
-- insert, no compression: 7-9 seconds, 15678 pages
-- insert, no compression then REBUILD OFFLINE with PAGE compression: 11-13 seconds, 4191 pages
-- insert into page compressed Clustered index: 21-23 seconds, 5999 pages
-- insert into page compressed Clustered index then REBUILD OFFLINE: 23-25 seconds, 4191 pages
Therefore:
Moderately unexpectedly, clustered index ordered INSERTs into a new, empty, page compressed table result in a poorly compressed table. To attain optimal compression, rebuild the clustered index afterwards.
Completely unexpectedly (to me), it is about twice as fast to INSERT into an uncompressed table and then do an OFFLINE index rebuild.
*So far, ONLINE index rebuilds seem to take twice as long as OFFLINE; so an INSERT to uncompressed and then ONLINE rebuild likely is still faster than inserting into a PAGE compressed table in the first place.
Conclusion:
There should be a way to speed up and increase compression ratio when inserting in clustered index order into a table whose clustered index has page compression; allocating more CPU would be one part.
Since INSERT INTO SELECT FROM does not appear to respect fillfactor on either compressed or uncompressed pages, this may have something to do with it.
Question:
Does anyone know either why this happens, or how to improve inserts into a page compressed table?
January 5, 2011 at 2:13 pm
Found your useful commentary, followed by some detailed reference from MS that answers most of your questions:
http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
BULK INSERT Followed by CREATE CLUSTERED INDEX
In many bulk loading scenarios, loading data is typically followed by creating a clustered index. In a sliding window scenario, usually new data is loaded into an empty staging table, and then a clustered index (and other appropriate indexes and constraints) is created on the staging table, to make it ready for switching into an empty partition in a partitioned table. If you are loading data into an empty table and then creating a clustered index, and the data needs to be compressed, there are multiple options:
Option 1: BULK INSERT into uncompressed heap, followed by CREATE CLUSTERED INDEX WITH (DATA_COMPRESSION = PAGE). Because the data is loaded into an uncompressed heap, it allows for faster loading compared to the other two options. This option allows compressing the data at the same time as creating the clustered index, thereby reducing the total time. However, more free space is needed in the user database compared to option 3, because, the uncompressed heap and the compressed clustered index need to reside in the user database simultaneously while the index is being created.
Option 2: BULK INSERT into a page-compressed heap, followed by CREATE CLUSTERED INDEX. Because the data is loaded into a heap, the loading is faster compared to option 3; however, because the heap is compressed, loading takes longer compared to option 1 (the data is compressed while being loaded). And, because the heap and the clustered index need to reside in the user database while the index is created, more free space is needed than option 3; but less than option 1, because both the heap and the clustered index are compressed.
Option 3: BULK INSERT into a page-compressed clustered index. This option takes longer, because the data is loaded into a clustered index, and data is compressed during loading, but all the tasks (loading, compressing, creating clustered index) are completed together. Because there is no post-processing involved in the form of creating the clustered index or compressing the data, no extra free space is required in the user database.
Figure 7 illustrates the time required for bulk loading data, creating a clustered index, and compressing the data. Figure 8 illustrates the workspace required for these tasks.
(see link above)
Figure 7: Time required for bulk data loading, creating a clustered index, and compressing the data (simple recovery model, ONLINE = OFF, SORT_IN_TEMPDB = ON)
(see link above)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply