August 17, 2009 at 1:53 pm
Background:
-----------
One of my former employer's servers experienced a catastrophic RAID failure. This failure destroyed the Master database. I couldn't start the SQL Server service without the Master database, so I had to uninstall SQL Server 2005, reinstall it, then restore all of the application databases to a point in time.
Once the server was back in production, the first thing I noticed was how long queries took to execute: minutes instead of milliseconds. I concluded this could only mean one thing: the databases' tables were no longer properly indexed.
I ran rebuild index operations on all tables in all databases. When these operations completed, all response time issues disappeared.
Question:
--------
Was my assessment of the problem correct? Were my actions the correct ones to perform?
I cannot seem to find any documentation on database restorations that discusses the condition of indexes after a restoration.
LC
August 17, 2009 at 2:03 pm
The indexes would not have gotten fragmented by restoring. My guess would be that after you re-indexed that a lot of your statistics got updated which then improved your query plans.
August 17, 2009 at 2:06 pm
A database restore will leave the database in exactly the same state that it was when it was backed up. Backup just copies data out bit by bit (or page by page to be more accurate), restore puts it back, page by page.
The state of the indexes (and also of the statistics) after restore would have been absolutely identical to their state when the backup happened
Index rebuilds would have forced recompiles of all procedures. It's possible (especially if you have some parameter sniffing-prone code) that the plans in cache were sub-optimal.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2009 at 2:45 pm
When I worked there, all indexes on all databases were rebuilt every night.
Thank you both for your responses.
LC
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply