When to periodically RESTART SQL SERVER 2008 R2? [closed]

Question

So I've read that it's really a bad practice to restart SQL Server on a production environment..

Does restarting SQL Server speed it up?

Automatically restart SQL Server 2008

Why are periodic restarts required to keep my instance performing well?

Then tell me this,

How many of you have maintained an SQL Server on a production environment without having to restart it for at least ONE year or more? If so, can you share some general guidelines on how to do this? namely with regards to the following:

1. Minimizing/eliminating deadlocks
2. Cost efficient CPU and Memory usage
3. Minimizing/eliminating memory leaks
4. Monitoring the servers' processes to identify increase resource consumption

Please pardon me if anyone would find this question as offensive, I would just like to be more enlightened with the subject matter.

Answer 1

The most important guideline: Don't, unless it's for OS or SQL Server patching reboots. If you've configured server memory usage properly, and you don't have a CLR component, SQL Server bug, or other software leaking resources, then "preventive reboots" aren't necessary.

I also wouldn't recommend keeping the server up for a year without patching. If you need this kind of uptime, you're better off looking at SQL Server's clustering/high-availability options and performing rolling updates rather than running way behind on patches. With a good HA setup, you can essentially create the illusion that the server reboot only takes a couple of seconds.

Answer 2

From my experience, I've never needed to restart a production server, except for patches. Just like Marian, I do not understand how a reboot could have a beneficial effect on your 4 points. My simple advice would be to avoid reboots as much as possible, as they are no cure to your problems. Proper configuration, profiling ... will get you much further.

Answer 3

Like @KookieMonster, I fail to understand how a reboot can minimize or eliminate deadlocks, make CPU and memory message "cost efficient", minimize or eliminate memory leaks, or reduce resource consumption.

I have maintained plenty of SQL Servers that have uptimes of way longer than a year. @vonPryz makes a good point though, there are often very good reasons to reboot servers, most importantly service packs and other updates. You also need to restart SQL Server in the event of certain configuration changes - both to SQL Server and the underlying OS and/or hardware.

I did have one exception where we intentionally restarted one node in a cluster every week, but it was due to a bug in SQL Server 2005 (SP2 I think) where the "temp tables for destruction" counter just climbed and climbed exponentially and eventually choked the server (memory exhaustion). We were issued a private hotfix after about 6 months of this.

That is an edge case, though, and disappeared when we applied the hotfix (and was only ever seen on one cluster, even though several were configured identically and ran the same app). In almost all cases you should not be looking at restarting SQL Server as preventative maintenance. Focus on real problems you actually have, and troubleshoot those problems proactively instead of just assuming that a restart is a magic cure-all.

There's an old joke in IT: if your computer isn't working, reboot. Please don't treat SQL Server this way.