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