How to reorganize a SQL Server index? This operation defragments the bottom level of the indexes by physically reordering the pages, from left to right. <\/p>\n\n\n\n
The reordering operation also compacts the pages of the index. To better understand this, imagine a recipe book whose pages are no longer in order. You must then reorganise the pages to quickly reach the desired page.<\/p>\n\n\n\n
First, let’s look at the index in this tutorial with sample code to create a non-clustered SQL Server index on a sales table.<\/a><\/strong><\/p>\n\n\n\n The index uses the main filters on the sales table from the time dimension, i.e. the year and month columns.<\/p>\n\n\n\n After many insertions and deletions, the data is no longer in a logical ascending order on the disks. This means that the indexes are fragmented.<\/p>\n\n\n\n To reorganise the data in an index and improve performance, the indexes must be reorganised regularly.<\/p>\n\n\n\n Or when the fragmentation exceeds a certain percentage.<\/p>\n\n\n\n Secondly, this query displays all statistics for all tables and indexes in an MSSQL database.<\/p>\n\n\n\n The query not only displays the data but also stores it in a hardcopy table to allow the data to be filtered and sorted with the second query.<\/p>\n\n\n\n Considering the column avg_fragmentation_in_percent, i.e., the average fragmentation in percent, displays the information for each index.<\/p>\n\n\n\n At what percentage should the indexes be rebuilt or reorganized?<\/p>\n\n\n\n It is a good SQL practice to reorganize indexes if the fragmentation percentage<\/a> is between 15 and 30%.<\/p>\n\n\n\n However, above 30%, rebuild the index with the REBUILD command.<\/p>\n\n\n\n This article shows how to reorganise a fragmented index with a simple example.<\/p>\n\n\n\n To go further, see Microsoft’s documentation on why to check for index fragmentation and how to rebuild and reorganise indexes.<\/p>\n\n\n\n This is the first tutorial in the series on views with a script to create a SQL Server view<\/a><\/strong>.<\/p>\n\n\n\nCheck the fragmentation average percentage on all SQL Server tables<\/h3>\n\n\n\n
SELECT * \nINTO dbo.dm_db_index_physical_stats_TEMP\nFROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);\nGO\n\nSELECT * \nFROM dm_db_index_physical_stats_TEMP\nORDER BY avg_fragmentation_in_percent DESC;<\/pre>\n\n\n\n
Reorganise a SQL Server index with this query<\/h2>\n\n\n\n
ALTER INDEX indexYearMonth\nON [dbo][SALES] REORGANIZE;<\/pre>\n\n\n\n