I see it often in the field – sometimes people regularly restart SQL Server! We all hopefully agree that a regular restart of SQL Server isn’t really a good idea. But in today’s blog post I want to dig a little more into the details what the side-effects are when you restart SQL Server on a regular basis.
As soon as you restart SQL Server, you have lost all of the contents of the Buffer Pool which is where SQL Server stores database pages retrieved from the storage subsystem. As a side-effect your queries will simply be slower after the restart, because SQL Server has to perform a lot of physical I/O to read the requested data from the storage subsystem back into the Buffer Pool.
Another very important side-effect of a SQL Server restart is that you lose all the compiled execution plans from the Plan Cache. For every query submitted to the database, SQL Server now has to compile a physical Execution Plan again – and this takes time. It can get even worse when you have some Plan Instability and SQL Server generates a different Execution Plan than you had previously. In that case your overall performance can suffer dramatically, because the inefficient plan is cached and afterwards blindly reused – over and over again…
In my last week’s blog post I talked about where SQL Server stores the whole data of the various DMVs and DMFs: it is stored directly in the process space of sqlservr.exe. When you then restart SQL Server, the process of sqlservr.exe is also destroyed and afterwards recreated. Therefore you will also lose all of the data that was accessible through the various DMVs and DMFs. From a performance troubleshooting perspective this is a really bad idea, because after the restart you no longer have any idea what happened in the previous life of SQL Server.
When you restart SQL Server, each database also goes through the Crash Recovery process where SQL Server finally performs a rollback of all uncommitted transactions to bring the databases into a consistent state. Therefore – as a side-effect – you will also lose all those transactions that were not yet committed when you initiated the SQL Server restart. You also have to bear that in mind: all uncommitted transactions are simply rolled back during the Crash Recovery process.
I hope that today’s blog post gives you some ideas about why a regular restart of SQL Server isn’t really a good idea. I have no problem when people restart SQL Server in some specific cases, but it doesn’t make sense to perform these restarts on a regular basis, just because you had some issues in the past. If you had some issues you should investigate them and find out what the underlying root cause was.
Thanks for your time,