22

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?

Improve this question
0

5 Answers 5

Reset to default
39

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.

Improve this answer
0
29

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:

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.

Improve this answer
2
  • Would the practice of "pinning" the memory to a MIN and MAX (albeit that they should and could be the exact same KB amount) help to ensure SQL Server only consumes a predefined amount of MIN/MAX RAM allowing other Windows services/applications/networking requests to continue to function. This has been my practice and has worked nicely. Any other thoughts?
    – SnapJag
    Oct 11, 2012 at 20:40
  • @SnapJag - not necessarily, because SQL doesn't consume the min amount right away. SQL starts at zero and gradually ramps up based on need.
    – Brent Ozar
    Oct 12, 2012 at 21:50
13

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.

Improve this answer
0
11

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 work.

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.

Improve this answer
2

I agree with the sentiment that if you are doing everything right, then you may not need to Reboot/Restart your MSSQL Server.
To me this applies to the scenario where everyone is competent and you can fix anything.

I am not a DBA. I am a Software Architect and part of that involves building entire Database Schemas from scratch and, unfortunately, working with 3rd Party Databases that I have absolutely NO control over.
The people, who created and maintain one of our major 3rd Party Databases, barely made it functional.

Did I mention I am also not a Security Expert or Network Engineer either?

  • At least one Major Window OS Update, Security Update, BIOS Update, OS/MSSQL Service Pack, or MSSQL Cumulative Update are bound to come out every month or two.
  • Applying these in a timely manner means Rebooting/Restarting your Server around every quarter.
  • Even when operating inside an Intranet, why wouldn't you apply Security Updates?
  • If I was allowed to have SSL on our PHI Intranet websites, I would do it because no network is infallible. I am paranoid I guess.


To me the question then becomes: Should I Restart SQL Server more often than every 3 months?

Scheduling Restarts for the promise of an extra inch of performance, is like dancing for rain.
Maybe it'll come, maybe it won't, but you won't know for sure what caused it to rain.

  • If you notice a significant performance bump after rebooting the server due to regular maintenance, then you should investigate why that happens.
  • If you are having issues, and are not sure what is causing them, then as you reduce your variables by stopping services and jobs to find something like a memory leak (in extreme cases like this) maybe rebooting/restarting your server with some services turned off (or traces turned on) will help you rule out those other services as the cause.

I don't like saying you NEVER need to Restart it to troubleshoot an issue or verify failover, but I do have a problem with scheduling Restarts to keep an unknown performance issue from randomly occurring.

The Only exception to this is if you manage a rogue 3rd Party Database where rebooting it every week or two seems to be the only way to keep it operating and you are not allowed to fix or even touch it.
Even then, you should look for fixes, share them with the owner, and raise hell till it's resolved.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.