Lukla Data Studio Docs

Docs Plans and performance

Index maintenance

Fragmentation of every index in a database, the recommended REORGANIZE or REBUILD at thresholds you set, and a script for the indexes you pick.

On this page
  1. How it decides
  2. Rebuild options
  3. The script

Database ▸ Tools ▸ Index Maintenance… (right-click a database) reads the fragmentation of every index in the database and recommends what to do, without installing a maintenance script on the server.

Index Maintenance on AdventureWorks2022: each index with its kind, fragmentation bar, pages and recommended action
Every index, with fragmentation, pages and the recommended action.

#How it decides

Fragmentation comes from sys.dm_db_index_physical_stats in LIMITED mode, the lightest one, largest first. For each index:

FragmentationAction
Below Reorganize ≥ (5% by default)Nothing.
Between the two thresholdsREORGANIZE (or REBUILD when the index doesn't allow page locks).
At or above Rebuild ≥ (30%)REBUILD.
Fewer pages than Min pages (1,000)Nothing: small indexes fit in a few pages and fragmentation doesn't matter.

Change the three thresholds at the top; the recommendations update as you do. Show switches between the indexes that need work and every index.

#Rebuild options

Rebuild options ▾ sets what goes into each REBUILD:

  • ONLINE = ON (Enterprise, Developer and Azure SQL), so the table stays usable while it rebuilds;
  • SORT_IN_TEMPDB = ON;
  • MAXDOP;
  • UPDATE STATISTICS after each REORGANIZE, since a reorganize doesn't update them.

#The script

Tick the indexes and press Script Selected. The script opens in a query tab, with each index's fragmentation and size as a comment; nothing runs until you run it:

T-SQL
-- Sales.SalesOrderDetail.IX_SalesOrderDetail_ProductID: 38.2% fragmented, 1207 pages
ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);

-- Person.Person.IX_Person_LastName_FirstName_MiddleName: 12.6% fragmented, 1054 pages
ALTER INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person] REORGANIZE;
UPDATE STATISTICS [Person].[Person] [IX_Person_LastName_FirstName_MiddleName];

Partitioned indexes get one statement per partition (REBUILD PARTITION = 2).