1

i want to know what operations will be execute in the reboot of SQL server?

The scenario is, the transaction log is almost full, one of my client felt the system is very slow (in fact queries are not executable, and he traced the server CPU & memory usage is very high), thus he choose to reboot the entire server.

Afterwards, he said the operation ran smooth again.

I would like to know, what operations are done in the reboot operation of sql server particular to the transaction log

for example, is a reboot could force the SQL server executing a checkpoint? or even a backup? so that reboot can somehow free the occupied space?

5

As part of database startup following the reboot, the transaction log for every database is read by SQL Server. Committed transactions are rolled forward, uncommitted transactions are rolled back and the previously active portions of the log would be marked inactive and leave most of the space in the log file free. This process is known as recovery and occurs on database startup, which happens as part of SQL Server startup, AG failover, mirroring failover or after restoring a database from backup.

This may have had an impact on performance, but rebooting the servers does so many things that would affect SQL Server performance. The buffer cache is cleared, as is the plan cache, all open connections are disconnected, any running SQL Agent jobs are cancelled, the list goes on.

The transaction log fullness may have been the problem, or at least part of the problem, but consider a scenario where you have encountered poor plan selection due to parameter sniffing issues. 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 parameterised query.

This issue has now been masked by a reboot because the plan cache has been cleared. If the first execution of that problem query picked the good plan after the reboot, you may not see this issue for some time until a poor plan is cached for this query again.

Tackling this issue long term (unexpected performance degradation) requires some proactive monitoring:

  1. Gather session and performance statistics using either a paid monitoring tool or free tools like sp_Blitz and sp_whoisactive. Log this information to a table for regular analysis. This also ensures you have information available after your client reboots the server.
  2. Gather your wait stats on a regular basis and log them to a table for analysis.
  3. Ask the client to stop rebooting the server. This wipes many of your valuable statistics from in-memory DMVs and makes identifying and resolving the issue more difficult.
  4. Enable the remote DAC. Next time this occurs, you want resources reserved for an administrator to be able to connect and analyse the server to get to the root cause.
  5. Educate your client - high memory utilisation on a SQL Server is not typically a problem. SQL Server wants memory, it provides best performance when it has access to sufficient memory to store more of the regularly accessed data in cache.
  6. If you have SQL Server 2016 or higher, enable Query Store. This will help you identify unexpected query regressions, such as the one described above. It also helps you implement short-term protections such as forcing known good plans for a query.

Also, take a look at this article which is a great resource for developing a performance troubleshooting methodology in SQL Server, and this article which has some great tips as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.