Performance degradation after rebuilding indexes

Question

I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and it predictable time to do it.
Then I executed script:

ALTER INDEX ALL ON dbo.'+ @TableName +' REBUILD

for every table in a database. And what I see now. The time of execution my task is increased to 24 minutes. What's going on if these is not any external influences on this task? I was waiting for increasing of performance (due rebuilding fragmented indexes) but got degradation.

Answer 1

It's because when you rebuild indexes, you also necessarily rebuild the statistics on each index as it happens. That means that SQL Server rebuilds internal "tables" of data about the distribution of data in your indexes themselves. The distribution of data indicated by your statistics govern how the cost-based query optimizer chooses to create plans for your queries.

In essence, it means that SQL Server is probably executing your queries using a different plan than it did before because the distribution of your data changed.

There's no simple fix to this. It involves real detective work. Start with SQL Server Profiler and get an idea of what queries are actually running in this job. Analyze each query plan in detail and find out what parts of the query are taking the most time, and adjust your query, indexing scheme, or even your schema accordingly.

Answer 2

In addition to query plan changes, you could also be seeing poor insert performance due to page splits that weren't happening before the index rebuilds. Might be worth looking into fillfactor.

Basically I'd suggest looking into what part of the task is slow, and that should provide some clues.