RECONFIGURE—When to use WITH OVERRIDE?

When you’re using sp_configure to change a system configuration, you have to run a RECONFIGURE to finalize your change. You even get the helpful output that reminds you to run RECONFIGURE:

Configuration option 'max degree of parallelism' changed from 0 to 1. 
Run the RECONFIGURE statement to install.

There are many places online with code samples (Books Online, blog posts, vendor documentation, etc) that actually use the additional “RECONFIGURE WITH OVERRIDE” syntax:
Here.
Here.
Here.
There are even posts that switch between including “WITH OVERRIDE” and not including it
Here.

If you use those old-fashioned paper book things, they use the “WITH OVERRIDE” syntax, too. This example’s a bit dated (SQL Server 2005), but I don’t have much for paper books these days.

 

Even scripting out changes from the SSMS Server Properties window generates code that has the “WITH OVERRIDE” syntax.

--Script Generated by Server Properties window in SSMS 2014
EXEC sys.sp_configure N'cost threshold for parallelism', N'500'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'4'
GO
RECONFIGURE WITH OVERRIDE
GO

 

Which brings me to the point of this post…

 

Should I use WITH OVERRIDE?

Probably not.

That was an easy post. All done! Thanks for reading.

 

……Oh, right. Let’s talk about why.  Books Online actually has the answer:

WITH OVERRIDE

Disables the configuration value checking (for values that are not valid or for nonrecommended values) for therecoveryinterval advanced configuration option.

Any configuration option can be reconfigured by using the WITH OVERRIDE option. In addition, RECONFIGURE WITH OVERRIDE forces the reconfiguration with the specified value. For example, the minservermemory configuration option could be configured with a value greater than the value specified in the maxservermemory configuration option. However, this is considered a fatal error. Therefore, specifying RECONFIGURE WITH OVERRIDE would not disable configuration value checking.

Yup. It disabled configuration value checking. I don’t know all the scenarios where this would matter, or all the scenarios where you could cause yourself pain. I don’t need to know because I don’t use WITH OVERRIDE and I never have a problem.

It’s a bad idea to disable validation checking, especially as your default behavior. I’d much rather try to do a RECONFIGURE, get blocked by validation checking, then explicitly decide to proceed anyway via RECONFIGURE WITH OVERRIDE.

Books Online mentions one specific configuration that is affected, but I don’t trust that is a fully complete list. If it is correct today, it might change in the future. The fact of the matter is (and as a general rule for anything!) you should use the safety features that are intentionally put into a product. If you get in the habit of circumventing the safety features of your SQL Server (or BMW or Cuisinart), you’re eventually going to hurt yourself with a stupid mistake.