Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
+1 you are right –  garik Jun 20 '13 at 20:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.