Can I defer a configuration change until the next time SQL Server is restarted?

Question

Let's say that I want to change a SQL configuration setting like MAXDOP. I know that changing this on an active instance will severely impact performance, as it will clear all of the cached query plans. Doing this while the instance is running is simply not an option.

Is there some way to modify the value now, but to "defer" the actual change until the next time SQL server is restarted?

What I'm looking for is an option like Oracle's "scope=spfile" for parameter changes. Does such an option exist in SQL Server?

Answer 1

Create a stored procedure in master:

USE master;
GO
CREATE PROCEDURE dbo.FixMaxDop
AS
BEGIN
  SET NOCOUNT ON;
  EXEC sys.sp_configure N'show advanced options', 1;
  RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'max degree of parallelism', 8;
  RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'show advanced options', 0;
  RECONFIGURE WITH OVERRIDE;
  -- optionally, drop or alter the procedure so it doesn't run again
END
GO

Now, you can set the procedure to be a startup procedure (see here and here):

EXEC sys.sp_procoption N'dbo.FixMaxDop', 'startup', 'true';

If you only want this to run once, then you'll want to flip that option off after the configuration option has been set correctly:

EXEC sys.sp_procoption N'dbo.FixMaxDop', 'startup', 'false';

Alternatively, you can do as MichaelK suggested - create a job with a step that calls the above procedure, make sure it is owned by sa or someone in the sysadmin role, and then set it to start when SQL Server Agent starts. However, you're relying heavily on SQL Server Agent being set to start automatically (it might not be, and that might change before the next restart), and that Agent will start successfully after the next service restart (it might fail for a variety of reasons, including service account changes). You also can't create a schedule that runs when Agent starts and only runs once (it's one or the other), so you'd have to do something else like script an additional job step that disables the job or removes the schedule.

Not really a big deal for the procedure not to run again, I suppose, unless you make some other manual change later and forget about this - then you'll revert to this explicit setting the next time you restart after that change...

Answer 2

You could setup a SQL Server Agent job to run when SQL Server Agent starts up and set the schedule to only run once.