Start a conversation

Optimize SQL setting (especially if full version of SQL 2008 Server); R2 instructions too

This will configure SQL so that it works well with RMS; from Jeff Hobbs (Check Point Software).
If HQ environment, all dbs should be configured with these settings including any offline dbs.
Run one at a time.

-- Turn on advanced options
EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

-- Set max server memory = 6400MB for an 8g Server machine  -- 1/2 for 4g
EXEC  sp_configure'max server memory (MB)',6400;
RECONFIGURE WITH OVERRIDE

R2: EXEC sys.sp_configure N'max server memory (MB)', N'6400'

 -- Turn on Ad Hoc Queries
EXECUTE SP_CONFIGURE 'Ad Hoc Distributed Queries', '1'
RECONFIGURE WITH OVERRIDE

 -- Turn off advanced options
EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

R2: EXEC sp_configure 'show advanced option', '0'

-- Turn off AutoClose in Express versions
EXEC sp_dboption 'databasename', 'autoclose', 'FALSE'
RECONFIGURE WITH OVERRIDE

R2: script not supported or necessary? (does not always work for some reason; make sure to enter db name)

 -- Change Recovery Mode to Simple to prevent log file growth
ALTER DATABASE "databasename" SET RECOVERY SIMPLE

R2: not supported or necessary?

Change the max memory line to your amount of machine's memory. Be sure to also change the 2 lines with databasename to your database.

To view all possible commands run this after enabling advanced options.

EXEC sp_configure

This also lets you know if advanced options are enabled and need to be disabled.

This should work for R2 (case sensitive?):

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE

Choose files or drag and drop files
Was this article helpful?
Yes
No
  1. Dave J

  2. Posted
  3. Updated

Comments