Every cached insight inDocumentation Index
Fetch the complete documentation index at: https://mintlify.com/A-Point-Systems-ltd/ms-sql-mcp/llms.txt
Use this file to discover all available pages before exploring further.
AIInsights.SchemaInsights stores a schema fingerprint and a ModifyDateAtAnalysis timestamp that were captured from the live object at the time the insight was written. On every introspection call (DescribeTable, DescribeView, GetObject), the server re-computes the fingerprint and modify_date from live sys.* metadata and compares them to the stored values. If the comparison detects a mismatch — meaning the object has changed since the insight was last authored — the cached row is moved to AIInsights.InsightHistory and the response returns insightFreshness: StaleArchived. This keeps the cache honest without any manual intervention.
insightFreshness values
The insightFreshness field appears in every introspection response and in the GetInsight tool output. It can take one of six values:
| Value | Meaning |
|---|---|
Fresh | Cached insight matches the live object definition; fingerprint and object identity are aligned. Use the insight. |
Absent | No row exists in SchemaInsights for this object. If INSIGHTS_AUTOPOPULATE is enabled, a baseline will be created automatically on the next introspection call. |
StaleArchived | The cached row was archived because DDL events or a fingerprint mismatch indicate the object has changed. The response will not include the stale insight; re-investigate and call UpsertInsight to rebuild the cache. |
LayerDisabled | USE_INSIGHTS_LAYER is set to false (or another falsey value). No cache operations are performed. |
AccessDenied | The server could not read the live object definition due to insufficient permissions. The cached insight is still returned but marked advisory — its freshness cannot be verified. |
DefinitionUnavailable | The object exists in sys.objects but OBJECT_DEFINITION() returned NULL. The cached insight is returned as advisory. This can occur for system objects or objects with WITH ENCRYPTION. |
Fingerprint mechanics
The server computes a schema fingerprint for each object type using a different strategy:- Tables — the fingerprint is a SHA-256 hex digest of a concatenated string built from
sys.columns: column ID, name, type name, max length, precision, scale, and nullability for every column in ordinal order. A column added, removed, renamed, or retyped produces a different fingerprint. - Views, stored procedures, and functions — the fingerprint is a SHA-256 hex digest of the full
OBJECT_DEFINITION()text. Any change to the definition text produces a different fingerprint. - Triggers — the fingerprint is a SHA-256 hex digest of the trigger’s
OBJECT_DEFINITION()text, resolved throughsys.triggersjoined to its parent object.
sys.* for the live fingerprint and compares it to SchemaFingerprint stored in SchemaInsights. When the fingerprints differ — or when the live object_id differs from the stored ObjectIdAtAnalysis — the row is archived and StaleArchived is returned. A small tolerance of 10 milliseconds is applied to modify_date comparisons to account for the rounding difference between SQL Server’s datetime type (1/300-second granularity) and the datetime2 storage column. The fingerprint is the authoritative staleness signal; modify_date serves as a secondary check when no fingerprint is present.
DDL audit path
TheDDL_Audit trigger fires at the database scope on DDL events (such as ALTER TABLE, DROP VIEW, CREATE PROCEDURE, and similar statements) and inserts one row per event into dbo.DDL_AuditLog. Each row records the event time, login, host, schema, object name, object type, event type, command text, and the full XML event data.
The InsightDdlProcessingQueue background service runs after every write operation (CreateTable, DropTable, InsertData, UpdateData, ExecuteSQL) and periodically scans for new DDL_AuditLog rows. It reads all rows whose ID is greater than the watermark stored in AIInsights.DdlChangeWatermark, archives the SchemaInsights rows for every affected object, then advances the watermark to the highest processed ID. This ensures that DDL changes made outside the MCP server — for example, an ALTER TABLE run directly in SSMS — are also detected and acted upon.
After archiving DDL-affected rows, the service runs an additional fingerprint scan across up to 500 cached rows to catch any changes that were not captured by the DDL trigger (such as schema modifications made before the trigger was installed). When INSIGHTS_AUTOPOPULATE is enabled, the service immediately rebuilds auto-mechanical baselines for every archived object.
RefreshInsights
CallingRefreshInsights manually triggers the full DDL backlog processing cycle synchronously and returns a summary of recent insights. This is useful when you want to force reconciliation without waiting for the background service, for example after a known schema migration or when InsightsCheck shows a large gap between lastProcessedAuditId and the current maximum DDL_AuditLog.ID.
RefreshInsights returns { recentInsights: [...], topQueryPatterns: [] }. The topQueryPatterns field is kept for response-shape compatibility and currently always returns an empty array.
RebuildBaselineInsights
RebuildBaselineInsights bulk-recreates auto-mechanical baselines for all objects that match the optional filters:
| Parameter | Type | Description |
|---|---|---|
schemaName | string? | Restrict to objects in this schema (e.g. "dbo") |
objectType | string? | Restrict to one object type (e.g. "Table", "View") |
take | int | Maximum number of objects to process; clamped to 1–2000 |
sys.objects, calls EnsureBaselineForObjectAsync for each, and returns a summary of how many baselines were created or refreshed. It does not overwrite rows that already have a non-mechanical insight (LlmModel != "auto-mechanical").