Troubleshooting

​

Symptom

​

Root Cause

​

Solution

1. Increase ConnectionTimeout:
   • Recommended: 30-60 seconds for high thread counts (>50 threads)
   • The setting applies to all connections in the test
2. Verify SQL Server connection limits:
   SELECT @@MAX_CONNECTIONS -- Default is usually 32767
   
3. Check connection pooling is enabled:
   • Verify EnableConnectionPooling = true (default in QueryStressSettings.cs:121)
   • Pooling is essential for multi-threaded testing
4. Reduce initial thread count:
   • Start with fewer threads and gradually increase
   • LoadEngine pre-allocates MinPoolSize = threads (LoadEngine.cs:47)

​

Prevention

​

Symptom

​

Root Cause

​

Solution

1. Verify proper connection handling:
   • LoadEngine uses using statements (LoadEngine.cs:382) to ensure disposal
   • Connections are explicitly closed after each iteration (LoadEngine.cs:456)
2. Check for blocking queries:
   • Long-running queries hold connections longer
   • Set appropriate CommandTimeout to prevent indefinite holds (default: 0 = infinite)
3. Clear connection pools:
   • LoadEngine calls ClearAllPools() on cancellation (LoadEngine.cs:194)
   • Restart the application if pools are corrupted

​

Symptom

​

Root Cause

​

Solution

1. Set reasonable CommandTimeout:
   • Default is 0 (infinite), which can cause issues
   • Recommended: 30-120 seconds depending on expected query duration
2. Enable KillQueriesOnCancel:
   • Default: true (QueryStressSettings.cs:125)
   • Ensures queries are cancelled when you stop the test
   • Uses timer to call SqlCommand.Cancel() (LoadEngine.cs:322-340)
3. Check SQL Server resources:
   • Memory pressure
   • TempDB contention
   • Lock escalation

​

Symptom

​

Root Cause

​

KillQueriesOnCancel Setting

• A timer checks every 2 seconds if cancellation is pending (LoadEngine.cs:324)
• Calls SqlCommand.Cancel() to request SQL Server terminate the query (LoadEngine.cs:334)
• Threads are given 5 seconds to exit gracefully (LoadEngine.cs:190)
• After 5 seconds, threads are forcefully interrupted (LoadEngine.cs:191)

• Threads wait for queries to complete naturally
• Can take a long time if queries are slow or blocked

​

Solution

1. Ensure KillQueriesOnCancel is enabled (default)
2. Wait up to 5 seconds for graceful thread termination
3. If threads still hang: Check for SQL Server blocking
   -- Find blocking queries
   SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0
   

​

Symptom

​

Root Cause

​

How It Works

if (_queryDelay > 0)
    Task.Delay(_queryDelay, ctsToken).Wait();

​

Solution

1. Ensure you’re using a recent version of SQL Query Stress
2. Check that KillQueriesOnCancel = true

​

Symptom

• “Must declare the scalar variable @paramName”
• “Parameter @paramName has no default value”
• Parameter values are incorrect or null

​

Root Cause

​

How Parameters Work

1. Load parameter values from your parameter query into a DataTable
2. Map parameter names to result set columns
3. Clone SqlParameter objects for each query execution
4. Rotate through parameter rows using Interlocked.Increment (LoadEngine.cs:223)

​

Solution

1. Verify parameter mappings:
   • Parameter names must match exactly (case-sensitive)
   • Column names in parameter query result must match mappings
   • Empty mappings are automatically removed (LoadEngine.cs:90-102)
2. Check parameter query returns data:
   -- Test your parameter query independently
   SELECT YourColumn1, YourColumn2 FROM YourTable
   
3. Ensure parameter types are compatible:
   • SQL Server will attempt implicit conversion
   • Explicit casting in parameter query is safer:
   SELECT CAST(Value AS INT) AS @ParamName FROM ParameterTable
   
4. Validate mapping configuration:
   • Each parameter in your main query needs a mapping
   • Each mapping needs a corresponding column in parameter query results

​

Symptom

​

Root Cause

​

Common Issues

1. Connection settings differences:
   • SQL Query Stress sets ARITHABORT ON (matches SSMS)
   • Sets QUOTED_IDENTIFIER ON
   • Sets ANSI_NULLS ON These settings can affect query plans and behavior.
2. Timeout differences:
   • CommandTimeout default is 0 (infinite) in SQL Query Stress
   • SSMS default is typically 0 as well, but can be configured differently
3. Connection context:
   • SQL Query Stress sets CurrentLanguage = "English" (LoadEngine.cs:49)
   • Date formats and error messages use English

​

Solution

1. Review querysettings.sql in the application directory
2. Test query with same SET options in SSMS:
   SET QUOTED_IDENTIFIER ON;
   SET ANSI_NULL_DFLT_ON ON;
   SET ANSI_PADDING ON;
   SET ANSI_WARNINGS ON;
   SET ANSI_NULLS ON;
   SET ARITHABORT ON;
   SET CONCAT_NULL_YIELDS_NULL ON;
   
   -- Your query here
   
3. Check query plan differences using SET STATISTICS PROFILE ON

​

Symptom

• Random deadlock errors (Error 1205)
• Some iterations succeed, others fail
• Results vary between test runs

​

Root Cause

​

Solution

1. Review query isolation level:
   • Default is READ COMMITTED
   • Consider if your test should use snapshot isolation:
   SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
   
2. Analyze deadlock patterns:
   • Enable trace flag 1222 for deadlock details
   • Check SQL Server error log
   • Identify conflicting resources
3. Reduce concurrency temporarily:
   • Lower thread count to isolate issue
   • If deadlocks disappear with fewer threads, this confirms lock contention
4. Add query hints if appropriate:
   • WITH (NOLOCK) for read-only queries (be aware of dirty reads)
   • WITH (READPAST) to skip locked rows
   • WITH (ROWLOCK) to prevent lock escalation

​

Symptom

​

Root Cause

​

How Statistics Work

1. SET STATISTICS IO ON and/or SET STATISTICS TIME ON is executed
2. SQL Server sends statistics as InfoMessage events
3. The InfoMessage handler parses messages using regex (LoadEngine.cs:278-287)
4. Extracted values populate QueryOutput (LoadEngine.cs:352, 361-362)

​

Solution

1. Enable statistics collection:
   • CollectIoStats = true (default in QueryStressSettings.cs:122)
   • CollectTimeStats = true (default in QueryStressSettings.cs:123)
2. Verify query produces statistics:
   • Some queries (like SELECT @@VERSION) don’t generate IO statistics
   • DDL statements may not produce expected statistics
3. Check for query errors:
   • If the query fails, statistics aren’t collected
   • Review the Exceptions column in results
4. Test statistics manually in SSMS:
   SET STATISTICS IO ON;
   SET STATISTICS TIME ON;
   -- Your query
   

​

Symptom

​

What Gets Measured

• ForceDataRetrieval = false: After ExecuteNonQuery() returns (LoadEngine.cs:432)
• ForceDataRetrieval = true: After all rows are read from all result sets (LoadEngine.cs:418-428)

• Query execution on server
• Network transfer
• Client-side data processing

​

Solution

1. Compare with SQL Server statistics:
   • Enable CollectTimeStats to see server-side elapsed time
   • Client time will always be >= server time due to network overhead
2. Consider ForceDataRetrieval setting:
   • If true, time includes reading all rows
   • Large result sets will show significantly longer times
3. Check for network latency:
   • Run test from same machine as SQL Server
   • Use SELECT @@SERVERNAME to verify server
4. Account for statistics collection overhead:
   • Parsing InfoMessages adds minimal time (<1% typically)
   • Disable to verify if you suspect overhead