question

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