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 most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness).

I somehow manage to rebuild the indexes or reorganize them, and update the statistics, which is not much of a use.

Will dropping and recreating the indexes, and then updating the statistics, be of any help?

share|improve this question

closed as not a real question by Marian, Mark Storey-Smith, Max Vernon, JNK May 24 '13 at 12:38

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
This question is too broad. You need to first determine the cause for the lack of performance before making a blanket statement that index recreation and stats update will/won't help. –  Thomas Stringer May 23 '13 at 12:51
    
Agreed with @ThomasStringer. Also, when you rebuild your indexes, behind the hood it drops and recreates your indexes. There might be many other factors like memory, CPU, server settings, workload, power plan setting, etc that can contribute to poor performance. You are doing the right thing of rebuild/reorg indexes when you move database from one server to another. –  Kin May 23 '13 at 13:06
    
Do both the servers have the same specifications/settings? –  StanleyJohns May 24 '13 at 12:06
    
Please consider rewriting this question to make it a lot more specific. Some info you could include to help would be sample queries/data/table structures, execution plans, server specs, etc etc etc. As it stands this is unanswerable and it's getting closed. –  JNK May 24 '13 at 12:37

4 Answers 4

Some notes:

  • After rebuilding indexes, do not update all statistics!
  • REBUILD indexes, don't REORGANISE

An index rebuild will rebuild statistics anyway. A further update actually means you'll have worse statistics because of sampling ratio. You can rebuild column statistics though.

Also, it's worth comparing the server specs (RAM, CPU, Disk) and query plan XML. Does the 2nd server have different connection options that has, say, a different ARITHABORT setting?

Finally, do a comparison (say with Red Gate SQL Compare) but do ensure you compare everything: statistics, fill factors, the lot.

share|improve this answer

Indexes fragmentation and statistics are not the only possible cause for a performance trouble. Apart from this rebuild and reorganize are different process.

  • INDEX REBUILD: it drops the existing index and recreates the index. Can be done online or offline.

  • INDEX REORGANIZATION: it physically reorganizes the leaf nodes of the index. It is always online.

Which one to choose? Index should be rebuild when index fragmentation is great than 30%. Index should be reorganized when index fragmentation is between 5% to 30%. Index rebuilding process uses more CPU and it locks the database resources. ONLINE option is availabe only with SQL Server development Enterprise version. Without ONLINE clause index is simply dropped and recreated. Consider that the optimizer might change its own preference in using an index due to heavy fragmentation. It's suggested to perform regular check on index fragmentation level.

share|improve this answer

When an index rebuilding operation is performed, it drops the index, recreates the index, and updates its statistic.

If I read the question correctly, your question of dropping indexes, recreating then update the statistics is pretty much the same as simply rebuilding the index.

Please note that index reorganization operation does not update the statistics.

share|improve this answer

Could it be that the database is used in a different way in the new location? This is a common theme - different queries running in the database copy (perhaps reporting-style queries).

In this case you may want to drop the existing indexes and create new ones that support these new queries.

You'd first have to let them use the db on the new location for a representative amount of time, then use the indexing DMVs to find the top 'wanted' ones, then consider which ones are worth adding.

If the usage pattern is very different, you may even want to drop some clustered indexes and recreate them on more appropriate fields - it depends (as always!)

share|improve this answer

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