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