Call a Single Stored Procedure

The application layer must call a single stored procedure that represents the AI system. Here is an example in .NET / C#:

public async Task<DataTable> ProcessQuery(string userQuestion)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        await conn.OpenAsync();
        using (SqlCommand cmd = new SqlCommand(
            "dbo.usp_ProcessUserQuery", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserQuestion", userQuestion);
            using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
            {
                DataTable result = new DataTable();
                result.Load(reader);
                return result;
            }
        }
    }
}

This guarantees no direct SQL execution, centralized control, enforced validation, and consistent behavior across every request.

Schema Isolation and CI/CD Pipeline

Your deployment must include the QueryHistory table, the ApprovedQueryLibrary table, the normalization function, and the orchestration stored procedure. Isolate all AI objects in their own schema:

CREATE SCHEMA ai;
GO

This ensures separation of concerns, easier management, and improved security. Production systems must also be deployed through automation — never manually:

This prevents environment drift, ensures consistency, and supports rollback. Maintain separate Development, QA, and Production environments to validate changes safely before they reach users.

Indexing, Partitioning, and Caching

Index the query library on the columns most frequently used in retrieval:

CREATE NONCLUSTERED INDEX IX_AI_QueryLibrary
ON dbo.ApprovedQueryLibrary (Tags, ExecutionCount);

Partition QueryHistory by date to prevent large table scans as history grows. For connection management, use connection pooling in the application layer, limit concurrent requests, and monitor waits and blocking.

Caching Strategy

Caching reduces database load, improves response speed, and is essential for scaling beyond a small user base.

Track What Matters

Monitoring is required for production systems. Use this query to surface the most important performance signals directly from your history table:

SELECT
    NormalizedQuestion,
    COUNT(*)                       AS UsageCount,
    AVG(ExecutionDurationMs)       AS AvgDuration,
    AVG(CAST(Success AS FLOAT))    AS SuccessRate
FROM dbo.QueryHistory
GROUP BY NormalizedQuestion
ORDER BY UsageCount DESC;

SQL Server tools for deeper investigation include Query Store, Dynamic Management Views (DMVs), and Extended Events.

Required Dashboard Panels

Use this query to power your top queries panel:

SELECT TOP 10
    NormalizedQuestion,
    COUNT(*)               AS UsageCount,
    AVG(ExecutionDurationMs) AS AvgDuration
FROM dbo.QueryHistory
GROUP BY NormalizedQuestion
ORDER BY UsageCount DESC;

Suitable tools include Power BI (native SQL Server connector), Azure Monitor, or a custom dashboard built on your existing stack.

Enforce at the Engine, Not Just the Application

Required validation rules: allow only SELECT queries, block system object access, enforce row limits, and validate all inputs before execution.

IF @sql NOT LIKE 'SELECT%'
BEGIN
    RAISERROR('Only SELECT statements allowed', 16, 1);
    RETURN;
END;

Wrap all execution in structured error handling so failures are logged, not silently dropped:

BEGIN TRY
    EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
    INSERT INTO dbo.QueryHistory
    (
        UserQuestion,
        NormalizedQuestion,
        GeneratedSQL,
        Success,
        ErrorMessage
    )
    VALUES
    (
        @question,
        @normalized,
        @sql,
        0,
        ERROR_MESSAGE()
    );
END CATCH;

Understand System Behavior Over Time

Observability goes beyond logging. It answers the questions that matter in production:

• Which queries are used most — and which are never used?
• Which question types fail most often?
• Which patterns are getting slower over time?
• How is usage evolving week over week?

Without observability, you are flying blind. Problems accumulate silently until users report them — and by then, trust has already eroded. See Microsoft’s observability guidance for a full framework.

The System Improves Through Usage, Not Retraining

Scale Into Vector-Based Matching

As usage grows and keyword matching reaches its limits, extend the system with semantic similarity:

• Introduce embeddings for vector-based question matching
• Enable semantic similarity alongside keyword retrieval
• Combine keyword filtering and vector scoring in a hybrid approach

This upgrade does not replace the existing architecture — it extends it. The keyword retrieval layer remains as the first-pass filter, and vector scoring refines the results. See Microsoft’s vector search overview for implementation options.