July 20, 2006 at 1:40 pm
Last week someone ran a process that took 75 minutes to complete. I captured the execution with SQL Profiler and here is what I found.
The statement taking 75 minutes was an "open cursor".
There were over 123 million logical reads performed for this statement, with only 21 writes.
I restored the database to a test server and applied the transaction log backups up to the point in time when the person began running her process. I was able to restore to the millisecond. My trace captured RPC:Completed, SP:Completed, SP:StmtCompleted, SQL:BatchCompleted and SQL:StmtCompleted.
When someone re-ran the process on our test server, it completed in about 3 minutes with only 26 logical reads.
Why would I see such a difference in reads and duration? I know the physical location on disk will be different and could impact performance, but I didn't think restoring a database reorganizes the data logically. I don't believe it does.
Any suggestions?
Dave
July 20, 2006 at 2:28 pm
stats would be my fist guess. Try this:
STATS_DATE ( table_id , index_id )
July 20, 2006 at 3:46 pm
Why would a restore change the stats?
July 20, 2006 at 4:23 pm
I don't know. It might not. Can't find anything that says one way or the other. That would just be my first line of investigation.
You might also want to look at the query plan on each DB.
July 20, 2006 at 4:29 pm
I just thought of something...
A newly restored DB should have no fragmentation (or very minor fragmentation). You should take a look at that as well.
July 20, 2006 at 4:48 pm
In regards to the fragmentation that's what I was trying to remember. I know the physical location on disk has a good chance of being less fragmented, assuming the database can be restored to contiguous location. However I'm still not sure this applies to the logical location of SQL Server pages. I believe logical pages should match the fragmentation found on the source database.
Thanks, Dave
July 20, 2006 at 5:10 pm
Have you run a DBCC SHOWCONTIG to verify this?
July 21, 2006 at 2:01 am
Hi
Could also possibly be out of date cached query plans or compiled procedures. The cache would be wiped when you restored the database.
I use
EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''
and
dbcc freeproccache
once in a while to keep on top of this sort of thing after I had a similar performance problem...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=240819
David
If it ain't broke, don't fix it...
July 21, 2006 at 11:01 am
I was in a similar situation not long ago. The exact same database (using backup and restore) is 10 times slower on a new faster server than old server running same queries. I called MS and spend several weeks working with them to trace the problem. At the end, simiply update statistics (full update, not partial) solved the problem. To this day, we still don't know why.
Chung.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply