Connection Settings - SQL Query Stress

SQL Query Stress automatically applies SQL Server connection settings to ensure query execution behavior matches SQL Server Management Studio (SSMS). This consistency is critical for accurate performance testing.

Overview

When you execute queries in SSMS, SQL Server applies a specific set of session-level settings. These settings affect query compilation, execution plans, and behavior. SQL Query Stress replicates these settings to ensure your stress tests produce the same results as production queries.

Default SSMS-Compatible Settings

SQL Query Stress reads connection settings from the querysettings.sql file located in the application directory. By default, the following settings are applied to every connection:

querysettings.sql

-- Default SQL Server Management Studio query execution settings
-- These settings match the default SSMS configuration for query execution

-- ANSI settings
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULL_DFLT_ON ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ANSI_NULLS ON;

-- Advanced settings
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;

-- Additional settings (default OFF in SSMS)
SET IMPLICIT_TRANSACTIONS OFF;
SET CURSOR_CLOSE_ON_COMMIT OFF;
SET XACT_ABORT OFF;

These settings are executed automatically when each connection is opened. You don’t need to modify your test queries to include them.

Why These Settings Matter

Query Plan Selection

Some settings affect which execution plan SQL Server chooses: ARITHABORT: When ON, queries may use different indexes. This setting particularly affects indexed views and computed columns.

-- This query may use an indexed view only if ARITHABORT is ON
SELECT SUM(Amount) FROM vw_OrderTotals
WHERE Year = 2024

ANSI_NULLS: Affects how NULL comparisons work:

SET ANSI_NULLS ON: NULL = NULL returns NULL (ANSI standard)
SET ANSI_NULLS OFF: NULL = NULL returns TRUE (legacy behavior)

Index Usage

Indexes on computed columns and persisted computed columns require specific settings:

-- This index requires ANSI settings to be used:
CREATE INDEX IX_ComputedColumn 
ON Orders (ComputedTotalAmount)

If the connection settings don’t match the index creation settings, SQL Server won’t use the index.

String Behavior

QUOTED_IDENTIFIER: Controls how double quotes are interpreted:

SET QUOTED_IDENTIFIER ON: Double quotes delimit identifiers (column/table names)
SET QUOTED_IDENTIFIER OFF: Double quotes delimit strings

-- With QUOTED_IDENTIFIER ON:
SELECT "CustomerName" FROM Customers  -- "CustomerName" is a column

-- With QUOTED_IDENTIFIER OFF:
SELECT "CustomerName" FROM Customers  -- "CustomerName" is a string literal

How Settings Are Applied

Settings are applied automatically through the ConnectionSettingsHelper class:

// From ConnectionInfo.cs line 189
ConnectionSettingsHelper.AttachEventHandler(conn);
conn.Open();

When the connection opens, the settings from querysettings.sql are executed before your test query runs.

Customizing Connection Settings

Modifying querysettings.sql

You can customize connection settings by editing the querysettings.sql file in the application directory:

Locate the File

Find querysettings.sql in the same folder as the SQL Query Stress executable:

Windows GUI: Same folder as SQLQueryStress.exe
CLI tool: Same folder as sqlstresscmd or sqlstresscmd.exe

Edit with Text Editor

Open the file in any text editor. The file contains standard T-SQL SET commands.

Add Custom Settings

Add any valid T-SQL SET commands. For example:

-- Original settings
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
-- ... etc ...

-- Custom additions
SET NOCOUNT ON;           -- Suppress "X rows affected" messages
SET STATISTICS IO ON;     -- Enable I/O statistics
SET STATISTICS TIME ON;   -- Enable timing statistics
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- Dirty reads

Save and Restart

Save the file. The new settings take effect the next time you run SQL Query Stress.

Changes to querysettings.sql affect ALL tests. Be careful when modifying this file, especially in shared environments.

Common Customizations

Suppress Row Count Messages

Prevent “(X row(s) affected)” messages:

SET NOCOUNT ON;

This reduces network overhead and can slightly improve performance.

Enable Additional Statistics

Capture more detailed metrics:

SET STATISTICS IO ON;      -- Logical reads, physical reads, etc.
SET STATISTICS TIME ON;    -- CPU time and elapsed time
SET STATISTICS PROFILE ON; -- Execution plan details

SQL Query Stress already collects I/O and time statistics when enabled in Options. Adding SET STATISTICS IO ON here provides the same data.

Change Transaction Isolation Level

Test with different isolation levels:

-- Allow dirty reads (fastest, least locking)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Prevent dirty reads but allow non-repeatable reads
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Prevent non-repeatable reads
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Full serializability (slowest, most locking)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Enable XACT_ABORT

Automatically roll back transactions on errors:

SET XACT_ABORT ON;

This is recommended for production applications but is OFF by default in SSMS.

Connection String Properties

In addition to session settings from querysettings.sql, you can configure connection-level properties in the ConnectionInfo class:

Pooling Configuration

EnablePooling: Control connection pooling (default: true)

{
  "MainDbConnectionInfo": {
    "EnablePooling": true,
    "MaxPoolSize": 100
  }
}

Connection pooling reuses connections across iterations, which:

Reduces overhead of establishing connections
Reflects real-world application behavior
Can affect parameterization testing (cached query plans)

Timeout Settings

ConnectTimeout: Maximum seconds to wait when establishing a connection (default: 15)

{
  "MainDbConnectionInfo": {
    "ConnectTimeout": 30
  }
}

CommandTimeout: Maximum seconds to wait for query execution (default: 0 = unlimited)

{
  "CommandTimeout": 300
}

Application Intent

ApplicationIntent: Specify read-only vs. read-write intent (useful for Always On Availability Groups)

{
  "MainDbConnectionInfo": {
    "ApplicationIntent": 1  // 0 = ReadWrite, 1 = ReadOnly
  }
}

When set to ReadOnly, SQL Server can route the connection to a readable secondary replica.

Encryption Options

Encrypt: Control connection encryption

{
  "MainDbConnectionInfo": {
    "EncryptOption": "Optional",  // Optional, Mandatory, Strict
    "TrustServerCertificate": false
  }
}

Options:

Optional: Encrypt if server supports it (default)
Mandatory: Require encryption
Strict: Require encryption with full certificate validation

Additional Parameters

Add custom connection string parameters:

{
  "MainDbConnectionInfo": {
    "AdditionalParameters": "MultipleActiveResultSets=true;Packet Size=8192"
  }
}

The AdditionalParameters string is appended to the generated connection string.

Separate Settings for Parameter Queries

When using parameterization, you can configure separate connection settings for the parameter database:

{
  "ShareDbSettings": false,
  "MainDbConnectionInfo": {
    "Server": "prod-server",
    "Database": "ProductionDB",
    "EnablePooling": true
  },
  "ParamDbConnectionInfo": {
    "Server": "test-server",
    "Database": "TestData",
    "EnablePooling": false
  }
}

When ShareDbSettings is true (default), both main and parameter queries use MainDbConnectionInfo.

Verifying Settings

To confirm which settings are active during your test:

Check Current Settings

Add this to your test query temporarily:

-- At the start of your query
SELECT 
    'QUOTED_IDENTIFIER' AS SettingName, 
    CASE WHEN (512 & @@OPTIONS) = 512 THEN 'ON' ELSE 'OFF' END AS SettingValue
UNION ALL
SELECT 
    'ANSI_NULLS',
    CASE WHEN (32 & @@OPTIONS) = 32 THEN 'ON' ELSE 'OFF' END
UNION ALL
SELECT 
    'ARITHABORT',
    CASE WHEN (64 & @@OPTIONS) = 64 THEN 'ON' ELSE 'OFF' END;

-- Your actual test query
SELECT * FROM Orders WHERE CustomerId = @CustomerId;

View Connection Properties

Query system views for connection information:

SELECT 
    session_id,
    program_name,
    quoted_identifier,
    arithabort,
    ansi_nulls,
    transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE program_name = 'SQLQueryStress';

Best Practices

Match Production Settings

Ensure your stress tests use the same settings as your production application:

Check your application’s connection string and session settings
Update querysettings.sql to match
Verify with @@OPTIONS query

Mismatched settings can produce misleading test results.

Don't Modify Settings in Test Queries

Avoid including SET commands in your test queries:

-- ❌ Don't do this:
SET NOCOUNT ON;
SELECT * FROM Orders;

-- ✅ Do this instead:
-- Put SET NOCOUNT ON in querysettings.sql
SELECT * FROM Orders;

Settings in querysettings.sql apply once per connection. Settings in queries apply every iteration, adding overhead.

Test Connection Pooling Impact

Run tests with pooling enabled and disabled to understand the impact:

// Test 1: With pooling
{ "EnablePooling": true }

// Test 2: Without pooling
{ "EnablePooling": false }

Pooling typically improves performance but can mask connection establishment issues.

Version Control querysettings.sql

If multiple people use SQL Query Stress on shared test servers, keep querysettings.sql in version control:

# Example: Keep settings with your test scripts
/tests
  /settings
    querysettings.sql
  /queries
    test1.sql
    test2.sql

Troubleshooting

Error: Index Cannot Be Used

If SQL Server won’t use an index:

Check if the index was created with specific ANSI settings
Verify querysettings.sql includes those settings
Use SET SHOWPLAN_ALL ON to see why the index isn’t used

Error: Identifier Cannot Be Bound

If column names aren’t recognized:

Check QUOTED_IDENTIFIER setting
Verify double quotes vs. square brackets in your query

Different Results Than SSMS

If results differ from SSMS:

Compare @@OPTIONS between SQL Query Stress and SSMS
Check for differences in isolation level
Verify querysettings.sql matches SSMS defaults

Next Steps

Using the GUI

Learn about connection configuration in the GUI

Using the CLI

Configure connection settings in JSON files

Query Parameterization

Set up separate connections for parameter queries

Interpreting Results

Understand how settings affect performance metrics

Get Started

Guides

Configuration

Advanced

Reference

​Overview

​Default SSMS-Compatible Settings

​Why These Settings Matter

​Query Plan Selection

​Index Usage

​String Behavior

​How Settings Are Applied

​Customizing Connection Settings

​Modifying querysettings.sql

​Common Customizations

​Suppress Row Count Messages

​Enable Additional Statistics

​Change Transaction Isolation Level

​Enable XACT_ABORT

​Connection String Properties

​Pooling Configuration

​Timeout Settings

​Application Intent

​Encryption Options

​Additional Parameters

​Separate Settings for Parameter Queries

​Verifying Settings

​Check Current Settings

​View Connection Properties

​Best Practices

​Troubleshooting

​Error: Index Cannot Be Used

​Error: Identifier Cannot Be Bound

​Different Results Than SSMS

​Next Steps

Using the GUI

Using the CLI

Query Parameterization

Interpreting Results

Build docs developers (and LLMs) love

Overview

Default SSMS-Compatible Settings

Why These Settings Matter

Query Plan Selection

Index Usage

String Behavior

How Settings Are Applied

Customizing Connection Settings

Modifying querysettings.sql

Common Customizations

Suppress Row Count Messages

Enable Additional Statistics

Change Transaction Isolation Level

Enable XACT_ABORT

Connection String Properties

Pooling Configuration

Timeout Settings

Application Intent

Encryption Options

Additional Parameters

Separate Settings for Parameter Queries

Verifying Settings

Check Current Settings

View Connection Properties

Best Practices

Troubleshooting

Error: Index Cannot Be Used

Error: Identifier Cannot Be Bound

Different Results Than SSMS

Next Steps