Skip to main content

​
Overview

SQL Query Stress uses JSON configuration files to define load test parameters. These files contain all settings including connection information, query text, thread counts, and statistics collection options.

​
Generating a Sample File

To generate a sample configuration file with all available options: 
sqlstresscmd -x
This creates sample.json in the current directory.

​
Root Configuration Object

The configuration file is a JSON object implementing the QueryStressSettings class with the following properties:

​
Query Settings

​
MainQuery
string
required
The SQL query to execute during the load test.Default: "" (empty string)Example:
"MainQuery": "SELECT * FROM Customers WHERE Region = @Region"
​
ParamQuery
string
SQL query to retrieve parameter values for the main query. Results from this query populate parameterized queries.Default: "" (empty string)Example:
"ParamQuery": "SELECT DISTINCT Region FROM Customers"
​
ParamMappings
object
Dictionary mapping SQL parameter names to column names from the ParamQuery result set.Default: {} (empty object)Example:
"ParamMappings": {
  "@Region": "Region",
  "@CustomerId": "CustomerID"
}

​
Load Test Settings

​
NumThreads
integer
required
Number of concurrent threads to execute the query.Default: 1Range: 1-1000+Example:
"NumThreads": 32
​
NumIterations
integer
required
Number of times each thread executes the query.Default: 1Example:
"NumIterations": 100
​
DelayBetweenQueries
integer
Delay in milliseconds between query executions within each thread.Default: 0 (no delay)Example:
"DelayBetweenQueries": 1000

​
Connection Settings

​
ConnectionTimeout
integer
Connection timeout in seconds. Value of 0 defaults to 15 seconds.Default: 15Example:
"ConnectionTimeout": 30
​
CommandTimeout
integer
Query execution timeout in seconds. Value of 0 means no timeout.Default: 0 (no timeout)Example:
"CommandTimeout": 300
​
EnableConnectionPooling
boolean
Enable ADO.NET connection pooling.Default: trueExample:
"EnableConnectionPooling": true

​
Statistics Settings

​
CollectIoStats
boolean
Enable collection of I/O statistics (logical reads). Sets SET STATISTICS IO ON.Default: trueExample:
"CollectIoStats": true
​
CollectTimeStats
boolean
Enable collection of CPU and elapsed time statistics. Sets SET STATISTICS TIME ON.Default: trueExample:
"CollectTimeStats": true

​
Execution Options

​
ForceDataRetrieval
boolean
Force the client to retrieve and read all data from result sets. If false, only executes the query without reading results.Default: falseExample:
"ForceDataRetrieval": true
​
KillQueriesOnCancel
boolean
Cancel active SQL commands immediately when the test is stopped (do not wait for completion).Default: trueExample:
"KillQueriesOnCancel": true

​
Database Connection Objects

​
MainDbConnectionInfo
object
required
Connection information for the database where the main query executes. This object implements the ConnectionInfo class.See Connection Info Properties below.
​
ParamDbConnectionInfo
object
Connection information for the database where the parameter query executes. Only needed if using parameterized queries.See Connection Info Properties below.
​
ShareDbSettings
boolean
If true, use MainDbConnectionInfo settings for parameter queries instead of ParamDbConnectionInfo.Default: trueExample:
"ShareDbSettings": true

​
Connection Info Properties

Both MainDbConnectionInfo and ParamDbConnectionInfo use the following structure:
​
Server
string
required
SQL Server instance name or address.Default: "(local)"Examples:
  • "localhost"
  • ".\\SQLEXPRESS"
  • "myserver.database.windows.net"
​
Database
string
Database name to connect to.Default: "" (empty string, uses default database)Example:
"Database": "Northwind"
​
IntegratedAuth
boolean
Use Windows integrated authentication instead of SQL Server authentication.Default: trueExample:
"IntegratedAuth": false
​
AzureMFA
boolean
Use Azure Active Directory interactive authentication (MFA).Default: falseExample:
"AzureMFA": true
​
Login
string
SQL Server login username. Required when IntegratedAuth is false and AzureMFA is false.Default: "" (empty string)Example:
"Login": "dbUser"
​
Password
string
SQL Server login password. Required when IntegratedAuth is false and AzureMFA is false.Default: "" (empty string)Example:
"Password": "!Se8ret"
​
ConnectTimeout
integer
Connection timeout in seconds. Value of 0 uses the root ConnectionTimeout setting.Default: 0Example:
"ConnectTimeout": 15
​
EnablePooling
boolean
Enable connection pooling for this connection.Default: trueExample:
"EnablePooling": true
​
MaxPoolSize
integer
Maximum connection pool size. Value of 0 uses ADO.NET default (100). SQL Query Stress automatically sets MinPoolSize equal to MaxPoolSize.Default: 0Example:
"MaxPoolSize": 64
​
ApplicationIntent
integer
Application intent for the connection.Values:
  • 0 = ReadWrite (default)
  • 1 = ReadOnly
Default: 0Example:
"ApplicationIntent": 1
​
EncryptOption
string
Connection encryption setting.Values:
  • "Optional" (default)
  • "Mandatory"
  • "Strict"
Default: "Optional"Example:
"EncryptOption": "Mandatory"
​
TrustServerCertificate
boolean
Whether to trust the server certificate without validation.Default: falseExample:
"TrustServerCertificate": true
​
AdditionalParameters
string
Additional connection string parameters to append.Default: "" (empty string)Example:
"AdditionalParameters": "MultiSubnetFailover=True"

​
Complete Example

Here is a complete, valid configuration file from sample.json: 
{
  "CollectIoStats": true,
  "CollectTimeStats": true,
  "CommandTimeout": 0,
  "ConnectionTimeout": 15,
  "DelayBetweenQueries": 0,
  "EnableConnectionPooling": true,
  "ForceDataRetrieval": false,
  "KillQueriesOnCancel": true,
  "MainDbConnectionInfo": {
    "ApplicationIntent": 0,
    "ConnectTimeout": 15,
    "Database": "Northwind",
    "EnablePooling": true,
    "IntegratedAuth": false,
    "Login": "dbUser",
    "MaxPoolSize": 2,
    "Password": "!Se8ret",
    "Server": ".\\SQLEXPRESS"
  },
  "MainQuery": "SELECT * FROM Customers",
  "NumIterations": 10,
  "NumThreads": 1,
  "ParamDbConnectionInfo": {
    "ApplicationIntent": 0,
    "ConnectTimeout": 0,
    "Database": "",
    "EnablePooling": true,
    "IntegratedAuth": true,
    "Login": "",
    "MaxPoolSize": 0,
    "Password": "",
    "Server": ""
  },
  "ParamMappings": [],
  "ParamQuery": "",
  "ShareDbSettings": true
}

​
Configuration with Parameters

Example configuration using parameterized queries: 
{
  "MainQuery": "SELECT * FROM Orders WHERE CustomerID = @CustomerId",
  "ParamQuery": "SELECT DISTINCT CustomerID FROM Orders",
  "ParamMappings": {
    "@CustomerId": "CustomerID"
  },
  "ShareDbSettings": true,
  "NumThreads": 10,
  "NumIterations": 50,
  "MainDbConnectionInfo": {
    "Server": "localhost",
    "Database": "Northwind",
    "IntegratedAuth": true
  }
}

Build docs developers (and LLMs) love

Get started for freeTalk to us