SQL Server performance testing: query latency under load

Albiferro Yazılım ve Sistem Limited Şirketi (Albiferro Software & System Limited Company) · Published 1 November 2026

Detailed documentation →

On SQL Server a query being fast on its own does not mean it stays fast under load: concurrent sessions compete for locks, memory and connections. A performance test runs the application's queries straight on the database at the target concurrency and measures p95, p99 and the error rate. The general method is in the database load testing guide; this page covers the SQL Server-specific part.

The SQL Server connection

1. Click Connections → Add connection, pick SQL Server as the Type and give it a name (e.g. orders-db).
2. Enter Host, Port (empty means 1433), Database / service, Username and Password. If you can, use a separate user with SELECT only on the tables you need; the password is stored encrypted and the test names only the connection.
3. Max open connections (per runner): 50 by default. Total connections ≈ number of runners × this value; set it so the database's connection limit is not exceeded.
4. Save, then Test. If the database can only be reached through a bastion, add an SSH tunnel to the connection.

A SQL step for SQL Server

Write the query in Query as fixed text; give the values with the @p1 placeholder under Parameters. Parameters can contain {{__VU}}, {{$randInt 1 100000}}, extracted variables or values from a CSV data file; templates cannot go inside the query text, so there is no SQL injection. In the Checks tab add e.g. Row count = 1, run one iteration with Try it to see the rows returned, then Save.

{"id": "ms", "name": "SQL Server", "protocol": "sql", "connection": "mssql-db",
 "sql": {"query": "SELECT TOP 10 * FROM Orders WHERE CustomerId = @p1", "params": ["{{__VU}}"]}}

A read's duration covers the wait for a pooled connection, the query and fetching every row. Rows go to checks and extraction as an array of JSON objects (such as $[0].status). Errors are told apart by kind: timeout, refused connection, permission, missing table, constraint violation, syntax and read-only violation.

Protecting the data

Spitfire works out whether a query reads or writes and counts anything it cannot recognise as a read as a write. Writes cannot be saved without an explicit approval on the step, and once approved the test asks for confirmation again every time it starts, recorded in the audit log (security statement).

This database has no read-only transaction; only Spitfire's statement check protects reads, and the SQL step's form shows a warning saying so. So give the connection a user that can only read.

Which queries to load?

Query Store tells which queries to load: it keeps queries with their execution counts and durations. The few queries run most often and taking the most time in total are the test's skeleton.

Instead of writing the queries by hand, you can have them suggested from the database's own schema (installation admins; query suggestions from the schema). The privileges needed on SQL Server: db_datareader, VIEW DEFINITION and SHOWPLAN for EXPLAIN; optionally VIEW SERVER STATE. Every suggestion is checked with SHOWPLAN_XML: only the plan is fetched, the query never runs; every suggestion has a row limit (TOP).

Then pick the load model: a steady load at the expected concurrency, or a stepped breaking point test to find the limit. Read the results with p95 and p99, not the average.

Common errors

• Login failed for user …: the username or password is wrong, or the user has no access to that database. Type the password again and save; check Database / service.
• A TLS error: the server requires an encrypted connection; add encrypt = true to Driver parameters.
• Connection limit errors: the number of runners × Max open connections (per runner) is more than the database accepts; lower the value or raise the limit.

Frequently asked questions