The bug did not announce itself as a performance problem. It announced itself as timeouts, on a list endpoint, on a dataset nobody would call large. The query joined a few tables, filtered on a tenant, ordered by a column, and returned twenty rows. Every part of that sentence describes a query that should be boring.
The first instincts were the usual ones, and all of them were wrong. The data had not grown much. The indexes existed and looked right. The endpoint had not changed. What had changed was traffic, and that turned out to be the clue: this was never a query that got slow as data grew, it was a query that was expensive on every single execution and only failed once enough of them overlapped. Our DBA traced it back to the one thing nobody had read, which was the SQL we were not writing ourselves. This is the same discipline I argued for with AI-generated code, pointed at a different generator.
The plan disagreed with the query#
A paginated query over an indexed sort column has an obvious good plan: walk the index in key order, join as you go, stop once you have the page. What we got instead was a full scan of the input and an explicit Sort operator sitting above it. The engine was building the entire join result, sorting all of it, and then handing back twenty rows.
Nothing in the GraphQL query explained that. The generated SQL did:
ORDER BY IIF([t1].[company_id] IS NULL, 0, 1), [t1].[company_id] ASC,
IIF([t1].[record_id] IS NULL, 0, 1), [t1].[record_id] ASCThose IIF expressions exist for a real reason. GraphQL engines expose null placement as part of the sort contract (asc_nulls_first, desc_nulls_last and friends), and the portable way to implement that across databases is to emit a leading sort key that is 0 for nulls and 1 for everything else. It is correct. It is also, on SQL Server, usually unnecessary, and it is never free.
Why an expression costs you the index#
An index is useful for sorting because it physically stores rows in key order. If ORDER BY asks for that column, the engine can read the index and stream rows out already sorted. If ORDER BY asks for an expression over that column, no index holds that order, so the engine has to compute the expression for every candidate row first. Computing something for every row means reading every row, and sorting a computed value means an explicit sort. The index is still there; it just cannot answer the question being asked.
The second cost is bigger and less obvious. Once the ordering can no longer be produced by reading an index, it cannot be preserved through the joins either, so the optimizer stops streaming and starts materializing: build the full join result, sort it, then apply the page. For a query whose entire point is to return twenty rows, that is the difference between work proportional to the page and work proportional to the table.
Why medium-sized data still timed out#
This is the part that surprised the team most, and the part worth carrying to other systems. A plan that scans and sorts does not fail when the data crosses some size threshold. It fails when concurrency crosses one, because the cost is paid per execution rather than per row returned. Ten concurrent requests do not share a scan. They each read the input, each ask for a memory grant for their sort, and each spill to tempdb if that grant was underestimated. Sorting is also where the server rations memory, so once grants are contended, requests queue behind each other and the symptom you see is a timeout, not a slow query.
That is why the accumulated read I/O added up to more than a terabyte on a dataset that was nowhere near a terabyte. It was the same rows, read again for every request, every page and every user.
| With the wrapper | Without it | |
|---|---|---|
| Access path | Full scan of the sort input | Read the index in key order |
| Sort | Explicit Sort over the whole result | None; index order is the order |
| Pagination | Build everything, then take the page | Stream, then stop at the page |
| Cost scales with | Rows in the table, times concurrency | Rows in the page |
| Failure mode under load | Memory grants, tempdb spills, timeouts | Flat |
SQL Server was already doing what we asked#
Here is the detail that turns this from a tuning exercise into a one-line deletion. SQL Server treats NULL as the lowest value there is. Sort ascending and nulls come first; sort descending and they come last. That is not configurable, and it is exactly what four of the six null-placement variants ask for.
| order_by variant | What SQL Server does natively | Extra sort key needed |
|---|---|---|
| asc | Nulls first | No |
| asc_nulls_first | Nulls first | No |
| asc_nulls_last | Nulls first | Yes |
| desc | Nulls last | No |
| desc_nulls_last | Nulls last | No |
| desc_nulls_first | Nulls last | Yes |
The translation layer was emitting the wrapper for all six. Two of them needed it. The other four were paying a full scan and a sort to ask the database for the ordering it was going to produce anyway.
The fix, and the half of it I did not expect#
The obvious half is a comparison: emit the null-handling sort key only when the requested placement differs from the engine’s native behavior. That covers asc, asc_nulls_first, desc and desc_nulls_last, and it is what the release notes name.
The half I found more interesting is nullability. If a column is declared NOT NULL, there are no nulls to place, so even asc_nulls_last and desc_nulls_first need no sort key at all: the requested placement and the native placement are trivially identical over a set with no nulls. Tracking nullability through query translation removes the wrapper in the remaining cases too, and it removes it precisely where it hurts most, because primary keys, foreign keys and timestamps are usually NOT NULL and are exactly the columns people sort and paginate on.
-- before: no index can produce this order
ORDER BY IIF([t1].[company_id] IS NULL, 0, 1), [t1].[company_id] ASC
-- after: an ordinary indexed sort
ORDER BY [t1].[company_id] ASCFix it upstream, not around it#
There were workarounds. We could have added a computed column and indexed the expression, or avoided the null-placement operators, or hand-written the endpoint. Each of those solves one query, permanently, for one team, and leaves the next engineer to rediscover the same thing. The generator was open source, so the honest option was to change the generator.
The engine change is around forty lines across two files. It landed in Hasura GraphQL Engine v2.50.0 with co-author credit, and the maintainers added a test module and a set of case files covering the null-ordering variants before shipping it. The original report of the problem, including a case of roughly one hundred times slower queries, is in issue #10844, and the discussion is in the pull request.
The ratio is worth sitting with. Weeks of intermittent timeouts across an unknown number of installations, and the cause was a sort key that never needed to exist.
What to check in your own stack#
None of this is specific to Hasura, or even to SQL Server. It applies anywhere a layer between you and the database writes the SQL: an ORM, a GraphQL engine, a reporting tool, a low-code backend.
- Log the SQL your data layer actually sends, in production shape, not the query you think you wrote.
- Scan the ORDER BY clauses for anything that is not a bare column: IIF, CASE, COALESCE, ISNULL, casts, string functions. Then do the same for WHERE.
- Look for a Sort operator in the plan of any paginated endpoint. A query that sorts its whole input to return one page is a bug waiting for traffic.
- Declare NOT NULL where it is true. Nullability is information the optimizer uses, not just documentation for humans.
- Alert on tempdb spills and memory grant waits, not only on query duration. Duration tells you something broke; those tell you what.
- Reproduce with concurrency, not with a bigger table. Cost-per-execution bugs are invisible in a single-user test.
- When the generator is open source and the fix is general, send it upstream. It is usually smaller than the workaround.
When a tool writes your SQL, the execution plan is the only honest documentation it has.