Indexing JSON: Read Gains, Write Costs and Maintenance

An index can make a query look dramatically better while making the surrounding application more expensive to operate. That is not a contradiction. The index creates another representation of information, and somebody must keep that representation synchronized with the document that produced it.

For JSON stored in SQL Server, the trade can be particularly easy to hide. A developer sees a predicate over a document property, adds a computed column and an index, and compares two SELECT timings. The comparison may establish a real read improvement. It has not yet priced document updates, additional storage or the rules that make the indexed key mean the same thing as the original property.

This article uses a real SQL Server 2022 laboratory to examine those boundaries together. It compares a JSON_VALUE predicate on an unindexed document property with a covered lookup through an indexed computed column. It then changes the indexed property in a controlled batch and inspects the additional work. All records are synthetic, all execution took place on 12 November 2026, and the raw plans and observations accompany the source.

The objective is not to produce a universal index recommendation. It is to make a local decision defensible: what becomes cheaper, what becomes more expensive, which semantics must remain true, and which assumptions would require another experiment?

Be precise about which JSON feature is being tested

The experiment uses JSON text in an nvarchar column, a computed column and an ordinary B-tree index. It does not use the native JSON index or native JSON data type introduced in a later SQL Server generation. Describing it simply as a benchmark of “JSON indexes” would invite readers to infer the wrong implementation.

Microsoft's documentation distinguishes the computed-column approach from the native JSON index and explicitly states that the latter is not supported in SQL Server 2022 and earlier. The computed-column pattern exposes a document property as a typed expression that an ordinary index can organize. Microsoft: index JSON data

The actual engine reported SQL Server 16.0.4315.2, Developer Edition. The database used compatibility level 160. A .NET 9.0.4 client, built with SDK 9.0.203 and Microsoft.Data.SqlClient 5.1.6, submitted the statements. These are contemporary execution details, not a claim that this engine patch existed at some earlier editorial date.

The container image was pinned by digest. The wrapper created a unique container with two CPUs and 4 GiB of memory, exposed only an ephemeral loopback port and removed the container in a finally block. The password was generated in memory and supplied through child-process environment variables, not written into source or reports. Microsoft's container configuration documents the relevant edition and setup variables. SQL Server container environment variables

These details matter because a different engine, compatibility level, index definition or collation can change the observed plan. Reproducing a query's text is not enough if the structure that makes the query efficient has changed underneath it.

Start with the result, then define the key

01 / See the idea

An index begins with a data contract

Enlarge diagram

The business-shaped question is intentionally simple: count products in region R07 and sum their integer amounts. The fixture contains 100,000 products. Every product has an identifier, an amount and a small JSON document containing a region and a note.

For identifier id, the amount is id % 1000 + 1, and the region is derived from id % 100. The region values run from R00 through R99. The independent C# oracle loops over the generator's mathematical rule, selecting identifiers whose remainder is seven. It expects 1,000 matching rows and an amount sum of 458,000.

That expected result is established independently of the SQL query under test. Comparing the indexed query only with the unindexed query could allow both to share the same mistake. A count and sum do not prove every possible property of the data, but they give this controlled fixture a clear acceptance condition.

The indexed representation uses a bounded string key. That bound is part of the design, not merely an optimization parameter. Silently truncating an arbitrary long region value could make two different source values map to the same key. The fixture therefore includes an explicit length constraint and tests rejection of an oversized value.

The constraint is not a complete JSON schema validator. It uses SQL string-length and comparison semantics, and the generated domain is deliberately narrow. It does not establish correct handling of every Unicode representation, nested object, duplicate JSON property or future document schema. Those requirements would need additional cases and potentially a different extraction contract.

This is the first price of the faster lookup: the application must know what its indexed value means. Indexing an expression that nobody has specified can make a semantically wrong query faster.

Two tables keep the read comparison explicit

The laboratory creates two tables with the same 100,000 logical records. CatalogBase stores the document and amount. CatalogIndexed adds the persisted computed key and the nonclustered index. Keeping the tables separate prevents the optimizer from using the new index while we are trying to observe the baseline path.

The relevant indexed schema is actual laboratory code:

CREATE TABLE dbo.CatalogIndexed
(
 Id int NOT NULL CONSTRAINT PK_CatalogIndexed PRIMARY KEY,
 AmountMinor int NOT NULL,
 Document nvarchar(400) NOT NULL,
 RegionKey AS CAST(JSON_VALUE(Document,'$.region') AS nvarchar(12)) PERSISTED,
 CONSTRAINT CK_IndexedJson CHECK (ISJSON(Document)=1),
 CONSTRAINT CK_IndexedRegion CHECK
   (JSON_VALUE(Document,'$.region') IS NULL
    OR LEN(JSON_VALUE(Document,'$.region'))<=12)
);

CREATE INDEX IX_CatalogIndexed_RegionKey
ON dbo.CatalogIndexed(RegionKey) INCLUDE(AmountMinor);

The included amount matters. The query needs the region to select rows and the amount to calculate its result. With both available through the nonclustered structure, the engine can satisfy this query without fetching each document from the clustered table. This is a comparison of complete access designs, not a claim that the computed expression alone accounts for every benefit.

Persistence matters too. The indexed design stores the computed key in the base row as well as maintaining the index. The write and storage comparison therefore includes the cost of this chosen persisted-column design. It does not isolate the marginal cost of only the B-tree or prove that persistence is necessary for every computed-column index.

The baseline and indexed tables have the same clustered identifier key and the same logical documents. Statistics are updated after loading. Every measured query uses MAXDOP 1, so this experiment examines serial access paths rather than mixing the result with changes in parallel execution.

These choices make the experiment easier to explain. They also limit it. A production table with additional indexes, a different included-column set or a much wider document can have a different balance of read and write costs.

Compare the actual read paths, not the query labels

02 / A concrete example

Same result, different access work

Enlarge diagram

The baseline query extracts the property during filtering:

SELECT COUNT_BIG(*) AS MatchCount,
       SUM(CAST(AmountMinor AS bigint)) AS AmountSum
FROM dbo.CatalogBase
WHERE JSON_VALUE(Document,'$.region')=@region
OPTION(MAXDOP 1);

The indexed query asks the equivalent fixture question through the bounded key:

SELECT COUNT_BIG(*) AS MatchCount,
       SUM(CAST(AmountMinor AS bigint)) AS AmountSum
FROM dbo.CatalogIndexed
WHERE RegionKey=@region
OPTION(MAXDOP 1);

The parameter is an explicitly typed nvarchar(12) containing R07. The application does not concatenate a region into the SQL text. Parameterization and stable types help keep the query contract inspectable, although they do not eliminate every plan-selection issue.

Both queries returned the expected count and sum. The actual baseline plan contained a clustered index scan whose runtime counters reported 100,000 rows read and 1,000 rows produced. The indexed plan contained an index seek with 1,000 rows read and 1,000 produced. Each then calculated the one-row aggregate result.

That is a stronger explanation than saying the second query is “sargable” and stopping there. The term is useful shorthand for an access opportunity, but the saved plan shows whether the engine actually took that opportunity. A well-written predicate can still receive another plan when selectivity, statistics or available indexes differ.

The baseline's access operator estimated 10,000 matching rows, while the indexed seek estimated 1,000. This is another observation, not an independently isolated cause of performance. We changed the access representation and its statistics together. It would be unjustified to attribute the entire difference to cardinality estimation alone.

Logical reads describe work, not elapsed time

The diagnostic execution enabled STATISTICS IO. It reported 4,016 logical reads for the baseline SELECT and seven for the indexed SELECT. Physical reads were zero in those captured executions.

Logical reads count page accesses through the data cache. They are not automatically a count of distinct pages, bytes transferred over the network or storage-device reads. A warm cache can remove physical I/O while leaving a substantial amount of logical work. Microsoft: SET STATISTICS IO

The lower count therefore supports a concrete access-path finding. The indexed query visited far less cached page data for this selective lookup. It does not establish a proportional reduction in wall-clock latency, because parsing, execution, client handling and other costs remain.

The actual plan was collected with STATISTICS XML. That statement executes the query and returns execution information; it is not merely an estimated plan generated without running the workload. Diagnostic collection was kept separate from the timed samples, since collecting and transferring a plan is additional work. Microsoft: SET STATISTICS XML

The saved .sqlplan files are part of the evidence pack. The summary extracts only counters actually present on relevant operators. An absent runtime counter must remain absent; it should not become a fabricated zero that implies an operator did no work.

The count of rows read and the logical-read report complement each other. One helps explain selectivity and intermediate work; the other helps explain access to pages. Neither should be substituted for the final correctness result.

Changing the document changes the index obligation

03 / Watch the mechanism

A changed key creates write obligations

Enlarge diagram

Static view. Play to see how the mechanism unfolds.

The write experiment changes the region property for identifiers 1 through 1,000. It uses the same statement shape against each design:

UPDATE dbo.CatalogIndexed
SET Document=JSON_MODIFY(Document,'$.region',N'R99')
WHERE Id BETWEEN 1 AND 1000
OPTION(MAXDOP 1);

SELECT CAST(@@ROWCOUNT AS bigint) AS AffectedRows;

The baseline statement targets CatalogBase. The identifier predicate allows the engine to locate the affected rows through the clustered key in both cases. The difference is what must happen after a document's indexed property changes.

Each write runs inside an explicit transaction. The harness verifies that all 1,000 target rows expose R99, then rolls back. This design restores the logical fixture between samples and prevents the first update from turning later samples into a different workload.

The timing boundary covers statement execution and draining its result. It excludes transaction creation, verification and rollback. It therefore measures update-statement work inside a transaction, not the full cost of a durable committed business operation. In particular, it cannot establish commit latency or the cost of a production log-flush policy.

Rolling back is also not physically identical to creating a fresh database for every sample. Allocation, cache and maintenance state may change even when logical values are restored. Five repeated statements in one warmed fixture remain a small experiment, and their ranges must be retained.

The diagnostic update reported 44 logical reads for the baseline table and 4,212 for the indexed design. Both affected 1,000 rows. The indexed plan includes work associated with maintaining the chosen persisted key and indexed representation. These observations show why the read-only comparison would have been incomplete.

They do not mean that every update to a JSON document pays exactly this cost. Updating an unindexed property, changing fewer rows or assigning a value with a different distribution can alter the work. The experiment deliberately changes the property on which the new index depends.

Additional storage is another part of the design

04 / A concrete example

The new structure has a storage cost

Enlarge diagram

Before the update experiments, the storage snapshot reported 4,016 used pages for the baseline clustered table. The indexed table's clustered structure also reported 4,016 used pages in this fixture, while its additional region index used 352 pages and reserved 859 pages.

The equal clustered-page totals do not prove that persisting a computed column is free. Page counts are discrete, and the additional value can fit within the existing page packing for this particular row shape. A wider key or a different fill pattern may cross another page boundary.

Used and reserved pages must also remain separate. Reserved capacity is not the same as currently occupied index pages, and neither directly tells us how much a database file would shrink after dropping an index. The report records the engine's counters without promoting them into a storage-reclamation promise.

The important result is qualitative and concrete: the faster read path is backed by another maintained structure. Its storage cost can be acceptable, but it belongs in the decision. A benchmark that reports only SELECT duration leaves that cost outside the frame.

The snapshot comes from sys.dm_db_partition_stats joined to index metadata, grouped by the specific laboratory objects. Its timing is explicit: after data loading, index creation and statistics update, before the diagnostic write cases. It is not a historical capacity trend or a production fragmentation assessment.

For a real application, include the expected index lifecycle. Creation time, rebuild strategy, backup volume and retained history can matter alongside steady-state size. This first experiment does not measure them, so the decision should not pretend they are already priced.

Preserve the semantic edge cases

The harness does more than compare the common R07 query. It adds missing, null and numeric region values within a transaction, checks that they are not incorrectly returned as R07 through the relevant access path, and rolls those rows back. It also verifies rejection of an oversized key value in both table designs.

These cases protect the relationship between the original property and the indexed key. They are deliberately small, because the aim is to expose a semantic discrepancy rather than to produce more throughput samples.

The length constraint deserves careful interpretation. SQL LEN does not count trailing blanks in the same way as a raw character-array length. This fixture uses the generated R00-to-R99 domain and SQL equality comparisons. The check is not presented as a comprehensive rule for arbitrary region identifiers in every language or comparison mode.

The table also checks ISJSON, but the experiment does not claim full document-schema validation. A syntactically valid JSON document can still have the wrong structure for an application. A document property can be absent, null, a number, or an object. A production contract must decide which cases are allowed before an index can safely become an authority about the data.

The read queries are rerun after the update rollbacks and compared with the independent oracle. This catches a fixture that was accidentally left in the modified state. A fast second query against different logical data would not be a valid comparison.

The broader lesson is that typed extraction is both a performance design and a data-model decision. It should be reviewed by the people who understand what the property means, not only by the person trying to lower a query duration.

Read the timing table with its missing costs visible

05 / A concrete example

Read gains and update costs

Enlarge diagram

The dedicated measurement execution warms all six query shapes in the shared laboratory, then runs five rounds in alternating order. Four shapes belong to this article: two SELECTs and two UPDATEs. Plan and STATISTICS IO collection are disabled during these timed samples.

The timer starts immediately before command execution and stops after the result sets have been drained. Connections and write transactions are opened before it. The table therefore concerns command-plus-result handling on a local container path, not a user's complete workflow or a committed transaction's end-to-end latency.

Managed allocation is recorded for the .NET client process. It is not SQL Server memory, buffer-pool size or native network-buffer allocation. A query returning one small aggregate can have low client allocation while the engine scans a large table.

The measurement run completed with all expected results validated. The table retains the median and observed range of five command executions per shape. It is not a confidence interval, a percentile of production requests or a projection of sustainable throughput.

Command	Median ms	Observed range ms	Client allocated bytes, median	Diagnostic logical reads
JSON property scan	65.42	64.21–75.41	4,048	4,016
Computed-key index lookup	1.54	0.84–6.48	4,048	7
Update baseline documents	16.24	12.64–18.93	3,592	44
Update indexed documents	55.51	34.86–58.57	3,592	4,212

The independently captured logical reads, actual rows and storage snapshot remain separate evidence, collected with diagnostic settings rather than silently mixed into the stopwatch samples. All SELECT samples returned count 1,000 and sum 458,000. All UPDATE samples reported 1,000 affected rows and passed the in-transaction content check before rollback.

The read result is consistent with the access-path evidence. The indexed lookup avoids evaluating the document predicate across the entire baseline table and uses a structure covering the needed amount. The local timing difference is substantial in this fixture, but the ratio is not a promise for another workload.

The update result moves in the opposite direction. Changing the indexed property takes more command time in this run and generates more diagnostic logical reads. The experiment includes the persisted computed value and index maintenance together, so it cannot assign each millisecond to one internal component. It can show the cost of choosing the complete indexed design.

One indexed-update sample allocated 52,792 client bytes while the other four recorded 3,592. The median does not erase that sample; the raw file preserves it. Since allocation is process-wide, this small client-side outlier is not evidence that SQL Server suddenly used the same amount of additional memory. The server process is outside that counter entirely.

The indexed SELECT also has a visible elapsed-time range despite a small result and stable logical-read count. That is a useful reminder that client scheduling, transport and other shared-host activity can vary independently of the engine's page-access work. Timing and logical reads agree on the broad mechanism here, but they are not interchangeable measurements.

When interpreting the result, ask whether the application's workload resembles the selected lookup and update. A read-heavy service repeatedly selecting one region may value the new structure differently from an ingestion-heavy service that frequently rewrites region values. The index is not good or bad independently of those responsibilities.

Nor should the write/read trade be collapsed into an invented financial saving. A production cost model needs actual request frequencies, maintenance requirements, storage pricing and service constraints. This laboratory supplies bounded observations that can inform that model, not the model's missing business inputs.

The C# collector keeps diagnostics separate from timing

The harness is a small console application using the SQL client directly. It subscribes to informational messages before enabling STATISTICS IO, and it reads every result set because the plan is returned separately from the business result. A tool that reads only the first aggregate row could silently discard the actual plan it was meant to collect.

The parameter declaration is explicit in the executed source:

q.Parameters.Add("@region",SqlDbType.NVarChar,12).Value="R07";

The diagnostic path enables STATISTICS XML and IO, executes the command, saves the returned XML and then disables those settings before the independent update verification. That ordering prevents the verification query's own I/O from being mixed into the update statement's diagnostic record.

The timed path never enables plan collection. It starts its stopwatch immediately before ExecuteReaderAsync and stops after the reader has consumed all results. Each write transaction is then inspected and rolled back outside the timed region. The report names that boundary so nobody mistakes a rolled-back statement experiment for a durable-commit benchmark.

The source also caps command time and the wrapper bounds the entire child process. If a command fails, the run is not quietly recorded as a fast completion. Container removal still occurs in the wrapper's finally block. This matters for reproducibility as much as for cleanup: the next invocation should not accidentally inherit a half-modified laboratory database.

The shared correctness run contains twelve recorded checks across the JSON and date-query families. The JSON-specific checks cover the read results, update row counts, selected document edge cases, oversized-key rejection and restored results after rollback. That count should not be presented as twelve independent JSON performance experiments. It describes the actual shared runner and its declared checks.

Ask what the workload would have to look like

The temptation after seeing this table is to calculate how many reads “pay for” one indexed update. That can be a useful hypothetical exercise, but these medians alone do not provide a production break-even point.

First, the units differ. Each read selects one region and calculates an aggregate; each update changes one thousand documents. A real workload might update one document, change only a nonindexed property, or perform a long transaction containing many additional operations. Treating every read and every write as one interchangeable event would hide those differences.

Second, the measured updates roll back. Their commit cost, transaction-log durability behavior and interaction with concurrent readers are not included in the reported command time. A business decision based on committed writes needs a benchmark that includes those responsibilities.

Third, workload shape can change the selected access path. A query matching nearly every region may not receive the same benefit from a selective key. A report needing the complete document may add lookups or favor a scan. The seven-read observation belongs to this covered aggregate, not to any SQL statement that mentions RegionKey.

A useful next experiment therefore begins with a distribution of operations, not a preferred index. Define the proportion of selective reads, broad reads, indexed-property updates and unrelated updates. Keep their correctness contracts separate. Then measure the candidate design under that declared mixture, including the concurrency and durability requirements that matter.

The present laboratory earns a narrower conclusion. The index is a plausible improvement for this selective covered read, and it creates measurable obligations for the tested update and storage paths. That is enough to reject both simplistic positions: adding the index is not free, and avoiding every index because writes exist is not a reasoned strategy.

Decide what would make you remove the index

A credible index proposal includes a reversal condition. If the query becomes unselective, the included data grows or updates dominate the workload, the balance may change. If the property stops having a stable typed meaning, the index can become a liability even before its performance changes.

Write those conditions next to the implementation. Record which query motivated the index, what result it must preserve, what write paths maintain it and which observations would trigger review. Otherwise the database gradually collects structures whose original purpose nobody can explain.

There are reasonable alternatives. A frequently queried property may belong in an ordinary typed column with the document retained for other fields. A rarely queried property may not justify another index. A different query may benefit from another key order or included-column set. Each option changes the data and maintenance contract in its own way.

This article does not decide among all those alternatives. It shows how to stop a promising SELECT result from carrying more weight than it deserves. The real engine evidence establishes a selective read benefit and additional update/storage work for one concrete design.

That is enough to support a useful engineering conversation. We can identify what the index purchases, what it obliges the system to maintain and which unanswered question matters next. The faster query is the beginning of that conversation, not its conclusion.

Source code and execution evidence

Download the complete lab, source record and raw execution results. This package supports the specific execution claims in this article. It is not a production certification.