Skip to main content
Delphix

Registry Settings for Optimal Database Performance and Stability (KBA1251)

 

KBA

KBA#1251

Required 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"