> ## Content Index
> Fetch the complete content index at: https://devinside.cloud/llms.txt
> Use this file to discover other available public pages before exploring further.

# A SQL Diagnostic Script as a Versioned Product
- URL: https://devinside.cloud/a-sql-diagnostic-script-as-a-versioned-product/
- Published: 2026-09-03T11:30:00.000Z
- Updated: 2026-11-12T17:08:32.000Z
- Description: A versioned evaluator replays actual SQL observations, preserves source hashes and refuses unsupported or incomplete evidence. A diagnostic status is a contract, not a universal health verdict.
- Author: Marcin Borecki
- Tags: Engineering, SQL & Diagnostic Engineering, #eng23, #devinside-review

START WITH THE MECHANISM

Interactive companion and laboratory update: 12 November 2026\. The editorial date places the topic on the timeline; this current explanation and its measured evidence retain their actual preparation and execution dates.

THE FULL INVESTIGATION

## A SQL Diagnostic Script as a Versioned Product

Editorial context: 3 September 2026 · Prepared and updated: 12 November 2026

Dates and technology context

This is a retrospective editorial timeline. Topic context: 3 September 2026\. First publicly available: 12 November 2026\. Current text, visuals and laboratory revisions were prepared on 12 November 2026.

The saved ENG21 observations, actual source hashes and 16 evaluator checks are a 2026-11-12 replay/update. Replaying stored data is not a live historical database check.

A diagnostic script can outlive the assumptions that made its output useful. The database version changes, permissions become narrower, the source schema evolves, or a column that once meant one thing begins to describe another. The script still runs and still produces a familiar label. That continuity can make the result look more trustworthy precisely when it needs another review.

The solution is not to treat every script as a large software product with a complicated release process. It is to recognize that a diagnostic rule has a contract. It accepts a defined observation, interprets it under known conditions and returns a result that should guide a particular next action. Changing any of those parts can change the meaning of the output.

This article packages a small, executable evaluator for saved SQL Server observations. The observations come from the real maintenance-postcondition laboratory, including a command that completed on the wrong object. The evaluator classifies the saved states under a versioned rule, checks unsupported and incomplete inputs, and preserves the hash of the source record.

It does not connect to a SQL server or perform maintenance. That is an explicit separation between collection and interpretation. The original SQL evidence remains inspectable, while the rule can be tested without repeatedly changing a database just to exercise the same classification logic.

## A diagnostic label should answer a named question

01 / A concrete example

### A label needs a question and a boundary

![The evaluator answers one metadata question. None of its statuses means the whole database is healthy or authorizes a maintenance action.](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v1.svg)

[Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v1.svg)

The evaluator answers one metadata question. None of its statuses means the whole database is healthy or authorizes a maintenance action.

Interpretation and evidence
- State the predicate.
- Preserve unknown.
- Separate evidence from advice.
Recorded offline evaluator results or an explicit diagram of its contract.

The selected question is narrow: does this saved observation satisfy the declared full-scan metadata condition for an unfiltered, quiescent target? It is not “is the database healthy?” or “should all statistics be updated?” Those broader questions need more evidence and different rules.

The observation includes the statistics row count, sampled row count, modification counter and exact current table count. Under the fixture conditions, matching counts and zero modifications support one condition. A mismatch motivates investigation. Missing or incompatible information leaves the result unknown.

This gives the evaluator three useful states. Condition-met means the selected predicate matches the saved data. Investigate means the selected data contradicts that predicate. Unknown means the evaluator cannot apply its rule responsibly. None of these states authorizes a database mutation.

The distinction is especially important for unknown. If permissions prevent the collector from seeing a statistic, an empty observation is not evidence of zero modifications. If the engine version is outside the tested rule contract, a familiar-looking row does not automatically establish compatibility. The program should preserve uncertainty rather than manufacture a healthy result.

A diagnostic product becomes easier to maintain when its label is this specific. A later rule can answer another question without silently changing what an earlier green or amber status meant.

## Preserve the collector's result before interpreting it

02 / Watch the mechanism

### Collection and interpretation are separate steps

![Replaying a saved observation executes the rule again, not the original SQL experiment.](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v2.svg)

Collection and interpretation are separate stepsReal SQL fixtureProduces a saved observationFrozen file + SHA-256Identifies the exact inputVersioned evaluatorApplies target-fullscan.v1New result fileNot a new live queryActual saved SQL observations; this evaluator does not make a live database query.

Play animation[Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v2.svg)

Static view. Play to see how the mechanism unfolds.

Replaying a saved observation executes the rule again, not the original SQL experiment.

Interpretation and evidence
- Keep observation time separate.
- Preserve the source snapshot.
- Do not imply current server state.
Recorded offline evaluator results or an explicit diagram of its contract.

The input file is a frozen copy of the accepted ENG21 SQL execution record. That run created a target table and a side table, established a baseline statistic, inserted additional target rows, refreshed the side statistic, then refreshed the correct target and repeated the operation.

The record contains actual before-and-after values. Initially the target had 10000 rows represented in its statistic and 10000 current rows. After the insert, the statistic remained at 10000 while the current count was 11000 and the modification counter was 1000\. The wrong-target command did not change that condition. The correct target refresh produced matching 11000 counts and zero modifications.

The evaluator does not rerun those SQL statements. It reads the preserved bytes, records their SHA-256 and applies an interpretation rule. The distinction prevents a successful offline check from being described as a new SQL execution or a new confirmation of the live database state.

The source hash is provenance for the input file, not a proof that the original collector was correct. That original evidence has its own source, environment and execution receipt. Hashing lets the evaluator state which recorded observation it used and detect an unexpected change in that input.

This arrangement is useful for regression tests. A rule change can be applied to the same observations, and its output can be compared without introducing fresh database state, timing or permissions into every test. New collection behavior still needs its own integration check.

## Put assumptions in the observation envelope

03 / A concrete example

### Unsupported evidence remains unknown

![The offline program checks declared envelope conditions. It does not independently prove the collector permissions or concurrency state.](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v3.svg)

[Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v3.svg)

The offline program checks declared envelope conditions. It does not independently prove the collector permissions or concurrency state.

Interpretation and evidence
- Name what was declared.
- Reject incompatible schemas.
- Expand support only with evidence.
Recorded offline evaluator results or an explicit diagram of its contract.

The evaluator expects an application-owned schema identifier, `di.sql-observation.v1`. It also expects an engine-version string, a confirmed-visibility marker, a population/concurrency scope and the numeric metadata object.

The schema identifier is not a SQL Server version. It identifies the shape and interpretation of the normalized observation. A future collector might add fields, rename a field or preserve another consistency boundary. That change needs a deliberate schema decision rather than an assumption that every JSON object with familiar names is interchangeable.

The engine guard accepts the selected major 16 version shape. This is a conservative rule boundary, not evidence that the rule was tested on every SQL Server 16 patch or cannot work on 17\. An unsupported version is classified unknown so that broader support must be added intentionally.

The visibility and population markers are declared provenance from the collector. The offline script cannot independently prove that the collector had sufficient permissions or that no concurrent writes occurred. It checks the envelope's declared conditions because those conditions determine whether the chosen comparison is meaningful.

A real collection service must earn those declarations. It should record how it selected the object, what identity ran the query and which assumptions about consistency were established. The evaluator should not turn an untrusted caller's string saying confirmed into a security guarantee.

## Understand the metadata before comparing it

The SQL diagnostic fields have specific meanings. The statistics row count belongs to the state represented when the statistic was updated. The sampled count concerns the rows used in that calculation. The modification counter is not a universal tally of every possible database change. The exact interpretation is documented by Microsoft and depends on the object type and context. [Statistics-properties documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-db-stats-properties-transact-sql?view=sql-server-ver16&ref=devinside.cloud).

The fixture deliberately chooses an uncomplicated case: an unfiltered statistic, a full scan and a quiescent target. That makes count equality a reasonable component of its postcondition. A filtered statistic can legitimately represent fewer rows than the whole table. A concurrent writer can change the current count immediately after a successful refresh.

The rule therefore refuses envelopes declaring a different population or concurrency scope. It does not try to solve those cases with a more complicated heuristic hidden inside the same version. A later evaluator can support them with additional inputs and expectations.

The numeric checks also matter. Required fields must be nonnegative integers. A missing value, a negative count or an inconsistent sampled count produces unknown. In Python, booleans are related to integer behavior, so the implementation explicitly checks the exact numeric type rather than accepting false as an apparent zero.

These checks may look mundane, but they protect the diagnostic meaning. A rule that silently turns malformed input into default zeros can produce a reassuring condition-met result from an observation that never existed.

## The rule is small enough to inspect completely

Once the envelope is accepted, the central predicate is straightforward:

```python
matches = (
    data['StatsRows'] == data['CurrentRows']
    and data['SampledRows'] == data['CurrentRows']
    and data['Modifications'] == 0
)

```

The result is condition-met when that predicate holds and investigate when it does not. Both results explicitly state that automatic mutation is false. The implementation does not issue UPDATE STATISTICS or generate a command for another system to execute automatically.

The conservative refusal path is equally important. Unsupported schema raises a format error. An unsupported engine version, unconfirmed visibility, incompatible population or missing observation returns unknown with a reason. The caller can distinguish an input contract violation from a record that is structurally recognizable but insufficient for this rule.

That distinction can inform the next action. A schema mismatch may require a different adapter. A visibility problem may require a permitted diagnostic identity or a revised query. A genuine mismatch in a valid observation may justify investigating the target. Those are different activities, and a generic failed flag would obscure them.

The implementation is supplied as a standard-library Python script because the current task is evaluating a frozen JSON artifact. Its SQL source is preserved in the earlier C# laboratory. Choosing a small tool for this boundary does not imply that a production .NET service should invoke Python for every diagnostic request.

## Test meanings with both accepted and refused observations

04 / A concrete example

### The wrong-target success remains unfavorable

![Five actual source stages and additional refusal cases produce16selected passing checks.](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v4.svg)

[Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v4.svg)

Five actual source stages and additional refusal cases produce16selected passing checks.

Interpretation and evidence
- Use independent expected classifications.
- Keep the negative control.
- A missing value is not zero.
Recorded offline evaluator results or an explicit diagram of its contract.

The executed validation includes five real saved stages: baseline, stale target, repaired target, repeated repair and wrong-target command. The expected classifications are chosen from the known fixture sequence, not by treating the evaluator's own output as the answer.

Baseline, repaired and repeated states satisfy the declared metadata condition. The stale state and the snapshot after refreshing the side table require investigation. The latter is the important negative control: a successful command in the source record must not override the unfavorable target observation.

Additional cases modify the envelope rather than the database. An engine 17 version, a missing engine version, denied visibility, a filtered population and a missing data object all produce unknown. Removing each required numeric field also produces unknown. A boolean substituted for a modification count is rejected as an invalid numeric observation.

Finally, an unsupported observation-schema version is refused rather than interpreted under version one's assumptions. The full run reports 16 checks passing. These are selected contract and classification cases, not a complete compatibility suite for every server, permission model or statistics feature.

The test output retains each original envelope and its result, along with the input-file hash and rule version `target-fullscan.v1`. A reader can inspect both what the rule accepted and why it declined other cases. There is no new performance table because the value of this companion is interpretive correctness and provenance, not a fabricated speed result.

## A rule update needs a migration story

05 / A concrete example

### A rule change can alter the meaning of green

![Changing acceptance is a contract change even when the output label remains the same.](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v5.svg)

[Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG23-v5.svg)

Changing acceptance is a contract change even when the output label remains the same.

Interpretation and evidence
- Version the interpretation.
- Record the changed cases.
- Do not rewrite old provenance.
Recorded offline evaluator results or an explicit diagram of its contract.

Suppose the team wants to support sampled statistics. The existing condition requires sampled rows to equal current rows, so merely removing that comparison would change the meaning of condition-met. The team should decide what the new rule promises and which observations support it.

A new rule might accept a declared sampling policy and classify whether the observed sample matches that policy. That is a different question from proving that the statistic fully represents the current table. It should have its own version, fixtures and explanation. Existing saved reports should not be silently relabelled as though they were produced under the new rule.

The same issue appears when adding support for another engine major version. The code should not simply broaden the version regular expression because a sample looks similar. The engineer needs to check the documented semantics, run the actual collection path in the intended environment and add relevant observations to the compatibility corpus.

Not every source change requires a major product release. But a change that alters acceptance, refusal or meaning needs a visible record. A diagnostic script's changelog is useful when it tells the reader which conclusion changed and why, rather than merely listing code-cleanup commits.

This is how a small script can behave like a responsibly maintained product without becoming an unnecessarily large system. Its interfaces and promises are explicit, so changes can be reviewed at the right boundary.

## A diagnostic report should distinguish evidence from advice

The evaluator's investigate result does not prescribe a full scan. The mismatch could have several explanations outside the controlled fixture, and a production decision depends on the workload, sampling policy, timing and cost of maintenance. A diagnostic tool should not leap from a local predicate to a universal operational recommendation.

Microsoft's UPDATE STATISTICS guidance describes the operation and its tradeoffs. That documentation is useful when deciding what action may be appropriate, but it does not turn every metadata mismatch into the same maintenance command. [UPDATE STATISTICS reference](https://learn.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver16&ref=devinside.cloud).

The report can instead expose the relevant observation and its interpretation boundary. It can say that the selected full-scan condition did not match the saved metadata, show the counts and identify the target. A human or another trusted component can then decide whether to collect more evidence, wait for an expected change or perform an authorized action.

This separation is particularly useful when an AI assistant consumes the report. The assistant can explain a bounded finding, but the report should not contain an implicit grant of mutation authority. A natural-language summary does not change the permissions or acceptance conditions of a database operation.

A useful assistant would preserve unknown as unknown and cite the rule and source snapshot behind its explanation. A less useful one might turn investigate into a confident instruction without acknowledging the fixture assumptions. The structured result helps make that difference testable.

## Preserve the distinction between a replay and a live check

An offline evaluation has practical advantages. It is deterministic for a fixed input and rule, cheap to repeat during development and safe from accidental production mutations. It can support review of the logic without requiring access to the original server.

It also has a hard limit: it cannot establish the current state of that server. A report based on an old snapshot remains an interpretation of that snapshot. If someone needs a live decision, the collection step must run again under the appropriate authorization and preserve its own observation time.

This is why the output includes the input hash and why the documentation names the source run. The artifact should not be displayed as a fresh health check merely because the evaluator was executed today. Evaluation time and observation time are separate facts.

The same principle applies to benchmarks, traces and source documentation. Re-reading a saved report can validate an interpretation or detect an inconsistency. It does not rerun the experiment. A publication that blurs those activities can accidentally claim more evidence than it has.

The companion makes the separation explicit so that its result can be reused honestly. A reader can run the script, inspect the source snapshot and see which part of the chain was newly executed.

## Make the collector and evaluator fail visibly

A complete diagnostic workflow needs a contract for collection failures as well as evaluation failures. A timed-out query, an unavailable object and insufficient metadata permissions should not all become an empty list that later code treats as healthy.

The current script models the visibility and missing-data boundaries through explicit envelope cases. It does not implement a live credential or permission probe. A future collector should emit enough information to distinguish an absent observation from an observed zero, without exposing secrets or unrelated database content.

The collector also needs a bounded execution policy. A diagnostic query can itself become a workload, as the monitoring laboratory demonstrates. A versioned query with a known target and finite timeout is easier to reason about than an open-ended script that discovers and scans every database it can access.

The evaluator should fail similarly clearly. An unsupported schema is a contract error; an unsupported engine or population is unknown. Those distinctions are part of the product behavior and should be preserved through logging, API responses and user-facing summaries.

A test that only verifies the happy-path label misses much of this contract. The refusal cases show whether the tool can admit when it lacks the evidence needed for its own conclusion.

## Reproduce the package and extend only the intended boundary

[Download the versioned diagnostic companion](https://devinside.cloud/content/files/2026/11/ENG23-lab.zip). It includes the frozen SQL execution record, the evaluator, expected cases and output. Running `python diagnose.py` from its lab directory requires no SQL connection and performs no database modification.

The script records a SHA-256 of the source file before interpreting it. It writes a result artifact containing the rule version and the evaluated observations. If the input changes, preserve the old file and identify the new observation rather than overwriting the provenance of an existing report.

A next version could add a real collector adapter with explicit permissions and schema mapping. Another could support a different statistics population or engine version. Each should add the corresponding integration evidence before broadening the rule's acceptance domain. The current 16 checks do not automatically certify those extensions.

The useful standard is simple: another engineer should be able to identify the question, input contract, source observation, interpretation rule, refusal behavior and limit of the conclusion. That makes a diagnostic script a tool people can depend on, even when its correct answer is that more evidence is needed.

## Treat the expected results as a compatibility promise

The five recorded stages form a small compatibility corpus. A later evaluator should not change their labels accidentally during a refactor. If the rule is deliberately broadened or renamed, the report should identify that new interpretation rather than imply the earlier result was always the same.

This is different from freezing every implementation detail. The Python code can be rewritten in C#, the data can be stored in another format, and the collector can change its transport. The stable contract is the meaning of each accepted input and output under the declared rule. A test that depends only on a function's internal structure would not protect that meaning as effectively.

For example, the current rule checks numeric types explicitly. A seemingly convenient conversion that maps a missing value to zero would change the refusal behavior. The happy baseline might still pass, but the missing-field cases would become falsely reassuring. That is exactly the kind of regression a diagnostic product should detect before it is used to summarize an incident.

The engine guard offers another example. Broadening it to accept a newer major version is not merely a cosmetic edit to a regular expression. It expands the domain over which the tool promises an interpretation. A release note should say which collection and classification evidence supports that expansion and what remains untested.

A useful changelog entry can therefore connect three things: the changed rule, the affected input cases and the practical interpretation. “Improved diagnostics” is too vague to help someone decide whether an older report should be reevaluated. “Previously unsupported major-version observations now supported after these integration checks” is more useful because it states the boundary of the new claim.

## Avoid a dashboard that hides the reason for unknown

A user interface might be tempted to display unknown as a grey icon without explanation. That can preserve the distinction from green, but it still leaves the operator without a next step. The reason should remain available alongside the status.

In this companion, unknown can mean that the engine is outside the supported contract, metadata visibility is unconfirmed, the population differs from the fixture, or numeric data is missing or inconsistent. These are separate reasons. A single retry button cannot address all of them.

The presentation should also show which source observation the rule evaluated. If an operator opens a report several days later, the evaluation time alone can make it appear current. The source run and hash help establish that the output describes a preserved snapshot. A live diagnostic view should make a fresh collection explicit rather than rely on the age of the browser tab.

These design choices matter when the output is consumed by an AI assistant as well. The structured reason and provenance should remain part of the tool result. Dropping them before summarization encourages a fluent answer that sounds more certain than the underlying evaluator.

The current companion has no dashboard or model integration. The discussion defines the contract such a consumer would need to preserve. The executed result remains the local script's classification and selected tests, not a claim that every future interface will communicate the uncertainty correctly.

## Keep the diagnostic query and the rule independently reviewable

The source query and the interpretation code can change for different reasons. A query may be revised to use another documented metadata source or to handle a permission boundary. A rule may change because the operational question changed. Combining both changes into one unexplained release makes it harder to identify why a report differs.

Where practical, preserve a normalized observation between the two stages. That gives a reviewer a place to compare the raw collection with the evaluator's expected schema. It also makes failed collection visible before interpretation begins. The schema should be small enough to understand, but rich enough to retain the assumptions required by the rule.

The fixture's normalized envelope is not an industry standard and is not advertised as one. It is an application-owned interface with a version. That ownership is useful: the team can decide how to evolve it, provided it preserves the meaning of earlier artifacts and tests the newly supported cases.

This is the central argument for treating a diagnostic script as a versioned product. The product is the dependable interpretation of evidence, not the amount of code or the sophistication of its packaging. A short evaluator can meet that standard when its inputs, refusals, provenance and change history are explicit.

## Source code and execution evidence

[Download the complete lab, source record and raw execution results](https://devinside.cloud/content/files/2026/11/ENG23-lab.zip). This package supports the specific execution claims in this article. It is not a production certification.