How it Works: XEL Health Session and Shutdown

  • Artikel

 

There are a variety of posts talking about the black box recorder (XEL Health Session) explaining what the recorder is and does: https://blogs.msdn.microsoft.com/psssql/2012/03/08/sql-server-2012-true-black-box-recorder/

This week I ran into niche behavior while looking at the health session output.  While it is unlikely to have any impact on your server the output during a SQL Server shutdown could mislead you.

The health session runs on a preemptive, above normal, priority thread.  This design allows the health monitor thread to provide rich output even when the overall server is under stress.   The session allocates memory and initializes all the necessary captures during startup and then goes into a loop until the session is stopped.   The loop waits for the designated interval to elapse, outputs information and continues the loop.

You may be aware that issuing a standard shutdown request to SQL Server does not stop the SQL Server right away.   SQL Server allows all administrator (sa) level commands to complete before the SQL Server is shutdown.  As soon as the shutdown request arrives it is recorded by the SQL Server.   If you attempt to connect or execute commands as a non-sa user they are rejected.   Shutdown also begins various cleanup activities.

What I uncovered is that shutdown, as you might expect, terminates the health session, right away.   The loop controlling the health session checks to see if the session itself or the SQL Server instance has been shutdown.   If either of the conditions is true the health session is terminated.   This means a long running query, by an sa connection, can continue keeping the SQL Server instance alive. 

Since the health session is terminated when the shutdown request arrives it can appear as if the health output was not produced.  By design, any interval, which could have occurred during the shutdown, are not present in the XEL health session output.  

Bob Dorr – Principal Software Engineer SQL Server

Comments

  • Anonymous
    July 04, 2016
    Good to know - thanks!
  • Anonymous
    July 27, 2016
    "SQL Server allows all administrator (sa) level commands to complete before the SQL Server is shutdown."So THAT'S why this varies so much from instance to instance.