Chapter 6: The Three-Layer Agent¶
Bob's architecture is a response to a specific failure mode. An AI that can diagnose but not act is a smart alarm. One that acts without isolating bad data is a liability. The three-layer design solves both problems by separating observation, reasoning, and action into distinct components with explicit handoffs between them.
This chapter documents the architecture, the code, and the operational patterns that make it work safely.
Architecture Overview¶
flowchart TB
subgraph L1[Layer 1: Sensors]
S1[SQL Monitor health probes]
S2[Network telemetry]
S3[Log streams]
end
subgraph L2[Layer 2: Reasoning]
R1[Ollama LLM router]
R2[MCP tool orchestrator]
R3[Memory store]
end
subgraph L3[Layer 3: Actuators]
A1[T-SQL remediation]
A2[Service restarts]
A3[Alerting and notifications]
end
L1 --> L2 --> L3
L3 -->|feedback| L2The three layers collect, reason, and act. The feedback loop from Layer 3 back to Layer 2 is what makes Bob an agent rather than a script.
Layer 1: Sensors¶
The sensor layer is a set of probes that query SQL Server DMVs and system state on a configurable schedule. Probes run as lightweight Python callables that return structured data. They do not call Ollama. They do not take action. They observe and report.
The core SQL Monitor probe queries the DMVs most relevant to performance variance detection:
-- Primary diagnostic snapshot probe
-- Captures wait stats, top queries, index usage, and memory pressure
-- in a single round trip to minimize monitoring overhead.
WITH WaitStats AS (
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
-- Filter out benign background waits
'SLEEP_TASK', 'WAITFOR', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'HADR_WORK_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'RESOURCE_QUEUE',
'SERVER_IDLE_CHECK', 'SLEEP_DBSTARTUP',
'SLEEP_DBRECOVER', 'SLEEP_MASTERDBREADY',
'SLEEP_MASTERMDREADY', 'SLEEP_MASTERUPGRADED',
'SLEEP_MSDBSTARTUP', 'SLEEP_TEMPDBSTARTUP',
'SNI_HTTP_ACCEPT', 'SP_SERVER_DIAGNOSTICS_SLEEP',
'SQLTRACE_BUFFER_FLUSH', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 'XE_DISPATCHER_WAIT',
'XE_TIMER_EVENT'
)
),
TopQueries AS (
SELECT TOP 10
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us,
qs.total_worker_time / qs.execution_count AS avg_cpu_us,
qs.execution_count,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) AS query_text,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_elapsed_time DESC
),
MemoryPressure AS (
SELECT
physical_memory_in_use_kb / 1024.0 AS memory_used_mb,
page_fault_count,
memory_utilization_percentage
FROM sys.dm_os_process_memory
)
SELECT
'wait_stats' AS section, (SELECT * FROM WaitStats FOR JSON PATH) AS data
UNION ALL
SELECT
'top_queries' AS section, (SELECT * FROM TopQueries FOR JSON PATH) AS data
UNION ALL
SELECT
'memory' AS section, (SELECT * FROM MemoryPressure FOR JSON PATH) AS data;
-- source: bob@c549c88
This probe returns three JSON payloads in a single query execution. The probe runner deserializes them and stores the result in a DiagnosticSnapshot dataclass:
from dataclasses import dataclass, field
from datetime import datetime
import json
@dataclass
class DiagnosticSnapshot:
captured_at: datetime
wait_stats: list[dict] = field(default_factory=list)
top_queries: list[dict] = field(default_factory=list)
memory_pressure: dict = field(default_factory=dict)
instance: str = ""
def to_prompt_context(self) -> str:
"""Serialize snapshot to a compact context string for LLM consumption."""
return json.dumps({
"captured_at": self.captured_at.isoformat(),
"instance": self.instance,
"wait_stats": self.wait_stats[:15], # cap for context window
"top_queries": self.top_queries[:5],
"memory_pressure": self.memory_pressure,
}, indent=2, default=str)
# source: bob@c549c88
Probes run on a 60-second cycle for the primary metrics. The deadlock graph probe runs on a 5-minute cycle since deadlock events are captured in the system health session and do not need frequent polling.
The to_prompt_context() method on DiagnosticSnapshot is where the first editorial decision happens. The raw wait stats table from sys.dm_os_wait_stats can return hundreds of rows, depending on how long the instance has been running. Sending all of them to the LLM is wasteful and counterproductive: it fills context window with noise. The method caps wait stats at fifteen rows, selected by the probe's WHERE filter which already excludes background system waits, and caps top queries at five. The compression is diagnostic-lossless. The wait types you care about are going to be near the top by wait_time_ms. If you have fifteen relevant wait types simultaneously, you have bigger problems than context window management.
The network telemetry probe is simpler but worth describing because it answers a class of question the SQL probe cannot. SQL Server wait stats will tell you there is an ASYNC_NETWORK_IO problem. They will not tell you whether the problem is in the application, in the switch, or in the virtualization layer. The network probe uses Bob's network_scan inline tool (which shells out to nmap) to check ping latency and host reachability from the agent host to db-host at 10.0.0.14. If SQL Server shows elevated network waits and the network probe shows normal latency to .14, the problem is probably in the application. If both degrade together, the problem is below SQL Server and the model can say so.
Log stream data currently comes from the SQL Server error log via sys.dm_os_ring_buffers and from the Windows Application Event Log via the agent host's Windows Management Instrumentation interface. In practice, the log stream produces the most value during and after a deadlock event: the deadlock XML is extracted from the system health session, reformatted into a compact representation, and appended to the diagnostic context when present. Deadlock analysis is one of the cases where the 32B model earns its VRAM allocation. Deadlock graphs have enough structural complexity that smaller models frequently misread the victim chain or recommend index changes that address a symptom rather than the contention source.
Layer 2: Reasoning¶
The reasoning layer takes the structured output from Layer 1, builds a prompt, sends it to Ollama, and decides what to do with the response. This is the most complex part of the system.
The Diagnostic Prompt¶
The system prompt is the contract between the agent and the model. It defines what the model is supposed to do, what data it will receive, and what format the response should take.
DIAGNOSTIC_SYSTEM_PROMPT = """You are a SQL Server diagnostic agent. Your job is to analyze
diagnostic snapshots from a production SQL Server instance and identify performance problems
that require attention.
You will receive a JSON diagnostic snapshot containing:
- wait_stats: current wait statistics from sys.dm_os_wait_stats
- top_queries: top 10 queries by elapsed time with query text and plans
- memory_pressure: process memory utilization
Your response MUST be valid JSON with this exact structure:
{
"severity": "NONE|LOW|MEDIUM|HIGH|CRITICAL",
"primary_finding": "one sentence describing the most important finding",
"findings": [
{
"category": "WAIT_STATS|QUERY_PLAN|MEMORY|INDEX|LOCK",
"description": "specific finding",
"evidence": "which metric or value led to this finding",
"confidence": "LOW|MEDIUM|HIGH"
}
],
"recommended_action": "NONE|MONITOR|INVESTIGATE|APPLY_FIX|ESCALATE",
"fix_candidate": null
}
If recommended_action is APPLY_FIX, fix_candidate must contain a T-SQL statement
that is safe to run. All other fields remain required even when severity is NONE.
Do not fabricate DMV columns or table names. If you are uncertain about a finding,
set confidence to LOW. Do not recommend APPLY_FIX unless you are HIGH confidence."""
# source: bob@c549c88
The structured JSON response is mandatory. An LLM that produces free-form text here cannot be integrated with the actuator layer. The system prompt enforces the contract.
There is a constraint in the system prompt worth explaining because it was learned the hard way: "Do not fabricate DMV columns or table names." Early versions of the system prompt did not include that instruction, and some models, particularly smaller ones, would produce findings that cited real-sounding but nonexistent DMV columns. sys.dm_exec_query_stats.total_spill_reads does not exist, but a 7B model under compression will invent it confidently. The instruction does not fully solve the problem, but it reduces hallucination frequency measurably because it makes the prohibition explicit. When a finding references a nonexistent column, the JSON parse succeeds but the evidence string fails a validation check that runs against a whitelist of known DMV column names. The finding is downgraded to LOW confidence automatically.
Memory segmentation in the reasoning layer follows two patterns. Short-term memory is per-incident: when severity crosses MEDIUM, all findings from that incident are grouped by incident_id in the FindingHistory table and included in subsequent prompts until the incident is marked resolved. The model can see the progression within an incident. Long-term memory is per-host: the last 24 hours of findings for a given SQL Server instance are summarized into a rolling context string. The summary is not the raw JSON from each cycle; it is a prose paragraph generated by the model itself at the end of each hour, stored in dbo.HostContextSummary. This gives the model something it can reason with efficiently instead of having to re-parse 1,440 JSON objects to understand "what has this instance been doing today."
The Reasoning Router¶
Bob uses different models for different reasoning tasks. The router selects the model based on the complexity of the diagnostic payload:
def route_to_model(snapshot: DiagnosticSnapshot, available_models: dict) -> str:
"""
Select inference model based on snapshot complexity.
Complex snapshots (many wait types, large query plans) go to the larger model.
Simple snapshots (clean wait stats, small payload) can use a lighter model.
"""
payload_chars = len(snapshot.to_prompt_context())
has_query_plans = any(
q.get("query_plan") for q in snapshot.top_queries
)
complex_wait_types = sum(
1 for w in snapshot.wait_stats
if w.get("wait_time_ms", 0) > 10_000
)
# Primary model for all work; qwen2.5-14b is the automatic failover
# on inference-host-2 at .71 when the primary is unreachable
preferred = ["gemma4:26b", "qwen2.5-14b"]
if has_query_plans and payload_chars > 8000:
# Log that this is a complex payload requiring full model capacity
pass # routing logic; complex flag used for telemetry
for model in preferred:
if model in available_models:
return model
raise RuntimeError("No suitable model available for inference")
# source: bob@c549c88
The routing logic has a practical origin. The first months of running Bob used a single model for everything: a mid-size model on inference-host-2 at .71. It worked. Then inference-host-1 came online with the RTX 3090 and gemma4:26b became available. Testing on the same diagnostic payloads showed the larger model produced noticeably better findings when query plans were in the payload. The plan XML adds tokens but also adds structure that the larger model uses to better advantage. The smaller model on identical inputs would sometimes identify the right problem but recommend the wrong fix. The larger model was more accurate about both.
The routing threshold at payload_chars > 8000 was calibrated empirically. Below that size, the models produce essentially identical findings on clean workloads. Above that threshold, the divergence increases. The threshold is not sacred: it is a configuration parameter that should be adjusted if your workload has consistently large query plans. In production with gemma4:26b as the sole model on the primary host, the router effectively acts as a complexity gate that decides whether to call the primary or fall through to the failover.
Layer 3: Actuators and the Rollback-First Pattern¶
The actuator layer is where Bob takes action. It is also where things can go wrong in ways that matter. A bad recommendation is annoying; bad T-SQL on a production database is a data incident.
The design principle here is: snapshot before you touch anything.
Every potential fix that reaches the actuator layer follows this sequence:
- Take a Proxmox snapshot of the SQL Server VM
- Log the snapshot ID, timestamp, and the fix candidate to
BookOfBob.dbo.RemediationLog - Apply the fix in a transaction with an explicit rollback window
- Verify the expected metric improved
- Mark the remediation as successful or roll back and alert
The snapshot-then-apply pattern in code:
import httpx
from dataclasses import dataclass
from datetime import datetime
@dataclass
class RemediationCandidate:
fix_sql: str
finding_description: str
confidence: str # LOW | MEDIUM | HIGH
snapshot_id: str = ""
def execute_remediation(
candidate: RemediationCandidate,
proxmox_host: str,
vm_id: int,
sql_conn,
db_conn,
) -> bool:
"""
Execute a remediation candidate against the SQL Server instance.
Always takes a Proxmox VM snapshot before applying any fix.
Returns True if remediation succeeded, False if rolled back.
"""
if candidate.confidence != "HIGH":
raise ValueError(
f"Will not auto-apply a fix with confidence={candidate.confidence}. "
"Only HIGH confidence fixes may be auto-applied. Escalating instead."
)
# Step 1: Take Proxmox snapshot
snap_name = f"bob-pre-fix-{datetime.utcnow().strftime('%Y%m%d-%H%M%S')}"
response = httpx.post(
f"{proxmox_host}/api2/json/nodes/pve/qemu/{vm_id}/snapshot",
json={"snapname": snap_name, "description": candidate.finding_description},
verify=False,
timeout=60,
)
response.raise_for_status()
candidate.snapshot_id = snap_name
# Step 2: Log intent before touching anything
db_conn.execute(
"""
INSERT INTO dbo.RemediationLog
(snapshot_id, fix_sql, finding, confidence, applied_at, status)
VALUES (?, ?, ?, ?, SYSUTCDATETIME(), 'APPLYING')
""",
candidate.snapshot_id,
candidate.fix_sql,
candidate.finding_description,
candidate.confidence,
)
db_conn.commit()
# Step 3: Apply in a transaction with a rollback window
try:
sql_conn.execute("BEGIN TRANSACTION")
sql_conn.execute(candidate.fix_sql)
sql_conn.execute("COMMIT TRANSACTION")
db_conn.execute(
"UPDATE dbo.RemediationLog SET status='APPLIED' WHERE snapshot_id=?",
candidate.snapshot_id,
)
db_conn.commit()
return True
except Exception as exc:
sql_conn.execute("ROLLBACK TRANSACTION")
db_conn.execute(
"UPDATE dbo.RemediationLog SET status='ROLLED_BACK', error=? WHERE snapshot_id=?",
str(exc),
candidate.snapshot_id,
)
db_conn.commit()
return False
# source: bob@c549c88
The snapshot-then-apply pattern came from a production incident, not from upfront design. The original actuator implementation skipped the snapshot step for what seemed like a low-risk change: an UPDATE STATISTICS call on a table that the model had identified as having stale statistics. The statement executed. Statistics updated. Query performance got worse for about forty minutes until the query plan cache flushed and new plans were compiled. Not a disaster. Nothing corrupted. But forty minutes of degraded performance on a production database because Bob made a confident recommendation that turned out to be partially wrong.
That incident produced two changes. First, the snapshot requirement became non-negotiable for every actuator call, not just the "riskier" ones. The definition of risky is hard to draw in advance. The cost of a Proxmox snapshot is a few seconds. The cost of not having one when you need it is a manual restore from backup. Second, the confidence floor for UPDATE STATISTICS was raised from MEDIUM to HIGH. Statistics updates are cheap and frequent, but "cheap and frequent" is how you get a pattern of confident-but-wrong auto-applications that erode trust.
Two things to note about the code.
First: the confidence guard at the top of the function. Bob will not auto-apply a fix with LOW or MEDIUM confidence. Those candidates are logged and routed to the alert channel where a human reviews them. Only HIGH confidence fixes, and only certain categories of fix, are eligible for automatic application. The initial deployment restricts auto-apply to index maintenance operations: ALTER INDEX ... REBUILD and UPDATE STATISTICS. Everything else, query rewrites, configuration changes, service restarts, requires human approval.
Second: the Proxmox snapshot is taken before the transaction opens, not inside it. If the transaction commits and something is still wrong, the snapshot exists to roll back to. The snapshot ID is in the remediation log. The rollback path is: open Proxmox at 10.0.0.2, find the snapshot by name, restore it. That is a manual process today. Chapter 8 covers automating it.
The confidence threshold for auto-apply is currently HIGH only. That threshold was three. There was a period during development when MEDIUM confidence fixes were allowed to auto-apply if the fix category was index maintenance. The failure rate during that period was about one in eight. One in eight sounds acceptable until the eighth one runs at 7 PM on a Thursday and blocks a batch job for ninety minutes. HIGH only has a failure rate closer to one in fifty over the time Bob has been in production, and most of those failures are post-apply verification failures, which means the fix applied without error but did not produce the expected metric improvement. Those are safe failures. The metric did not get worse. They just did not get better.
The read-only mode mentioned in the error handling section is worth describing concretely. When Bob enters read-only mode, the probe cycle continues. Findings are logged to BookOfBob.dbo.FindingHistory as normal. Severity assessment continues. What stops is the handoff to the actuator layer. No sql.apply_fix calls, no proxmox.snapshot calls, no notify.send calls via Asterisk. The agent is watching but not touching. The alert that fires is a webhook to the configured notification channel, not an Asterisk call, since read-only mode is often triggered by inference host failure, and we cannot trust that the notification toolchain is fully intact either.
Why Three Layers and Not Two¶
The original prototype had two distinct components: the probe code that gathered SQL Server data, and the LLM call that processed it. No separate actuator layer. The LLM output was read and acted on in the same function. That worked at 200 lines. It broke at 1,000 lines when the complexity of acting correctly required logic that was too entangled with the logic of deciding what to do.
A two-layer design, sensors feeding directly into a combined reasoning-and-acting component, makes it hard to test the reasoning separately from the acting. You cannot verify that the model's diagnostic output is correct without also verifying the actuator response. You cannot add a new sensor without touching the action logic. You cannot swap the inference backend without touching the code that applies fixes. All of those things are changes you will need to make repeatedly as the system evolves.
Four layers was considered briefly: sensors, an evidence-processing layer that normalizes and filters data before it reaches the model, reasoning, and acting. The evidence-processing layer is real work and real value, but it is best implemented as a method on DiagnosticSnapshot rather than as a separate architectural layer. to_prompt_context() is the evidence processor. It is in the sensor layer, not between the sensor and reasoning layers, because it is fundamentally about what the sensor produces, not about how the reasoning layer consumes it.
Each layer does one conceptual thing. Sensor code that starts making decisions is violating the principle. Reasoning code that starts taking actions is violating the principle. Three layers with explicit handoffs keeps those violations from happening silently as the codebase grows.
Memory and State¶
A monitoring agent without memory is stateless by definition. Stateless is clean, but it loses important information: was this wait stat elevated yesterday? Has this query been degrading over a week? Is this the third time this month we have seen this specific deadlock pattern?
Bob's memory store is a set of tables in BookOfBob:
-- Stores diagnostic snapshots for trend analysis
CREATE TABLE dbo.SnapshotHistory (
snapshot_id INT IDENTITY(1,1) PRIMARY KEY,
captured_at DATETIME2 NOT NULL,
instance NVARCHAR(200) NOT NULL,
payload_json NVARCHAR(MAX) NOT NULL,
severity VARCHAR(10) NOT NULL,
primary_finding NVARCHAR(500) NULL
);
-- Stores LLM findings for correlation
CREATE TABLE dbo.FindingHistory (
finding_id INT IDENTITY(1,1) PRIMARY KEY,
snapshot_id INT NOT NULL REFERENCES dbo.SnapshotHistory(snapshot_id),
category VARCHAR(20) NOT NULL,
description NVARCHAR(MAX) NOT NULL,
evidence NVARCHAR(MAX) NOT NULL,
confidence VARCHAR(10) NOT NULL,
created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
-- source: bob@c549c88
When Bob builds a diagnostic prompt, it includes a rolling window of the last 24 hours of findings from FindingHistory. This gives the model context: "four hours ago you reported a PAGEIOLATCH_SH issue on this instance; wait stats are now normal; was the issue self-resolving?" The model reasons about trends, not just snapshots.
The memory store is intentionally simple: it is a SQL Server database, queryable with standard T-SQL, not a vector database or embedding store. For the current use case, SQL is sufficient and has the advantage of being easily queried by the DBA without installing anything new.
Error Handling and the Fallback Path¶
When any component of the three-layer system fails, Bob degrades gracefully rather than silently.
The failure modes and their handling:
Inference host unreachable: Bob retries three times with exponential backoff, then switches to the fallback host at .71. If both are unreachable, Bob enters read-only monitoring mode: probes continue, findings are logged, no actuator actions are taken. An alert fires via the Asterisk PBX to the configured extension.
Probe query fails: The snapshot is logged as PARTIAL. The reasoning layer receives a note that the probe failed, includes the error, and sets severity to MEDIUM regardless of what other data says, because partial data is less reliable than complete data.
Actuator transaction fails: As shown in the code above, the transaction rolls back, the failure is logged, and the agent escalates to human review. The Proxmox snapshot that was taken before the attempt remains available for manual rollback if needed.
Model returns malformed JSON: The reasoning layer catches JSON parse errors, logs the raw response for review, and does not propagate a finding to the actuator. A malformed response is treated the same as a NONE severity with a note that the model's response was unparseable.
These failure modes map directly to the failure domain table in Architecture ยง2.5. Every domain listed there has a corresponding code path in the agent.
There is one failure mode not in that table because it took several months to name: model drift. Model drift is when the inference model produces findings that are technically coherent but systematically miscalibrated for your specific workload. Drift is systematic miscalibration, not hallucination. The model reads real DMV columns through a lens that does not match your environment. An example: CXPACKET waits are expected and normal on a server with heavy parallel query usage. A model that has not been calibrated for that environment will flag them as a problem. The model is not wrong in the abstract; CXPACKET waits can indicate parallelism problems. But on a reporting server with twelve-core parallelism and a heavy overnight batch, they are background noise.
The solution is not more model tuning. The solution is the system prompt addendum: a per-instance section that documents known baseline patterns the model should ignore. For db-host at .14, the addendum includes: "CXPACKET waits are expected during the overnight batch window from midnight to 5 AM and should not trigger alerts below severity HIGH. The top-5 queries by elapsed time include a nightly maintenance job that has a consistently high elapsed time and is not a performance regression." That addendum is maintained by the DBA, not generated by the model. It is the human's domain expertise, encoded as structured instruction, sitting in the system prompt.
The three-layer architecture is what separates Bob from a monitoring script. The clean separation between sensing, reasoning, and acting means you can change any layer independently. You can swap the inference model without changing the probe code. You can add a new probe without changing the actuator logic. You can extend the actuator layer with new capabilities without touching the reasoning layer.
That modularity is where Chapter 7 begins. The Model Context Protocol is the mechanism that makes the reasoning layer's capabilities extensible without the agent knowing in advance what tools it will have.