SQL Tweaking Tips

​1.
Maximum degree of parallelism should be set to half of the number of CPU's


2.
Cost threshold for parallelism is default 5, but should be more in the area of 50


3.
Check that you have set effective Schedule indexing jobs


4.
Tempdb
It is recommended to have the same number of tempdb files as CPU's, set to same size initial size 256 MB
File grow should be set to 256MB, not 10%
One way of changing this is:
First, make sure tempdb is on in this example the F: drive
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLData\templog.ldf');
GO


Then make sure to shrink file and set to 256 MB:

USE tempdb
GO
DBCC SHRINKFILE (tempdev, EMPTYFILE);
GO


5.
Database Autogrowth
Change from 10% to 512 MB


6.
If SQL Report Server is not beeing used, it can be deinstalled – to save some Resources


7.

Server Properties - Memory - Maximum server memory (in MB):

Always leave some memory available for the OS using the following rule:

If the Server has a total of 4GB, allocate 1GB to the OS by allocating SQL Max Mem 3072MB
If the Server has a total of 8GB, allocate 2GB to the OS by allocating SQL Max Mem 6144MB
If the Server has a total of 12GB, allocate 3GB to the OS by allocating SQL Max Mem 9216MB
If the Server has a total of 16GB, allocate 4GB to the OS by allocating SQL Max Mem 12288MB
If the Server has a total of 24GB, allocate 5GB to the OS by allocating SQL Max Mem 19456MB
If the Server has a total of 32GB, allocate 6GB to the OS by allocating SQL Max Mem 26624MB