Registry Settings for Optimal Database Performance and Stability (KBA1251)
KBA
KBA#1251Required Registry Settings for Optimal Database Performance
There are a number of Windows registry entries that must be set in order to provide stability for Delphix Virtual Databases (VDBs) and Staging databases. These settings are not necessary on Windows Servers that host source databases.
These requirements are described in our documentation at Requirements for Windows iSCSI Configuration.
The default Windows settings for these registry entries provide little tolerance to temporary service disruptions, such as network maintenance or failovers.
Failure to properly set these entries can result in a number of failures when such disruptions occur, including:
- Delays in ingesting backups for SQL Server dSources
- VDBs entering a "Stopped" or "Cannot Monitor" status, and needing to be manually restarted
- SQL Server and Windows reporting disk errors for VDBs or Staging databases
Manually updating the registry entries can be time consuming and error prone. This article contains PowerShell scripts to validate and set our required parameters for SQL Server Target hosts:
Troubleshooting
As a Windows Administrator, run the "DelphixRegistryChecker.ps1" PowerShell script to get a report on the current registry values.
When prompted for an IP address, enter this server's IP address for the network interface that is connected to the Delphix Engine.
The following example shows example output of DelphixRegistryChecker.ps1 when the Windows default settings are present:
PS C:\Temp> .\DelphixRegistryChecker.ps1 Delphix requires several registry settings be set to specific values in order for staging databases and VDBs to perform optimally and to give them operational stability. Failure to update the registry results in poor performance and can cause SQL Server to offline the databases. In addition to the registry settings, Delphix recommends enabling Receive Side Scaling (RSS): https://docs.delphix.com/docs/architecture-best-practices/architecture-best-practices-for-target-db-and-os-settings/receive-side-scaling-rss-for-windows-staging-target-and-targets This script reports the current values of the registry settings. It also prompts for an IP address to allow setting TcpAckFrequency on a desired network interface. Some network interfaces may be used for non-Delphix activity and users may wish to have a different setting. The wildcard asterisk may be entered on the following prompt (10.43.5.*) in case you want to apply the TcpAckFrequency setting to multiple network interfaces. Enter IP address to be used by Delphix: 10.0.0.4 Found IP: 10.0.0.4 NIC Registry Path: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\system\currentcontrolset\services\tcpip\parameters\interfaces\{5134238e-35ef-4729-8726-269c6d904982} Driver Provider: Microsoft Driver Description: Microsoft Hyper-V Network Adapter Driver Version: 10.0.17763.2928 Driver Date: 2006-06-21 Interface Name: Ethernet Network Link Speed: 50 Gbps Receive Link Speed: 50000000000 Transmit Link Speed: 50000000000 MTU Size: 1500 Receive Side Scaling (RSS) is enabled on the specified network adapter. ERROR: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\system\currentcontrolset\services\tcpip\parameters\interfaces\{5134238e-35ef-4729-8726-269c6d904982}\TcpAckFrequency entry not found. ERROR: TcpAckFrequency needs to be created and set to 1. iSCSI initator path: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004 ERROR: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004\Parameters\iSCSIDisableNagle entry not found. ERROR: iSCSIDisableNagle needs to be created and set to 1. ERROR: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004\Parameters\MaxRequestHoldTime: 60 ERROR: MaxRequestHoldTime needs to be set to 300. ERROR: Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\iSCSI\Discovery\MaxRequestHoldTime entry not found. ERROR: MaxRequestHoldTime needs to be created and set to 900. ERROR: Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Disk\TimeOutValue: 60 ERROR: TimeOutValue needs to be set to 900. Receive Side Scaling (RSS) is enabled in the TCP Global Parameters. Logical CPUs: 16; Physical CPUs: 8; Number of Cores: 1 WARNING: Hyperthreading: Active WARNING: RSS does not use hyper-threaded processors: WARNING: https://docs.microsoft.com/en-us/windows-hardware/drivers/network/introduction-to-receive-side-scaling
Applicable Delphix Versions
- Click here to view the versions of the Delphix engine to which this article applies
-
Major Release All Sub Releases 6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1
5.3
5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0 5.2
5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1
5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0
5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4
Resolution
If the output from the "DelphixRegistryChecker.ps1" script reports any errors, these need to be corrected.
Either manually run "regedit.exe" and correct the entries by hand in accordance with our Requirements for Windows iSCSI Configuration, or use the "DelphixRegistrySetter.ps1" script.
When prompted for an IP address, enter this server's IP address for the network interface that is connected to the Delphix Engine.
If any changes are made, the Staging/Target host will need to be rebooted before the changes will take effect.
Example output from DelphixRegistrySetter.ps1:
PS C:\Temp> .\DelphixRegistrySetter.ps1 This script sets the value of the 8 registry settings Delphix uses to provide optimal performance and stability to databases. In addition to the 8 registry settings, Delphix recommends enabling Receive Side Scaling (RSS): https://docs.delphix.com/docs/architecture-best-practices/architecture-best-practices-for-target-db-and-os-settings/receive-side-scaling-rss-for-windows-staging-target-and-targets The wildcard asterisk may be entered on the following prompt (10.43.5.*) in case you want to apply the TcpAckFrequency setting to multiple network interfaces. Enter IP address to be used by Delphix: 10.0.0.4 Backup up registry to delphix_pre_changes.reg... Found IP: 10.0.0.4 NIC Registry Path: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\system\currentcontrolset\services\tcpip\parameters\interfaces\{5134238e-35ef-4729-8726-269c6d904982} Receive Side Scaling (RSS) is enabled. TcpAckFrequency : 1 PSPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\system\currentcontrolset\services\tcpip\parameters\interfaces\{5134238e-35ef-4729-8726-269c6d904982} PSParentPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\system\currentcontrolset\services\tcpip\parameters\interfaces PSChildName : {5134238e-35ef-4729-8726-269c6d904982} PSProvider : Microsoft.PowerShell.Core\Registry Added Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\system\currentcontrolset\services\tcpip\parameters\interfaces\{5134238e-35ef-4729-8726-269c6d904982}\TcpAckFrequency: 1 iSCSI initator path: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004 iSCSIDisableNagle : 1 PSPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004\Parameters PSParentPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004 PSChildName : Parameters PSProvider : Microsoft.PowerShell.Core\Registry Added Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004\Parameters\iSCSIDisableNagle: 1 Updated Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004\Parameters\MaxRequestHoldTime: 300 MaxRequestHoldTime : 900 PSPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\iSCSI\Discovery PSParentPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\iSCSI PSChildName : Discovery PSProvider : Microsoft.PowerShell.Core\Registry Added Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\iSCSI\Discovery\MaxRequestHoldTime: 900 Updated Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Disk\TimeOutValue: 900 Receive Side Scaling (RSS) is enabled. WARNING: The server must be rebooted in order for the changes to take effect.
Once these parameters have been set correctly, DelphixRegistryChecker.ps1 should report that no changes are required:
PS C:\Temp> .\DelphixRegistryChecker.ps1 Delphix requires several registry settings be set to specific values in order for staging databases and VDBs to perform optimally and to give them operational stability. Failure to update the registry results in poor performance and can cause SQL Server to offline the databases. In addition to the registry settings, Delphix recommends enabling Receive Side Scaling (RSS): https://docs.delphix.com/docs/architecture-best-practices/architecture-best-practices-for-target-db-and-os-settings/receive-side-scaling-rss-for-windows-staging-target-and-targets This script reports the current values of the registry settings. It also prompts for an IP address to allow setting TcpAckFrequency on a desired network interface. Some network interfaces may be used for non-Delphix activity and users may wish to have a different setting. The wildcard asterisk may be entered on the following prompt (10.43.5.*) in case you want to apply the TcpAckFrequency setting to multiple network interfaces. Enter IP address to be used by Delphix: 10.0.0.4 Found IP: 10.0.0.4 NIC Registry Path: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\system\currentcontrolset\services\tcpip\parameters\interfaces\{5134238e-35ef-4729-8726-269c6d904982} Driver Provider: Microsoft Driver Description: Microsoft Hyper-V Network Adapter Driver Version: 10.0.17763.2928 Driver Date: 2006-06-21 Interface Name: Ethernet Network Link Speed: 50 Gbps Receive Link Speed: 50000000000 Transmit Link Speed: 50000000000 MTU Size: 1500 Receive Side Scaling (RSS) is enabled on the specified network adapter. CORRECT: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\system\currentcontrolset\services\tcpip\parameters\interfaces\{5134238e-35ef-4729-8726-269c6d904982}\TcpAckFrequency: 1 iSCSI initator path: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004 CORRECT: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004\Parameters\iSCSIDisableNagle: 1 CORRECT: Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}\0004\Parameters\MaxRequestHoldTime: 300 CORRECT: Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\iSCSI\Discovery\MaxRequestHoldTime: 900 CORRECT: Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Disk\TimeOutValue: 900 Receive Side Scaling (RSS) is enabled in the TCP Global Parameters. Logical CPUs: 16; Physical CPUs: 8; Number of Cores: 1 WARNING: Hyperthreading: Active WARNING: RSS does not use hyper-threaded processors: WARNING: https://docs.microsoft.com/en-us/windows-hardware/drivers/network/introduction-to-receive-side-scaling
Additional Information
Microsoft recommends enabling vRSS on Windows VMs hosted on Microsoft Hyper-V hypervisors. Currently the scripts do not attempt to check or enable vRSS. They do raise a message if they detect that the VM is running on a Hyper-V hypervisor. More information can be found in the Microsoft documentation titled "Virtual Receive Side Scaling (vRSS)".
The script also attempts to alert users when a host has hyper-threading enabled because RSS does not use hyper-threaded processors. For more information, please see "Introduction to Receive Side Scaling"
Related Articles
External Links: