June 30, 2006 at 5:01 pm
The data can get fragmented on the live server. When you restore the database on another server, the databases gets Contiguous space allocated on the disk. You can defragment at the index, data file or disk level. The best thing would be to backup the database and restore it if you can get the downtime.
July 11, 2006 at 9:59 am
Hello everyone,
Here is a final update. We have resolved the performance problem by reinstalling SQL Server on the production machine. The server was reinstalled with SP3a. We ran the test query, and it performed exactly as it should returning results almost instantly 0.0035 seconds per row. Which is about 60x better then it had been performing before.
I know we didn't get to the bottom of the performance issue, we tried everything I could think of (with all of your help of course!). I know a reinstall does not give us the answers as clearly as we like, but now it’s a matter of documenting our current performance baseline, so that we have something to compare against if performance degrades again.
Again, thank you everyone for all of your help.
Brian T
July 13, 2006 at 12:29 pm
How did you get your databases back online. A restore?? Jag might have stated it correctly, with the actual database file being fragmented. Is the database set to autogrow. How big is the database? How big was the original size? If you started with a 10mb db and now have a 100mg db, with an autogrow of 10%. Your physical db file would be very fragmented. A restore of the db puts that file in contiguous disk space.
Tom
August 2, 2006 at 3:52 pm
Hi Tom
Yes we did a full restore from a backup of the databases. FYI, the database .bak files before and after we're exactally the same file size. I'm not sure about the actuall db files though. I have no record of them.
I could restore the db's from their original bak's but, I don't have time to do that at the moment. I'm assuming that if the bak files are similar in size, so would the dbs.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply