SQL SERVER ->> Data Compression

Update 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 objectproperty(i.object_id,‘IsUserTable‘) = 1 ORDER BY [Percent_Update] ASC --Scan 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 objectproperty(i.object_id,‘IsUserTable‘) = 1 ORDER BY [Percent_Scan] DESC

 

那么如果要压缩，需要注意些什么呢？或者说对系统有哪些影响呢？

压缩都是通过REBUILD INDEX来完成。

SORT_IN_TEMPDB选项影响tempdb的空间增长

ONLINE影响用户数据库的空间增长和事务日志文件大小的增长

RECOVERY MODE影响事务日志文件大小的增长

 

表或者索引在被压缩的过程中，旧的索引在被替换前是和被压缩后的索引同时存在用户数据库里面的，替换后才把空间还给文件组，那么最起码一条索引所需要的空间只是索引现有空间大小+（索引现有空间大小-savings）

Online选项会消耗更多的CPU

 

对于tempdb空间增长的影响，

 

如果RECOVERY MODE是SIMPLE，每次CHECK

 

SQL SERVER ->> Data Compression

标签：