Skip to main content

Resource Governance

Speediful bounds how much memory and concurrency it allows itself while loading and querying data, so that a run can't exhaust the machine's memory or overwhelm SQL Server with simultaneous bulk writes. The limits apply within a single run (one EXEC of a Speediful procedure). Runs that execute at the same time each get their own budget, so the limits do not add up across them.

Every setting starts at auto, which Speediful sizes as shown below. You can pin any setting to an explicit value instead, or clear an override to go back to auto.

SettingWhat it controlsDefault (auto)
SQL Server Co-locatedWhether SQL Server runs on this same machine. Changes how Max In-Flight Bytes is sizedDetected from the configured server name
Max In-Flight BytesTotal memory Speediful uses for data across all in-progress loads and downloads within one runSized from this machine's RAM, between 256MB and 8GB. With SQL Server on this machine: 75% of the RAM left after SQL Server's max server memory and a reserve for everything else (2GB or 10% of RAM, whichever is larger). If max server memory is unlimited or cannot be read: 10% of RAM, up to 2GB. With SQL Server on another machine: 50% of RAM
Max Concurrent SQL WritesHow many BULK INSERT operations can run at the same time within one run3
Max Concurrent DownloadsHow many Salesforce downloads (blobs, bulk job results) can run at the same time within one runMax Concurrent SQL Writes plus 2 (5 when Max Concurrent SQL Writes is auto)
Max Write Unit BytesThe memory reserved up front for each download before its real size is known, and the memory each Bulk v2 query page is sized to fit. SQL writes are charged their own estimated memory, not this valueMax In-Flight Bytes divided by the sum of Max Concurrent SQL Writes and Max Concurrent Downloads (one eighth of Max In-Flight Bytes when both are auto)

Tune for throughput​

Max In-Flight Bytes is the setting that most often limits speed. When the memory budget is low, downloads and writes wait for memory even though Max Concurrent Downloads and Max Concurrent SQL Writes would let more of them run.

The usual cause of a small budget is SQL Server's max server memory. SQL Server leaves it unlimited unless you set it, and Speediful then sizes its own budget at 10% of RAM, up to 2GB, because SQL Server can claim the rest of the machine's memory. When that happens, a run's messages include:

WARNING: SQL Server's max server memory is uncapped on a co-located install; sizing SLAM's own memory budget conservatively (10% of RAM, capped at 2GB) because SQL Server may claim the rest under pressure. Run configure_resources to set an explicit MaxInFlightBytes instead.

Every run also lists the values it used, under Resource governance settings for this run:.

Either option gives Speediful a larger memory budget. You can also do both: setting max server memory caps SQL Server, and the pinned value sets Speediful's budget.

  • Option 1: Set SQL Server's max server memory. Speediful then sizes its budget at 75% of the RAM left over, up to 8GB. The setting also caps SQL Server, which needs memory for BULK INSERT, so choose the value with your DBA (see SQL Server out of memory). This example caps SQL Server at 24GB (24576MB):

    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;

    EXEC sp_configure 'max server memory (MB)', 24576;
    RECONFIGURE;
  • Option 2: Pin Max In-Flight Bytes with the Speediful > Configure Resource Settings item in the Windows Start menu, or with configure_resources. A pinned value is not limited to 8GB. While SQL Server's max server memory is unlimited, SQL Server competes with Speediful for the memory you pin, so pin only what the machine can spare. This example pins it at 8GB:

    (admin powershell prompt)
    PS C:\WINDOWS\system32> cd "C:\Program Files\Speediful\SLAM"
    PS C:\Program Files\Speediful\SLAM> .\speediful_slam.exe configure_resources --max_in_flight_bytes "8GB"

    Speediful then prompts for the other settings. Press Enter at each one to leave it unchanged.

Max Concurrent SQL Writes and Max Concurrent Downloads cap how many writes and downloads run at once, but raising them does not give Speediful more memory. With Max Write Unit Bytes at auto, the same budget is split into more, smaller units. If SQL Server runs out of memory during BULK INSERT, lower Max Concurrent SQL Writes.

Change the settings​

Change the settings with configure_resources, from a PowerShell prompt running with administrator permissions, either interactively (recommended) or with command-line parameters. A Windows Start menu item also updates your resource settings.

Speediful prompts for each setting in turn and shows its current value (or auto if it is not overridden) in brackets. Press Enter to leave a setting unchanged, type a new value to override it, or type auto to clear an existing override and let Speediful size it itself.

(admin powershell prompt)
PS C:\WINDOWS\system32> cd "C:\Program Files\Speediful\SLAM"
PS C:\Program Files\Speediful\SLAM> .\speediful_slam.exe configure_resources
Version: x.x.x; Build: xxxxxxxxxx [xxxxxx] (xxxxxxxxxxxxx)
Process ID: 25636


**Configure Resource Governance Settings**
Press Enter to skip/use defaults shown in [brackets]
IMPORTANT: Type 'auto' for any setting to clear an override and let Speediful size it itself.

Note: This command requires administrator privileges to write to the registry.

Max In-Flight Bytes (e.g. 2GB) [auto]:
SQL Server Co-located with Speediful (true/false/auto) [auto]:
Max Write Unit Bytes (e.g. 64MB) [auto]:
Max Concurrent SQL Writes [auto]:
Max Concurrent Downloads [auto]:

Resource governance settings saved successfully!
MaxInFlightBytes: 2GB

Run show_resources to see the fully resolved settings, including any auto-sized values.

Press Enter to exit...

Command-line mode​

Pass the settings as parameters. Speediful prompts for any parameter you leave out. The backtick (`) is PowerShell's line continuation character.

(admin powershell prompt)
PS C:\WINDOWS\system32> cd "C:\Program Files\Speediful\SLAM"
PS C:\Program Files\Speediful\SLAM> .\speediful_slam.exe configure_resources `
--sql_server_colocated "false" `
--max_in_flight_bytes "2GB" `
--max_concurrent_sql_writes "3" `
--max_concurrent_downloads "5" `
--max_write_unit_bytes "64MB"

To clear an override and let Speediful auto-size a setting again, pass auto for that parameter:

PS C:\Program Files\Speediful\SLAM> .\speediful_slam.exe configure_resources `
--max_in_flight_bytes "auto"

configure_resources Command Line Parameters

NameTypeDefaultDescription
--sql_server_colocatedSTRINGautoWhether SQL Server runs on this same machine, true or false. Affects how Max In-Flight Bytes is auto-sized. Pass auto to clear an override and let Speediful re-detect it
--max_in_flight_bytesSTRINGautoMaximum memory Speediful uses for data in flight at once, e.g. 2GB. Accepts a plain byte count or a B/KB/MB/GB-suffixed size. Pass auto to clear an override
--max_concurrent_sql_writesSTRINGautoMaximum concurrent BULK INSERT writes. Pass auto to clear an override
--max_concurrent_downloadsSTRINGautoMaximum concurrent blob/result downloads. Pass auto to clear an override
--max_write_unit_bytesSTRINGautoMemory reserved up front for each download before its real size is known, and the memory each Bulk v2 query page is sized to fit, e.g. 64MB. SQL writes are charged their own estimated memory, not this value. Pass auto to size it from Max In-Flight Bytes
tip

Use SLAM_show_resources to see the fully resolved settings after saving, including which values are auto-sized and which are explicit overrides. It reports the values every run on this machine uses, including Max In-Flight Bytes sized from SQL Server's max server memory.