Does restarting SQL Server speed it up?

Question

I have noticed that some DBAs restart SQL Server very frequently, sometimes even nightly. I believe they do it to free up some memory, or perhaps to speed up queries too. I know that after a restart query plans have to be recompiled, but even including that I wonder if there is a net benefit to this practice.

Is it true that restarting SQL Server daily makes it run faster?

Answer 1

Restarting the server is probably one of the most damaging things for performance. It means you force a cold cache for data, a cold cache for query plans, and all SQL Server internal caches are also nuked in the process. Not to mention that by throwing away all the statistics collected in the operational stats DMVs, you diminish your chances of ever investigating something successfully.

There is no official guidance backing this practice, I have never seen it mentioned in any good practice reputable work, I never hear of a reputable expert ever mentioning this as a practice. Don't do it.

Answer 2

You shouldn't be restarting SQL Server unless you have changed service properties or set startup traces that you want to take effect immediately.

As @RemusRusanu has stated many points, it clears a lot of caches and causes SQL Server to do a lot of unnecessary startup procedures.

It sounds like this server isn't a dedicated SQL Server/database server. It is best practice to have a production database server have only one purpose...to be a database server. In which case, you would set aside enough memory and resources for the OS and give everything else to SQL Server. This would lead you to not starve any other applications or server roles.

Answer 3

If it speeds up queries, parameter sniffing might be involved. If a crap plan is cached and applied to inappropriate subsequent calls, then the miracle of rebooting allows the common/correct plan to get cached. If that's the case, there are infinitely better ways of correcting the behaviour as other have indicated. But until they stop rebooting the box, there's no way to perform root cause analysis.

Answer 4

While the other answers are good, they're missing an important piece: Windows' file cache.

On 64-bit Windows, there's no cap to the amount of memory Windows will use to cache files. Windows can drain your system completely dry of memory, and at that point, you'll start swapping to disk. It's been documented in a few places:

• https://blogs.msdn.com/themes/blogs/generic/post.aspx?WeblogApp=ntdebugging&y=2007&m=11&d=27&WeblogPostName=too-much-cache&GroupKeys=
• https://blogs.msdn.com/themes/blogs/generic/post.aspx?WeblogApp=ntdebugging&y=2009&m=02&d=06&WeblogPostName=microsoft-windows-dynamic-cache-service&GroupKeys=
• https://blogs.msdn.com/themes/blogs/generic/post.aspx?WeblogApp=ntdebugging&y=2007&m=10&d=10&WeblogPostName=the-memory-shell-game&GroupKeys=

By restarting SQL Server, you force SQL to give up memory, thereby letting Windows get more, and the paging stops temporarily. SQL will start again at near-zero memory use and gradually go up, and when the box runs out of memory again, the restart will help temporarily. By restarting the entire OS, you'll also force Windows' file cache use down.

The real fix: stop copying files from the Windows server or cap the amount of file cache in use with the Dynamic File Cache Service as documented in those blog posts above.