您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:读书笔记,数据压缩

新葡亰496net:读书笔记,数据压缩

发布时间:2019-07-05 13:00编辑:网络数据库浏览(127)

    1. 索引重建和重组有什么用?

    当修改表(UPDATE、INSERT、DELETE等)中数据,数据库引擎自动维护索引的数据和结构。但是随着修改次数的累积,可能会现:

    • 索引中记录的数据顺序(逻辑顺序)和数据的实际顺序不一致(物理顺序),这也称之为外部碎片
    • 索引页的数据填充度变小(页密度),也称之为内部碎片
      有索引碎片是正常的,但是有大量的碎片,会降低查询性能,可以通过重建和重组索引来减少或消除碎片。

    1. 决定压缩哪些对象

    1. 决定压缩哪些对象

    《Microsoft Sql server 2008 Internals》索引目录:

    2. 索引重建和重组有什么区别?

    • 重建是删除索引并重新创建。通过这种方式移除碎片、回收磁盘空间(根据现有的或指定的填充因子压缩(Compact)页数据)、对相邻页中的索引进行重新排列。重组索引使用的系统资源最少。它在叶级层从左至右,重新排列叶级页使之于索引的逻辑顺序一致。同时也会对页按填充因子进行压缩。由此可知重建对于消除碎片和空间回收上的程度更高。
    • 重建索引是单个事务,如果指定了ALL关键字,则所有的索引重建做为一个事务。重组索引(包括指定了ALL),在内部会分解为多个较小的事务执行。重建事务回滚,需要回滚所有已经发生的修改。重组可以在任意时间点停止并且只回滚当前的某个较小的事务,已经发生的修改不会回滚(这个有点像DBCC SHRINKFILE)。
    • 重组只能在ONLINE模式下,重建可以指定为ONLINE或者OFFLINE。

    通过sp_estimate_data_compression_savings 评估在ROW和PAGE压缩时分别节省的空间量。

    通过sp_estimate_data_compression_savings 评估在ROW和PAGE压缩时分别节省的空间量。

    《Microsoft Sql server 2008 Internal》读书笔记--目录索引

    3. 索引重建时的ONLINE和OFFLINE选项是什么意思?

    顾名思义,表示重建索引的模式。

    • OFFLINE时,会在表上获取Sch-M锁来阻止所有用户的访问,然后将旧索引的数据复制到新索引中,完成重建后才会释放表锁。
    • ONLINE时,也是复制旧索引数据到新索引中,同时旧索引是可以读写的。重建过程中旧索引的修改操作同时会被应用到新索中,还有一个中间数据结构实现新旧数据的映射和修改冲突。在重建完成后,会使用Sch-M锁定表非常短的时间,然后使用新索引替代旧索引,并释放Sch-M。详情参考:How Online Index Operations Work
    • 本地临时表的索引不能使用ONLINE模式。
    • 相对来说,ONLINE要比OFFLINE使用更多的资源,但提供并发支持。

    表包含如下数据模式时,会有较好的压缩效果:

    表包含如下数据模式时,会有较好的压缩效果:

     

    4. 在重组(或重建)大表的索引时,日志文件变得很大,怎么办?

    说明一下,小表的索引整理问题没有太多意义。

    数据库的所有有损操作都需要记录到日志,这个跟哪种恢复模式没有关系。也就是说从数据库的角度来看,这些日志都是它必须要写的。我们要做的是:引导它少写点日志和提高写日志的性能。下面是一些考虑点:

    • 最重要考虑点:我整理索引的目的是什么?消除碎片,回收空间,迁移数据等等?只有重建/重组索引才能达到我的目的吗?

    • 我们知道重组始终是ONLINE模式,它提供了并发支持,却会使用更多资源。这些资源中就包括日志。这很好验证,构建两个库,创两个同样的表和同样的索引,分别导入足够多的会产生碎片的数据,截断日志后分别执行重组和重建,你会发现重组产生的日志量要远多于重建。

    • 重建索引时的ONLINE和OFFLINE的选择,要结合前一点和实际系统应用情况考虑。我们可以做一些准备工作,比如:重建前先截断日志,对日志文件做一次手动增长来避免自动增长。
    • 事务在提交或者回滚后才能被截断,从前面的问题的,我们也知道重建的事务是原子性的,而重组被分成了多个小事务。也就说,在重建过程中,我们不能截断它的日志,而重组时可以截断。同理,不要在显式事务中使用ONLINE,这会导致显式事务提交后,才能截断日志。
    • 考虑使用 SORT_IN_TEMPDB选项。这个选项使得索引整理的事务日志写到tempdb,而不是用户数据库。这样就减少了用户数据库事务日志量,当然tempdb的空间要足够。如果tempdb位于独立的磁盘,就可以进一步的减少与用户数据库的存储空间和性能的竞争。
    • 如果可能,可以考虑切换到simple和bulk_logged恢复模式,索引的重建和重组可以利用最小化日志减少日志量。最小化日志,它不对每一行数据记录日志,而是对页和区的改变写日志。但是它不支持时间点还原。
    • 如果需要预留日志空间,索引大小的2~3倍会比较安全
    • 数字类型的列和固定长度的字符类型数据,但两者的大多数值都不会用到此类型的所有字节。如INT列的值大多数少于1000.
    • 允许为NULL的列有很多NULL值
    • 列值中有很多一样的值或者相同的前缀。
    • 数字类型的列和固定长度的字符类型数据,但两者的大多数值都不会用到此类型的所有字节。如INT列的值大多数少于1000.
    • 允许为NULL的列有很多NULL值
    • 列值中有很多一样的值或者相同的前缀。

     

    5. 在重建大表的索引时,数据文件也增长到很大了,怎么办?

    索引重建过程中,旧索引结构和新索引结构是并存的,如果是ONLINE模式下,还有一个中间数据结构存在。如果涉及到数据排序操作,数据排序的临时数据结构也是需要占用空间的。跟日志的问题一样,我们能做的是减弱,不可能杜绝

    • 合理配置MAXDOP选项。在SQL Server 2012/2014/2016 Enterprise上,可以使用多个处理器来执行与索引语句关联的扫描、排序和索引操作。默认是0,由SQL Server引擎决定并行度。并不是越大越好,要根据系统和负载合理设置。
    • 对于临时的排序空间,它一次只能被一个索引操作使用,所以如果执行多个索引操作,只需要保证临时排序空间与最大的那个索引一样大即可。例如重建聚集索引,会同时重建相关的非聚集索引,只需要保证预留的空间与其中最大那个索引一样大即可。
    • 当SORT_IN_TEMPDB=ON时,临时排序空间则位于tempdb(重建索引的事务日志也在tempdb)。如=OFF,则排序空间位于当前用户数据库中。
    • 对于ONLINE模式重建的中间数据结构的位置,由SORT_IN_TEMPDB决定,跟上一点一样。
    • ONLINE操作使用行版本控制,这样读取行时不需要S锁,避免了并发的数据修改事务对索引操作的影响。使用了行版本,对于并发的数据修改操作,在tempdb中存储相关的行版本数据也需要一些空间。

    表包含如下数据模式时,压缩效果较差:

    表包含如下数据模式时,压缩效果较差:

      在上篇文章中,主要简单介绍了数据库的存放机制和snapshot的简单应用。下来我们看看一个基本的系统数据库Tempdb,关于tempdb,MSDN有详细的描述,

    总结

    1. 索引整理优化,对tempdb的使用较多,而tempdb本身的配置也是需要优化的。如果可能,将索引和数据分开存储,于性能和管理也有一定帮助。
    2. 将平时的一些零散的记录整理汇总而成,如有疏谬,请轻拍。
    • 数字类型的列和固定长度的字符类型数据,但是两者的大多数值都会用尽此类型的所有字节。
    • 非常少量的重复值
    • 重复值不具有相同的前缀
    • 数据存储在行外
    • FILESTREAM数据
    • 数字类型的列和固定长度的字符类型数据,但是两者的大多数值都会用尽此类型的所有字节。
    • 非常少量的重复值
    • 重复值不具有相同的前缀
    • 数据存储在行外
    • FILESTREAM数据

    请参考;

     

     

    一些关于tempdb的基本常识:

    2. 评估应用负载模式

    2. 评估应用负载模式

    新葡亰496net 1新葡亰496net 2代码

    被压缩的页在磁盘和内存都是压缩的。下面两种情况下会被解压缩(不是整页解压缩,只解压缩相关的数据):

    被压缩的页在磁盘和内存都是压缩的。下面两种情况下会被解压缩(不是整页解压缩,只解压缩相关的数据):

    tempdb 系统数据库是一个全局资源,可供连接到 SQL Server 实例的所有用户使用,并可用于保存下列各项:

    • 因为查询中的filtering, sorting, joining操作而被读取
    • 被应用程序更新
    • 因为查询中的filtering, sorting, joining操作而被读取
    • 被应用程序更新

        * 显式创建的临时用户对象,例如全局或局部临时表、临时存储过程、表变量或游标。
        * SQL Server 数据库引擎创建的内部对象,例如,用于存储假脱机或排序的中间结果的工作表。
        * 由使用已提交读(使用行版本控制隔离或快照隔离事务)的数据库中数据修改事务生成的行版本。
        * 由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。

    解压缩会消耗CPU,但是数据压缩会减少物理IO和逻辑IO,同时会提高缓存效率。对于数据扫描操作,减少的IO量非常可观。对于单个的查找操作,减少的IO量较少。

    解压缩会消耗CPU,但是数据压缩会减少物理IO和逻辑IO,同时会提高缓存效率。对于数据扫描操作,减少的IO量非常可观。对于单个的查找操作,减少的IO量较少。

    tempdb 中的操作是最小日志记录操作。这将使事务产生回滚。每次启动 SQL Server 时都会重新创建 tempdb,从而在系统启动时总是保持一个干净的数据库副本。在断开联接时会自动删除临时表和存储过程,并且在系统关闭后没有活动连接。因此 tempdb 中不会有什么内容从一个 SQL Server 会话保存到另一个会话。不允许对 tempdb 进行备份和还原操作。 

    行压缩导致的CPU开销通常不会超过10%。如果当前的系统资源充足,增加10%CPU毫无压力的话,建议所有的表都启用行压缩。

    行压缩导致的CPU开销通常不会超过10%。如果当前的系统资源充足,增加10%CPU毫无压力的话,建议所有的表都启用行压缩。

    tmpdb的主数据逻辑名为tempdev,文件名为tempdb.mdf,10%自增长到硬盘空间满。日志逻辑名为templog,文件名为templog.ldf,10%自增长到2TB。

    页压缩比行压缩的CPU开销高一些,所以确定是否使用页压缩会困难一些。可以通过一些简单的准则来帮助我们判断:

    页压缩比行压缩的CPU开销高一些,所以确定是否使用页压缩会困难一些。可以通过一些简单的准则来帮助我们判断:

    tempdb 的大小可以影响系统性能。例如,如果 tempdb 的大小太小,则每次启动 SQL Server 时,系统处理可能忙于数据库的自动增长,而不能支持工作负荷要求。可以通过增加 tempdb 的大小来避免此开销。

    • 从那些不常用的表和索引开始
    • 新葡亰496net:读书笔记,数据压缩。如果系统没有足够的CPU余量,不要使用页压缩
    • 因为 filtering, joins, aggregates和sorting操作使用解压缩后的数据,所以数据压缩对这类查询没有太多帮助。如果工作负载主要由非常复杂的查询(多表JOIN,复杂聚合)组成,页压缩不会提高性能,最主要是节省存储空间。
    • 大型数据仓库系统中,扫描性能是其重点,同时存储设备的成本较高,在CPU性能允许下,建议对所有表使用页压缩。
    • 从那些不常用的表和索引开始
    • 如果系统没有足够的CPU余量,不要使用页压缩
    • 因为 filtering, joins, aggregates和sorting操作使用解压缩后的数据,所以数据压缩对这类查询没有太多帮助。如果工作负载主要由非常复杂的查询(多表JOIN,复杂聚合)组成,页压缩不会提高性能,最主要是节省存储空间。
    • 大型数据仓库系统中,扫描性能是其重点,同时存储设备的成本较高,在CPU性能允许下,建议对所有表使用页压缩。

    不能对 tempdb 数据库执行以下操作:

    可以通过两个更细的度量值来帮我们评估使用何种数据压缩方式:

    可以通过两个更细的度量值来帮我们评估使用何种数据压缩方式:

        * 添加文件组。
        * 备份或还原数据库。
        * 更改排序规则。默认排序规则为服务器排序规则。
        * 更改数据库所有者。tempdb 的所有者是 dbo。
        * 创建数据库快照。
        * 删除数据库。
        * 从数据库中删除 guest 用户。
        * 启用变更数据捕获。
        * 参与数据库镜像。
        * 删除主文件组、主数据文件或日志文件。
        * 重命名数据库或主文件组。
        * 运行 DBCC CHECKALLOC。
        * 运行 DBCC CHECKCATALOG。
        * 将数据库设置为 OFFLINE。
        * 将数据库或主文件组设置为 READ_ONLY。

    • U:特定对象(表、索引或者分区)的更新操作占所有操作的百分比。越低越适合页压缩。
    • S:特定对象(表、索引或者分区)的扫描操作占所有操作的百分比。越高越适合页压缩。
    • U:特定对象(表、索引或者分区)的更新操作占所有操作的百分比。越低越适合页压缩。
    • S:特定对象(表、索引或者分区)的扫描操作占所有操作的百分比。越高越适合页压缩。

    以上部分来自MSDN,现在补充一些:

    通过如下脚本查询数据库所有对象的U:

    通过如下脚本查询数据库所有对象的U:

    1、tempdb和其他数据库最大的不同是:它每次在SQL Server启动时是被重建(Re-Create)的,而不是被复原(Recovered)的 。我们可以把它看成一个工作空间,有点像Eclipse或powerDesigner的工作空间(workspace),这个workspace存放了临时的用户对象和内部对象(internal Objects),这些对象被SQL自身显式创建。

    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
    
    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
    

    2、 每次tempdb被重建时,它会从model数据库继承大多数的数据库选项,但是还原模式下不会copy这些选项,因为 tempdb总是使用简单恢复(Simple recovery)。

    通过如下脚本查询数据库所有对象的S:

    通过如下脚本查询数据库所有对象的S:

    3、在简单恢复模式(SIMPLE recovery model)下 ,tempdb的日志不断被清空,并且不能被恢复。因为每次SQL重新启动时,前一个用户创建的所有临时对象都消失了。

    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
    
    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
    

    4、日志文件与其他日志不同,仅仅保存了一些用于回滚事务的必要信息,而不能恢复非事务以外的其他信息,

     

     

    5、还原一个正在运行的数据库的第一步,是创建快照。我们无法还原tempdb,是因为我们无法创建它的一个快照。 这意味着我们不能使用DBCC CheckDB选项,另外一个区别是DBCC收缩时,SQL Server将跳过所有的分配(Allocation)和分类(Catalog)检查。

    这两个查询用到了DMV sys.dm_db_index_operational_stats。DMV只是记录上次SQL Server实例启动以来的积累值,所以在实际应用中要选择一个合适的时间来查询。

    这两个查询用到了DMV sys.dm_db_index_operational_stats。DMV只是记录上次SQL Server实例启动以来的积累值,所以在实际应用中要选择一个合适的时间来查询。

    tempdb的对象(Objects) 

    通常U<20%和S>75%会是比较合理的考虑启用压缩的出发点,但是对于只插入有序数据的流水表,页压缩会比较合适(即使S值很低)。

    通常U<20%和S>75%会是比较合理的考虑启用压缩的出发点,但是对于只插入有序数据的流水表,页压缩会比较合适(即使S值很低)。

    tempdb存储的对象包括:用户对象、内部对象和一些版本存储,主要是用于快照隔离。

     

     

    用户对象(user Objects)包括所有以#或##开头的临时表,还有表变量(table variables)和表值函数(table-valued function) ,所有这些需要空间来物理存放。

    3. 评估资源需求

        使用ALTER TABLE… REBUILD和ALTER INDEX … REBUILD对表和索引启用压缩,其它原理和重建索引是一样的。通常需要的资源包括空间、CPU、IO、空间需求

    在压缩过程中,已压缩的表和未压缩表是并存的,只有完成压缩后,未压缩的表才会被删除并释放空间。如果Rebuild是ONLINE的话,则还有Mapping Index需要额外的空间。

    事务的空间需求由压缩方式是否是ONLINE(ON or OFF)和数据库的恢复模式决定。

    当SORT_IN_TEMPDB=ON时(推荐为ON),为了实现并发DML操作,会在tempdb中Mapping index的内部结构来映射旧书签和新书签的关系。对于版本化存储的,tempdb的使用量由并发DML操作所涉及的数据量和事务时间长度决定。

    通常行压缩操作的CPU开销是重建一个索引的1.5倍左右,页压缩是它的2到5倍。ONLINE模式还需要额外的CPU资源。Rebuild和Compress可以被并行化的,所以还要结合MAXDOP一起考虑。

    并行化的注意事项:

    • SQL Server在Create/Rebuild/Compress一个索引时,使用索引首列(最左列)的统计信息确定并行操作在多个CPU间的分布。所以当索引首列的筛选度不高,或者数据倾斜严重使得首列的值很少时,并行化对性能提升的帮助就很少。
    • 使用ONLINE=ON方式Compress/Rebuild堆表是单线程操作。但是压缩和重建的表扫描操作是并行多线程的。

    下表总结对比了压缩和重建一个聚集索的资源开销:

    • X = 压缩或者重建前的页数量
    • P = 压缩后的页数量(P < X)
    • Y = 新增和被更新的页数据 (只适用于ONLINE=ON时并发应用所做修改)
    • M = Mapping index的大小 (基于<TEMPDB Capacity Planning>白皮书的预估值)
    • C = 重建聚集索引所需CPU时间

    新葡亰496net 3

    在判断何时和怎么压缩数据时,下面是一些参考点:

    • Online vs. Offline:

            Offline更快,需要的资源也更少,但是压缩操作过程中会锁表。Online自身也会有一些限制。

    • 一次压缩一个table/index/partition vs. 多个操作并发:

            这个由当前资源的余量决定,如果资源很充足,多个压缩操作并行也可以接受的,否则最好一次一个。

    • 表压缩操作的顺序:

            从小表开始,小表压缩需要的资源少,完成快。完成后释放的资源也利于后续表的压缩操作。

    • SORT_IN_TEMPDB= ON or OFF:

            推荐ON。这样可以利用tempdb来存放和完成Mapping index操作,从而也减少用户数据的空间需求。

    压缩操作副作用:

    • 压缩操作包括重建操作,所以会移除表或索引上的碎片。
    • 压缩堆表时,如果有非聚集索引存在,则:当ONLINE=OFF,索引重建是串行操作,ONLINE=ON,索引重建是并操作。

     

    4. 维护压缩数据

     

    新插入数据的压缩方式

    新葡亰496net 4

    *通过以页压缩方式重建堆表来将行级压缩页转换为页级压缩。

    **页压缩中,并不是所有的页都是页压缩的,只有当页压缩节省的空间量超过一个内存阈值时才是。

     

    更新和删除已压缩的行

    所有对行压缩表/分区数据行的更新会保持行压缩格式。并不是每次对页压缩表/分区的数据行的更新都会导致列前缀和页字典被重新计算,只有当在上的更新数量超过某个内部阈值时,才会重新计算。

     

    辅助数据结构的行为

    Table compression

    Transaction log

    Mapping index for rebuilding the clustered index

    Sort pages for queries

    Version store (with SI or RCSI isolation level)

    ROW

    ROW

    NONE

    NONE

    ROW

    PAGE

    ROW

    NONE

    NONE

    ROW

     

    页压缩索引的非叶级页是行压缩的

    索引的非叶级页相对较小,就算应用页级压缩,节省的空间也不会很显著。而对非叶级页的访问会很频繁,使用行级压缩可减少每次访问时解压缩成本。

     

    5. 回收数据压缩释放的空闲空间

    1. 不回收,留着给将在的数据增长使用。这个不适合分区表(每个分区对应一人不同的文件级)的只读分区,压缩旧的只读分区不会增长,压缩可以节省大量空间。
    2. DBCC SHRINKFILE (或者DBCC SHRINKDATABASE) 。这个操作会带来大量碎片,同时它是一个单线程操作,可能会耗时较长。
    3. 如果压缩了一个文件组上的所有表,则新建一个文件组,然后在压缩时将表和索引移动到新的文件组。数据移动可以通过Create/Recreate聚集索引的方式实现(如,WITH (DATA_COMPRESSION=PAGE, DROP_EXISTING=ON, SORT_IN_TEMPDB=ON) ON [FG_NEW] )。移动完数据之后,删除原来的文件组即可。但是这种方式不能移动LOB_DATA数据到新文件组。
    4. 在新文件组上创建压缩的表,然后将数据导入到这些表。

     

    6. BULK INSERT 和数据压缩

    BULK INSERT WITH (TABLOCK)导入数据到已压缩的表,速度最快。很明显,这会锁表。

    压缩数据时,BULK INSERT和创建聚集索引的顺序考虑:

    序号

    方式

    比较

    1

    BULK INSERT导入数据到未压缩的堆表,然后再 CREATE CLUSTERED INDEX WITH (DATA_COMPRESSION = PAGE).

    所需时间:1<2<3

    2

    BULK INSERT导入数据到页压缩的堆表,然后再  CREATE CLUSTERED INDEX

    所需空间:1>2>3

    3

    BULK INSERT导入数据到页压缩的聚集索引

     

     

    7. 数据压缩和分区表维护

    1. Switch操作要求目标分区(或目标表)与源分区的压缩方式相同。

    2. Split后的分区继承原分区的压缩方式。

    3. Merger操作,被删除的分区称为源分区,接收数据的分区称为目标分区:

    目标分区的压缩方式

    数据合并到目标分区的方式

    NONE

    在Merger期间,数据会被解压缩到目标分区

    ROW

    在Merger期间,数据会被转换成行压缩格式

    PAGE

    -堆表: 在Merger期间,数据会被转换成行压缩格式

    - 聚集索引: 在Merger期间,数据会被转换成页压缩格式

    分区表Merger操作规则

    1. LEFT RANGE时,删除边界值所在的分区,保留"左"侧的分区,并向其移动数据

    2. RIGHT RANGE时,删除边界值所在的分区,保留"右"分区,并向其移动数据

     

    8. 数据压缩和透明数据加密(TDE)

    TDE是当数据页写入磁盘时加密,从磁盘中读出页放入到内存时解密。而数据压缩/解压缩操作是对内存中的页执行的,所以数据压缩/解压缩总是用到解密后的页。因此两者之前的相互影响很小。

    ----20160725 添加以下内容----

    9. 数据压缩和复制

    如果将数据压缩与复制一起使用,则应注意以下事项:

    • 当快照代理生成初始架构脚本时,新架构将对表及其索引使用相同的压缩设置。 不能仅对表启用压缩,而不对索引启用压缩。

    • 对于事务复制,项目架构选项决定了必须对哪些依赖对象和属性编写脚本。 有关详细信息,请参阅 sp_addarticle。

      分发代理在应用脚本时,不对下级订阅服务器进行检查。 如果选择了压缩的复制,则在下级订阅服务器上创建表将会失败。 在混合拓扑中,不启用压缩的复制。

    • 对于合并复制,发布兼容级别优先于架构选项,并决定了将编写脚本的架构对象。

      在混合拓扑中,如果不是必须支持新的压缩选项,则发布兼容级别应设置为下级订阅服务器版本。 否则,应在创建表后在订阅服务器上压缩表。

    下表列出了在复制期间控制压缩的复制设置。

    User intent
     Replicate partition scheme for a table or index
    Replicate compression settings
    Scripting behavior
    复制分区方案并在该分区上的订阅服务器上启用压缩。 True True 对分区方案和压缩设置均编写脚本。
    复制分区方案,但不压缩订阅服务器上的数据。 True False 对分区方案编写脚本,但不对分区的压缩设置编写脚本。
    不复制分区方案,也不压缩订阅服务器上的数据。 False False 不对分区和压缩设置编写脚本。
    如果发布服务器上的所有分区均压缩,则压缩订阅服务器上的表,但不复制分区方案。 False True 检查是否对所有分区均启用了压缩。

    在表级别对压缩编写脚本。

     

    10. 压缩对其他 SQL Server 组件的影响

    压缩发生在存储引擎中,数据以未压缩状态呈现给 SQL Server 的其他大部分组件。 这决定了其他组件上的压缩效果仅限于以下方面:

    • 大容量导入和导出操作

      导出数据时,即使采用本机格式,数据也以未压缩的行格式输出。 这会导致导出的数据文件的大小比源数据要大得多。

      导入数据时,如果已对目标表启用压缩,则存储引擎会将数据转换为压缩的行格式。 这样所使用的 CPU 资源会比将数据导入未压缩表时使用的 CPU 资源多。

      如果以大容量方式将数据导入具有页压缩设置的堆,则在插入数据时,大容量导入操作会尝试使用页压缩来压缩数据。

    • 压缩对备份和还原没有影响。

    • 压缩对日志传送没有影响。

    • 数据压缩与稀疏列不兼容。 因此,无法压缩包含稀疏列的表,也不能将稀疏列添加到压缩表。

    • 启用压缩可以导致查询计划更改,因为数据是用不同的页数和每页不同的行数存储的。

     

    总结

    1. 本文来基于白皮书<Data Compression: Strategy, Capacity Planning and Best Practices.aspx)>的简译和总结。此白皮书是基于SQL Server 2008的。

    2. 数据压缩是一个被低估SQL Server技术,个人认为很有必要将之做为标准化最佳实践之一。

    3. 评估资源需求

        使用ALTER TABLE… REBUILD和ALTER INDEX … REBUILD对表和索引启用压缩,其它原理和重建索引是一样的。通常需要的资源包括空间、CPU、IO、空间需求

    在压缩过程中,已压缩的表和未压缩表是并存的,只有完成压缩后,未压缩的表才会被删除并释放空间。如果Rebuild是ONLINE的话,则还有Mapping Index需要额外的空间。

    事务的空间需求由压缩方式是否是ONLINE(ON or OFF)和数据库的恢复模式决定。

    当SORT_IN_TEMPDB=ON时(推荐为ON),为了实现并发DML操作,会在tempdb中Mapping index的内部结构来映射旧书签和新书签的关系。对于版本化存储的,tempdb的使用量由并发DML操作所涉及的数据量和事务时间长度决定。

    通常行压缩操作的CPU开销是重建一个索引的1.5倍左右,页压缩是它的2到5倍。ONLINE模式还需要额外的CPU资源。Rebuild和Compress可以被并行化的,所以还要结合MAXDOP一起考虑。

    并行化的注意事项:

    • SQL Server在Create/Rebuild/Compress一个索引时,使用索引首列(最左列)的统计信息确定并行操作在多个CPU间的分布。所以当索引首列的筛选度不高,或者数据倾斜严重使得首列的值很少时,并行化对性能提升的帮助就很少。
    • 使用ONLINE=ON方式Compress/Rebuild堆表是单线程操作。但是压缩和重建的表扫描操作是并行多线程的。

    下表总结对比了压缩和重建一个聚集索的资源开销:

    • X = 压缩或者重建前的页数量
    • P = 压缩后的页数量(P < X)
    • Y = 新增和被更新的页数据 (只适用于ONLINE=ON时并发应用所做修改)
    • M = Mapping index的大小 (基于<TEMPDB Capacity Planning>白皮书的预估值)
    • C = 重建聚集索引所需CPU时间

    新葡亰496net 5

    在判断何时和怎么压缩数据时,下面是一些参考点:

    • Online vs. Offline:

            Offline更快,需要的资源也更少,但是压缩操作过程中会锁表。Online自身也会有一些限制。

    • 一次压缩一个table/index/partition vs. 多个操作并发:

            这个由当前资源的余量决定,如果资源很充足,多个压缩操作并行也可以接受的,否则最好一次一个。

    • 表压缩操作的顺序:

            从小表开始,小表压缩需要的资源少,完成快。完成后释放的资源也利于后续表的压缩操作。

    • SORT_IN_TEMPDB= ON or OFF:

            推荐ON。这样可以利用tempdb来存放和完成Mapping index操作,从而也减少用户数据的空间需求。

    压缩操作副作用:

    • 压缩操作包括重建操作,所以会移除表或索引上的碎片。
    • 压缩堆表时,如果有非聚集索引存在,则:当ONLINE=OFF,索引重建是串行操作,ONLINE=ON,索引重建是并操作。

     

    4. 维护压缩数据

     

    新插入数据的压缩方式

    新葡亰496net 6

    *通过以页压缩方式重建堆表来将行级压缩页转换为页级压缩。

    **页压缩中,并不是所有的页都是页压缩的,只有当页压缩节省的空间量超过一个内存阈值时才是。

     

    更新和删除已压缩的行

    所有对行压缩表/分区数据行的更新会保持行压缩格式。并不是每次对页压缩表/分区的数据行的更新都会导致列前缀和页字典被重新计算,只有当在上的更新数量超过某个内部阈值时,才会重新计算。

     

    辅助数据结构的行为

    Table compression

    Transaction log

    Mapping index for rebuilding the clustered index

    Sort pages for queries

    Version store (with SI or RCSI isolation level)

    ROW

    ROW

    NONE

    NONE

    ROW

    PAGE

    ROW

    NONE

    NONE

    ROW

     

    页压缩索引的非叶级页是行压缩的

    索引的非叶级页相对较小,就算应用页级压缩,节省的空间也不会很显著。而对非叶级页的访问会很频繁,使用行级压缩可减少每次访问时解压缩成本。

     

    5. 回收数据压缩释放的空闲空间

    1. 不回收,留着给将在的数据增长使用。这个不适合分区表(每个分区对应一人不同的文件级)的只读分区,压缩旧的只读分区不会增长,压缩可以节省大量空间。
    2. DBCC SHRINKFILE (或者DBCC SHRINKDATABASE) 。这个操作会带来大量碎片,同时它是一个单线程操作,可能会耗时较长。
    3. 如果压缩了一个文件组上的所有表,则新建一个文件组,然后在压缩时将表和索引移动到新的文件组。数据移动可以通过Create/Recreate聚集索引的方式实现(如,WITH (DATA_COMPRESSION=PAGE, DROP_EXISTING=ON, SORT_IN_TEMPDB=ON) ON [FG_NEW] )。移动完数据之后,删除原来的文件组即可。但是这种方式不能移动LOB_DATA数据到新文件组。
    4. 在新文件组上创建压缩的表,然后将数据导入到这些表。

     

    6. BULK INSERT 和数据压缩

    BULK INSERT WITH (TABLOCK)导入数据到已压缩的表,速度最快。很明显,这会锁表。

    压缩数据时,BULK INSERT和创建聚集索引的顺序考虑:

    序号

    方式

    比较

    1

    BULK INSERT导入数据到未压缩的堆表,然后再 CREATE CLUSTERED INDEX WITH (DATA_COMPRESSION = PAGE).

    所需时间:1<2<3

    2

    BULK INSERT导入数据到页压缩的堆表,然后再  CREATE CLUSTERED INDEX

    所需空间:1>2>3

    3

    BULK INSERT导入数据到页压缩的聚集索引

     

     

    7. 数据压缩和分区表维护

    1. Switch操作要求目标分区(或目标表)与源分区的压缩方式相同。

    2. Split后的分区继承原分区的压缩方式。

    3. Merger操作,被删除的分区称为源分区,接收数据的分区称为目标分区:

    目标分区的压缩方式

    数据合并到目标分区的方式

    NONE

    在Merger期间,数据会被解压缩到目标分区

    ROW

    在Merger期间,数据会被转换成行压缩格式

    PAGE

    -堆表: 在Merger期间,数据会被转换成行压缩格式

    - 聚集索引: 在Merger期间,数据会被转换成页压缩格式

    分区表Merger操作规则

    1. LEFT RANGE时,删除边界值所在的分区,保留"左"侧的分区,并向其移动数据

    2. RIGHT RANGE时,删除边界值所在的分区,保留"右"分区,并向其移动数据

     

    8. 数据压缩和透明数据加密(TDE)

    TDE是当数据页写入磁盘时加密,从磁盘中读出页放入到内存时解密。而数据压缩/解压缩操作是对内存中的页执行的,所以数据压缩/解压缩总是用到解密后的页。因此两者之前的相互影响很小。

    ----20160725 添加以下内容----

    9. 数据压缩和复制

    如果将数据压缩与复制一起使用,则应注意以下事项:

    • 当快照代理生成初始架构脚本时,新架构将对表及其索引使用相同的压缩设置。 不能仅对表启用压缩,而不对索引启用压缩。

    • 对于事务复制,项目架构选项决定了必须对哪些依赖对象和属性编写脚本。 有关详细信息,请参阅 sp_addarticle。

      分发代理在应用脚本时,不对下级订阅服务器进行检查。 如果选择了压缩的复制,则在下级订阅服务器上创建表将会失败。 在混合拓扑中,不启用压缩的复制。

    • 对于合并复制,发布兼容级别优先于架构选项,并决定了将编写脚本的架构对象。

      在混合拓扑中,如果不是必须支持新的压缩选项,则发布兼容级别应设置为下级订阅服务器版本。 否则,应在创建表后在订阅服务器上压缩表。

    下表列出了在复制期间控制压缩的复制设置。

    User intent
     Replicate partition scheme for a table or index
    Replicate compression settings
    Scripting behavior
    复制分区方案并在该分区上的订阅服务器上启用压缩。 True True 对分区方案和压缩设置均编写脚本。
    复制分区方案,但不压缩订阅服务器上的数据。 True False 对分区方案编写脚本,但不对分区的压缩设置编写脚本。
    不复制分区方案,也不压缩订阅服务器上的数据。 False False 不对分区和压缩设置编写脚本。
    如果发布服务器上的所有分区均压缩,则压缩订阅服务器上的表,但不复制分区方案。 False True 检查是否对所有分区均启用了压缩。

    在表级别对压缩编写脚本。

     

    10. 压缩对其他 SQL Server 组件的影响

    压缩发生在存储引擎中,数据以未压缩状态呈现给 SQL Server 的其他大部分组件。 这决定了其他组件上的压缩效果仅限于以下方面:

    • 大容量导入和导出操作

      导出数据时,即使采用本机格式,数据也以未压缩的行格式输出。 这会导致导出的数据文件的大小比源数据要大得多。

      导入数据时,如果已对目标表启用压缩,则存储引擎会将数据转换为压缩的行格式。 这样所使用的 CPU 资源会比将数据导入未压缩表时使用的 CPU 资源多。

      如果以大容量方式将数据导入具有页压缩设置的堆,则在插入数据时,大容量导入操作会尝试使用页压缩来压缩数据。

    • 压缩对备份和还原没有影响。

    • 压缩对日志传送没有影响。

    • 数据压缩与稀疏列不兼容。 因此,无法压缩包含稀疏列的表,也不能将稀疏列添加到压缩表。

    • 启用压缩可以导致查询计划更改,因为数据是用不同的页数和每页不同的行数存储的。

     

    总结

    1. 本文来基于白皮书<Data Compression: Strategy, Capacity Planning and Best Practices.aspx)>的简译和总结。此白皮书是基于SQL Server 2008的。

    2. 数据压缩是一个被低估SQL Server技术,个人认为很有必要将之做为标准化最佳实践之一。

    内部对象(intenal Objects) 用正常的工具看不到,但他们仍然占用空间,这些对象不能被目录视图(catalog View)列出,因为,它们的元数据是存放在内存中的,三种基本类型的内部对象是:工作表(work tables)、工件文件(work files)、排序单元(sort units)

    在执行下列操作时工作表被SQL Server创建:

    1、 执行一个大查询(large query)时后台存放中间结果。

    2、运行DBCC CHECKDB或DBCC CHECKTABLE

    3、与XML变量或Varchar(Max)一起工作时

    新葡亰496net,4、处理数据库中问对象(Broker Objects)时

    5、与静态或键集(keyset)的游标一起工作时

    工作文件,在SQL Server处理一个哈希操作或关联/聚合查询时被使用。

    排序单元在执行一个排序子句时被创建,排序单元保存了一些被排序的数据,通常是order by 或聚合操作的排序结果

    版本存储(version store)提供了对Row-Level行级数据的支持,所有已更新的旧行在以下情况下被保留:

    1、当一个Alter trigger被触发时

    2、在一个允许快照事务的数据库中,当一个DML(Data Modification Language)语句被执行时

    3、当一个MARS(multiple active result sets) 被客户端应用程序调用时。

    4、创建一个在线索引或索引中有一个并发的DML语句时重建索引。

     

    tempdb的优化

    tempdb的优化可以参看MSDN:

     http://msdn.microsoft.com/zh-cn/library/ms175527.aspx

    这里再补充一些:

    1、tempdb有可能是生产环境中创建或删除新对象最多的数据库,如果可能,请采用多个数据文件的形式,这样.SQL Server会自动进行有效的空间分配。并会计划每个单独文件的自由空间。

    2、如果需要删除(drop)一个工作表,或者一个小于8M的用户对象时,一个IAM和一个extent(范围)被存储,这会重新引起空间的再分配,这时需要借助tempdb的cache功能。

    3、删除(drop)一个大表时,所有数据库的删除立即进行,不必线程等待,一个后台线程会清除所有分配给已删除表的空间,但此时tempdb的分配空间仍然未变。

     优化有最佳实践:

    1、默认情况下,tempdb数据库创建时只有一个数据文件,你可能会发现,用多个文件会使你的I/O性能更佳,并且在全局分配结构(如GAM,SGAM,and PFS页)减少争夺(contention),一个推荐的初始化设置是每CPU一个Data File,不过,你最好根据自己的数据量和使用模式(usage pattern)作测试。为了取得计划填充算法的最佳效率,各个文件应该保持一致的大小。多个文件的副作用是每个对象将拥有多个IAM页,这将增加访问该对象时切换成本。无论一个或多个文件,你都应该把数据文件放在最快的分区。一个日志文件足够,但也最好放在最快的分区。注意,邀月提醒:默认文件tempdb位于初始安装目录下,所以SQL 2008强烈不推荐安装在默认的X:Program FilesMicrosoft SQL ServerMSSQL10.AGRONET08MSSQLDATA下,可以像我这样,G:SQL2008MSSQL10.AGRONET08MSSQLDATA、这个G盘的性能是最佳的,呵呵。

    2、 为了决定tempdb的最优大小。请结合你的数据量和应用程序做测试,但是知道tempdb什么时候和怎样被使用将有助于你作初步的评估。记住:每个SQL实例只有一个tempdb,一个很蹩脚的应用程序将会影响到所有其他应用程序中的所有用户。

    3、不建议收缩tempdb数据库,特别是自动收缩选项将直接被忽略。收缩tempdb的最佳方式是alter database改变文件大小,然后停止并重启SQL Server以便tempdb被重建到完全的大小。你应该允许tempdb自增长以防止空间不够而出错,但文件的大小完全由自增长来控制是更错误的做法,文件的大小还是应该籍由计划测试得出。

    下面是一些最佳做法的小提示:

    ◆充分利用tempdb对象缓存

    ◆事务尽可能的短,特别是使用事务隔离,MARS或触发器的事务。

    如杲你预料到更多的分配页面争夺,可以强制一个查询计划更少的使用tempdb.邀月对这句不理解?原文是:If you expect a lot of allocation pages contention,force a query plan that uses tempdb less.

    ◆通过保持列固定大小而不是可变大小,避免空间的分配和释放,例如update就比"一个delete紧跟一个insert"要好的多。

    ◆在同一个数据库实例中,如果版本(versioning)被使用,不要混合位于不同数据库中的长事务与短事务。

    tempdb的空间监测

    只有一个视图可以用于tempdb的空间监测,sys.dm_db_file_space_usage

     小结:tempdb是一个很重要也很独特的系统数据库,本文对其基本特性作了一些描述,后面的章节将会继续提到相关的内容。

    下面一章将是第五章Tables,更具有实战意义的一章。

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:读书笔记,数据压缩

    关键词:

上一篇:数量定义语言,SqlServer中Alter语句的运用

下一篇:没有了