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