Post reply

How I can check index size after compression?

  • January 10, 2024 at 1:17 pm

    #4359676

    For example I have table PlayersSets and make compression for below index:

    ALTER INDEX [IX_PlayersSets_Req_CL] ON [dbo].[PlayersSets] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)

    Now I want to check the index size.

    Then to run compression again but with (DATA_COMPRESSION = PAGE)

    And to check the size again.

    May be will run multiple times in order to reduce size as much as possible

     

  • January 10, 2024 at 1:36 pm

    #4359712

    For a quick view I would just right click on the database and select Reports\Standard Reports\Disk Usage By Top Tables and look at the report.

    Or you could write a query:

    SELECT i.name AS IndexName,
           SUM(ps.used_page_count) * 8.0 / 1024 AS IndexSizeMB
      FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.indexes i
             ON i.object_id = ps.object_id
            AND i.index_id = ps.index_id
     WHERE i.object_id = OBJECT_ID('dbo.PlayersSets')
       AND i.name = 'IX_PlayersSets_Req_CL'
     GROUP BY i.name
    ;

  • January 10, 2024 at 1:48 pm

    #4359715

    Thank you!

  • January 10, 2024 at 2:37 pm

    #4359740

    After you've row compressed the data, you can get a pretty idea for page compression using SQL's estimate:

    EXEC sys.sp_estimate_data_compression_savings 'dbo', 'PlayersSets', NULL, NULL, 'PAGE'

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply