The purpose of this article is to set basic guidelines and standards for setting up SQL Servers. Different SQL Server installations may have different configuration requirements depending on how those servers might be used, however, there are some basic configurations that the SQL Servers should be built with and then tweaked further as needed.
Max Server Memory
| Config Item | Default Value | Standard |
| Max Server Memory | 2,147,483,647 MB
(2 PB or 2048 TB or, unlimited) |
Non-default max value, based on 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 (2 PB or 2048 TB or, unlimited). This means that the max memory is not capped and there can be cases where SQL Server may starve the OS of its memory needs, because “by-design” SQL server will not release the memory, which it has already acquired and this can in turn result into overall slowness of the server and other performance issues.
To avoid such a scenario, a non-default Max Server Memory value must be specified however, we must be cautious and must leave around 10% of the available RAM for the operating system, with a minimum of 4 GB and a maximum of 32 GB.
Please refer to the following table for examples:
| Total Physical Memory | Memory Reserved for OS | Max Server Memory |
| 8 GB | 4 GB (4,096 MB) | 4 GB (4,096 MB) |
| 16 GB | 4 GB (4,096 MB) | 12 GB (12,288 MB) |
| 24 GB | 5 GB (5,120 MB) | 19 GB (19,456 MB) |
| 32 GB | 7 GB (7,168 MB) | 25 MB (25,600 MB) |
| 48 GB | 10 GB (10,240 MB) | 38 GB (38,912 MB) |
| 64 GB | 13 GB (13,312 MB) | 51 GB (52,224 MB) |
| 72 GB | 15 GB (15,360 MB) | 57 GB (58,368 MB) |
| 96 GB | 20 GB (20,480 MB) | 76 GB (77,824 MB) |
| 128 GB | 26 GB (26,624 MB) | 102 GB (104,448 MB) |
| 256 GB | 32 GB (32,768 MB) | 224 GB (229,376 MB) |
| 512 GB | 32 GB (32,768 MB) | 480 GB (491,520 MB) |
| 768 GB | 32 GB (32,768 MB) | 736 GB (753,664 MB) |
| 1024 GB | 32 GB (32,768 MB) | 992 GB (1,015,808 MB) |
The following t-sql code can be used to specify a Max Server Memory value:
/* Set Max Server memory SQL Server instance. */ Exec sp_configure 'max server memory (MB)', <8192> -- Number to be changed, as appropriate. go Reconfigure go
Specifying a non-default value for Min Server Memory (default = 0) is not important however, it is advised to allocate at least 1 GB ram to SQL server during startup.
In case of servers with multiple named SQL instances running, Windows clusters with multiple clustered SQL instances, or in AlwaysON configuration, careful thought should be given while distributing memory between the SQL instances. We should always leave enough memory for OS and other processes, so that all SQL instances can operate smoothly. SQL clusters or, AlwaysON must be designed and configured in a manner, that if all SQL instances which are part of the cluster/ availability groups end up failing over to the same node, the system should be able to handle this situation gracefully.
Tempdb database configuration:
Number of tempdb data files:
| Config Item | Default Value | Standard |
| No. of Tempdb data files | One 8 MB data file. | Systems <= 8 processors:
Multiple, equal-sized data-files, configured with the same autogrow increment in MB, matching the number of processors. Systems > 8 processors: Always start with 8 equal sized data files with same autogrow option. In case, there is still contention, increase data files in groups of 4 however do not exceed the number of processors. |
Tempdb is a special database which is re-created every time SQL server is restarted. It is used by the system as a temporary space to store intermediate results of joins, intermediate results of sorting operations, 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. Various SQL gurus and MSDN articles have recommended multiple equal sized data files for this database. This database must be placed on its dedicated drive. SQL Server 2016 onwards, Tempdb can be configured during the installation itself however, it is important to determine the correct values prior to the installation.
Please refer to the following links for more information:
Optimizing Tempdb Performance (Support Article)
Paul Randall’s blogs about Tempdb
Tempdb traceflag:
Traceflag –T1118 should be turned on for Tempdb to reduce Tempdb contention in allocation pages however, it is only applicable till SQL server 2014. SQL server 2016 onwards, this has been replaced by MIXED_PAGE_ALLOCATION and the behavior enabled by the trace flag is set by default, so the trace flag is not required in SQL server 2016 and has no effect.
| Config Item | Default Value | Standard |
| Traceflag –T1118 | Off | On |
Please refer to the following links for more information:
- Misconceptions and the need to enable Trace Flag 1118 (Paul Randall)
- Explaining the behavior of Trace flags 1118 and 1117 (Rajendra Gupta – MSSQL Tips)
- Recommendations to reduce allocation contention in SQL Server Tempdb database (MSDN Article)
- Article by MSSQL Tiger Team @ Microsoft (MSDN Article)
t-sql code (SQL server 2016):
ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION {ON | OFF}
t-sql code (till SQL server 2014):
DBCC TRACEON (<Trace#>)
Instant data file initialization:
| Config Item | Default Value | Standard |
| Instant Data File Initialization | Off | On |
To avoid additional over head for SQL server while growing a data file or, while creating a new database, data files should be initialized instantly, which essentially means during a new file creation or, autogrow operation, the system will not have zero-initialized the new space before allowing it to be used.
This option must be enabled for every SQL server instance. Please note that this option is applicable only to database data files and not log files. To enable this, the SQL Server service account must be granted the Perform Volume Maintenance Tasks privilege. Members of the Windows Administrators automatically have this privilege.
To enable this option, Goto Runà secpol.msc –> Local Policies –> User Rights Assignment –> “Perform volume maintenance tasks and then add the SQL server service account.
Please refer to the below screen shot for details.
Please refer to the below links for more details:
Blitz Result: Instant File Initialization (Brent Ozar)
Database IFI (MSDN Article)
Lock pages in memory:
| Config Item | Default Value | Standard |
| Lock Pages in Memory | Off | On |
Granting “Lock Pages in Memory” to SQL server service account ensures that the account can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. This setting must be enabled in conjunction with a non-default value for the “Max Server Memory” configuration setting. Do not enable this option while leaving the Max server memory set to unlimited. To enable this option, Goto Runà secpol.msc –> Local Policies –> User Rights Assignment –> “Lock pages in Memory” and then add the SQL server service account.
Please refer to the below screen shot for details.
Enable LPIM Option (Windows) (MSDN Article)
Glenn Berry’s SQL Server Performance
Reduce paging of buffer pool memory (MSDN Article)
Great SQL server debates-LPIM
SQLServer Agent Properties:
| Item | Default Value | Standard Value |
| SQLServerAgent AutoStart | 0 | 1 |
| SQLServerAgent Restart | 0 | 1 |
| JobHistory_Max_Rows | 1000 | 2000 |
| JobHistory_Max_Rows_Per_Job | 100 | 200 |
- SQLServerAgent service should be configured to start automatically along with SQL server service and restart in case of unexpected failure.
- 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. We normally change it to 2000 rows for all jobs, with a maximum of 200 rows per job. Please refer to the below example to set the above configurations:
The t-sql to set the same properties is:
Exec msdb.dbo.sp_set_SQLagent_properties @auto_start = 1, @SQLserver_restart = 1, @monitor_autostart = 1, @jobhistory_max_rows = 2000, @jobhistory_max_rows_per_job = 200
SQL Server Errorlog:
| Item | Default Value | Standard Value |
| No. of SQL Server ErrorLog files | 7 | 10 |
SQL server is configured to have 7 error log files however; it is recommended to increase the number of files to troubleshoot issues and to get assistance while performing a RCA of an issue. Please note that the value must be between 6 and 99. To change this setting, Login to SSMS –> Management –> SQL Server Logs and then right click on SQL Server Logs and choose “Configure”:
SPN (Service Principal Name):
| Item | Default Value | Standard |
| SPN for SQL Servers running under domain accounts | Not created (because domain accounts lack permissions to create & register SPNs, by default) | All SQL servers running under a domain account must have an SPN created and registered in Active Directory, for the domain account under which the MsSQLserver service is running. |
On servers without an SPN, you might encounter an error similar to the below while making an attempt to connect to SQL server:
“SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.”
Per Microsoft Article 677949, “A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. This allows a client application to request that the service authenticate an account even if the client does not have the account name.”
In simpler words, SPN is to uniquely identify a service on a given computer/ server using Kerberos. It essentially is a combination of (Type of service + computer name:port number + name of service).
All SQL servers running under a domain account must have an SPN created and registered in Active Directory, for the domain account under which the MSSQLserver service is running.
Check if a SPN is set for the service in question on for the related computer:
Setspn -L <HOSTNAME>
Setspn -L <SQL Server Service Account>
If no SPN is found, use the command with “-s” switch. “Setspn -s”. this will register a SPN if a duplicate SPN does not exist. If a duplicate SPN exists, you can use the “-r” switch to re-register or, “-d” and “-s” one by one to delete the existing SPN and create a new one.
For more information about SPNs see the following links:
Service Principal Name (MSDN Article)
Registering a Service Principal Name (MSDN Article)
How to use SPNs when you configure Web applications (MSDN Article)
SETSPN syntax (MSDN Article)
SETSPN
SQL Server Data Drives:
It is always a good idea to keep database files on a drive other than the one reserved for O.S. Your log and data should be on separate drives and tempdb on its own dedicated drive.
Database Statistics:
AutoCreateStatistics and AutoUpdateStatistics database options must be kept on, so that table and index statistics are kept up to date to aid the SQL optimizer in providing the best possible query performance.
These options might differ for SharePoint and BizTalk servers. Please check with your application team before setting these options.
Please refer to the following links for more information:
Statistics (MSDN Link)
Optimizing SQLServer for SharePoint
Why auto create statistics and auto update statistics option are disabled for Biztalk
Autoshrink and Autoclose Database:
These options should be given thoughtful consideration as while they can result into unwanted downtime, they might be useful in cloud hosted environments to save on resource utilization.
Autogrow and autoshrink settings considerations
Why to turn autoshrink off
Autoclose should be off
Datafile autogrowth:
| Item | Default Value | Standard Value |
| Database files autogrow setting | 1 MB for data files and 10% for log files. | Database data and log file growth must be configured to grow in MB and not in percentage. |
By default, database data and log files are configured to autogrow in percentage which is not appropriate for large databases. DBAs must identify an appropriate value for the auto-growth based on the size of the database.
Database maintenance:
I am a huge fan of Ola Hallengren. Setting up appropriate and standard maintenance plans can be a challenging task. Fortunately, Ola has done all the hard work. Please refer to the below links for the award-winning maintenance solution from him.
Ola’s Backup Solution
Ola’s Integrity Check solution
Index and Statistics Maintenance
IT-Noesis 