Rebuild all indexes in a Database

sql

index-maintenance

database-performance

sql-server

byAlex Kataev·Nov 25, 2024

To rebuild all indexes with dynamic SQL, iterate through sys.indexes:

DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL += 'ALTER INDEX ' + QUOTENAME(name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) + ' REBUILD;' + CHAR(10)
FROM sys.indexes
WHERE index_id > 0 AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1;

EXEC sp_executesql @SQL; -- Make sure you have "supreme powers" to run this

This script compiles ALTER INDEX REBUILD commands for each index in your tables and puts them to work, just like ordering a pizza from the comfort of your home.

Decide: Rebuild or Reorganize?

Indexes are like kids, they don't all need the same level of attention:

Rebuild indexes when fragmentation go over 30%. It's like hitting the reset button.
Reorganize indexes when fragmentation is between 5% and 30%, akin to cleaning your room regularly.

Here's a little code to check index fragmentation:

-- Feeling like an index health-checkup? Run this code:
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
    ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
    indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.indexes AS ind ON ind.object_id = indexstats.object_id
    AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 5
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

After rebuilding, call the SQL doctor to update statistics:

EXEC sp_updatestats; -- Whispering SQL doctor: "I do the medical check-up post-operation"

Dealing with Large, Busy Databases

For databases that are as large as your old DVD collection and as busy as Times Square:

Schedule maintenance when the database is enjoying its beauty sleep (off-peak hours).
Use dynamic SQL for a tailor-made index maintenance.
Batch your operations, like splitting a huge pizza order into multiple deliveries.
Treat your performance counter and wait stats reports like morning coffee, always have it hot and first thing in the day!

Automating and Handling Errors

Automation makes your life easier. Use SQL Server Agent scheduled jobs for automating index rebuilding. It's like having a robotic maid for your databases!

But you know what they say about technology, right? Always be ready for a hiccup with TRY-CATCH blocks!

BEGIN TRY
    -- Include your index rebuild logic here, the yummy part of the pizza
END TRY
BEGIN CATCH
    -- Error handling here, like grabbing a napkin when a pizza slice slips from your hand
END CATCH

Know-How: What to Exclude and Consider?

Being smart is knowing when to act and when not to:

Avoid rebuilding on system tables and databases. For that, consult sys.databases and sys.tables.
Tables changing as frequently as fashion trends may not benefit from constant rebuilds. Strike a balance between rebuilds and performance cost.

Customization and Best Practices

Just like in DIY projects, keep these aspects in mind while customizing your maintenance script:

Adjust index fill factor according to your storage and performance needs.
Ignore read-only databases. You can find them using sys.databases.
Check compatibility with data compression settings, like seeing if the new couch fits your living room.