Post reply

Problem with Restored DB

  • February 17, 2016 at 4:01 am

    #308640

    HI! I have 2 Remote SQL 2014 Servers: Server A with Version 12.0.4100.1 and Server B with Version 12.0.4213.0.

    I have exported a DB from server B, copied and restored it to server A for tests. I have a Querry (really long) which I execute on both Servers. The Server B returns the results in 2 min and server A in 1-2 sec. I have tried to export back the DB from Server A to server B, the same DB and nothing changes.

    I have noticed the folowings on server B: if I export all the Tables of the DB into a TempDB, then delete all the Tables from DB and then import all the Tables from TempDB back into DB the Problem is gone. This happens for both the old (original) DB and the new (exported back from server A) one as well.

    My Problem: what can cause this "Problem" and how to prevent it?

  • February 17, 2016 at 7:21 am

    #1859247

    Statistics out of date?

  • February 17, 2016 at 7:42 am

    #1859258

    data already previously loaded into memory on one server, but reading from disk on other server?

    index fragmentation?

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • February 17, 2016 at 7:58 am

    #1859264

    HI! @jo-2 Pattyn: I have ran a EXEC sp_updatestats on server B. This did not helped.

  • February 17, 2016 at 8:05 am

    #1859266

    HI! @HanShi: i don't know exactly what you are referring to, but if it has to do with the cache, i did not cleaned the cache on the Servers. What I find strange is that the server B, from where I exported the DB is the original source and this querry used to work there(until recently) without problems. Another thing is the Import thing that resolves atm my Problem.

  • February 17, 2016 at 8:09 am

    #1859267

    @HanShi: I have let the querry to run fully on the server B, got the result after 2 min. Re-Run it again, same result: the result came after 2 min. Index fragmentation = what do i have to do?

  • February 17, 2016 at 8:31 am

    #1859274

    Compare the actual execution plans for the executions on each server. That will point you to the difference. Without doing that it's all (educated) guessing 🙂 If you would like us to take a quick look at them as well, you can save the actual execution plans for the fast and slow versions as .sqlplan files and attach them to a post here.

    For emphasis, make sure they're the actual execution plans, not the estimated plans.

    Cheers!

  • February 17, 2016 at 10:26 am

    #1859318

    I think Jo got it right at the start. If you're exporting and importing, as opposed to using backup and restore, then you're looking at differences in the statistics based on how they get generated. Further, export and import may or may not include all the primary keys, foreign keys, constraints and indexes. Any of these that are different between the two servers can result in differences in the execution plan. Further, differences in the ANSI settings between the two servers could result in differences in the execution plans. Also, you need to worry about the max degree of parallelism and the cost threshold for parallelism between the two servers. Any of these things could result in differences in execution plans and differences in execution time.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 18, 2016 at 12:39 am

    #1859458

    HI! I will follow the execution plan suggestion and post back when i have a result.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply