Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?
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:
- Why did a SQL Server Restart roll-back seemingly committed transactions?
- KILLED/ROLLBACK query doesn't stop running
- Rollback taking forever
- SQL Server 2008 R2 - Service shutdown - what with running stored procedures?
- https://dba.stackexchange.com/a/160293/1186
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.