troubleshoot-microsoft-sql-server

# Troubleshoot Microsoft SQL Server ## When to use this skill - **Log full / disk full**: Transaction log grows unchecked (missing log backups, long-running transaction, replication latency), fills the volume, halts all writes. The most common "unexpected" outage. - **Memory pressure spiral**: Buffer pool shrinks (OS memory pressure, memory grants, max server memory misconfigured), PLE drops, physical I/O increases, I/O subsystem saturates, query latency rises, more concurrent sessions pile up, more memory pressure. Self-reinforcing. - **Worker thread exhaustion**: Blocking chain or external wait causes workers to pile up. New connections get `THREADPOOL` waits. From the application's perspective, SQL Server stops responding. - **TempDB contention**: Allocation page latch waits on PFS/GAM/SGAM pages in TempDB. Manifests as elevated `PAGELATCH_UP` / `PAGELATCH_EX` waits on pages in database ID 2. Common on high-concurrency OLTP. - **Blocking cascade**: A single long-held lock (often an uncommitted transaction from an application bug, or a table lock from lock escalation) blocks dozens of other sessions. CPU looks idle, I/O looks idle, but nothing is processing. - **Parameter sniffing / bad plan**: A query gets compiled with atypical parameter values, producing a catastrophically wrong plan. Can consume all memory grants, spill massively to TempDB, and saturate I/O. - Any time the user reports a Microsoft SQL Server service behaving outside its expected envelope (elevated errors, latency, saturation, resource exhaustion, or unexpected restarts). - An on-call engineer is paging on a Netdata alert tied to a Microsoft SQL Server instance and wants a structured triage path. ## Key facts - This skill wraps the Netdata operator playbook for Microsoft SQL Server. It does not replace the playbook; it routes a coding agent through MCP queries against the same signals the playbook relies on. - Microsoft SQL Server is a single-process, multi-threaded relational database engine. The process (`sqlservr.exe` on Windows, `sqlservr` on Linux) manages its own memory, scheduling, and I/O subsystems through an internal layer called **SQLOS**; a user-mode operating system that sits between the SQL engine and the host OS. - Dominant failure archetypes the playbook calls out: Log full / disk full; Memory pressure spiral; Worker thread exhaustion; TempDB contention; Blocking cascade. - Netdata observes the signals listed in the rule files via its native collectors, plus any OpenTelemetry-shipped metrics that your Microsoft SQL Server instrumentation adds. Both paths end at the same MCP query surface. - Netdata's mssql collector emits 71 context(s) under `mssql.*`. The rule files enumerate which contexts surface which domain; the Verification section below names the load-bearing ones explicitly. ## Step-by-step 1. Confirm the Microsoft SQL Server service is up. Query Netdata via MCP with `list_nodes` and filter by the host running the target. A missing node means the symptom is at the network or orchestrator layer, not inside the service. 2. Pull the last 15 minutes of signals for the target. Use `query_metrics` against the contexts listed in the domain rule files. Run `find_anomalous_metrics` in parallel over the same window; anomalies frame which rule file to read first. 3. Check for **Log full / disk full**. Transaction log grows unchecked (missing log backups, long-running transaction, replication latency), fills the volume, halts all writes. The most common "unexpected" outage. Inspect the rule file whose signals move first for this mode. 4. Check for **Memory pressure spiral**. Buffer pool shrinks (OS memory pressure, memory grants, max server memory misconfigured), PLE drops, physical I/O increases, I/O subsystem saturates, query latency rises, more concurrent sessions pile up, more memory pressure. Self-reinforcing. Inspect the rule file whose signals move first for this mode. 5. Check for **Worker thread exhaustion**. Blocking chain or external wait causes workers to pile up. New connections get `THREADPOOL` waits. From the application's perspective, SQL Server stops responding. Inspect the rule file whose signals move first for this mode. 6. Check for **TempDB contention**. Allocation page latch waits on PFS/GAM/SGAM pages in TempDB. Manifests as elevated `PAGELATCH_UP` / `PAGELATCH_EX` waits on pages in database ID 2. Common on high-concurrency OLTP. Inspect the rule file whose signals move first for this mode. 7. Check for **Blocking cascade**. A single long-held lock (often an uncommitted transaction from an application bug, or a table lock from lock escalation) blocks dozens of other sessions. CPU looks idle, I/O looks idle, but nothing is processing. Inspect the rule file whose signals move first for this mode. 8. Correlate with host-level signals (`system.cpu.utilization`, `system.memory.usage`, `system.disk.io_time`). Many service-level failures have a host-resource precursor. 9. Apply the remediation hinted at in the matching rule file or the operator playbook. Re-run the MCP queries from the Verification section to confirm the signals returned to expected ranges. A fix that does not move the signal back is not a fix. ### Handy MCP call templates ```text # Discover metrics from Microsoft SQL Server list_metrics with q="mssql" # Pull a specific context over the last window query_metrics with context="mssql.user_connections", relative_window=-15m # Rank anomalies for the service or host find_anomalous_metrics with node=<host> and context_pattern="mssql.*" # Correlate a known problem context with others find_correlated_metrics around the incident window # Show current alert state list_raised_alerts scoped to the node ``` ## Common mistakes - Treating Microsoft SQL Server as a generic HTTP or process health check. Microsoft SQL Server has specific failure archetypes (see Key facts) that generic checks miss. - Stopping at the first anomalous metric. Several archetypes produce correlated spikes; use `find_correlated_metrics` to widen the search before concluding a root cause. - Quoting percentile latency without the sample count. Low traffic plus a single slow request moves p99 by seconds. - Reading dashboards for a window shorter than the failure's fingerprint. Slow-brew failures (queue growth, bloat, memory fragmentation) need 30+ minutes of data to see the trend. - Skipping the host-level correlation. A process-level fix for a noisy-neighbour problem does not hold. - Assuming alert thresholds are tuned for your workload. Tune against observed Microsoft SQL Server traffic before escalating an alert configuration issue. ## Verification Run these MCP queries against the Netdata instance that sees the Microsoft SQL Server service. Every context listed below is a real Netdata chart name; the agent does not need to guess. ```text 1. list_metrics filtered by q="mssql" (returns every mssql.* context Netdata sees) 2. query_metrics with contexts=[mssql.user_connections, mssql.session_connections, mssql.buffer_page_lookups, mssql.memory_connection, mssql.database_backup_restore_throughput, mssql.database_read_only] and relative_window=-30m 3. find_anomalous_metrics filtered by node=<host> and context_pattern="mssql.*" ``` Load-bearing contexts for this service: - `mssql.user_connections`: User Connections (connections). Dimensions: user. - `mssql.session_connections`: Session Connections (connections). Dimensions: user, internal. - `mssql.buffer_page_lookups`: Buffer Page Lookups (lookups/s). Dimensions: lookups. - `mssql.memory_connection`: Connection Memory (bytes). Dimensions: memory. - `mssql.database_backup_restore_throughput`: Backup/Restore Throughput (bytes/s). Dimensions: throughput. - `mssql.database_read_only`: Database Read-Only Status (status). Dimensions: read_only, read_write. A clean result means every context is within its expected band and the `find_anomalous_metrics` list is empty or contains only already-acknowledged items. If the fix was real, re-running the same queries 10 minutes after applying it will show a clean result. If it does not, revert and look deeper. ### When the fix does not hold If signals drift back into the anomalous range within 30 minutes of a remediation, the cause was deeper than the applied change. Typical misdiagnoses for Microsoft SQL Server: - Host-resource pressure masquerading as application bug. - Dependent service (DB, cache, upstream) causing a secondary symptom in the instrumented service. - Configuration change that was never reloaded (some subsystems only pick up config on full restart). Escalate by widening the query window: 2-6 hours instead of 15 minutes. Slow-moving causes are invisible at triage window sizes. ## References - [`rules/overview.md`](./rules/overview.md) - Netdata operator playbook: the authoritative source material this skill summarizes. - `skills/netdata-mcp-integration/` for the transport setup. - `skills/netdata-otel-setup/` if additional application signals are needed beyond what Netdata collects natively.