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)