Unable to Reproduce Performance Problem after DB Restore - Why?

  • 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

  • stats would be my fist guess.  Try this:

    STATS_DATE ( table_id , index_id )
  • Why would a restore change the stats? 

  • 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.

  • 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.

  • 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

  • Have you run a DBCC SHOWCONTIG to verify this?

  • 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...

  • 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