Wednesday, 4 March 2015

Fine Tuning SQL Server for SharePoint Configuration (Best Practices for SQL Server Configuration)

MAX Server Memory
Config Item
Do not use Default Value
Use Correct Standard
Max Server Memory
2,147,483,647 MB
(2 PB or 2048 TB. Basically means unlimited)
Non-default upper bound, based upon the total memory installed on the server.

By default the Max Server Memory for a SQL Server is set at 2,147,483,647 MB. This basically means that when it comes to memory consumption, there is no upper bound configured for SQL Server. This can lead to performance issues. There are cases where SQL Server may starve the OS of its memory needs, since it has a hard time releasing memory, once it has acquired the RAM. As a best practice, it is good to specify a non-default Max Server Memory value (generally 80% of the physical memory (RAM) available to the server)

To specify a Max Server Memory value, we can use the following code:

/* Configuring an upper bound for SQL Server memory. */
Exec sp_configure 'max server memory (MB)', <8192> -- Number to be changed, as appropriate.
Reconfigure

MAX Server Memory

Total Physical Memory (GB) on SQL server
Use Max Server Memory (MB)
8 GB
5,120 MB (or 5 GB)
16 GB
12,228 MB (or 11.94 GB approx.)
24 GB
18,432 MB (or 18 GB)
32 GB
25,600 MB (or 25 GB)
48 GB
39,936 MB (or 39 GB)
64 GB
56,320 MB (or 55 GB)
72 GB
63,488 MB (or 62 GB)
96 GB
88,064 MB (or 86 GB)
128 GB
120,832 MB (or 118 GB)
256 GB
251,904 MB (or 246 GB)
512 GB
514,048 MB (or 502 GB)
768 GB
776,192 MB (or 758 GB)
1024 GB (or 1 TB)
1,038,336 MB (or 1,014 GB)


Backup Compression Default

Config Item
Default Value
Use Standard
Backup Compression Default
0
1

If we turn this on for a SQL server and do a native SQL Server backup, we will get a compressed backup by default, as opposed to a regular uncompressed backup.
Backup performance will be better and it will take up less space on the disk, so it is a good idea to turn this on.

To implement this, we can use the following code:

/* Changing the default backup mechanism to SQL Server native compressed. */
Exec sp_configure 'backup compression default', 1
Reconfigure

Maximum Degree of Parallelism (MAXDOP)

Config Item
Do not use Default Value
Use Standard
MaxDop
0
OLTP Servers: 1

Reporting Servers: Non-zero value, e.g., 2, 4, etc., up to a max of 8.

NOTE: Most prod SQL Servers these days have >= 4 processors and the above recommendations are good for those. A default MaxDop of 0 may be ok for servers with 1 or 2 processors.

The max degree of parallelism setting governs the maximum number of processors that a particular query or statement can utilize at run time. 
In general, this value can be any number from 0 to N, where N is the number of physical processors available on the server. 

To implement this for SQL Servers with processors >= 4, we can use the following code:

/* Changing MaxDop to 1. */
Exec sp_configure 'max degree of parallelism', 1
Reconfigure

Instant Data File Initialization

Data files can be initialized instantly, meaning that while creating a new database or while acquiring additional space for the data file of a database,
the system does not have to zero out the pages before it can use them. This provides for better performance.
Config Item
Default Value
Use Standard
Instant Data File Initialization
Off
On

Tempdb database configuration

The Tempdb database is very important. It is used by the system as a temporary work area to store intermediate results of sorting operations,
intermediate results of joins, version store information in case a row versioning kind of isolation level is used, etc.
By default this database has an 8 MB data file and a 1 MB log file. We recommend multiple equi-sized data files for this database,
equal to the number of processors on the SQL Server, up to a maximum of 8 data files. As far as the log file is concerned, just like any other user database,
we should have just one pre-grown log file, with room to grow further.

Config Item
Default Value
Standard
No. of Tempdb data files
One 8 MB data file.
Configure multiple, equi-sized data-files, matching the number of processors, up to a maximum total of 8.

Lock Pages in Memory

If the account under which SQL Server is running is granted the Lock Pages in Memory privilege, then this ensures that the SQL Server’s in-use memory pages never get paged out.
This is a safe thing to do on dedicated SQL Servers, provided we specify a non-default upper bound for the “Max Server Memory” configuration option.
SQL Ipak by default grants this privilege for new SQL Server installs. We can grant this privilege by using mmc.exe and then by adding “Group Policy Object Editor” as a snap-in.
Here is the path in the UI that we would have to follow to grant the privilege:

Config Item
Default Value
Standard
Lock Pages in Memory
Off
On

Authentication Mode
SQL Server authentication authenticates the user to the database using a database user name and password. Windows authentication is also referred to as "Windows Integrated Security" or a "trusted connection" because it relies on the user being authenticated, or “trusted,” by the operating system. Our recommendation and standard is Windows authentication, since it is more secure. Do not use mixed mode authentication even for development or testing.




SQLServerAgent Properties
It is good to configure SQL ServerAgent to start automatically when SQL Server comes up and also to restart in case of an unexpected failure.
In addition to this, configuring job history is very important. By default the system will keep a total of 1000 job history rows for all jobs and up to a maximum of 100 rows per job.
In most cases, this is not enough. We recommend specifying a maximum of 100,000 rows for all jobs , with a maximum of 1000 rows per job.

Config Item
Default Value
Standard
SQLServerAgent AutoStart
0
1
JobHistory_Max_Rows
1000
100,000
JobHistory_Max_Rows_Per_Job
100
1000



Master, Model & MSDB databases configuration

Recovery model is something that the application development team needs to have given a lot of careful thought and consideration to. In general,
most SQL Servers only have a few databases (one or two in most cases), which need to be in full recovery mode.
This is something that should be explicitly done for specific databases, based upon need and not something that should be set by default.

Config Item
Default Value
Standard
Model db recovery model
Full
Simple
Master db size
Data file = 4 MB (AG 10%)
Log file = 1 MB (AG 10%)
Data file = 50 MB (AG 5 MB)
Log file = 20 MB (AG 5 MB)
Model db size
Data file = 2 MB (AG 1 MB)
Log file = 1 MB (AG 10%)
Data file = 50 MB (AG 5 MB)
Log file = 20 MB (AG 5 MB)
Msdb db size
Data file = (AG 10%)
Log file = (AG 10%)
Data file = 50 MB (AG 5 MB)
Log file = 20 MB (AG 5 MB)

No. of SQL Server ErrorLog files

By default we have 7 error log files (ErrorLog, ErrorLog.1, ErrorLog.2 through ErrorLog.6). Having more error logs is helpful while troubleshooting issues and
for performing a root cause analysis. We recommend setting this to 30, so that even for servers where the error log is cycled once a day, we might have one months’ history.

Config Item
Default Value
Standard
No. of SQL Server ErrorLog files
6
30




Static TCP ports for Named SQL Instances

Due to the SQL Slammer computer worm (http://en.wikipedia.org/wiki/SQL_slammer_(computer_worm)) from 2003, UDP port 1434 for SQL Servers at Microsoft
is supposed to be shut down. Unavailability of this port affects the SQL Browser service since it uses this port. Normally, the SQL Browser Service
provides port information for connection requests to named SQL instances.

Config Item
Default Value
Standard
TCP port that named SQL instances listen on
Dynamic
Named instances must be associated with static TCP ports. Dynamic ports for named instances are not supported because UDP port 1434 used by the SQL Browser service will be blocked.


SQL Database Backups Retention Period

The purpose of this standard is to prevent disk space from being used by old SQL database backups, which are no longer needed. Today most SQL servers have
two kinds of database backups being done. Firstly, local database backups are performed via a local SQL Server Agent job and secondly DPM backups
are performed, which are stored on a network server.

Config Item
Default Value
Standard
SQL database backups retention period
Local database backups are retained for a day and then get overwritten by the next backup.
Database backups older than 14 days should not be kept on the SQL server itself. 


No comments:

Post a Comment