Basic Guide to SQL Server Configuration (sp_configure)
Published: 3rd May 2013
Configuring your SQL Server correctly is one of the key tasks that must be completed in order to get the best performance out of your system.

I won’t go into every single configuration option, just those which I tend to change and tamper with when installing and running a new SQL Server instance.

Let’s start by opening a query window in SSMS and executing sp_configure. If you have never run this before, you should see something similar to the following:

Allow Updates



This option, when set to 1, allows you to make direct updates to system tables within SQL Server. This is something that is very rarely required, if ever, and therefore it is safest to be left as 0. However, if a time does come when you need to update a system table, then this is how you do it…
sp_configure 'allow updates', 1
go
reconfigure
go 
Note: All changes made using sp_configure require a reconfigure statement to be subsequently run to commit the change. There are rare exceptions that require a service restart, but most simply need reconfigure.

Backup Compression Default



This defaults at 0 and therefore backups are, by default, NOT compressed. If you wish to use backup compression as standard across your server then you would set this to 1 as follows:
sp_configure 'backup compression default', 1
go
reconfigure
go 
Backup compression is a VERY good inclusion in SQL Server. However, it does use a hefty amount of CPU, so if your machine is limited in that area, you may not want to turn this on. If, however, you have the CPU to spare then this is a very good function to use.

It is also only available in Enterprise edition for SQL Server 2008, but Standard and Enterprise for SQL Server 2008 R2.

Note: For further reading with regard to if you should enable this or not, please read this article.

CLR Enabled



If you wish to run CLR code on your SQL Server then you need to have this enabled. Simple as that.
sp_configure 'CLR enabled', 1
go
reconfigure
go 

Max Text Repl Size (b)



If you intend to have replication running on your server and you are going to be replicating BLOB data then it may well be a good idea to increase this figure. I recently had someone come to me with the following application error:

They had set their tables up to be nvarchar(max) and therefore could not understand why their application was complaining. It turns out that they had never changed this value in sp_configure. One simple piece of code and the problem vanished:
sp_configure 'max text repl size (B)', 2147483647
go
reconfigure
go 

Advanced Options



The remainder of the basic options on display can be left as default unless you have reason to change them (such as timeout settings etc).

Now it’s time to look at the advanced settings. Run this piece of code:
sp_configure 'show advanced options', 1
go
reconfigure
go 
Now when you run sp_configure you’ll see that the list of configuration options has drastically increased.

(There are 70 rows in total, I won’t be showing them all here…)

Again I’ll just pick out the key ones that I tend to change, most others should mostly be left alone.

Ad Hoc Distributed Queries



If you intend to query your server using OPENROWSET or OPENDATASOURCE then you will need this setting turned on.
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go 

Database Mail XPs



If you intend to use Database Mail (which I advise, if possible, that you do as it’s incredibly useful and well integrated… refer to my article on Database Mail), then you will need this setting turned on…
sp_configure 'Database Mail XPs', 1
go
reconfigure
go 

Max Server Memory (MB)



I urge everyone to look very carefully into changing this setting as SQL Server is a VERY RAM hungry application and will NOT give memory back to the Operating System. Numerous times I’ve been asked to look at SQL Servers which are performing incredibly badly as a whole, but the SQL Server itself is running fine, and 99% of the time it’s because the Operating System has no RAM to fulfil even the simplest of tasks because SQL Server has stolen it all. Therefore I always set this figure in order to give SQL Server the most RAM possible whilst also holding some back for the Operating System to use.

For example, in a server with 64GB RAM I would generally allocate 56GB to SQL Server and leave the remaining 8GB for the Operating System. This seems to work well.

IMPORTANT: The figure is in MB and therefore you need to scale accordingly. I know someone who specified the max as 20MB (thinking it was GB) at which point SQL Server shut down and refused to come back up again without a LOT of hard work.

On my desktop machine I only have limited RAM so I’m going to restrict my SQL Server instance to just 2GB…
sp_configure 'max server memory (MB)', 2000
go
reconfigure
go 

Xp_cmdShell



This will be a controversial one because I know there are a lot of people who argue that this should never be used, but I actually use it a lot, for a good many things. Therefore this is a setting I always turn on…
sp_configure 'xp_cmdShell', 1
go
reconfigure
go 

Cost Threshold For Parallelism & Max Degree of Parallelism



I have brought these two together and left them until the end for good reason. These are settings that I would rarely touch when setting up a SQL Server, but are something I would revisit once the server is bedded in and has been running consistently for a while.

These are, by default, set at 5 and 0 respectively. The 0 implies that there is no limit to the number of processors that SQL can utilise when working out how best to execute a query (well, there is a limit, but it varies depending on edition… http://msdn.microsoft.com/en-us/library/ms181007(v=sql.105).aspx). The 5 refers to the cost of the query as calculated by the execution plan (and listed therein).

Therefore if you have a query that has a cost < 5 then it will not use multiple processors to execute. However, if a query has a cost > 5 then SQL Server will refer to the MAXDOP setting and calculate the execution plan accordingly.

After having run your SQL Server for a while you may notice some patterns that make you wish to edit these settings…for example if you have a good few queries which have a cost of 5-7 yet are running really heavily and slowing down the server because they use all available processors on the machine, then maybe you could increase the cost threshold to 7, therefore removing these and then test the server performance again. Or you could leave the threshold low and reduce the degree of parallelism so that not all CPUs are used for individual queries. Or you could alter both settings if you wished.

Either way you would need a stable benchmark to see how any adjustment you make works (or doesn’t) with your system. In many systems I’ve worked with I’ve found the defaults are fine, but there are others which work considerably better after these settings have been tweaked.

Also, it’s wise to note here that if you have just 1 or 2 queries which are using excessive parallelism then you don’t need to go changing server-wide settings as there is a query hint “option (MAXDOP 1)” which forces the query to use just 1 processor and therefore for a handful of troublesome queries this is a much better alternative than forcing a server-wide setting.

Comments:
NB: Comments will only appear once they have been moderated.