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.

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.

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.

• SQL SERVER – Fundamentals of Columnstore Index
• SQL SERVER – ColumnStore Frequently Asked Queries
• SQL SERVER – ColumnStore Index Displaying Actual Number of Rows To Zero
• SQL SERVER – Creating a Nonclustered Columnstore Index on Temp Table
• SQL SERVER – ColumnStore Indexes Without Aggregation
• How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153

Let us learn about it on Twitter.

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