1107
Chapter 48: Data Compression
48
if suffi cient CPU overhead is available on your system, you can holistically page compress
your entire data warehouse.Most of the references so far have been focused on data warehouses, but what about opera-
tional databases? Should you compress tables in these types of databases? This implemen-
tation requires a more detailed approach. An analysis of the tables, indexes, and their
workload characteristics are required. This analysis is based on two basic metrics: updates
and scans.You can use the sys.dm_db_index_operational_stats dynamic management view to
compute the metrics. Consider page compressing tables with high scan values. For those
tables with high update values, you should avoid compression at any levels. However, as
previously mentioned if the space savings can offset the CPU increase, you could consider
row or page compression. Remember, as with any DMV, the values returned are cumula-
tive since the last server restart. Therefore, ensure that your server has been running long
enough before depending on the values in the DMV.Coupling a few system objects with the aforementioned DMV helps to produce queries that
provide enough information to determine the amount of updates and reads performed on an
object. Use the following query to determine update percentages for each object:SELECT
o.name AS [Table_Name],
ISNULL(x.name, 'HEAP') AS [Index_Name],
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.type = 'U'
ORDER BY [Percent_Update] ASCUse the following query to determine scan percentages for each object:SELECT o.name AS [Table_Name], ISNULL(x.name,'HEAP') AS [Index_Name],
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]c48.indd 1107c48.indd 1107 7/31/2012 10:24:10 AM7/31/2012 10:24:10 AM
http://www.it-ebooks.info