PerformanceWiki [the blog]

For an OLTP workload, start with the following advanced SQL Server 2008
tuning recommendations. Some are from Microsoft, others are just good practices. Run these as queries in the Microsoft SQL Server Management Studio UI:

USE master
EXEC sp_configure ’show advanced options’, 1
RECONFIGURE WITH OVERRIDE

(the above must be run first before any advanced tuning parameters can be set)

sp_configure ‘awe enabled’, 1
go
RECONFIGURE
go

sp_configure ‘cost threshold for parallelism’, 5
go
RECONFIGURE
go

sp_configure ‘cost threshold for parallelism’, 5
go
RECONFIGURE
go

sp_configure ‘fill factor’, 80
go
RECONFIGURE
go

sp_configure ‘index create memory’,
1000
go
RECONFIGURE
go

sp_configure ‘lightweight pooling’, 1
go
RECONFIGURE
go

sp_configure ‘locks’, 10000000 /* 10 million */
go
RECONFIGURE
go

sp_configure ‘max degree of parallelism’, 16 /* we have 16 lcpu */
go
RECONFIGURE
go

sp_configure ‘min server memory’, 1000
go
RECONFIGURE
go

sp_configure ‘max worker threads’, 704
go
RECONFIGURE
go

sp_configure ‘min memory per query’, 2048
go
RECONFIGURE
go

sp_configure ‘network packet size’, 4096 /* to match jdbc settings */
go
RECONFIGURE
go

sp_configure ‘priority boost’, 1
go
RECONFIGURE
go

sp_configure ‘user connections’, 1000
go
RECONFIGURE
go

.pw.