8

As far as I know adjusting Maximum server memory and Max Degree of Parallelism does not require the instance to be restarted. Will new settings always come into effect without restart?

10

Both of those server configuration options do not require a restart of the database engine. Running RECONFIGURE will be sufficient in this case.

exec sp_configure 'show advanced options', 1
go
reconfigure
go

exec sp_configure 'max server memory', 12288
go
exec sp_configure 'max degree of parallelism', 4
go

reconfigure
go

-- the new configuration has taken effect

Please see BOL for reference.

  • 6
    Note that the reconfigure will still do things to your instance that you might only expect during a restart, e.g. blow away the plan cache. So some queries might feel like the service was restarted. :-) – Aaron Bertrand Aug 13 '12 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy