Just update statistics
You mentioned that fragmentation was causing performance issues. A lot of times it seems like fragmentation is the issue, when in reality the problem is bad statistics (due to how much data has changed in the table).
You might be better off just updating statistics on the tables involved:
- immediately after these large operations, or
- on a schedule (weekly, daily, or even a couple times a day - depending on how often issues crop up)
Rebuilding indexes causes statistics to be updated, which can reinforce the perception that fragmentation is the problem. These operations are generally far less disruptive and resource intensive than an index rebuild.
UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;
You could also play around with "sample percent" to avoid scanning the whole table:
UPDATE STATISTICS dbo.YourTableName WITH SAMPLE 50 PERCENT;
Take care when testing different sample rates, as there is a tipping point where sampled stats can take longer than
FULLSCAN. This depends on multiple factors (such as your data, and resources available). Erin Stellato has a great post on the subject here: Sample Size and the Duration of UPDATE STATISTICS: Does It Matter?
Avoid common causes of fragmentation
You may be able to change your schema / workload to avoid common causes of fragmentation, and thus slow or stop the problem before it starts. The go-to example would be to avoid having a
uniqueidentifier primary key being filled via
More generally, a good clustered index should be narrow, unique, static, and ever-increasing.
Rebuild indexes online
If rebuilding indexes during normal user activity is extremely important, you may have to try to justify upgrading to the Enterprise Edition of SQL Server. Then you could specify the index rebuild to occur "ONLINE":
ALTER INDEX [IX_YourIndex] REBUILD WITH (ONLINE = ON);
This can allow user activity to continue during the index operation, other than at the very end (which should be brief). Of course, the rebuild still uses server resources, so it could impact overall performance.
I put this last because it has a big price tag on it. You should definitely explore the other options before going this direction, and test to make sure
REBUILDs will actually fix the problem.