Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 7 down vote accepted

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...

share|improve this answer
    
This is exactly what I'm looking for. Thanks. – andba77 Feb 18 at 18:18

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

share|improve this answer
1  
There isn't really a way to do both of those things together, as far as I can tell. You also have to rely on SQL Server Agent to start automatically (it may not be set that way). – Aaron Bertrand Feb 18 at 18:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.