Resist the temptation.
I’ve always enjoyed Think Geek and all the nerdy goodness for sale on their site. They have all sorts of t-shirts with Geeky messages.
Over the years, I have been directed several times by Made2Manage Support to do just that, to re-boot my SQL Server for various reasons. This is the obvious “quick fix” to many problems with M2M performance and quite often works and while minimizing downtime.
However, there are several reasons not to do so.
- When you restart SQL Server 2005, you lose the contents of the Dynamic Management Views (DMVs), which store information about what’s going on inside the server. You lose all the information that would have helped you troubleshoot the problem.
- Computer problems don’t just happen, there is always a reason even if we cannot yet understand it. If you re-boot the server, the symptom will most likely happen again because you haven’t dealt with the underlying problem that caused it.
- Restarting the server erases the cached data and execution plan. The cache is free performance.
- If you restart the sever, you’ve missed a great opportunity to learn more about SQL Server. Anybody can re-boot the computer to “fix” a problem, but a skillful SQL Admin learns to monitor and diagnose problems before the symptoms get severe.
- If your server performance is degraded, it might be due to someone having an open transaction that’s doing work. Rebooting the server at that point causes their work to be rolled back, which might take even longer, even after the server starts up again.
- The server might be doing maintenance jobs, like DBCCs or index rebuilds, and you could fix the problem by looking at the jobs and adjusting their schedules.
Ideally, we could replicate these symptoms on a test box and take all the time in the world to diagnose them. Reality usually involves your boss standing over you tapping his foot, as your company hemorrages money due to lost productivity. So, sometimes you have no choice in the short term but the dreaded re-boot.
I’m suggesting you think twice before doing it. In future blog posts, I’ll demonstrate how to monitor the performance of your M2M server as well as trace the exact SQL statements generated by M2M itself.
I really appreciate Brent Ozar for his expert advice in regards to this article.