Seek The Sun Slowly

Q by SQL Server Ask the Experts

I am using SQL Server 2000, and I backup and restore my LIVE database to a new backup server every night. Do you have any ideas as to why my restored database runs slow when compared to the LIVE database? Do I need to rebuilding indexes? Is there any way of backing up indexes as part of the main backup?

Re by Mrdenny

Indexes are backed up as part of the full database backup.

One reason why the database will be running slower is because the live database has most of the database in cache already, while the server you are restoring to does not have any of the data in cache. All the data needs to be loaded off disk for each query until all the needed data is cached in RAM.

Depending on the system configuration of your live and backup database servers the slow response time of the backup system could be normal.

Does the performance ever increase to normal? What’s the hardware configuration for each server?

Re by Ritchie1

I would expect the performance of the new database (recovered from backup) to be better than the Live database because the indexes would have been built fresh – i.e. no fragmentation etc, and it would not be handling the load of the Live database. Regarding holding data in cache, this would only really be a factor if the RAM available was large enough to store a significant percentage of database in RAM, i.e. huge RAM or very small database. In a normal system where RAM is a very small proportion of database size, the cache is regularly flushed out. However the tempdb – stored on hard-disk – is often large on a Live system which has not been restarted in a while. Tempdb does store data and aid query speed but I would still expect fresh indexes to outway a populated tempdb. Are you using a backup file to restore (which includes indexes) or are you using another method e.g. data transfer wizard etc (which will not copy indexes). Also are both servers of similar standard – eg RAM, Processor, Hard-drive space etc.

Original Address: http://itknowledgeexchange.techtarget.com/itanswers/backing-up-and-restoring-a-live-database-in-sql-server-2000/