Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Priority Boost Setting Caused Consistent Cluster Failover Issues


By:   |   Last Updated: 2017-06-23   |   Comments   |   Related Tips: More > SQL Server Configurations

Problem

What happens when you have a SQL Server 2016 machine that is setup in a Cluster, but the SQL Server Service consistently restarts or fails over to the passive node?

Solution

We recently had an issue with a newly minted SQL Server 2016 clustered setup.  The server had been up for a few months without many issues. Suddenly, the SQL Server service began to terminate and to restart intermittently. Within the span of few hours, the service would restart up to 100 times.

Service Stop - Description: SQL Server Service Stopping 1

SQL Service Stopping - Description: Second example of SQL Server Service Stopping

Furthermore, the server would often failover to the passive node, sometimes when the SQL Server Service was restarted or even other times that seemed equally random.

Failover Message - Description: Failover Message 1
Failover Message - Description: Failover Message 2

As we tried to troubleshoot the issue, we looked at everything from the Virtual Machine (VM) which housed the server to the operating system, to Operating System patches, to cumulative updates for SQL Server. The Server was Windows Server 2012 with all the current patches along with SQL Server 2016 with SP1 installed.  One of our first steps was to install the most recent Cumulate Update for SQL 2016, but unfortunately, that did not help.

We even saw several network related errors that popped up.

Network Messages - Description: Network Failover Message

We checked the memory allocated to SQL Server, which looked acceptable for a server with only SQL Server on it.

Memory Setting - Description: SQL Server Memory Settings

Additionally, we checked and validated many other server settings such as parallelism and network packet size.

Server Settings - Description: SQL Server Settings

To be honest, we were at our wits end as nothing was indicating what the problem could be. Of course, we were researching extensively online, but nothing in particular surfaced as an indicator. I was ready to blame the VM, the data center, the SAN, anybody, but then I saw it.

Someone had checked the Boost SQL Server priority checkbox!! I know that property stands out as a “GO Fast” button like running queries with NOLOCK. I also know that it is BAD, very bad in most cases to use as SQL Server will, in essence consume all the resources on the machine. We unchecked the “magic Boost SQL Server priority” option, and then suddenly all went calm.  Well not immediately, but it did solve the SQL Server Service restart and failover issues pretty much immediately. We are trying to ID the “perpetrator” who fell for the magic button bait and checked that box to begin with! No suspects have come forward yet!

Boost Check Box - Description: Boost SQL Server Priority screen
Next Steps


Last Updated: 2017-06-23


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools