SQL SERVER – Columnstore Index and Fragementation

One of the questions I had received from a client was about how to find out fragmentation in columnstore index. They asked me this question while we were working together on a Comprehensive Database Performance Health Check. Let us discuss it today.

SQL SERVER - Columnstore Index and Fragementation clindexfrag-800x314

Please note that the following script will only return results if you have a column store index in your database. If you have no such index, you will not get any result at all. Let us see the script.

SELECT
    tables.name AS TableName,
    indexes.name AS IndexName,
    SUM(dm_db_column_store_row_group_physical_stats.total_rows) 
        AS TotalRows,
    SUM(dm_db_column_store_row_group_physical_stats.deleted_rows) 
        AS DeletedRows,
	SUM(dm_db_column_store_row_group_physical_stats.deleted_rows)*100.00/
		SUM(dm_db_column_store_row_group_physical_stats.total_rows) ChangePercentage
FROM sys.dm_db_column_store_row_group_physical_stats
INNER JOIN sys.indexes
ON indexes.index_id = 
    dm_db_column_store_row_group_physical_stats.index_id
AND indexes.object_id = 
    dm_db_column_store_row_group_physical_stats.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
GROUP BY tables.name, indexes.name

Now let us see the result of the query.

SQL SERVER - Columnstore Index and Fragementation clindex

In the query above there is the last column which displays the percentage changes in the columnstore index. If you see a change in the percentage which is beyond 20%, it is recommended that you rebuild the index.

Here is the script to rebuild the index of the datatype column store.

ALTER INDEX IndexName
ON SchemaName.TableName
REBUILD 

Well, there you go. A very simple script today but I am confident that you will be able to run that on your database where you will see high fragmentation and rebuild those indexes.

If you want to read more about the column store indexes, you can read more here.

Let us learn about it on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)

ColumnStore Index, SQL Index, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Find Count of Table Used in Query From Cache
Next Post
SQL SERVER – Compression Delay for Columnstore Index

Related Posts

1 Comment. Leave new

  • Hi Pinal,
    A reorganise is also a very good way to remove fragmentation. That works especially well when you have a large table and not the space to rebuild the hole clustered columnstore.

    Reply

Leave a Reply