Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?
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:
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.
- sql server/system restart
- dbcc dropcleanbuffers statement execute
- Memory pressure.
i hope this would help you to understand.