Restarting SQL Server – always a good idea?

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.

Never ever automate this process!!!

Buffer Pool

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.

Plan Cache

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…

DMVs/DMFs

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.

Crash Recovery

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.

Summary

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,

-Klaus

7 thoughts on “Restarting SQL Server – always a good idea?”

  1. Unfortunately, not all SQL Systems are in a fail-over environment, so all SQL systems will (and should) be rebooted each month because of the Windows patches. It would be nice if there was a feature that would dump buffer pool and/or the plan cache to be reloaded after a reboot due to Windows security patches.

    1. Klaus Aschenbrenner

      Hello Rick,

      Thanks for your comment.
      The content of the Buffer Pool is stored in your data files, and gradually reloaded when you request the pages after the SQL Server restart 😉
      And for your execution plans you have to make sure that you have a plan stability, so that the Query Optimizer can generate you the same plan again after the restart.

      Thanks,

      -Klaus

  2. I like the principal of your idea Rick. I’m a DBA and frequently have to explain to the Windows team why I don’t like frequent reboots. Your suggestion sounds like a feasible compromise.

    Klaus – I think Rick is saying that it would automatically reload the contents directly back after a reboot rather than wait for user requests to naturally reload the buffer pool

    1. Klaus Aschenbrenner

      Hello Darryl,

      The problem with that approach is that you can’t predict which pages all subsequent user requests will need.
      Maybe users are querying for different data sets after the reboot as previous?

      Thanks,

      -Klaus

      1. That’s true of course Klaus but for a server that has a decent steady PLE it might be a nice option for the DBA to have as an option.

  3. Probably should throw Hekaton in the mix too…. Durability SCHEMA_AND_DATA is also something to consider on server restarts 🙂

  4. Hi Rick..

    I’m newbie in SQL Server. I just read your post here, and finally understand why I lost my data when my server start automatically due to power outtage.

    Is there any recovery plan I should follow to safe my data?

    Thanks,

    -Dwi

Leave a Reply to Rick Cancel Reply

Your email address will not be published. Required fields are marked *