none
SQLSERVER 2008的数据表压缩问题请教,谢谢! RRS feed

  • 问题

  • 1、将表用语句ALTER TABLE tbname REBUILD WITH ( DATA_COMPRESSION = PAGE )将数据表压缩后,怎么判断这个表已经压缩过了?

    2、为什么用1中的语句压缩数据表,比先创建压缩表,再插入数据快很多呢,我一个3千多万条的数据表对于无压缩的表插入数据需要9:48,对于压缩的表需要15:16,而用1中的语句压缩表只需要几十秒就可以了,这是为什么呢?

    谢谢各位大侠!

    2018年8月29日 2:39

答案

  • Hi Tim-2009,

    你可以试试这个查询:

    SELECT st.name, st.object_id, sp.partition_id, sp.partition_number, sp.data_compression, 
    sp.data_compression_desc FROM sys.partitions SP
    INNER JOIN sys.tables ST ON
    st.object_id = sp.object_id
    WHERE data_compression <> 0

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 Tim-2009 2018年9月11日 8:47
    2018年8月29日 7:20
    版主

全部回复

  • 通过 sp_spaceused 对比一下压缩前后的空间占用

    如果压缩经高,那么压缩降了IO,这个降低的效果大于压缩/解压需要的性能开销,效率就提升了

    2018年8月29日 6:02
  • Hi Tim-2009,

    你可以试试这个查询:

    SELECT st.name, st.object_id, sp.partition_id, sp.partition_number, sp.data_compression, 
    sp.data_compression_desc FROM sys.partitions SP
    INNER JOIN sys.tables ST ON
    st.object_id = sp.object_id
    WHERE data_compression <> 0

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 Tim-2009 2018年9月11日 8:47
    2018年8月29日 7:20
    版主
  • Hi Tim-2009,

    你可以试试这个查询:

    SELECT st.name, st.object_id, sp.partition_id, sp.partition_number, sp.data_compression, 
    sp.data_compression_desc FROM sys.partitions SP
    INNER JOIN sys.tables ST ON
    st.object_id = sp.object_id
    WHERE data_compression <> 0

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    这个可以,多谢!另外,请问数据表的压缩比和什么有关,有的压缩比高一些有的比较低。谢谢
    2018年9月7日 2:08
  • 批量压缩比起单条压缩快不是很正常吗

    想不想时已是想,不如不想都不想。

    2018年9月11日 1:11
    版主
  • 这个有好几种情况的,你可以参考下这个文档,https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation?view=sql-server-2017


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年9月11日 9:10
    版主