I am wondering if rebooting a server in a schedule would be good idea for performance.
Let's say we want to reboot the server at 02:00 AM per 2 nights.
The server here is Windows Server 2008 R2. Mainly, SQL Server and IIS 7.5 (nearly 15 apps running) are running under this server. Server has 4GB memory.
If you are looking to reboot the computer to improve performance it probably means that eventually you are running into memory management issues.
If anything, rebooting servers would hurt performance (and uptime of course) in a more ideal environment. One of the fundamentals of performance in computing is to take advantage of caching (having data available in fast memory). Each time you reboot, you blow away your cache. This is true of both SQL server and IIS. Although you might not have the ideal environment, the following should help guide you to a better option than rebooting the server on a schedule.
Now you mentioned that this is IIS 7.5. Although I find it depressing, so many web apps that run on IIS 7.5 have memory leaks that the defaults in IIS is to restart the APP every X minutes and shut it down if an APP pool is idle. The ideal is to fix the memory leaks -- but if you can't you might adjust these settings which include memory limits and timers. You can use perfmon to figure out which w3wp process is using the memory. It is a bit of a pain but you can tie it back the app pool with %systemroot%\system32\inetsrv\APPCMD list wps.
Going back to caching, SQL will take what memory it can. You can limit this in the properties for SQL server. If you don't limit the memory and you are also running IIS on the box, these can start to battle for memory killing performance. This excellent article goes into this in detail: A Sysadmin's Guide to Microsoft SQL Memory.
Since you have both IIS and SQL on the same box, you will have to balance their memory usage. If you don't, you might get memory that is likely to be used again swapped out to disk -- which is a terrible place to be (There should be perfmon counters for swap activity). By using the IIS Recycle settings and the SQL Memory limits, you should be able to make this system stable. To balance this you might need more memory than 4GB. Also, if it is an option, I would strongly recommend putting SQL server on a dedicated machine -- it is going to make performance much better and greatly simplify things.
While I would agree that there is nothing wrong with rebooting the box, per se, based on your comment that the SQL Server Agent is stopping I would advise some additional root cause analysis. Services don't typically just stop, and the SQL Server Agent services hasn't acted that way in my experience, typically.
I think you'd do well, aside from rebooting, to examine the event logs and run a long-term performance counter log that you can analyze with Performance Analysis of Logs (PAL) to see if it "sees" anything wrong. You should try, if nothing else, to correlate the events associated with the SQL Agent stopping with other factors.
I'm not a proponent of rebooting servers on a schedule, especially not as a means of resolving some underlying issue. If you need to reboot this server to resolve a performance issue then the better course of action is to find the cause of the issue and resolve it. Rebooting the server on a regular schedule is only obfuscating the underlying issue.
If you've got significant memory leaks then sure, why not - otherwise reboot monthly with updates.
If you really want to reboot the server on a schedule (because of the above-mentioned memory leaks or updates or any other reason) - why not look at a cluster solution? Put up another server in parallel, hook them up to a load balancer (even a simple one would do) and you can reboot them as much as you want without losing service up-time or worrying that the server won't boot at all and you'll be out.
It's not a horrendous idea, but if it's just 'voodoo', it's probably not going to help you much.
However, there are two reasons not to let this be the end of your investigation into improving your performance.
One is future scalability. If your outages are the result of load, a certain number of queries, a particular query that hits a caching, query compilation, or btree indexing bug, or other issues that currently recur on a daily basis, they will probably occur more frequently as load increases over time. Nip that in the bud.
The other problem is that I suspect you will need to halt incoming requests from dependent services during your restart. You've just created an operational cadence. Every time some daily task needs to be run, it will end up tied to your restart. At some point, you'll have these massive rolling restarts that take six hours (I am not exaggerating here, I have seen it happen at more than one company) and no one will remember why everything needs to be stopped and restarted in the middle of the night.
My recommendation would be to monitor the SQL process and restart as needed. As mentioned by an earlier poster, SQL doesn't have the memory leak people think it does (and I say this as a person who was on the MSSQL team in the mid-90s). You want your database server to use almost 100% memory and CPU. Anything less is wasting resources.
If you have poorly written code and memory leaks then rebooting may be the only way to return the allocated memory back to the pool. If you have memory bound processes then this refresh of the pool to a clean state can certainly improve performance....for a while. But this is really a bad way to handle performance issues, the actual cause should be nailed down and fixed.
Otherwise, let it run until you need a maintenance window to apply patches/applications/restore data. This might be a good time to suggest a performance engineer take a look at the servers in question for exactly why/what issues are compelling this.
While not a complete answer per se, but is it a viable option to add some more RAM to the server? 4GB is a tad on the low side for a IIS/SQL Server machine. Depending if it is an actualy dedicated server unit or a desktop pressed into service you might be able to get it 8GB or more for a fairly low cost. Granted, if it is a server it might cost a bit more than standard desktop RAM but it would give you a bit more time between forced reboots.
Saying that, see if you can limit SQL Server to use a maximum of 80% of the RAM, or look at logs to fine out exactly what is going wrong and/or why the service is stopping.
Unrelated to the SQL issue you may be dealing with if you have Windows servers and are following any sort of patching routine you'll be rebooting the servers on a regular basis without having to reboot "just because". When I worked for "BIG MULTINATIONAL" we were mandated to patch monthly and as such all of our servers were rebooted on a monthly basis at least once.
Im doing this on 3 server, 1 is our and 2 customers. Ive setted it up for various reason - one server 2008R1 have many updates pending for install, but I cannot batch install them, so im installing it one by one every day; another server 2012R2 - for boot troubleshooting and some performance issues etc. I don't think that's a bad practice to schedule a periodic reboot, from the other hard It can help track various hardware and software problems, especially those which are involved in auto start.
I know a big company which is not only rebooting their Windows servers nightly, but some of them even get reinstalled every 24hours. For them, its necessary because of memory leeks in the software and security issues.
Seems that some companies are rebooting every 24hours - although it seems strange to me as a linux administrator. To make it clear: I would never recommend to do this because of a memory issue - track down the issue and solve it.
If your memory usage stays fixed at 75% for months, then there is possibly no need to reboot - it's totally normal for a server application to use all the memory available - it boosts performance a lot because you need fewer disk-IO's if you use the RAM to cache your data.
