SQL DB slow after restore

  • I just want to understand what happens.

    I restore databases again and again in my developement cycle.

    in my developement, I run the same stored procedures again and again.

    If its first time after a restore, they can take 'Several minutes'

    If its later - I am not sure when, it takes the usual 'Few seconds'

    So something happens after having used a DB for some time.

    But what?

    Best regards

    Edvard Korsbæk

  • My first guess is that all the execution plans have to be compiled again, and that maybe there is no caching at first.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Loading data from disk into the data cache

    Compiling the query the first time and placing the plan into the plan cache

    The first is usually a far larger impact than the second.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Does hardware performance of the machine are the same?

    make rebild indexes and update statistics

  • So, when the DB runs a script, it first looks in a db adking'Have i done this before'

    If the script has been run before, it knows 'How to'

    Right?

    Data Cache - Not understood?

    Thanks for helping with some basic understanding.

    Best regards

    Edvard Korsbæk

  • Disks are slow, memory is fast. Therefore when SQL reads a page off disk it puts it into the data cache so that if it needs to read it again, it can go to memory, not to disk, to fetch the page. That's the data cache in a nutshell.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes, and thats too how iI understand a Data Cachee

    What Iam not sure of, is about this is different for the first read after restore and for the rest.

    Ok,if its the same machine and the same session, yes, but I have found, that the DB is slow from whatever PC I use untill it has been opened a coule of times.

    That could be explained by recompiling executening plans and indexes - But you speaks about the data cachee too?

    Best regards

    Edvard Korsbæk

  • Because the first time you run a query the data has to be loaded from disk. Disk is slow. After that first execution, SQL Server can read the pages from the data cache instead of having to go to disk. Memory is fast, hence subsequent executions are faster than the first one after a restore/restart.

    Nothing to do with index rebuilds

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Make a simple test. Take stored procedure which is running slow after reboot of the server (or RESTORE) and execute quickly from the second execution onwards.

    Simply recompile that stored procedure and see if you are having more time or less?

    Let us see know the outcome.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

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

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