I have a stored procedure that performs very poorly on SQL Server 2000 standard edition. It takes approximately 40 seconds to execute. In the course of troubleshooting the issue I backed up the database to disk and restored it to another SQLServer and the performance issue was resolved. On a hunch I went to the original server, backed up the database to disk, and then restored the database from the backup I just created and voila the problem was resolved. Why would this resolve the issue? I have another database with the same stored procedure with the same problem. I have tried the following on the stored procedure and all related tables with no success:
DBCC DBREINDEX
UPDATE STATISTICS
sp_recompile my_storedprocedure.
What is the restore doing to resolve the issue? What command can I run to resolve this. I have a feeling it is the query plan but wouldn't update statistics correct this?
Is it possible that the physical database files are getting fragmented?
The restore would then lay the files down contiguously, which would improve performance.
Other than that, the content of the database including statistics would be exactly the same after the restore as it was at the time of backup. SQL backup is a physical copy of every allocated byte in the database files.
No comments:
Post a Comment