Skip to main content

​
Connection Pooling Strategy

SQL Query Stress automatically configures connection pooling based on your thread count to eliminate pool allocation overhead during testing.

​
Pool Size Configuration

The LoadEngine sets both MinPoolSize and MaxPoolSize equal to your thread count (LoadEngine.cs:46-48): 
var builder = new SqlConnectionStringBuilder(connectionString)
{
    MinPoolSize = threads,
    MaxPoolSize = threads,
    CurrentLanguage = "English"
};
Why this matters: Pre-allocating the connection pool ensures all connections are ready before testing begins, preventing connection creation time from skewing your initial results.

​
Enable Connection Pooling

Always keep connection pooling enabled (default: true in QueryStressSettings.cs:121) unless you specifically need to test connection overhead. Disabling pooling will dramatically slow down your tests and produce unrealistic results for most scenarios.

​
Thread and Iteration Tuning

​
Thread Count Recommendations

Start with thread counts that match your expected concurrent user load. Each thread represents one concurrent database connection.
Guidelines:
  • Low concurrency testing: 1-10 threads for single-user or light load scenarios
  • Moderate concurrency: 10-50 threads for typical application load
  • High concurrency: 50-200+ threads for stress testing under peak load
  • Maximum throughput: Gradually increase threads until performance plateaus

​
Iteration Count

Each thread executes the query for the specified number of iterations (QueryStressSettings.cs:72). The total query executions = threads × iterations.
For reliable averages: Use at least 100 iterations per thread to account for variance in execution times.
Testing strategies:
  • Quick validation: 10-50 iterations per thread
  • Performance baseline: 100-1000 iterations per thread
  • Endurance testing: 10,000+ iterations to identify memory leaks or degradation

​
Query Delay Configuration

The DelayBetweenQueries setting (QueryStressSettings.cs:84) adds a pause between each query execution within a thread.

​
When to Use Query Delay

Use delay (> 0ms) when:
  • Simulating realistic user think time
  • Testing sustained load over time rather than maximum throughput
  • Preventing resource exhaustion on test systems
  • Matching production usage patterns (e.g., reports run every 30 seconds)
Use no delay (0ms) when:
  • Testing maximum throughput capacity
  • Stress testing to find breaking points
  • Measuring query execution time in isolation
The delay is implemented using Task.Delay() (LoadEngine.cs:491) and supports cancellation tokens for responsive test stopping.

​
ForceDataRetrieval vs ExecuteNonQuery

This setting significantly impacts what gets measured and how the server processes your query.

​
ForceDataRetrieval = false (Default)

Uses ExecuteNonQuery() (LoadEngine.cs:432):
  • Best for: INSERT, UPDATE, DELETE, stored procedures without result sets
  • Measures: Query execution time only
  • Performance: Minimal client overhead

​
ForceDataRetrieval = true

Uses ExecuteReader() and retrieves the first column of every row (LoadEngine.cs:418-428): 
var reader = _queryComm.ExecuteReader();
do
{
    while (!runCancelled && reader.Read())
    {
        var x = reader[0]; // Forces data retrieval
    }
} while (!runCancelled && reader.NextResult());
  • Best for: SELECT queries, measuring complete data transfer time
  • Measures: Query execution + network transfer + client processing
  • Performance: Adds overhead for large result sets
Use ForceDataRetrieval for realistic SELECT performance: Applications must retrieve and process data, not just execute the query. This setting ensures you measure the complete operation.

​
CommandTimeout Configuration

The CommandTimeout setting (QueryStressSettings.cs:30, default: 0 = infinite) controls how long SQL Server will execute a query before terminating it.

​
Timeout Guidelines

Default is 0 (infinite): Queries will run indefinitely unless you set a timeout. For stress testing, this can mask performance problems.
Recommended values:
  • Development/baseline: 30 seconds - catches obviously slow queries
  • Production simulation: Match your application’s timeout (typically 30-120 seconds)
  • Stress testing: 15-30 seconds - identifies queries that degrade under load

​
Timeout vs Connection Timeout

  • ConnectionTimeout (QueryStressSettings.cs:36, default: 15s): Time allowed to establish connection
  • CommandTimeout (QueryStressSettings.cs:30, default: 0): Time allowed for query execution
Both timeouts are important but serve different purposes.

​
Statistics Collection Overhead

SQL Query Stress can collect IO and time statistics directly from SQL Server.

​
CollectIoStats and CollectTimeStats

When enabled (both default to true in QueryStressSettings.cs:122-123), the engine executes: 
var setStatistics = (_collectIoStats ? @"SET STATISTICS IO ON;" : string.Empty) + 
                    (_collectTimeStats ? @"SET STATISTICS TIME ON;" : string.Empty);
(LoadEngine.cs:139) The statistics are parsed using regex patterns to extract:
  • Logical reads (LoadEngine.cs:280)
  • CPU time (LoadEngine.cs:284-286)
  • Elapsed time

​
Performance Impact

Statistics collection has minimal overhead (typically <1% for most queries), but the engine must parse InfoMessage events (LoadEngine.cs:343-365) for each execution.
When to disable:
  • Pure throughput testing where you only care about queries/second
  • Very high-volume tests (100,000+ iterations) where parsing overhead becomes noticeable
  • Queries with very fast execution times (<1ms) where overhead is proportionally higher
When to enable:
  • Understanding why queries are slow (logical reads indicate IO patterns)
  • Comparing optimization attempts (did logical reads decrease?)
  • Identifying CPU vs IO-bound queries

​
Thread Priority

All worker threads run at ThreadPriority.BelowNormal (LoadEngine.cs:152) to prevent SQL Query Stress from overwhelming the test client machine. This is generally optimal but can be modified in the source if you need different behavior.

​
Connection Pool Management

The LoadEngine calls SqlConnection.ClearPool() (LoadEngine.cs:115) before testing begins to ensure a clean starting state. After test cancellation, SqlConnection.ClearAllPools() (LoadEngine.cs:194) cleans up all connections.
Pool clearing ensures each test run starts fresh and connections from previous tests don’t affect results.

​
Performance Testing Workflow

  1. Establish baseline: Run test with current query/schema (enable statistics)
  2. Make one change: Modify query, add index, or adjust settings
  3. Re-run identical test: Same threads, iterations, and settings
  4. Compare metrics: Focus on average execution time, logical reads, and CPU time
  5. Iterate: Make additional changes based on results
Change only one variable at a time. If you modify the query and add an index simultaneously, you won’t know which change improved performance.

Build docs developers (and LLMs) love

Get started for freeTalk to us