Warning:- Settings mentioned below are listed by keeping developer workstation in mind. Even if some setting might applicable for live production server, please refrain using the same script or recommendation from this post against SQL production/UAT/QA server.

In developer environment it helps in many ways if you have database is on same machine as your application. This is also needed if need to do some work offline. As Developer edition comes at cost around  2000 Rs. or $50 and if you have proper MSDN subscription, you can install it without encoring any additional cost. Due to all these reasons, many developers have SQL server installed on their workstations.

In our development environment, we need to run lots of other applications other than SQL server, all these applications need resources. It helps if we can reduce the amount of resources used by SQL server. Below is the list of all such options we can consider in development environment.

Please note that all these settings are listed below are for SQL 2008 Developer edition, some of these settings are not applicable to earlier version of SQL or expression edition. Also based on your application.nature of work this setting is not applicable or may produce unwanted results. So, please go through the summary of each setting before applying.

1) Change max server memory

This is most important setting in development environments, as SQL tries to get as much memory available and because of this other applications starves for memory. Generally, I tend to allocate half of memory here. If you have more than one instance of SQL running, this should be a rather less value depending on instance usage. Setting max memory means that, SQL will not use more memory for cache, though there are some SQL processes which use memory outside this limit.

--Query to find out amount of memory on system
select physical_memory_in_bytes/1048576 MemoryAvailableinMB 
from sys.dm_os_sys_info 
go 
exec sp_configure 'show advanced options', 1
go
reconfigure
go
--generally half of total memory availabe
exec sp_configure 'max server memory (MB)', 2000
reconfigure
go

2) Change max degree of parallelism

In development phase, there are chances that we do not have optimal indexes available for query and which tends to increase the cost of a query. Increased cost can lead to the Parallelism. Parallelism has some overhead associated with them, so we can reduce the amount of Parallelism a particular query use, by setting

select count(scheduler_id) as AvailabeSchedulers 
from sys.dm_os_schedulers where scheduler_id < 255
go
exec sp_configure 'max degree of parallelism',2
go  

3) Change Cost degree of threshold

If you do not want to change the max degree of parallelism for whatever reason, you can set this option to control the parallelism. If estimated query cost goes beyond this then and then parallelism will be considered. Default cost threshold is 5. The value need to set will differ from application to application. But for example

exec sp_configure 'cost threshold for parallelism', 50
reconfigure
go

4) Change tempDB Size

By default tempDB is created with only 8MB of data size, which leads lots of auto growth of tempDB files. So, for development environment it’s good to set some startup value.

use [master]
go
alter database [tempdb] modify file ( name = N'tempdev', size = 102400KB , filegrowth = 51200KB )
go
alter database [tempdb] modify file ( name = N'templog', size = 51200KB , filegrowth = 25600KB )
GO

5) Enable Backup Compression By default

Many times we face the situation where a colleague wants a DB backup. Compressed backups saves the space and generally backups and restore are quicker if compression is used. For SQL server 2008 and above we can compress the backup by using with Compression option, but this is not the default option. We should definitely set this value.

EXEC sp_configure 'backup compression default', '1';
RECONFIGURE
GO  

6) If not needed change recovery mode of all user databases to SIMPLE

It’s good to set the database to SIMPLE recovery mode as we do not need the log and we do not take log backups. Please not that minimally log operations are faster in this recovery mode so if you are doing any performance tuning, see if minimally log operation is in used. This query needs to be executed for all such databases.

--find out all such database
select name,recovery_model_desc from sys.databases where database_id> 4 and recovery_model != 3
go
--this needs to be executed for all databases
alter database [DBName] set recovery simple
GO

7) If you are changing the recovery mode to SIMPLE, its good to truncate the log file

Now, if you have changed the DB mode to Simple, there is no meaning of keeping big log files. This will free up some space on the drive if you running out of the space. Below query will create SQL statements for all user databases in SIMPLE recovery mode.

select 'USE '+ d.name + '; DBCC ShrinkFile(' + quotename(df.Name,'[') + ',TruncateOnly)'
from sys.databases  d
inner join sys.master_files df on d.database_id = df.database_id
where d.database_id> 4 
and d.recovery_model = 3
and df.type = 1
go

8) Mark database which are not used frequently as offline

Sometimes developer environment tends to have more than one copy of production databases of different dates. All databases which are not needed very frequently should be set to offline mode. This will increase the startup time. Please note that of you drop the database while its offline database files are not getting deleted. Below query needs to be executed for all such databases.

alter database [DBName] set offline

9) Enable instant file initialization

Enable instant file initialization speeds up auto-grow of data files, restoring database and SQL startup. Kindly have a look at this post from Brad McGehee to see how to enable instant file initialization. Most of the time in development environment to get this enabled but its worth to check it.

10) There are chances that IT guy has installed all features even if it is not needed. If you are not using any of below services in developer environment, stop the service and disable it.

• SQL Server agent (used for scheduling jobs, SSRS subscription etc.)
• SQL server Reporting service
• SQL server Analysis Service
• SQLWriter:- SQL Volume Shadow copy service

All the above settings are based on my observation, if you feel something can be added to list, please post a comment, I will be more than happy to include this in the list.

Update(5/11/2012):- Updated script for point 7 as per comment from Dubelewskyj Oleksandr