Optober rocks on today with a quick post on Priority Boost. I hadn’t planned to deal with this one until much later in the piece, but I’ve been reviewing a very unstable server for a client today, and it tracks back to this option. It’s another one of those things like ‘autoshrink’ which sound like a perfectly reasonable idea until you understand what it actuially does.
Danger – Only enable this setting if you like running with scissors.
Option Name: Priority Boost
Usage: sp_configure ‘priority boost’, 1
Default: 0
Maximum: 1
Requires Restart: Yes
What it does: Just like the name says, this option boosts priority of the SQL Server processes. Which sounds like a good idea, but what does it actually mean? Behind the scenes here is what is happening – SQL Server threads are being made uber important. They get precedence over just about everything else. Now this still sounds like a good thing right – this box is dedicated to SQL, so it should get precedence over everything. Only unfortunately in this case everything includes the operating system. The stability of the SQL Server is dependent on the stability of the operating system. I spent 4 years in Christchurch when it was shaking like a frozen puppy, so I can tell you that when the foundations rock, buildings fall over. The only threads that are a higher priority than SQL Server with Priority Boost enabled are the REALTIME_PRIORITY_CLASS level threads, and the usage of those comes with all types of warnings. You can read more about Windows Operating System Priority Scheduling to see all the big flashy warnings about doing this in an application. SQL Server is just another application and all the standard warnings apply.
If you do go ahead and enable it, at the very least, you can expect your interactions with the server to be unpredictable. You could be moving your mouse or clicking the start button, but SQL has priority over that. You could be opening management studio – but SQL has priority over that. You could be trying to stop your SQL instance because it is running horribly, but guess what…anything running in SQL has priority over that too.
It gets even worse if you are using a cluster or availability groups. Giving priority to SQL can mean that your cluster heartbeat is interrupted and your instance initiates a failover. I’m sure you don’t want your HA continually initiating failovers.
When should I use it: Alright…so if you got this far, you haven’t really absorbed the above. This setting should never be used unless explictely instructed by a Microsoft Engineer to resolve a specific issue on a server. Even then I suspect I would argue against with them before throwing the switch. Take a gun and fire buckshot into your server from 100 paces – it’s probably less damaging than having this setting enabled for any extended period.
What else should you know: There is some light. This configuration is on the chopping block and will be removed in a future version of SQL Server. But every silver lining has a cloud….I’ve been reading that on the books online page for Priority Boost for as long as I can remember.