Hidden Operation Restarting a SQL Server Database

When starting the database after rebooting, the transaction log for each database is read by SQL Server. Validated transactions are reported, uncommitted transactions are canceled, and previously active portions of the journal are marked as inactive and most of the free space in the log file is free. This process is called recovery and occurs when the database is started, as part of SQL Server startup, AG failover, mirror failover, or after a database is restored from scratch. a backup.

This may have had an impact on performance, but restarting the servers has many consequences for SQL Server performance. The buffer cache is cleared, as well as the plan cache, all open connections are disconnected, all current jobs of the SQL Agent are canceled, and the list expands.

The entire transaction log may be the problem, or at least part of the problem, but consider a scenario in which you have encountered a bad plan selection because of parameter detection problems. This could easily cause performance issues in SQL Server because the cached plan is not optimal for the number of rows returned by a particular execution of a parameter query.

This problem has now been hidden by a restart because the plan cache has been cleared. If the first run of this problematic query selected the correct plan after rebooting, you might not see this problem for a while until a bad plan is cached again. for this query.

Addressing this problem in the long run (unexpected performance degradation) requires proactive monitoring:

  1. Collect session and performance statistics with the help of a paid monitoring tool or free tools such as sp_blitz and sp_whoisactive. Record this information in a table for regular analysis. This also ensures the availability of information after your server has restarted the server.
  2. Gather your wait statistics regularly and connect them to a table for analysis.
  3. Have the client stop restarting the server. This clears many of your valuable DMV statistics in memory and makes it more difficult to identify and resolve the problem.
  4. Enable the remote DAC. The next time this happens, you want the resources reserved for an administrator to be able to connect and scan the server to find the cause.
  5. Educate your customer – the intensive use of memory on an SQL server does not usually pose a problem. SQL Server wants memory, it offers the best performance when it has enough memory to store more cached data that is accessed regularly.
  6. If you have SQL Server 2016 or later, enable Query Store. This will help you identify unexpected request regression, such as the one described above. It also helps you implement short-term protections, such as forcing good plans known for a query.

Also check out this article, which is an excellent resource for developing a performance troubleshooting methodology in SQL Server, as well as this article, which also contains some quality tips.