How To Use sp_configure In SQL Server 2008

In order to check and set the value of many configuration options in SQL Server 2008 (and 2000\2005), you can use the sp_configure system stored procedure. In order to see and set many of the options, you need to call it and enable ‘Show Advanced Options”, then issue a RECONFIGURE command (as you see below).  Then you can just run sp_configure by itself to see what your current values are.

Below are some of the configuration items that I like to change from their default values.  If you are running SQL Server 2008, I would enable backup compression (by default), and I would turn on “optimize for ad hoc workloads”.  If you have an OLTP workload (with lots of write activity and many frequently run, low cost queries), I would set “max degree of parallelism” to 1. I would also set your max memory to an appropriate value based on the amount of physical RAM on the server (as I wrote about here).

Finally, you will need to enable the Common Language Runtime (CLR), if you are going to use .NET assemblies inside of SQL Server.

 

-- How to use sp_configure to set some common Advanced Options

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO


-- See what the current values are
EXEC sp_configure



-- Turn on backup compression by default (new for SQL 2008)
EXEC sp_configure 'backup compression default', 1
GO
RECONFIGURE
GO

-- Turn on optimize for ad-hoc workloads (new for SQL 2008)
EXEC sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO

-- Set MAXDOP = 1 for the server (if you have an OLTP workload and you see CXPACKET waits)
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE
GO

-- Set max server memory for the server (based on how much physical RAM you have)
EXEC sp_configure 'max server memory (MB)', 6500 -- This value = 6.5GB
GO
RECONFIGURE
GO

-- Enable CLR (if you need it)
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

This entry was posted in SQL Server 2008. Bookmark the permalink.

One Response to How To Use sp_configure In SQL Server 2008

  1. Jose Eduardo M. Cardoso says:

    Smart tips…thanks!!! For a list of configurations just run:
    SELECT * FROM sys.configurations ORDER BY name ;
    GO
    or
    exec sp_configure ‘show advanced options’,’1′;
    go
    reconfigure;
    go
    exec sp_configure;
    go

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s