8

Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?

Improve this question

3 Answers 3

Reset to default
13

Yes it does. Since SQL Server is in charge of maintaining those caches, when you shut down SQL server's services, it releases the memory back to the operating system.

Improve this answer
4
  • Thank you! As a bonus question, do you know if restarting just the services during a query being rolled back could potentially result in the server coming back up in the "recovering" state, or is that only a potential outcome from restarting the whole instance / machine?
    – J.D.
    Commented May 6, 2019 at 16:45
  • 1
    It could, but I don't know if it would. The perpetual rollback is a problem of course, and I have had to do what you are asking about before. The DB did not come back in a recovering state, but I might just have been lucky. Some of the consultant class people, like Brent Ozar's crew or Aaron Bertrand may know for sure.
    – Laughing Vergil
    Commented May 6, 2019 at 16:53
  • Recovery is only needed on unclean shutdown or with preexisting corruption
    – eckes
    Commented May 6, 2019 at 20:59
  • 3
    A database always start in the recovering state, but recovery is typically so quick so you don't catch it. (There is an exception where there's a bit in the mdf file stating that shutdown of clean database, but you should conceptually think if a database always starting in recovery state). The recovery phase can take a long time, if you have a large rollback, i.e., you might now notice this state. But you can also have a problem with the recovery, like a database file missing, and you will now have "recovery pending" (and this won't sort itself out).
    – Tibor Karaszi
    Commented May 7, 2019 at 10:27
16

The behavior is no different between restarting the service alone or restarting the service due to rebooting the underlying operating system. Which information is wiped on such a restart?

  • Query plans? Yes.
  • Table/index data? Yes.
  • Statistics? No.

(Query plans, which use statistics, will have to be recompiled, but statistics won't have to get re-created unless there was also some event or condition to trigger that (there are several).)

If you stop the service while a transaction is rolling back, you likely haven't accomplished anything, since rollback may just start again from where it left off (or it may have to start over completely, depending on the type of activity that is getting rolled back).

If you restarted the service because you got impatient waiting for a rollback to complete, that's probably an expensive lesson you'll only have to do one or two times before you stop trying. See:

Improve this answer
4
  • Thanks Aaron, insightful as usual! When you say "If you stop the service while a transaction is rolling back, you haven't accomplished anything, rollback will just start again from where it left off" are you saying that's always true or in most cases? If the former, how come when we restarted the services, sp_who2 no longer showed the processes being rolled back (their SPIDs were gone)?
    – J.D.
    Commented May 6, 2019 at 17:04
  • 1
    @J.D. sorry, will should be may. It's possible the rollback was blocked before the restart and after the restart there was nothing blocking it so it was able to complete immediately. Killing the services is just a dangerous and unpredictable game.
    – Aaron Bertrand
    Commented May 6, 2019 at 17:06
  • Ah gotcha. And agreed, unfortunately it wasn't my choice in this case, but I figured it had the same potential repercussions as an instance restart. Do you know if it's possible for the database to come back up in a "recovering" state as well for just the services being restarted similarly to what can happen when the instance is restarted during a rollback?
    – J.D.
    Commented May 6, 2019 at 17:07
  • 1
    @J.D. Sure, anything is possible if you sever SQL Server's artery. :-) Depending on what else was going on in the database at the time you killed the service, you will have to wait for both redo and undo to complete before the database will be available, and there could be other more catastrophic causes for Recovery Pending specifically.
    – Aaron Bertrand
    Commented May 6, 2019 at 17:17
0

sql server reserves operating system memory called buffer pool when starts. day to day workload increases the buffer pool and every page , cache, query stats,procedure stats etc are stored in buffer pool. this information reside in buffer pool untill following events happen.

  1. sql server/system restart
  2. dbcc dropcleanbuffers statement execute
  3. Memory pressure.

i hope this would help you to understand.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.