Disabling Nonclustered Indexes Before a Large Load, Then Rebuilding

Loading millions of rows into a table with six nonclustered indexes maintains every index as each row arrives. Disabling nonclustered indexes before a large load can reduce that insert work, but the rebuild afterward has its own time, log, and availability cost. Compare the full process, not just the INSERT.

Decide Whether Disabling Nonclustered Indexes Fits the Window

The approach suits a controlled load into a table whose secondary indexes can be unavailable temporarily. It is risky for a live table with readers that depend on those indexes or constraints. Check maintenance window, recovery model, available log space, tempdb, and storage before touching an index. If the table remains in service, a staged table and switch or another loading design can be safer.

I have seen a load advertised as fast because the INSERT finished quickly, while the rebuild ran into business hours. What is the maximum end-to-end outage the application accepts? Write that number beside the benchmark before changing the table.

Script and Review the Index List

Inspect sys.indexes for the target object. Nonclustered rowstore indexes use type 2; a clustered index is type 1 and must stay online for table access. Review unique indexes and indexes backing constraints separately. Save full definitions, keys, includes, filters, compression, and options so you can recover from a failed rebuild.

SELECT i.index_id, i.name, i.type_desc,
       i.is_unique, i.is_primary_key,
       i.is_unique_constraint, i.is_disabled,
       i.filter_definition
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.LoadTarget')
ORDER BY i.index_id;

Do not generate ALTER INDEX ALL DISABLE. Disabling a clustered index makes the underlying table inaccessible for normal reads and writes; a plain SELECT then fails with error 8655. A blanket disable can also collide with constraint enforcement and application expectations. Build an explicit list of eligible nonclustered indexes from the catalog and review it with the table owner.

Limit Disabling Nonclustered Indexes to an Approved List

Run one ALTER INDEX statement per reviewed index. The example names two ordinary nonclustered indexes; substitute the actual list. Confirm is_disabled = 1 afterward and verify the clustered index remains enabled. Schedule the load immediately so the table does not spend unnecessary time without its access paths.

ALTER INDEX IX_LoadTarget_CustomerID
ON dbo.LoadTarget DISABLE;
ALTER INDEX IX_LoadTarget_OrderDate
ON dbo.LoadTarget DISABLE;
SELECT name, type_desc, is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.LoadTarget');

A disabled nonclustered index cannot serve queries and is not maintained by inserts. Its statistics and related objects deserve review during the rebuild. Some index designs, foreign keys, and constraint-backed indexes need special handling. Test the exact production schema on a restored copy rather than assuming every type can be disabled safely.

Load in a Controlled Batch

Use the normal validated bulk load command and record rows inserted, elapsed time, log growth, and errors. Keep the source file or staging rows stable for comparison. If the load is huge, batch size can control transaction duration and recovery risk, but each batch has overhead. The optimal batch size is measured, not chosen from a generic rule.

DECLARE @started datetime2(3) = SYSDATETIME();
-- Replace this with the approved INSERT or BULK INSERT into dbo.LoadTarget.
SELECT SYSDATETIME() AS finished_at,
       DATEDIFF_BIG(millisecond, @started, SYSDATETIME())
         AS load_elapsed_ms;

This is a timing frame, not an invented sample data load. Run the same source rows and validation in both benchmark branches. A faster INSERT that skips rejects or changes transformation rules is not a valid comparison.

Rebuild Before Reopening Workload

After the load, rebuild the disabled indexes and verify all intended ones are usable. ALTER INDEX ALL … REBUILD is a simple recovery command, but it also rebuilds enabled indexes, including the clustered index, which can add substantial work. For a large live table, rebuilding only the disabled index names can be cheaper. The brief end-to-end test uses ALL so its cost is visible.

ALTER INDEX ALL ON dbo.LoadTarget REBUILD;
SELECT name, type_desc, is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.LoadTarget')
ORDER BY index_id;

Check the edition and version for online or resumable options if availability matters. An offline rebuild can block access. Monitor log free space and tempdb during the operation. A failed rebuild leaves the table with disabled secondary indexes, so the maintenance plan needs a clear recovery route and enough time to finish.

Compare the Full Cost

On two equivalent restored copies, run one load with indexes enabled and one with approved nonclustered indexes disabled and rebuilt. Measure INSERT time, rebuild time, total elapsed, peak log used, log backup volume, CPU, and query availability. Use sys.dm_db_log_space_usage before and during each branch to observe log consumption, and record actual log-file growth separately.

SELECT total_log_size_in_bytes / 1048576.0 AS total_log_mb,
       used_log_space_in_bytes / 1048576.0 AS used_log_mb,
       used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

A log-space snapshot is not the total log generated. Log backups and reuse can make used space fall while substantial work was logged. Preserve file-growth and backup-size evidence too. I include index validation and application smoke tests in the elapsed total. The winner is the method that finishes within the real window and leaves the table ready for users.

Check Dependencies Before Disabling Nonclustered Indexes

Before disabling nonclustered indexes, identify whether each one supports a uniqueness requirement, a foreign-key lookup, or a critical read path. A unique constraint is part of correctness, not just performance. Disabling an index that enforces it can be prohibited or can change access in ways the load plan did not anticipate. For a referenced table, missing lookup indexes can make validation and concurrent writes much slower. Script and classify the indexes, then exclude anything the application needs during the load window.

Plan for Partial Failure

A load can fail after indexes are disabled and before they are rebuilt. Keep the exact list of affected indexes and a tested rebuild command in the runbook. Do not mark the table ready merely because the data rows arrived. Validate row counts, duplicate rules, constraints, and every index's is_disabled flag. If rebuild time exceeds the window, pause application reopening and use the agreed recovery action rather than leaving performance to chance. Confirm that application queries use their expected access paths after the rebuild, not merely that catalog flags changed.

The benchmark should include a failed-load rehearsal. Measure how long it takes to restore the table to a known state and whether log space remains sufficient. A method that is fast when everything succeeds but impossible to recover within the maintenance window is not the best method for that system.

Related reading on this blog: sp_updatestats Performance and Disabled Nonclustered Indexes and Speed Up Index Rebuild with SORT IN TEMPDB.

A load without indexes is not automatically faster, it is faster only when load plus rebuild beats the indexed load.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.