As the gatekeeper of the Advisory Conditions Packs at SentryOne, I sometimes receive questions about changing or turning off conditions in a pack to better serve our customers' unique requirements. While we strive to provide conditions that are useful to all clients, we understand that in the world of SQL Server, "it depends" and "YMMV" are the rules of the land.
Recently, one condition in particular (*cough* Contention in Memory Objects *cough*) has come up with the question of "how do we stop this thing?". For a little background, SQL Server 2016 dynamically relieves contention when contention factor > 1, so this condition is not as useful as I originally hoped. SQL Server performance improvements are good for all of us though, so this isn't the worst thing to happen. See the "SQL Server 2016 – it just runs faster" GroupBy session presented by Bob Ward for more information.
You can fully disable an Advisory Condition in two steps (as long as you don't get caught up counting the finer details like opening the client or right-clicking – yes, I'm familiar with *some* of you and am not knocking your attention to detail).
Disabling an Advisory Condition in Two Steps
Once a condition is disabled, the name will be highlighted in gray, and it will no longer execute.
Any associated actions will be crossed out (Menu –> View –> Conditions).
Disabling Advisory Conditions is my preferred and recommended way of dealing with ones that shouldn't be running. There are a couple of other options that I'll mention.
If you delete the associated actions for an Advisory Condition, it will no longer evaluate. I don't recommend this option because if a condition no longer executes, it should be visibly disabled (gray) in the Conditions List. Also, if you change your mind (enable the condition), you won't need to add the actions manually. Associated actions automatically reactivate with the Enable right-click option.
You can also Delete an Advisory Condition (located right under Disable). However, if you later have questions about it, change your mind, or want to clone it to make useful changes, this option makes that difficult. Deletes are serious business. As always, delete with caution.
Disabling Advisory Conditions is a simple process and the recommended action to stop one from executing.