question

gotqn avatar image
gotqn asked

How row compression affects CRUD operations?

I have a table with clustered index and non clustered index. Generally, when the a query is executed the non clustered index is used only, because it is covering. I have read about how page and row compression could reduce size and I/O operations and decided to test it with this table. First, of all, I have run `sp_estimate_data_compression_savings` to check how much space is going to be saved: - The clustered index size can be reduced from 6.6 GB to 3.6 GB(row compression) or 3.3 GB(page compression) - The non clustered index size can be reduced from 2 GB to 1.3 GB(row compression) or 1.1(page compression) Then following the [official documentation ][1]I have run two queries to check how often the indexes are scan and update: Updates: NONCLUSTERED 0.081370623928375 CLUSTERED 12.355644704896153 Scans: CLUSTERED 0.672507084973161 NONCLUSTERED 0.021474712661825 and here are the queries I have used from the article: SELECT o.name AS [Table_Name], x.name AS [Index_Name], i.partition_number AS [Partition], i.index_id AS [Index_ID], x.type_desc AS [Index_Type], i.leaf_update_count * 100.0 / (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + i.leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count ) AS [Percent_Update] FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i JOIN sys.objects o ON o.object_id = i.object_id JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id WHERE (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count) != 0 and o.name IN ('tablename') AND objectproperty(i.object_id,'IsUserTable') = 1 ORDER BY [Percent_Update] ASC SELECT o.name AS [Table_Name], x.name AS [Index_Name], i.partition_number AS [Partition], i.index_id AS [Index_ID], x.type_desc AS [Index_Type], i.range_scan_count * 100.0 / (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + i.leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count ) AS [Percent_Scan] FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i JOIN sys.objects o ON o.object_id = i.object_id JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id WHERE (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count) != 0 and o.name IN ('tablename') AND objectproperty(i.object_id,'IsUserTable') = 1 ORDER BY [Percent_Scan] DESC According to the article it is important to have many scans and less updates to get benefits from compression. I have compressed only the non-clustered index using row compression and indeed the execution plan shows less IO cost (anyway there was not execution time improvement but I guess it will behave different in production environment). Could anyone tell what is the impact of compression over Insert and Update operations (I supposed there is no difference when deleting)? Also, if the clustered index in my case is not often scan is it a good idea to compressed it and reduce used space? [1]: http://msdn.microsoft.com/en-us/library/cc280449.aspx
clustered-indexcompressiondata-compression
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Scans are not the only thing that benefit when using compression. Seeks benefit as well since they also will walk between pages within a range on the index. So having more rows per page will help there too. The performance cost for compression is very low. If you're system is not currently under CPU stress, there's a good chance you'll never even notice. But, if you do lots and lots of large data loads, those may run considerably slower.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Generally, you have confirmed what I have read. Are you aware of any ebooks or articles that are explaining and analyzing compression in more depth - talking about ROW and PAGE compression only.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.