Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    122

    Unanswered: Why does restarting speed performance?

    Why does restarting my server improve SQL's performance so dramatically? What can I do to achieve the same effect, without restarting? Thanks for any ideas.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummm you have to give us a little more background

    first off the cuff remark is that it cleared all of the locks?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    122
    The most specific example I can give is when I am printing a batch job of about 200. The day I restart the server, this batch job is spooled lightning fast. As each day goes by, this same job begins to drag to the point where it affects other users who are attempting to access the database while the job is printing. At that point, I simply restart the server and all is well.

    It seems I recall seeing a post at one point that mentioned something about why restarting can have such a profound impact.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Next time try running DBCC FREEPROCCACHE to see if the query plan is getting bad over time. This will, however clear all of the procedure cache, and is not at all suggested on a high traffic server. If this is a high traffic server, run sp_recompile on all of the tables underlying the query you are running.

  5. #5
    Join Date
    Sep 2003
    Posts
    122
    I did a little reading on sp_recompile and just want to confirm that when I shutdown the server, a recompile is happening, which in turn, speeds the database significantly. Is this the only thing I need to do to achieve the same performance gains of restarting the server? Also, by using sp_recompile on all of my tables, will this improve performance over the entire database (like a restart currently does)? Thanks for the help.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    When you reboot, the procedure cache is cleared. It is memory resident only. As for whether you will realize any performance gains, well, that depends on the actual problem. This one is easy to rule out, so I start here.

  7. #7
    Join Date
    Sep 2003
    Posts
    122
    Okay, so sp_recompile is better way of clearing the procedure cache while there is heavy traffic. How often should I run this procedure? I currently print the batch once a day. Is there any harm in running the procedure once a day on every table? Is this a potential permanent solution to my problem? Also, is there an easy way to have the procedure run on all tables at once or do I have to manually specify each table? Again, thank you for the help. I really appreciate it.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Whoa, dragon! Does clearing out the query plan from the procedure cache help? If not, then you do not need to recompile anything. In fact, you should not, as it will cause all queries to generate new query plans (which utilizes extra CPU cycles). That could land you in a worse place than where you are now. If this does end up helping the problem, I would advise the least invasive fix, which will very likely end up being one single table. It will be a mater of some experimentation to figure out which table that will be, but likely candidates are often tables that are deleted and refilled on some cycle.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think I would do sql profile to osee what's going on

    In any case without more specifics it will be difficult

    For example what is the batch? A sproc? How about the code that does this process?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •