Skip to main content
SQL Query Stress supports parameterized queries where parameter values are dynamically retrieved from a database query. This allows you to stress-test queries with realistic, varying data values.

​
How Query Parameters Work

The parameter system consists of three components:
  1. ParamQuery - A SQL query that returns parameter values
  2. ParamMappings - Maps parameter names to columns from the query results
  3. ParamServer - Internal system that cycles through parameter values during execution

​
Parameter Flow

  1. Before the load test starts, ParamQuery executes and results are stored in a DataTable
  2. Parameter names from ParamMappings are mapped to columns in the result set
  3. During execution, each thread retrieves parameter values by calling ParamServer.GetNextRow_Values()
  4. Values are cycled through using modulo arithmetic: rowNum % totalRows
  5. When all rows are consumed, the system loops back to the first row

​
Configuration Properties

​
ParamQuery
string
default:""
SQL query to execute for retrieving parameter values. Results should return columns that match your parameter mappings.
SELECT CustomerID, OrderDate, ProductID FROM Orders
​
ParamMappings
Dictionary<string, string>
Maps SQL parameter names to column names from the ParamQuery results.
  • Key: Parameter name (including @ prefix)
  • Value: Column name from ParamQuery result set
"ParamMappings": {
  "@CustomerID": "CustomerID",
  "@OrderDate": "OrderDate",
  "@ProductID": "ProductID"
}
​
ParamDbConnectionInfo
ConnectionInfo
Connection information for the database where ParamQuery will execute.Can be the same as or different from MainDbConnectionInfo.
​
ShareDbSettings
bool
default:"true"
When true, ParamQuery uses the same connection as MainQuery.Set to false if parameter values should be retrieved from a different database.

​
Parameter Initialization

From LoadEngine.cs:244-271, the ParamServer.Initialize() method: 
public static void Initialize(string paramQuery, string connString, 
    Dictionary<string, string> paramMappings)
{
    using var sqlDataAdapter = new SqlDataAdapter(paramQuery, connString);
    _theParams = new DataTable();
    sqlDataAdapter.Fill(_theParams);

    _numRows = _theParams.Rows.Count;

    _outputParams = new SqlParameter[paramMappings.Keys.Count];
    _paramDtMappings = new int[paramMappings.Keys.Count];

    var i = 0;
    foreach (var parameterName in paramMappings.Keys)
    {
        _outputParams[i] = new SqlParameter { ParameterName = parameterName };
        var paramColumn = paramMappings[parameterName];

        if (paramColumn != null)
            _paramDtMappings[i] = _theParams.Columns[paramColumn].Ordinal;

        i++;
    }
}

​
Parameter Value Cycling

From LoadEngine.cs:221-230, values are retrieved and cycled: 
public static void GetNextRow_Values(SqlParameterCollection newParam)
{
    var rowNum = Interlocked.Increment(ref _currentRow);
    var dr = _theParams.Rows[rowNum % _numRows];

    for (var i = 0; i < _outputParams.Length; i++)
    {
        newParam[i].Value = dr[_paramDtMappings[i]];
    }
}

​
Key Behaviors

  • Thread-safe: Uses Interlocked.Increment for atomic row counter increment
  • Automatic cycling: Modulo operator wraps back to first row when all rows consumed
  • Per-iteration: New parameter values are retrieved for each query iteration (LoadEngine.cs:408-411)

​
Complete Examples

​
Basic Parameterized Query

{
  "MainQuery": "SELECT * FROM Orders WHERE CustomerID = @CustomerID",
  "ParamQuery": "SELECT DISTINCT CustomerID FROM Customers",
  "ParamMappings": {
    "@CustomerID": "CustomerID"
  },
  "ShareDbSettings": true,
  "NumThreads": 5,
  "NumIterations": 100
}
Behavior: Each of 5 threads executes 100 iterations. Parameter values cycle through all 91 customers, repeating when exhausted.

​
Multiple Parameters

{
  "MainQuery": "EXEC GetOrderDetails @OrderID, @StartDate, @EndDate",
  "ParamQuery": "SELECT OrderID, OrderDate, DATEADD(day, 30, OrderDate) AS EndDate FROM Orders",
  "ParamMappings": {
    "@OrderID": "OrderID",
    "@StartDate": "OrderDate",
    "@EndDate": "EndDate"
  },
  "ShareDbSettings": true,
  "NumThreads": 10,
  "NumIterations": 1000
}

​
Separate Parameter Database

{
  "MainQuery": "SELECT * FROM Products WHERE ProductID = @ProductID",
  "ParamQuery": "SELECT ProductID FROM TestData.dbo.ProductSample",
  "ParamMappings": {
    "@ProductID": "ProductID"
  },
  "ShareDbSettings": false,
  "MainDbConnectionInfo": {
    "Server": ".\\SQLEXPRESS",
    "Database": "Production",
    "IntegratedAuth": true
  },
  "ParamDbConnectionInfo": {
    "Server": ".\\SQLEXPRESS",
    "Database": "TestData",
    "IntegratedAuth": true
  },
  "NumThreads": 3,
  "NumIterations": 50
}

​
Complex Parameter Query with Calculations

{
  "MainQuery": "EXEC AnalyzeCustomerOrders @CustomerID, @MinTotal, @MaxTotal",
  "ParamQuery": "SELECT CustomerID, MinTotal, MaxTotal FROM ( SELECT c.CustomerID, MIN(o.OrderTotal) AS MinTotal, MAX(o.OrderTotal) AS MaxTotal FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID HAVING COUNT(*) > 5 ) AS CustomerStats",
  "ParamMappings": {
    "@CustomerID": "CustomerID",
    "@MinTotal": "MinTotal",
    "@MaxTotal": "MaxTotal"
  },
  "ShareDbSettings": true,
  "NumThreads": 8,
  "NumIterations": 500
}

​
Parameter Validation

From LoadEngine.cs:90-102, empty parameter mappings are automatically removed: 
var badParams = new List<string>();
foreach (var theKey in _paramMappings.Keys)
{
    if ((_paramMappings[theKey] == null) || (_paramMappings[theKey].Length == 0))
    {
        badParams.Add(theKey);
    }
}

foreach (var theKey in badParams)
{
    _paramMappings.Remove(theKey);
}

​
Parameter Requirements

  • Parameter names must include the @ prefix
  • Column names in ParamMappings must exactly match columns returned by ParamQuery
  • ParamQuery must return at least one row
  • If ParamMappings is empty or all mappings are removed, no parameters are used

​
When Parameters Are Applied

From LoadEngine.cs:408-411: 
if (_queryComm.Parameters.Count > 0)
{
    ParamServer.GetNextRow_Values(_queryComm.Parameters);
}
Parameters are:
  • Added to the SqlCommand once during initialization (LoadEngine.cs:134-137)
  • Values are updated before each query execution within each iteration
  • Cloned for each thread to avoid conflicts

​
Performance Considerations

  1. Parameter Query Execution: Runs once at test start, results cached in memory
  2. Memory Usage: All parameter values stored in DataTable for duration of test
  3. Thread Safety: Interlocked.Increment ensures thread-safe row counter without locks
  4. Cycling Overhead: Minimal - simple modulo operation and array lookup
For best performance:
  • Keep ParamQuery result set reasonably sized (< 100,000 rows)
  • Ensure ParamQuery executes quickly (< 1 second)
  • Use indexed columns in ParamQuery for faster retrieval

​
Sample Configuration from Source

From sample.json: 
{
  "MainQuery": "SELECT * FROM Customers",
  "ParamQuery": "",
  "ParamMappings": [],
  "ShareDbSettings": true,
  "NumThreads": 1,
  "NumIterations": 10
}
This configuration runs a simple query without parameters. To add parameters: 
{
  "MainQuery": "SELECT * FROM Customers WHERE CustomerID = @CustomerID",
  "ParamQuery": "SELECT CustomerID FROM Customers",
  "ParamMappings": {
    "@CustomerID": "CustomerID"
  },
  "ShareDbSettings": true,
  "NumThreads": 1,
  "NumIterations": 10
}

Build docs developers (and LLMs) love

Get started for freeTalk to us