An index in SQL is a special data structure that improves the speed of data retrieval. An index acts like a table of contents, allowing the database to locate data more efficiently, especially in large databases. Indices are meant to increase performance, but they can become fragmented over time. This can cause noticeable performance degradation.

We recommend regularly rebuilding or reorganizing the indices in your QC-CALC database. This article goes through how to check the fragmentation level of the indices in your database using either QC-CALC SPC 4.3 or Microsoft's SQL Server Management Studio (SSMS), as well as how to reorganize or rebuild the indices.

Rebuilding versus Reorganizing Table Indices

Rebuilding an index takes longer than reorganizing, but it does a more thorough job. Reorganizing an index is faster but might leave minor fragmentation behind. Whether you should choose to rebuild or reorganize your indices depends on various factors like the size of your database, the amount of index fragmentation, and how long you can have the database offline to perform maintenance.

As a general rule of thumb, an index with > 50% fragmentation should be rebuilt rather than reorganized.

Checking Table Index Fragmentation in QC-CALC SPC Pro 4.3

1. In QC-CALC SPC, go to Tools > Administrator > Data Admin > Administer Database
2. Select the icon to load the grid. It will look something like the picture below:

Note the tables and indices that have > 50% fragmentation. In the next section we will rebuild those indices.

Managing Indices with SSMS

1. In SSMS, expand your QC-CALC database.
2. Under your QC-CALC database, expand the Tables folder.
3. Expand the Indexes folder.
4. Right-click on an index and choose either Rebuild or Reorganize.
   
   
5. If you want to rebuild or reorganize all the indices on a table, right-click on the Index folder and choose either Rebuild All or Reorganize All.
   
   
6. In the Rebuild Indexes window, choose whether to rebuild the indices Online or Offline. Online rebuilds take longer and consume more resources.

SQL Query to Rebuild all Indices

The following stored procedure will rebuild ALL indices in the selected database. Your IT team can run this query in SSMS. This stored procedure should be run overnight or on the weekend when users are not connected to the database.

Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'

GO

Automating Index Maintenance

You can set up a Maintenance Plan in SSMS to Rebuild or Reorganize all the indices in one or more databases automatically. This feature requires a full version of SQL (not SQL Express), and your instance of Microsoft SQL Server must also have Maintenance Plan creation enabled.

This topic is not covered further this article. Please refer to Microsoft's documentation on SQL Server Maintenance Plans for more information: https://learn.microsoft.com/en-us/sql/relational-databases/maintenance-plans/maintenance-plans?view=sql-server-ver17

Conclusion

After you rebuild or reorganize the indices, you can go back into Tools > Administrator > Data Admin > Administer Database and click the button to view the fragmentation percentages of each index to confirm they are less fragmented.

It is also possible to view a fragmentation report in SSMS by right-clicking on your QC-CALC database and choosing Reports > Standard Reports > Index Physical Statistics. That report will list various statistics about the indices, including the amount of fragmentation in each.

In the example report below, the fragmentation percentage of each index is visible as well as a recommendation from Microsoft to either rebuild or reorganize each index.