Sql-server – Dropping and recreating indexes

performancequery-performancesql serversql-server-2008sql-server-2008-r2

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?

Best Answer

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.