Recommendations for RIMSS SQL Server Database Servers
CPU minimum Requirements:
Intel Xeon Silver 4114, 10C/20T or greater (processor(s) should have 8 cores/16 threads minimum)
Memory minimum Requirements:
32GB RAM for less than 20 users. 64GB RAM if greater than 20 users. Greater than 75 users, please contact RIMSS Sales.
Drives minimum Requirements:
C drive – OS, ISS and user attachments to database – 300GB – 600GB 15k SAS or SSD 30 class or greater drives, in a RAID 1 configuration
D drive – SQL data – 300GB SAS 15k or SSD 30 class or greater drives, in a RAID 5 configuration
E drive – TempDB – 120 GB SSD, in a RAID 1 configuration
For smaller 5 – 15 user systems, a single RAID 10 configuration can be explored. Please discuss with a RIMSS representative.
Software:
Windows Server 2019 Standard or greater; Windows 2022 is compatible.
SQL Server 2019 Standard or greater
The server must not be configured as the Domain Controller, nor should it used for other uses besides use for the RIMSS WinNetStar Database. We also suggest that the server is not joined to a domain until after RIMSS has configured the server for production use.
Anti Virus Configuration:
When you configure your antivirus software settings, make sure that you exclude the following files or directories (as applicable) from virus scanning. This improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them.
• SQL Server data files
These files usually have one of the following file name extensions:
• .mdf
• .ldf
• .ndf
• SQL Server backup files
These files frequently have one of the following file name extensions:
• .bak
• .trn
• Trace files
These files usually have the .trc file name extension. These files can be generated either when you configure profiler tracing manually or when you enable C2 auditing for the server.
• SQL query files
These files typically have the .sql file name extension and contain Transact-SQL statements.
• DBCC CHECKDB files
• Files will be of the format <Database_data_filename.extension>_MSSQL_DBCC.
• These are temporary files.
Processes to exclude from virus scanning
• %ProgramFiles%\Microsoft SQL Server<Instance_ID>.\MSSQL\Binn\SQLServr.exe
• %ProgramFiles%\Microsoft SQL Server<Instance_ID>.\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
• %ProgramFiles%\Microsoft SQL Server<Instance_ID>.<Instance Name>
\OLAP\Bin\MSMDSrv.exe
VM Ware Configuration – SQL Server Best Practices https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf
Power Management:
Previous versions of ESXi default to “high performance” power schemes, vSphere 5.0 and later defaults to a “balanced” power scheme. For critical applications, such as SQL Server, the default power scheme in vSphere 6.0 is not recommended.
• Configure power schemes to “high performance”
DBA will also configure the Power Options in the Windows OS Control Panel to “High Performance”
CPU Configuration:
On a physical SQL Server that RIMMS recommends having 8 physical CPU cores, avoid allocating more than 8 virtual vCPUs for the VMs on that vSphere host.
Hyper-threading is an Intel technology that exposes two hardware contexts (threads) from a single physical core, also referred to as logical CPUs. This is not the same as having twice the number of CPUs or cores. By keeping the processor pipeline busier and allowing the hypervisor to have more CPU scheduling opportunities, Hyper-threading generally improves the overall host throughput anywhere from 10 to 30 percent. VMware recommends enabling Hyper-threading in the BIOS/UEFI so that ESXi can take advantage of this technology.
Numa Consideration: For small VMs running SQL Server, allocate vCPUs equal to or less than the number of cores in each physical NUMA node. When you do this, the guest operating system or SQL Server does not need to consider NUMA because ESXi makes sure memory accesses are as local as possible.
For wide SQL Server virtual machines, where the number of allocated vCPUs is greater than the number of cores in the NUMA node, ESXi divides the CPU and memory of the VM into two or more virtual NUMA nodes and places each vNUMA on a different physical NUMA node. The vNUMA topology is exposed to the guest OS and SQL Server to take advantage of memory locality.
CPU hot plug is a feature that enables the VM administrator to add CPUs to the VM without having to power it off. This allows adding CPU resources “on the fly” with no disruption to service. When CPU hot plug is enabled on a VM, the vNUMA capability is disabled. This means that SQL Server inside the VM cannot utilize vNUMA and will have degraded performance because the NUMA architecture exceeds that of the underlying physical server.
Therefore, VMware recommends to not enable CPU hot plug by default.
Memory Configuration:
One of the most critical system resources for SQL Server is memory. Lack of memory resources for the SQL Server database engine will induce Windows Server to page memory to disk, resulting in increased disk I/O activities, which are considerably slower than accessing memory. When a SQL Server deployment is virtualized, the hypervisor performs virtual memory management without the knowledge of the guest OS and without interfering with the guest operating system’s own memory management subsystem.
If the VM is sized larger than the NUMA node memory size, NUMA can be exposed to the underlying Windows Server guest OS with vNUMA allowing SQL Server to take advantage of NUMA.
When achieving adequate performance is the primary goal, we recommend setting the memory reservation equal to the provisioned memory. This will eliminate the possibility of ballooning or swapping from happening and will guarantee that the VM gets only physical memory.
The maximum amount of memory that the DBA will configure for a typical server with 32G of memory will be 20G. No minimum setting is configured. This allows for the Operating System to have 12G of memory for other applications to run. On a physical machine this is a proven configuration so it is expected to run equally well on the virtual machine.
Balloon Driver:
When designing SQL Server for performance, the goal is to eliminate any chance of paging from happening. Disable the ability for the hypervisor to reclaim memory from the guest OS by setting the memory reservation of the VM to the size of the provisioned memory. The recommendation is to leave the balloon driver installed for corner cases where it might be needed to prevent loss of service.
Note: Ballooning is sometimes confused with Microsoft’s Hyper-V dynamic memory feature. The two are not the same and Microsoft recommendations to disable dynamic memory for SQL Server deployments do not apply for the VMware balloon driver.
Storage:
Storage configuration is critical to any successful database deployment, especially in virtual environments where you might consolidate multiple SQL Server VMs on a single ESXi host. Your storage subsystem must provide sufficient I/O throughput as well as storage capacity to accommodate the cumulative needs of all VMs running on your ESXi hosts.
When virtualizing SQL Server on a SAN using vSphere Virtual Volumes as the underlying technology, the best practices and guidelines remain the same as when using a VMFS datastore. Make sure that the physical storage on which the VM’s virtual disks reside can accommodate the requirements of the SQL Server implementation with regard to RAID, I/O, latency, queue depth, and so on.
Most SQL Server performance issues in virtual environments can be traced to improper storage configuration. SQL Server workloads are generally I/O heavy, and a misconfigured storage subsystem can increase I/O latency and significantly degrade performance of SQL Server.
Aligning file system partitions is a well-known storage best practice for database workloads. Partition alignment on both physical machines and VMFS partitions prevents performance I/O degradation caused by unaligned I/O.
Additional Settings:
In addition to the previously mentioned vSphere best practices for SQL Server, there are configurations that can be made on the SQL Server and Windows Server side to optimize the its performance. Many of these settings are described by Microsoft and generally, none of our recommendations contradict Microsoft recommendations, but the following are important to mention for a vSphere virtualized environment.
RSS:
Enable RSS (Receive Side Scaling) – This network driver configuration within Windows Server enables distribution of the kernel-mode network processing load across multiple CPUs. Enabling RSS is done in the following two places:
• Enable RSS in the windows kernel by running the netsh interface tcp set global rss=enabled command in elevated command prompt. You can verify that RSS is enabled by running the Netsh int tcp show global command.
• Enable RSS on the VMXNET network adapter driver. In Windows in Network adapters, rightclick the VMXNET network adapter and click Properties. On the Advanced tab, enable the setting Receive-side scaling.
SQL Server Memory configuration: (Configured on both physical and virtual)
SQL Server can dynamically adjust memory consumption based on workloads. SQL Server maximum server memory and minimum server memory configuration settings allow you to define the range of memory for the SQL Server process in use. As previously documented, the minimum server memory for SQL Server will remain at default and the maximum memory for a 32G server memory configuration will be configured to 20G configured by the RIMSS DBA.
Lock Pages in Memory:
(Configured on both physical and virtual – note if a global security policy is implemented; this setting must be configured in the global security policy instead of the local security policy)
Granting the Lock Pages in Memory user right to the SQL Server service account prevents SQL Server buffer pool pages from paging out by Windows Server. This setting is useful and has a positive performance impact because it prevents Windows Server from paging a significant amount of buffer pool memory out of the process, which enables SQL Server to manage the reduction of its own working set. Any time Lock Pages in Memory is used, because SQL Server memory is locked and cannot be paged out by Windows Server, you might experience negative impacts if the vSphere balloon driver is trying to reclaim memory from the VM. If you set the SQL Server Lock Pages in Memory user right, also set the VM’s reservations to match the amount of memory you set in the VM configuration.
Set the Lock Pages in Memory user right and setting VM memory reservations to improve the performance and stability of your SQL Server running vSphere. Setting VM memory reservations eliminates contention for the VM on memory resources. This prevents the balloon driver from inflating into the SQL Server VM’s memory space.
CXPACKET – Parellel Queries: (configured on both physical and virtual)
This is a SQL Server wait statistic that is commonly seen with the RIMSS application and on virtual environments seems to be on a higher level.
When a query runs on MS SQL Server using a parallel plan, the query job is divided to multiple packets and processed by multiple cores. The time the system waits for the query to finish is calculated as CXPACKET.
The SQL Server maximum degree of parallelism, is an advanced configuration option that controls the number of processors used to execute a query in a parallel plan. The RIMSS DBA does configure this setting. With a physical server with 8 cores and 16 Threads for the CPU are installed the DBA will configure this setting to 8; leaving the 16 threads to assist the OS with the operations. In most all cases, all 8 cores are not used for processing. SQL Server will automatically calculate how many processors (cores) it will require to process the query. In order for SQL Server to know when it will need to calculate this value in order to run the query in parallel, it uses the Cost Threshold for Parallelism setting. The default setting is also modified by the DBA. The setting will typically be configured to 150 for the RIMSS applications, thus eliminating all of the queries from running in parallel. By using this value only the queries that have this high cost will run in parallel thus using the multiple cores to process.