5 Reasons Why Restarting SQL Server Can Make Performance Problems Worse
I understand that people are busy and just need things to work. So, when there’s a performance problem with SQL Server, it can seem like the best, fastest way to get things working again is to restart the SQL Server service, or even reboot the machine. This is an even more attractive option when you’re a small shop with little or no database expertise. But what if what you’re doing is causing more harm than good?
Here are five reasons not to restart to solve a performance problem.
1. Restarting SQL Server only covers up the problem.
Restarting SQL Server is only a short-term fix, because whatever caused the problem is likely to recur. For example, if the problem you’re facing is blocking, it’s fairly likely that the queries involved will run at the same time again and cause the same issue again. As another example, if some of the queries have large memory grants, those queries will run again and will likely also consume large amounts of memory the next time they run. Do you have certain queries that use a lot of CPU? Restarting won’t help any of these issues in the long term.
2. Restarting clears the SQL Server cache.
This can cause issues with CPU usage to recompile plans. Plan recompilation can cause its own performance problem called parameter sniffing. This cache clearing effect also means more IO on the disks to read data back into memory. For SQL Server instances running 2017 and 2019 Enterprise Edition, there is a feature called Memory Grant Feedback. This feature helps with the size of memory grants for queries. The catch in 2017/2019 is that it does not persist. The memory Grant Feedback process is all in memory. So, when the SQL Server is restarted, the feedback process starts over.
3. Restarting SQL Server clears out helpful troubleshooting metrics.
SQL Server keeps track of a variety of metrics, like wait statistics and index usage information. Wait statistics are SQL Server’s way of keeping track of what resources are either being consumed or are needed for queries to run. This data can be very helpful at the server level and the query level. At the server level, certain patterns are classic indicators of certain kinds of workload problems. At the query level, wait statistics can be used to guide performance tuning efforts for individual T-SQL statements or entire stored procedures. When SQL Server restarts, this data is lost.
The same is true with index usage information from the missing index dynamic management views. A restart clears these statistics.
4. Restarting SQL Server kills all sessions that were running, instead of just dealing with the problematic sessions.
For example, if the problem is blocking, then there may be as few as 2 sessions involved in the performance problem. Restarting the SQL Server kills all sessions, causing rollback of work for many sessions instead of just the two involved in the issue. The SQL Server restart creates downtime by killing more sessions than is needed.
5. Restarting SQL Server triggers the crash recovery process.
If you restart SQL Server, this can mean long, single-threaded rollback for long-running transactions. I’ve seen this take up to 45 to 60 minutes in some cases. If the instance is older, then the modern feature called Accelerated Database Recovery can’t save you from a long rollback, and the database will be inaccessible until crash recovery completes. This means the outage will last even longer.
A Better Approach
So, what else can you do if you are experiencing performance problems? I’d suggest that you
- Run and examine the output of sp_whoisactive
- Run and examine the output of sp_BlitzFirst
- Run and examine the output of sp_QuickieStore
or - Contact a SQL Server consultant and ask for help if you don’t know how to do those first three things.
Want to work with The SERO Group?
Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.