Query Parameterization

Parameterization allows you to test queries with different input values on each iteration, simulating realistic workloads where queries use varying parameters. This feature is powered by the ParamServer class in LoadEngine.cs.

Overview

The parameterization system works by:

Executing a parameter query once to get a result set of values
Mapping columns from that result set to parameters in your main query
Cycling through parameter values for each iteration
Each thread gets the next row in sequence (thread-safe)

When to Use Parameterization

Use parameterization when:

Testing queries that filter by different IDs or values
Simulating real-world usage patterns with varying inputs
Testing parameter sniffing and plan reuse behavior
Measuring performance across different data distributions
Avoiding cached query results that would skew metrics

How It Works

Main Query with Parameters

Your main query includes T-SQL parameters:

SELECT *
FROM Orders
WHERE CustomerId = @CustomerId
  AND OrderDate >= @StartDate

Parameter Query

A separate query returns the parameter values:

SELECT TOP 1000
    CustomerId,
    DATEADD(day, -30, GETDATE()) AS StartDate
FROM Customers
ORDER BY NEWID()  -- Random order

Parameter Mappings

You map each parameter to a column from the parameter query:

@CustomerId → CustomerId column
@StartDate → StartDate column

Setting Up Parameterization

GUI Application
CLI / Configuration File

Write Your Main Query

Include parameters using the @ParameterName syntax:

SELECT ProductName, UnitPrice, UnitsInStock
FROM Products
WHERE CategoryId = @CategoryId
  AND UnitPrice <= @MaxPrice

Open Parameter Window

Click the Parameters button in the main window.

Configure Database Connection

Choose where to get parameter values:

Use same database settings: Parameters come from the same database as your main query
Use separate database settings: Parameters come from a different database

For most cases, use the same database settings.

Write Parameter Query

Enter a query that returns parameter values:

SELECT TOP 100
    CategoryId,
    UnitPrice AS MaxPrice
FROM Products
ORDER BY NEWID()

The parameter query executes once at the start of the test. Make sure it returns enough rows for your thread count and iterations.

Map Parameters

The tool detects parameters in your main query and displays them.For each parameter:

Select the parameter from the list
Choose the corresponding column from the parameter query dropdown
The mapping is created automatically

Example mappings:

@CategoryId maps to CategoryId
@MaxPrice maps to MaxPrice

Test and Save

Click OK to save the parameter configuration.When you run your test, each iteration will use the next row from the parameter query results.

In your JSON configuration file, add the parameter settings:

{
  "MainQuery": "SELECT * FROM Orders WHERE CustomerId = @CustomerId AND Status = @Status",
  "ParamQuery": "SELECT TOP 500 CustomerId, 'Active' AS Status FROM Customers ORDER BY NEWID()",
  "ParamMappings": {
    "@CustomerId": "CustomerId",
    "@Status": "Status"
  },
  "ShareDbSettings": true,
  "NumThreads": 10,
  "NumIterations": 50
}

Configuration Properties

ParamQuery: SQL query that returns parameter values
ParamMappings: Dictionary mapping parameter names to column names
ShareDbSettings: If true, uses MainDbConnectionInfo for parameters. If false, uses ParamDbConnectionInfo

In JSON, parameter names must include the @ symbol, and mappings use a dictionary/object structure.

Parameter Value Distribution

The ParamServer class distributes parameter values across threads using a thread-safe counter:

// From LoadEngine.cs line 223
var rowNum = Interlocked.Increment(ref _currentRow);
var dr = _theParams.Rows[rowNum % _numRows];

How Rows Are Assigned

Thread 1, Iteration 1: Row 1
Thread 2, Iteration 1: Row 2
Thread 1, Iteration 2: Row 3
Thread 2, Iteration 2: Row 4
And so on…

Cycling Behavior

If you run more iterations than parameter rows, values cycle:

100 parameter rows
10 threads × 50 iterations = 500 total executions
Each row is used 5 times (500 ÷ 100)

To ensure every iteration gets unique parameters, return at least (NumThreads × NumIterations) rows from your parameter query.

Real-World Examples

Example 1: Testing Customer Lookups

-- Get customer orders for stress testing
SELECT 
    c.CustomerName,
    COUNT(o.OrderId) AS TotalOrders,
    SUM(o.TotalAmount) AS TotalRevenue
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE c.CustomerId = @CustomerId
GROUP BY c.CustomerName

Example 2: Date Range Queries

-- Test report performance with varying date ranges
SELECT 
    ProductId,
    SUM(Quantity) AS TotalSold,
    SUM(Revenue) AS TotalRevenue
FROM Sales
WHERE SaleDate BETWEEN @StartDate AND @EndDate
GROUP BY ProductId

Example 3: Stored Procedure with Multiple Parameters

-- Execute stored procedure with parameters
EXEC dbo.GetProductInventory
    @CategoryId = @CategoryId,
    @MinStock = @MinStock,
    @WarehouseId = @WarehouseId

Advanced Techniques

Testing Parameter Sniffing

Parameter sniffing occurs when SQL Server creates a query plan based on the first parameter value, which may not be optimal for other values. Test this by:

Using a wide range of parameter values that return different result set sizes
Comparing metrics across iterations
Looking for high variance in execution times

-- Parameter query with varying selectivity
SELECT TOP 1000
    CASE 
        WHEN ROW_NUMBER() OVER (ORDER BY NEWID()) % 10 = 0 
        THEN 1  -- High selectivity (many rows)
        ELSE CustomerId  -- Low selectivity (few rows)
    END AS CustomerId
FROM Customers

Null Parameter Values

Include NULL values in your parameter data to test NULL handling:

SELECT TOP 100
    CustomerId,
    CASE WHEN ROW_NUMBER() OVER (ORDER BY NEWID()) % 10 = 0 
         THEN NULL 
         ELSE Region 
    END AS Region
FROM Customers

Cross-Database Parameters

Use a separate database for parameter data:

{
  "ShareDbSettings": false,
  "ParamDbConnectionInfo": {
    "Server": "test-server",
    "Database": "TestData",
    "IntegratedAuth": true
  },
  "ParamQuery": "SELECT TestCustomerId AS CustomerId FROM TestCustomers"
}

Best Practices

Return Enough Rows

Your parameter query should return at least as many rows as the total number of iterations:

Minimum rows = NumThreads × NumIterations

If you have fewer rows, values will repeat. This is fine for some tests but may cache results inappropriately.

Randomize Parameter Values

Use ORDER BY NEWID() to randomize parameter selection:

SELECT TOP 1000 CustomerId
FROM Customers
ORDER BY NEWID()  -- Different values each test run

This prevents caching from skewing your results.

Use Realistic Data Distributions

Mirror your production data patterns:

-- If 80% of queries are for recent orders, reflect that:
SELECT CustomerId
FROM Customers
WHERE LastOrderDate >= DATEADD(month, -1, GETDATE())
UNION ALL
SELECT TOP 20 PERCENT CustomerId
FROM Customers
WHERE LastOrderDate < DATEADD(month, -1, GETDATE())
ORDER BY NEWID()

Consider Parameter Data Types

Ensure parameter query columns match the expected data types:

-- Explicit casting ensures compatibility
SELECT 
    CAST(CustomerId AS INT) AS CustomerId,
    CAST(OrderDate AS DATETIME) AS OrderDate
FROM Orders

Test With and Without Parameters

Compare results:

Without parameters (same value every time)
With parameters (varying values)

This reveals caching effects and parameter-sensitive performance issues.

Troubleshooting

Error: Parameter Not Mapped

If you see parameter-related errors:

Verify parameter names match exactly (including @)
Check that all parameters in MainQuery are mapped
Ensure parameter query returns the mapped columns

Error: Column Not Found

If a column isn’t found:

Run the parameter query independently to verify column names
Check for typos in column names
Ensure case-sensitivity matches

Unexpected Results

If results seem wrong:

Run parameter query manually to inspect values
Reduce to 1 thread and 1 iteration to debug
Check SQL Server Profiler to see actual parameter values being used

Next Steps

Interpreting Results

Learn how to analyze performance metrics from parameterized tests

Connection Settings

Configure connection settings for parameter queries

Using the GUI

Set up parameters in the GUI application

Using the CLI

Configure parameters in JSON files

Get Started

Guides

Configuration

Advanced

Reference

Overview

When to Use Parameterization

How It Works

Main Query with Parameters

Parameter Query

Parameter Mappings

Setting Up Parameterization

Configuration Properties

Parameter Value Distribution

How Rows Are Assigned

Cycling Behavior

Real-World Examples

Example 1: Testing Customer Lookups

Example 2: Date Range Queries

Example 3: Stored Procedure with Multiple Parameters

Advanced Techniques

Testing Parameter Sniffing

Null Parameter Values

Cross-Database Parameters

Best Practices

Troubleshooting

Error: Parameter Not Mapped

Error: Column Not Found

Unexpected Results

Next Steps

Interpreting Results

Connection Settings

Using the GUI

Using the CLI

Build docs developers (and LLMs) love

Get Started

Guides

Configuration

Advanced

Reference

​Overview

​When to Use Parameterization

​How It Works

​Main Query with Parameters

​Parameter Query

​Parameter Mappings

​Setting Up Parameterization

​Configuration Properties

​Parameter Value Distribution

​How Rows Are Assigned

​Cycling Behavior

​Real-World Examples

​Example 1: Testing Customer Lookups

​Example 2: Date Range Queries

​Example 3: Stored Procedure with Multiple Parameters

​Advanced Techniques

​Testing Parameter Sniffing

​Null Parameter Values

​Cross-Database Parameters

​Best Practices

​Troubleshooting

​Error: Parameter Not Mapped

​Error: Column Not Found

​Unexpected Results

​Next Steps

Interpreting Results

Connection Settings

Using the GUI

Using the CLI

Build docs developers (and LLMs) love

Overview

When to Use Parameterization

How It Works

Main Query with Parameters

Parameter Query

Parameter Mappings

Setting Up Parameterization

Configuration Properties

Parameter Value Distribution

How Rows Are Assigned

Cycling Behavior

Real-World Examples

Example 1: Testing Customer Lookups

Example 2: Date Range Queries

Example 3: Stored Procedure with Multiple Parameters

Advanced Techniques

Testing Parameter Sniffing

Null Parameter Values

Cross-Database Parameters

Best Practices

Troubleshooting

Error: Parameter Not Mapped

Error: Column Not Found

Unexpected Results

Next Steps