Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
19  
If the DBA has to reboot the database server nightly then he should really do some profiling on that server to see what's breaking it and work with the developer(s) to correct the problem. –  David Oct 24 '11 at 17:31
8  
If a DBA restarts the server nightly... this is a reason for termination without notice. Dear DBA- MDonalds looks for someone serving burgers. –  TomTom Oct 24 '11 at 18:00
add comment

migrated from stackoverflow.com Oct 24 '11 at 18:19

This question came from our site for professional and enthusiast programmers.

4 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
1  
+1 Was aware of the issue, wasn't aware the cache could be limited using the dynamic cache service. –  Mark Storey-Smith Oct 26 '11 at 22:33
    
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 '12 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 '12 at 21:50
    
Yes, true, thanks for clarifying that Brent. –  SnapJag Jan 8 '13 at 22:58
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.