SQLServer Gradually Becomes Slow Until Reboot

Recently a user in sqlcommunity slack workspace mentioned that their SQLServer gradually becomes slow until it is restarted.

In my experience, I have faced this issue of the server becoming slow over time unless rebooted in 2 conditions –

• Sometimes the queries got old & poor execution plans due to stale statistics.
• Other times it was due to increased IO latency

In the first condition, I usually find that the DBA Update Stats maintenance job is running as per schedule on a daily basis. So it was not necessarily a statistics issue but rather Ascending Key problem. This problem is quite typical, and the resolution could be query tuning, delta update stats for specific tables, or embedding update stats in your code itself, and even more things suggested in the below video.

The second scenario where I saw gradual slowness was when the underlying disk was showing latency. With disk latency, more and more queries take more execution time than usual, and eventually making the problem even worse with time.

In this disk latency scenario, this is how it looks like in the open source SQLMonitor All Server dashboard.

For immediate relief in case of disk latency, the most practical solution is to reboot the SQL Server machine. Rebooting just the SQLServices may not help in this case.

For the permanent resolution of disk latency, I would suggest going through my blog Troubleshooting SQL Server Storage IO.

I hope this will be helpful to fellow SQLServer users facing this kind of weird slowness issue 🙂