SQL Server installation and initial configuration – points to consider



The journey starts with the SQL Server installation. Installing a SQL Server is key for a stabilized and healthy database system. A properly installed and configured SQL Server rarely shows any issues. So let’s get started.

Service Account recommendation:

Although a single DOMAIN\ACCOUNT can be used for the SQL Server and the SQL Agent service, it is not desirable and not recommended as both services have different execution context. Use separate domain accounts for each service.

Installation recommendation:

If a DOMAIN\ACCOUNT is used for the SQL Server service account, then

(a)   Add the DOMAIN\ACCOUNT to the local administrator group.

(b)   Log on to the intended server with this account (optional).

(c)    Run the SQL Server setup executable as “run as administrator” privileges.

(d)   Installation media can be resided in a network share or in a DVD.

Once installation is done, you need to remove the SQL Server account from the local admin group; this is recommended for security reasons. However, please note that you may still prefer the SQL Server Service account to be a part of the local admin group to avoid permission related issues.

Local policy rights to SQL Server Service Account:

SQL Server service account rights varies from edition to edition. Do not assign any local policy rights to SQL Server and SQL Agent service account in advance which are not recommended. Each edition of the SQL Server installation process will determine the required local policy rights and will be added automatically during the installation of SQL Server. To validate appropriate service rights, review the MSDN recommendations from the following link:

http://msdn.microsoft.com/en-us/library/ms143504.aspx

Windows consideration:

(a)   Network Binding order:

1.      Make sure that the domain network is the first bound network. The miss-ordering issue usually reports in SQL Server fail-over cluster installation.

2.      Usually, TCP/IP will be the most used protocol. Consider changing the order of “network protocol bindings” to make network communications faster.

(b)   Disk alignment and block size:

1.      NTFS Allocation Unit (formerly known as cluster size) must be 64K. When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb.

2.      Ensure that Volume Alignment (also known as disk, partition or sector alignment) has been set correctly. Review and align the sector to 1024KB (or 2048KB max) before formatting the disk.

3.      HBA Disk Queue Depth: In the SAN, a queue depth of 64 is generally accepted as a good starting point in the absence of any specific vendor recommendations.

(c)    Windows Power Policy option: In Windows 2008, there are three power consumption options (power plan), where “Balanced” is set to default. Make sure that the “High Performance” has been selected. To make “power plan” configuration effective, the server BIOS power management needs to be changed to “OS Control” mode.

(d)   TCP Chimney setting: Disable the TCP Chimney offload in Windows level. “TCP Chimney Offloads” functionality does not work with virtualized Windows OS (VMware and hyper-v), therefore it can be disabled permanently.

(e)   SQL Server Port: Create an appropriate inbound rule in Windows firewall for SQL Server ports (e.g. 1433, 5022) instead of disabling the firewall service.

Instant file initialization: To take advantage of the instant file initialization feature, assign the “Perform volume maintenance tasks” windows right to the SQL Server Service Account. When it is enabled, the data file (mdf and ndf, but not ldf) will be initialized instantaneously without filling any empty spaces with zeros.

Tempdb consideration: It is not necessary to create multiple tempdb data files without understanding the workload nature and concurrency in a Server. If the OLTP server experiences high concurrencies, latch contention, row versioning, a high number of sort/hash operations, then adding multiple data files will be helpful.

(a)   In a SMP based system, 8 tempdb data files can be added initially. If more data files are required, then add two tempdb data files at a time.

(b)   In a NUMA based system, add data files based on the number of NUMA nodes detected by the SQL Server Engine. For a 4-NUMA system, the tempdb might have 8 or 12 data files. If more data files are needed, then increase the tempdb data files by the number of NUMA nodes.

(c)    Make sure that all tempdb data files are equal in size.

(d)   Tempdb data files and log must be isolated and resided in a single dedicated drive. Log files of tempdb can be placed in the Log drive, but it is not necessary.

MAXDOP: The default value of MAXDOP is 0 (zero). It works best for a well written OLTP application and if the database is maintained properly. However, in some workloads, MAXDOP = 1 works best, such as SharePoint database. There is no appropriate settings for MAXDOP and it varies from server to server, and sometimes even in the same server time to time (review http://support.microsoft.com/kb/2806535). Following are some considerations:

(a)   In a SharePoint implementation, it is highly recommended by Microsoft to use MAXDOP=1.

(b)   If parallelism introduces blocking on CXPACKET, lowering the MAXDOP setting may help.

(c)    In a system, if there is an enormous amount of spinlock and latch contention occurring, lowering the MAXDOP value will help to improve the performance.

(d)   Consider increasing “cost threshold for parallelism” to fine tune the parallelism along with or instead of MAXDOP.

(e)   In case of high CAXPACKET, consider redesigning indexes, removing fragmentations and updating statistics instead of changing MAXDOP.

(f)     Please remember that creating or rebuilding an index will suffer from lower values of MAXDOP.

Transaction Log:  Following are the recommendations for the Transaction Log:

(a)   Place the transaction log in a dedicated drive.

(b)   Do not create multiple transaction logs for a single database.

(c)    Pre-size the log file well in advance; use 4G or 8GB increments per growth.

Database Integrity: Make sure that the “Page Verify” option for all databases is set to “CHECKSUM”. Running DBCC CHECKDB in production server is resource intensive and it does not help to protect the database from corruption. Instead of running DBCC CHECKDB, consider configuring SQL Server “Event Alert Service” for database fatal error (Severity from 022 to 025) and get notifications right away.

Memory allocation for SQL Server - points to consider

Memory Allocation:

When we are talking about memory allocation for SQL Server, we usually mean memory allocation for the buffer pool. The two well-known parameters - the “max server memory” and the “min server memory” - always refer to the data buffer only.

These options do not include the memory that is allocated for other components within the SQL Server process. These components include the following:

(a)    The SQL Server worker threads.

(b)   The Multipage Allocator of SQL Server Memory Manager.

(c)    Various DLLs and components that the SQL Server process loads within the address space of the SQL Server process.

(d)   The backup and restore operations.

(e)   SQL Server Agent process.

Windows 2003 vs. Windows 2008/2008R2/2012:

In Windows 2003, OS manages its memory aggressively and always tries to free up physical memory; this generally introduces “paging out” issues in SQL Server. Windows 2008 and later versions are more educated and can manage memory dynamically and non-intrusively. Therefore a SQL Server running on a Windows 2008 or later edition has better experience and needs extra consideration for configuring SQL Server memory.

Windows 32-bit vs. 64-bit:

Windows 32-bit edition can’t use more than 4GB of physical memory while 64-bit Windows has varying limitations based on the edition.

(a)    In a 32-bit Windows Server, the /PAE switch is required to access more than 4GB of physical memory if installed. Enabling AWE in SQL Server is also required.

(b)   In a 64-bit environment, /PAE and /AWE are not required to be configured.

PAE and AWE Switch (physical memory <= 4GB):

If a 32-bit environment has less than or equal to 4GB of memory, the /3GB switch can be used to allocate more memory to the SQL Server. Once a /3GB switch is used, the total virtual memory will be splitting since1GB is for kernel mode and 3GB is for user mode usages.

PAE and AWE Switch (physical memory > 4GB):

In a 32-bit Windows Server and SQL Server implementation, if more than 4GB of memory is installed then both the PAE in Windows and AWE in SQL Server need to be turned on. In a 32-bit physical environment, the following configurations must be configured should the physical memory be used beyond 4GB.

(a)    LPIM – Assign this local right to the SQL Server service account.

(b)   Max Server Memory – use a fixed amount of memory for SQL Server.

(c)    /PAE – Enable switch in Windows if it is not done yet.

(d)   /AWE – Enable this switch in SQL Server.

If this is a Virtual implementation, then the provisioned Guest Memory must be reserved in VMWare ESXi or in MS Hyper-V to prevent memory swapping (balloon driver effect).

Memory configuration in 32-bit environment:

Follow the guidelines below when configuring memory for 32-bit SQL Server in a 32-bit environment.

Physical RAM	AWE	Boot.ini	Maximum “Max Server Memory”
4 GB	0 (Disabled)	/3GB	Dynamic (default)
8 GB	1 (Enabled)	/3GB, /PAE	6.5 GB
16 GB	1 (Enabled)	/3GB, /PAE	14.5 GB
32 GB	1 (Enabled)	/PAE	29.5 GB

Max Server Memory Allocation Guideline:

Although Microsoft recommends using dynamic memory configuration in a 64-bit environment, this recommendation does not consider suboptimal SQL database design, and other executing applications and processes. So the “Max Server Memory” setting is strongly recommended to limit the buffer pool of SQL Server. Following is the guideline for configuring Max server memory in a 64-bit SQL Server. Always consider leaving some additional memory for Windows and other applications based on workload.

Physical Memory	Minimum Memory for Windows	Maximum “Max SQL Server Memory”
<16	2	Dynamic / best judgment
16	4	12
32	6	26
64	8	56
128	16	112
256	16	240
512	32	480
1TB	48	976

Max and Min Server memory:

Consider allocating an equal amount of memory to the “Min Server memory” and “Max Server Memory” option. This will eliminate internal page allocation and de-allocation overhead.

NUMA and Memory Configuration:

When SQL Server detects NUMA in a physical or virtual server, it calculates memory allocation from “Max Server Memory” and assigns an equal amount of memory to each NUMA node. In case of dynamic memory configuration, SQL Server Engine assumes an arbitrary value which may not be sufficient for the current workload or over estimation may introduce OS memory issues.

Please note that SQL Server 2000 SP4 and later editions support NUMA memory.

Lock Pages in Memory (LPIM) and SQL Server Editions:

By default, Enterprise and Developer 64-bit editions of SQL Server 2005 and later versions support Locked Pages.

(a)    The standard edition of SQL 2005, SQL 2008 and SQL 2008 R2 support Lock Pages in Memory if the startup trace flag 845 is set.

(b)   The standard edition of SQL 2012 and SQL 2014 supports Lock Pages in Memory natively and does not require a 845 startup trace flag.

(c)    All 32-bit versions support Lock Pages in Memory with /PAE and /AWE switch.

Lock Pages in Memory (LPIM):

In 64-bit Windows 2008 or later editions, LPIM is not required as Windows manages memory dynamically in a non-intrusive way. However, certain workloads may need a generous allocation of guaranteed fixed memory to operate smoothly. In this case, LPIM can be assigned explicitly to the SQL Server Service account.

Following is the guideline for LPIM:

(a)    When Windows sits on a physical Machine:

(1)    Make sure that the “Max Server Memory” has been assigned.

(2)    For standard editions of SQL Server, add startup trace flag 845.

(b)   When Windows sits on a virtual Machine:

(1)    Make sure that the “Max Server Memory” has been assigned.

(2)    For standard editions, add startup trace flag 845.

(3)    Make sure that the provisioned guest memory has reservation in VMWare or in Hyper-V.

The trace flag 845 has no effect on the SQL Server Enterprise edition.

Index Creation Memory:

The ‘index creation memory’ option is self-configuring and usually works without requiring adjustment. A larger value might be helpful to improve index creation performance but there is no specific recommended value. Please note that the run value for this parameter will not exceed the actual amount of memory that can be used for the operating system and hardware platform on which SQL Server is running. On 32-bit operating systems, the run value will be less than 3 GB.

The run value needs to be equal or greater than the “Min memory per query” when set.

Min memory per query:

The default value of 1024 KB works well for almost all cases. If some queries experience excessive sort and hash operation, and if optimizing the query and refactoring associated indexes out of scope then this value can be bumped up gradually to improve performance. If changing the setting helps then consider adding more physical memory and allocating more memory to the data buffer pool. The maximum limit is 2GB for this setting.

-g Switch (mostly applicable for SQL 2000 and 2005):

The –g switch reserves additional memory outside SQL Server’s buffer pool in the SQL Server process for extended stored procedures, executing distributed queries, loading DLLs of extended stored procedures and calling OLE automation objects from Transact-SQL. Consider using this switch if the following message is logged in the Error Log.

WARNING: Failed to reserve <n> bytes of contiguous memory

WARNING: Failed to reserve contiguous memory of Size= <allocation_size>

More reading:

http://www.vmware.com/files/pdf/perf-vsphere-memory_management.pdf

http://support.microsoft.com/kb/918483

http://blogs.technet.com/b/beatrice/archive/2008/11/26/x64-lockpages-in-memory-and-awe-should-i-or-should-i-not.aspx