One of the common questions that arise when working with SQL Server is how to find out fragmentation in columnstore indexes. In this article, we will explore this topic and provide a simple script to help you identify and address fragmentation in your columnstore indexes.

Before we proceed, it’s important to note that the following script will only return results if you have a columnstore index in your database. If you don’t have such an index, you will not get any result at all.

Let’s take a look at 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

The above query retrieves information about the fragmentation in columnstore indexes. It displays the table name, index name, total rows, deleted rows, and the percentage change in the columnstore index.

If you notice a change in the percentage that is beyond 20%, it is recommended to rebuild the index. Here is the script to rebuild the index:

ALTER INDEX IndexName
ON SchemaName.TableName
REBUILD

By running this script on your database, you will be able to identify high fragmentation in your columnstore indexes and rebuild them accordingly.

Columnstore indexes are a powerful feature in SQL Server that can significantly improve query performance for large datasets. If you want to learn more about columnstore indexes, you can explore the following resources:

• 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

Thank you for reading this article. If you have any questions or feedback, feel free to reach out to us on Twitter.