none
After deleting from clustered columnstore index table, space does not get released according to sp_spaceused

    Question

  • Hi, 

    we have sql server 2014, partitioned clustered columnstore index table (100+GB), and when we purged data (several partitions) using regular delete statement (don't ask me why we did not slide partitions out), sp_spaceused reports even less space available vs before deleting data.

    I know that when purging columnstore data, space is not released immediately, but any idea what needs to be done to speed this process up, or to get report about space accurately?

    Thanks,



    Pedja

    Wednesday, February 7, 2018 3:15 PM

All replies

  • Deleting data doesn't remove rows. Rows cannot be (imediately) removed from column-index. Instead, data is *added* to a bitmap specifying that "this row doesn't exist anymore".

    A REBUILD or REORGANIZE should fix that. See: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-defragmentation


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, February 7, 2018 4:06 PM
    Moderator
  • Hi Pedja,

    >> I know that when purging columnstore data, space is not released immediately, but any idea what needs to be done to speed this process up, or to get report about space accurately?

    If the row is in the columnstore, SQL Server marks the row as logically deleted but does not reclaim the physical storage for the row until the index is rebuilt. For more detailed information, please refer to the following:

    Using Clustered Columnstore Indexes

    Rebuilding and Reorganizing Clustered Columnstore Indexes

    If you have any other questions, please let me know.

    Regards,

    Hannah


    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.

    Thursday, February 8, 2018 2:43 AM
  • To add on to Tibor's answer, you can also look through the segments and find the number of "deleted" records that are still holding onto space in the table by querying

    sys.column_store_row_groups

    See Columnstore Row Groups for more info on the system table.

    Thursday, March 8, 2018 4:53 PM