> ## 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. # The Command Completed, the Goal Did Not: SQL Maintenance Postconditions - URL: https://devinside.cloud/the-command-completed-the-goal-did-not-sql-maintenance-postconditions/ - Published: 2026-07-02T13:00:00.000Z - Updated: 2026-11-12T17:08:31.000Z - Description: A real SQL Server fixture successfully refreshes the wrong statistic, rejects the resulting state, and verifies the correct repair. Command success is not the maintenance goal. - Author: Marcin Borecki - Tags: Engineering, SQL & Reliability, #eng21, #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 ## The Command Completed, the Goal Did Not: SQL Maintenance Postconditions Editorial context: 2 July 2026 ยท Prepared and updated: 12 November 2026 Dates and technology context This is a retrospective editorial timeline. Topic context: 2 July 2026\. First publicly available: 12 November 2026\. Current text, visuals and laboratory revisions were prepared on 12 November 2026. The wrong-object success, corrected target update, metadata and independent row-count checks are actual 2026-11-12 laboratory events. A maintenance command can return successfully while the intended system remains unchanged. That is not a paradox and does not require a broken database engine. The command may have targeted the wrong object, run in the wrong context, or completed a narrower operation than the person or automation believed it represented. The distinction matters because many operational records describe attempts rather than outcomes. A scheduler records that a process exited with code zero. A script prints that statistics were updated. An agent reports that its tool call succeeded. Those observations can all be true without establishing the condition that justified the action. This article implements a small SQL Server laboratory around that gap. It creates two tables, deliberately changes one, successfully updates statistics on the other, and then asks an independent postcondition whether the intended target was repaired. The failed outcome is visible in actual metadata and row counts, not invented for a diagram. The experiment is not a recommendation to refresh statistics on a universal schedule. It is a way to make maintenance acceptance precise. The same discipline can help with other operational work, but this laboratory does not claim to have tested backup recovery, index fragmentation, failover or a production performance improvement. ## Start with the condition that should be true afterward 01 / A concrete example ### Define the intended object before running SQL ![The goal names one statistic on one table. A valid command on another object cannot fulfill that target condition.](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v1.svg) [Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v1.svg) The goal names one statistic on one table. A valid command on another object cannot fulfill that target condition. Interpretation and evidence - Carry target identity. - Define expected state independently. - Keep the observation scope. Recorded SQL state and explicitly limited conceptual interpretation. The intended target is a named statistics object, `TargetCategory`, on `dbo.Target` in a dedicated database. The fixture initially creates 10,000 rows and performs a full-scan statistics creation. It then inserts 1000 additional rows into the target table, leaving the recorded statistics behind the current data. The desired postcondition is deliberately narrow. After the correct refresh, the target statistics should report 11,000 rows sampled and represented, and its modification counter should be zero in this isolated fixture. A separate exact table count should still be 11,000\. The action is accepted only if that target-specific comparison passes. This is not a general freshness rule for every SQL statistic. Filtered statistics, partitioned data, concurrent changes, sampling policies and permissions can change what should be expected. The fixture avoids those ambiguities so that the relationship between command and outcome is easy to inspect. The independent table count matters. Comparing a statistics object only with another field from the same object can produce a self-consistent but stale story. The data mutation provides a known expectation, and the actual table count confirms that the intended data is present. The object identity matters too. A successful update on `dbo.Side` is not evidence about `dbo.Target`. A script that records only the database name and the phrase update statistics has not retained enough information to explain which promise was fulfilled. ## Construct a controlled stale state The laboratory creates a disposable database and turns off automatic creation and automatic updating of statistics within that database. This is an experimental control, not advice to disable those features in production. Without it, another mechanism might refresh the target while the example was trying to observe the result of one specific command. The two tables begin with identical columns and 10,000 rows each. The target receives a named statistic on its category column; the side table receives a separate named statistic on its own category column. Both are created with a full scan. The fixture then inserts1000new rows into the target only, with a category value chosen to make the mutation unambiguous. The selected SQL has no external data dependency. The identifiers and values are generated inside the isolated database. The client uses a temporary loopback connection to a dedicated SQL Server container. It does not connect to any user database or read customer records. The container image is pinned by digest, and the actual run uses SQL Server 2022\. The runner limits it to two CPUs and 4 GiB of memory. A fresh temporary credential exists only in the runner's memory and the child process environment. It is not written into source, reports or the downloadable archive. The database disappears with the container after the run. The runner's cleanup path checks that the task-owned container has been removed. That makes the example a bounded laboratory rather than an instruction to leave another unmanaged service on the developer's machine. ## Read the target, not the success message The C# companion reads the named statistic through `sys.stats` and `sys.dm_db_stats_properties`, and reads the target table's current count in the same result. The query is explicit about the object and statistics names: ```sql SELECT p.rows, p.rows_sampled, p.modification_counter, CONVERT(varchar(40), p.last_updated, 126), (SELECT COUNT_BIG(*) FROM dbo.Target) FROM sys.stats AS s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p WHERE s.object_id = OBJECT_ID('dbo.Target') AND s.name = 'TargetCategory'; ``` If the query does not return a row, the companion fails. It does not translate missing metadata into an empty but healthy state. In a real tool, that absence could reflect a missing object, an incorrect identifier or insufficient visibility. The operator needs an explanation before deciding whether maintenance succeeded. The metadata fields have defined meanings. The row count belongs to the statistics snapshot, the sampled count describes how many rows participated in its calculation, and the modification counter concerns changes since that statistic was updated. They are not interchangeable synonyms for the table's current state. [Microsoft's statistics-properties reference](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 timestamp is useful provenance, but the fixture does not use timestamp advancement alone as its acceptance rule. A new timestamp on the wrong object would still not satisfy the intended outcome. A very fast repeated operation can also make timestamp comparisons less informative than a direct check of the relevant data and metadata conditions. The result is materialized into a small record containing statistics rows, sampled rows, modifications, update time and current rows. The report retains every stage so that the final success can be understood in relation to the controlled stale state and the deliberately wrong operation. ## Execute the wrong operation successfully 02 / Watch the mechanism ### A valid command can address the wrong target ![The wrong-target UPDATE STATISTICS completed, but the independent target query still found the controlled stale state.](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v2.svg) A valid command can address the wrong targetExecuted commandRefresh dbo.Side statisticCommand resultSucceededTarget statistics / current rows10000 / 11000Acceptance decisionTarget condition failedActual disposable SQL fixture. No query-speed improvement is claimed. Play animation[Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v2.svg) Static view. Play to see how the mechanism unfolds. The wrong-target UPDATE STATISTICS completed, but the independent target query still found the controlled stale state. Interpretation and evidence - Success is not proof of intent. - Read the actual target. - Do not erase a negative control. Recorded SQL state and explicitly limited conceptual interpretation. The failure case is simple on purpose: ```sql UPDATE STATISTICS dbo.Side(SideCategory) WITH FULLSCAN; ``` The command is valid and completes successfully. It does exactly what its text says. The error exists in the relationship between that command and the intended target, not in SQL Server's execution of it. Afterward, the companion reads `TargetCategory` again. Its metadata still reports 10,000 rows while the actual target contains 11,000\. The modification counter remains 1000\. The postcondition fails even though the command completed. This is a useful negative control because it rules out a weak acceptance strategy. If the harness accepted the side-table update as a target repair, it would be measuring the completion of an instruction rather than the state that matters. The test forces those two notions apart. The scenario is not presented as a story about a particular administrator or a real customer incident. It is a constructed example of an ordinary class of mistake: a valid action applied to an unintended resource. That class is especially important in automated systems because a well-formed tool call can be mistaken for a correct decision. The right response is not to make the command's success message more elaborate. It is to preserve the target identity and inspect the condition independently. A better log can support that process, but it cannot replace it. ## Repair the actual target and verify the result 03 / A concrete example ### The accepted repair changes the observed state ![These stage counts come from the executed SQL fixture. The target refresh also reset the modification counter in this isolated scenario.](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v3.svg) [Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v3.svg) These stage counts come from the executed SQL fixture. The target refresh also reset the modification counter in this isolated scenario. Interpretation and evidence - Preserve before and after. - Verify the independent row count. - Do not infer query speed from metadata. Recorded SQL state and explicitly limited conceptual interpretation. The correct action names the intended table and statistic: ```sql UPDATE STATISTICS dbo.Target(TargetCategory) WITH FULLSCAN; ``` The next snapshot reports 11,000 statistics rows, 11,000 sampled rows and zero modifications. The exact current table count remains 11,000\. That combination satisfies the fixture's declared postcondition. The companion then repeats the same maintenance command and verifies that the target remains in the accepted state. The repetition is not called free or harmless under every workload. It establishes that, in this controlled no-concurrent-write fixture, the selected result conditions remain consistent after another refresh. The actual recorded stages are: | Stage | Statistics rows | Sampled rows | Modifications | Current target rows | Accepted target state | | ------------------------ | --------------- | ------------ | ------------- | ------------------- | --------------------- | | Initial full scan | 10000 | 10000 | 0 | 10000 | Baseline | | Insert 1000 rows | 10000 | 10000 | 1000 | 11000 | No | | Refresh side table | 10000 | 10000 | 1000 | 11000 | No | | Refresh target statistic | 11000 | 11000 | 0 | 11000 | Yes | | Repeat target refresh | 11000 | 11000 | 0 | 11000 | Yes | These are observations from a real SQL Server execution on 12 November 2026\. They are not projected values based only on what the code should do. The accepted output and container execution receipt are included in the source package. No query-speed measurement accompanies this table. Updating statistics can influence optimization and recompilation, but this experiment does not run a representative application query before and after the change. It therefore cannot claim that the refresh made the product faster. The achieved result is the specified target metadata state. ## A postcondition is an agreement, not a universal formula 05 / A concrete example ### The fixture conditions make the predicate meaningful ![The accepted predicate depends on deliberate laboratory conditions. Production statistics and concurrent changes need their own acceptance rule.](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v5.svg) [Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v5.svg) The accepted predicate depends on deliberate laboratory conditions. Production statistics and concurrent changes need their own acceptance rule. Interpretation and evidence - Do not copy a lab predicate blindly. - State concurrency assumptions. - Keep automatic-stats policy separate. Recorded SQL state and explicitly limited conceptual interpretation. The simple equality between current rows and statistics rows is appropriate because the fixture uses an unfiltered statistic, a full scan and no concurrent mutation after the refresh. In a real system, blindly applying the same equality to every statistic would be a mistake. A filtered statistic represents a subset. A sampled update can have a different sampled-row count. Data can change immediately after an update completes. Permission constraints can hide metadata. The acceptance rule must match the object, method and operational boundary being used. This does not make postconditions impractical. It makes them specific. A maintenance tool can define the target object identity, requested operation, allowed sampling policy, expected visibility and the observation window. It can then distinguish a demonstrated result from an unresolved one rather than reducing every outcome to success or failure. Unknown deserves a real place in that model. If the verification query cannot read the target metadata, the tool has not established the result. It should not automatically rerun a potentially expensive operation or report success based on the earlier return code. The next action may be to restore visibility, inspect permissions or clarify the target. The fixture does not implement a full production scheduler or that complete decision model. It shows the essential separation on a small path. The downloadable code makes it possible to add the next boundary deliberately instead of assuming it is already covered. ## The command result and the business result have different owners A database engine can establish that a valid command completed according to its execution contract. It cannot infer every intention the caller had when selecting that command. The caller or orchestration layer owns the relationship between a requested outcome and the chosen target. An application can make that relationship explicit by carrying a target record through planning, execution and verification. Such a record should identify the server or environment, database, schema, object and operation scope. Names alone may not be enough if resources can be replaced or renamed between stages; the application needs a policy for that change. The principle also applies to an AI-assisted diagnostic workflow. A model may suggest updating a statistic after reading evidence. Trusted code must still validate the target and allowed action, execute within the intended scope and inspect the postcondition. A fluent explanation of why the action should help is not an execution record. The [agent-authority companion](https://devinside.cloud/agent-spending-authority-reservations-approval-scope-and-uncertain-charges/) illustrates a related boundary with synthetic actions and approvals. This SQL laboratory does not integrate that approval system or claim to provide a safe autonomous database administrator. The connection is conceptual: a correct action needs both authority and an observable result. Treating the model's message as the final authority would erase the very distinction the experiment demonstrates. The model can be useful in proposing hypotheses or explaining recorded facts. It should not turn an unverified postcondition into a completed operational promise. ## Maintenance has a cost even when the postcondition passes A successful full scan is not automatically a sensible maintenance policy. It consumes resources and can cause other work around compilation and execution. Microsoft's command documentation explicitly describes tradeoffs around updating statistics and the work involved. [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 laboratory chooses a full scan because it makes a small deterministic example easier to interpret. It does not establish a schedule, a threshold for every database, or a recommendation to update all statistics after every data change. Those decisions need evidence about the actual workload and operational constraints. A production maintenance review should therefore ask two different questions. Did the operation achieve its declared result? Was that result worth the cost at that time? The first does not settle the second. A perfectly executed operation can be unnecessary, mistimed or directed at a symptom that is not responsible for the observed problem. Likewise, a query that remains slow after the refresh does not automatically prove the refresh failed. The postcondition may have been satisfied while the performance hypothesis was wrong. Distinguishing those outcomes helps the next investigation: one calls for repairing execution or targeting, the other for revisiting the diagnosis. This separation avoids a common cycle of repeating maintenance because the visible symptom persists. If the intended metadata state is already established, another identical command may add cost without testing a new explanation. The evidence should tell the operator when to stop repeating the same action. ## What the experiment deliberately does not automate The companion uses a narrowly privileged laboratory connection because the entire database is disposable. It is not a deployment-ready authorization design. A real operational tool should limit what its execution identity can change and separate read-only diagnosis from mutation according to the organization's requirements. There is no automatic discovery of every database on a server. The fixture creates and uses one named database in a task-owned container. That restriction makes ownership and cleanup unambiguous. A production inventory tool has a different scope and should not inherit mutation rights merely because a diagnostic query found an object. There is also no arbitrary SQL supplied by a model or a user. The statements are fixed in the source, and the deliberate wrong-target case is part of the test. This keeps the example focused on outcome verification rather than introducing an unbounded SQL-execution interface. The test does not prove crash recovery, power-loss durability or transaction coordination across systems. The container is removed after a successful recorded run, not used as a platform for a broader resilience claim. Those limits are important when a small example is later reused in a more ambitious service. The result is still meaningful. We have actual engine state before and after a controlled mutation, an operation that succeeds on the wrong target, a failed independent acceptance check, a correct repair and a repeated accepted state. Each step can be inspected without relying on a narrative of what probably happened. ## Preserve the unsuccessful path in the evidence package The wrong-target operation belongs in the published record. Removing it would leave only a demonstration that the intended command works in a clean fixture. Keeping it shows that the acceptance check can reject at least one plausible mismatch between a valid action and its purpose. The package also records the pinned SDK/runtime and dependency configuration. The execution used .NET 10.0.15 with SDK 10.0.400, Microsoft.Data.SqlClient 6.1.0 and an explicit patched cryptography dependency in the local project. These are contemporary execution inputs, not a claim that they were available at an earlier context date. During preparation, an initial C# compilation error prevented the executable from being produced. A container started by the first readiness attempt was identified by its task label and removed before the corrected run. That attempt is not counted as a successful database check. The final run compiled without warnings, executed the intended SQL sequence and confirmed container removal. The detail matters because reproduction is a process, not just the final screenshot. A script should stop at a failed build rather than proceed as though a valid application exists. The accepted commands and receipts make the successful path explicit while preserving the fact that earlier preparation needed correction. [Download the complete maintenance-postcondition laboratory](https://devinside.cloud/content/files/2026/11/ENG21-lab.zip). It includes the source, actual stage snapshots and bounded container runner. The runner creates a temporary credential in memory, refuses to take over an existing container with the same name, and removes its owned container after the application finishes. ## Make completion a state you can inspect A trustworthy operational report should let someone distinguish the requested target, the attempted action, the observed execution result and the verified postcondition. These are related records, not one interchangeable boolean. When the action fails, the failure should remain visible. When the command succeeds but verification fails, the report should say exactly that. When verification cannot establish the result, unknown should not be silently converted to success. When the postcondition passes but the original symptom remains, the diagnostic hypothesis needs another look. This vocabulary is useful for scripts, scheduled jobs and AI-assisted tools alike. It makes the next action depend on evidence rather than on the optimism of a message saying done. It also protects the engineer from confusing a successful interaction with a successful outcome. The small laboratory demonstrates that distinction with real SQL and a deliberately ordinary mistake. The wrong command is not malformed. It simply does not fulfill the intended promise. Detecting that gap is the job of the surrounding system, and it becomes easier when the promise is written before execution begins. ## Design a result object that preserves the distinction 04 / A concrete example ### One boolean loses the next useful action ![Different outcomes require different next actions. A failed verification is not automatically a reason to rerun the same mutation.](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v4.svg) [Enlarge diagram](https://devinside.cloud/content/images/2026/11/devinside/ENG21-v4.svg) Different outcomes require different next actions. A failed verification is not automatically a reason to rerun the same mutation. Interpretation and evidence - Separate execution and acceptance. - Unknown is not success. - Avoid blind repeated maintenance. Recorded SQL state and explicitly limited conceptual interpretation. A production-facing wrapper can learn from the laboratory without inheriting every simplification. Instead of returning only a boolean, it can retain a structured result with separate fields for target, attempted operation, execution status, observation status and acceptance decision. The laboratory's JSON already keeps the wrong command's successful execution beside a failed postcondition. Those fields should not be collapsed when the result is presented to another system. An orchestrator might need to retry a transport failure, investigate a target mismatch, or stop after a verified result. Treating all three as the same generic failure encourages the wrong recovery action. Treating all three as success hides unfinished work. The target record should travel with the verification query. In this fixture, the database, schema, table and statistics name are fixed and visible. In a broader tool, the target can be selected from a constrained inventory and validated again before execution. If selection changes between planning and execution, the prior approval or expectation may no longer describe the operation being performed. The observation should also retain its scope. An exact count in a no-writer laboratory is different from a count taken while production writes continue. The latter can be useful, but the acceptance condition needs to account for its consistency boundary. A result object that labels the observation as an exact current truth without preserving that condition can be more misleading than a plain log line. For the demonstrated fixture, a simple predicate is sufficient: ```csharp bool accepted = snapshot.StatsRows == 11000 && snapshot.SampledRows == 11000 && snapshot.Modifications == 0 && snapshot.CurrentRows == 11000; ``` The literal expected total comes from the generated input, not from the command's output. The predicate would be inappropriate if copied into a generic maintenance service without the same fixture conditions. Its value is that every term has a reason. A reviewer can point to the known mutation, the full-scan request and the absence of concurrent writers to explain why the condition means what it claims. The same review should ask what happens when a term cannot be observed. A missing row from the metadata query is not equivalent to zero modifications. A timeout during verification is not evidence that the preceding update failed. The wrapper should preserve the distinction between an adverse result and an unavailable observation. It may need a separate, bounded read attempt before deciding what to do next. A further extension could test a deliberately stale target identity or an insufficiently privileged read-only observer. Those scenarios have not been executed by the current companion. Their absence should be recorded as additional scope, not hidden behind the successful wrong-table case. The current test proves that its declared predicate catches the mismatch it was designed to expose. ## Use the experiment as a review exercise Ask a colleague to read the requested outcome and the final JSON without first reading the command text. Can they identify which stage failed to meet the target condition? Then show the wrong-target statement. The evidence should explain the mismatch without requiring a story about the intentions of the person who ran it. This is a proposed review exercise, not a measured human study. It is useful because it tests the explanatory quality of the artifact. If the evidence cannot distinguish command completion from target repair, adding a natural-language summary generated by a model will not fix the underlying ambiguity. The next improvement may therefore be a better result contract rather than another database command. Once that contract is explicit, automation can enforce it consistently, and a human can inspect the cases where the evidence remains incomplete. That is a practical way to make operational work more trustworthy without pretending that every successful tool call completes the task. ## Source code and execution evidence [Download the complete lab, source record and raw execution results](https://devinside.cloud/content/files/2026/11/ENG21-lab.zip). This package supports the specific execution claims in this article. It is not a production certification.