Skip to main content
2 of 4
added 184 characters in body
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

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 either set the procedure to be a startup procedure (see here and here):

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

Or 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 can't create a schedule that does that and only runs once (it's one or the other), so you'd have to do something else like script a 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...

Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619