SQL Server with Mr. Denny

Feb 11 2019   4:00PM GMT

Should SQL Servers be rebooted weekly

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:

There are people out there that reboot SQL Servers (or all servers) weekly. Usually the argument that I hear for doing this is that SQL Server has memory leaks which can only be resolved by rebooting Windows.

My argument against this, is that no SQL Server doesn’t have memory leaks which cause it to need to be rebooted weekly.  SQL Server is designd to use all the RAM that’s allocated to it. By default SQL Server is configured to use 2147483647 MB of RAM, or all of the RAM in the server. You can and should be changing this setting to a lower number so that there is memory for Windows and anything else that’s installed on the box (anti-virus, SSIS packages, SSAS, etc.).  The people that I typically see doing these server reboots, have been doing them for 15-20 years, typically because back then rebooting a server would fix a problem.  It won’t anymore.

There are servers that are running SQL Server that have been up for months, or years without issue (I’m ignoring the fact that you aren’t patching SQL Server and Windows here). I’ve persoally seen boxes with pretty heavy workloads on them that have run for years without having to be rebooted.

SQL Server doesn’t have any memory leaks. It’s a world class product of Microsoft, that runs millions of databases (if not more) and makes Microsoft billions of dollars in revenue each year. Whatever memory leak you think there is in SQL Server, there’s probably isn’t. If you think there is, then contact CSS at Microsoft to report the issue as Microsoft needs to patch the problem.

The biggest problem that people will see if that the buffer pool is flushed when the SQL Server restarts, and this causes SQL Server to read all the data it needs from disk as there’s no data in RAM. This causes performance issues right after the restart of SQL Server.

If you’re restarting SQL Server because there’s some performance issues that “goes away” after a SQL Server restart, we’re going to be better off dealing with the root cause of what’s causing the problem to come up to begin with. It’s probably going to be indexing or statistics, but that’s just a guess as every server is different, but it’s probably a safe guess.  Looking at the server will tell us what the problem is for sure, so we can solve the problem (and hopefully make the server faster in the process).

Denny

2  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • clweeks
    I have no doubt that you're right. But, in my experience there are a lot of shops that don't have anyone who knows how to track down the root cause and when they compare the cost of rebooting every Saturday at 0300 against hiring a gunslinger to come fix it, they rationally choose the reboot.
    10 pointsBadges:
    report
  • GrantF
    Well, SQL Server 6.5 needed to be rebooted weekly, so anyone still using that is probably rebooting. However, no one else should be.
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: