It is important to setup your windows server correctly for optimum SQL Server performance. The following outlines the most important setup steps to get right.
- Format Disks with NTFS allocation unit size of 64K
- Use VMDK Paravirtual or RDM disks
- Set Max Degrees Of Parallelism to # Cores
- Set Max Server Memory 90% of system
- Use 8 Tempdb files
- Enable High Performance Power Plan
- Place Page File on Separate disk
- Install Latest Patches
Disks
Format Disks with NTFS allocation unit size of 64K
The unit of data storage in SQL Server is a 8kb page, and disk I/O operations are performed at the page level – so SQL Server always reads or writes whole data pages.
Pages are organized in Extents – a collection of eight physically contiguous pages, so one Extent is 8 x 8kb – the reasoning behind the 64K allocation unit size.
On server 2012 format your data drives with the /L flag (Use large size file records).
Use VMDK Paravirtual or RDM disks
On ESXi 5.5 always use ParaVirtual SCSI adapters and create disks as “Eager Zero Thick”.
On ESXi 6.0 and higher use RDM disks
SQL Server Settings
Set Max Degrees Of Parallelism to # Cores
Set this to the number of cores per socket. SQL serer 2019 will set this automatically during install.
Starting with SQL Server 2016 (13.x), during service startup if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default.
On older versions, or to setup manually run the following script
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO
Set Max Server Memory 90% of system
Set your SQL Server memory to 80 – 90% of total system memory.
Use 8 Tempdb files
Use 8 tempdb (mdf) files as a starting point. Only one tempdb log file (ldf) is needed.
Increase this if PFS (Page Free Space)/GAM (Global Allocation Map)/SGAM (Shared Global Allocation Map) allocation contention is observed.
Windows settings
Enable High Performance Power Plan
Ensure your power plan is set to High performance, and not the windows default – balanced.
Place Page File on Separate disk
Place your page file on a disk partition not used by your database or tempdb files.
Install Latest Patches
And finally – always ensure you have the latest windows and sql patches installed.