From Rows to Reasoning: Designing SQL Server Databases for AI Apps and Agents

---

The way applications consume your database is changing in a way that matters more than any query optimization you will do this year. Traditional applications were built for human users. They opened a screen, asked for specific data, got a row back, and displayed it. The access patterns were predictable: point lookups, range queries, filtered lists. You indexed for those patterns and performance was manageable.

AI agents are different consumers. They do not ask for a specific row. They ask questions. They need context, history, similar documents, and the ability to reason across large amounts of data in a single operation. An agent handling a customer support ticket does not just need the ticket record. It needs similar past tickets, the customer’s history, relevant knowledge base articles, and the outcome of previous resolutions, all retrieved in one pass and delivered to the LLM as context.

That is a fundamentally different workload from what most SQL Server databases were designed to serve. This article covers what changes about database design when the consumer is an AI agent, how SQL Server 2025 adds the capabilities to serve that workload natively, and the practical schema, indexing, and architecture patterns that make it work in production.

1 How AI Agents Consume Databases Differently Beginner

Bob Ward, Microsoft’s Principal Architect for SQL Server, framed the change clearly at the Dell Technologies World event in May 2026: “The key architecture for AI capabilities is integrating with things like embedding models for vector searching or chat completion models for AI agents. But we do it all inside the engine, the SQL Server Engine.”

That statement reflects a recognition that the database engine itself has to evolve to serve AI workloads, not just sit underneath them. To understand why, consider what an AI agent actually does when it processes a user request.

A traditional application asks: “Give me customer 12345.” One row, one lookup, done.

An AI agent asks: “Here is a customer support request. Find me the ten most relevant past tickets, the customer’s history, any related knowledge base articles, and the resolution patterns that worked for similar issues.” That is a semantic similarity search across potentially millions of records, combined with relational filtering, combined with context assembly, all in a single operation that needs to complete in under two seconds to maintain a responsive user experience.

The other major difference is scale of concurrent access. At Azure Cosmos DB Conf 2026, Guillermo Rauch described what he called “agent ergonomics”: when agents can deploy software and operate autonomously, the number of applications and operations explodes. Databases designed for hundreds of concurrent human users suddenly serve thousands of concurrent agent operations. The patterns that survive that scale are those with predictable per-operation cost.

2 What Your Database Needs to Support an AI Agent Beginner

Supporting an AI agent workload requires four capabilities that most SQL Server databases built before 2025 do not have natively:

• Vector storage and similarity search. Embeddings are numerical representations of meaning. To find documents semantically similar to a user’s question, you need a data type that stores high-dimensional float arrays and an index that can find nearest neighbors efficiently without scanning every row.
• Hybrid retrieval. Pure vector search is not always enough. You often need to combine semantic similarity with traditional SQL filters: find the ten most semantically similar support tickets, but only from this customer and only from the last 90 days. That requires vector search and relational filtering in the same query.
• Conversation history storage. LLMs are stateless. They have no memory between calls. The agent must supply recent conversation history as context with every API call. Your database stores that history and retrieves it efficiently on every turn of the conversation.
• Fast context assembly. The agent needs to retrieve all relevant context from multiple sources, assemble it into a structured prompt, and deliver it to the LLM within the response time budget. Schema and indexing decisions directly determine whether this is possible.

3 The VECTOR Data Type and DiskANN Index Intermediate

SQL Server 2025 introduces the VECTOR data type for storing high-dimensional float arrays. A vector embedding from a language model is a list of 768 to 3,072 floating-point numbers that encodes the semantic meaning of a piece of text. SQL Server 2025 supports vectors up to 10,000 dimensions, covering every current embedding model in production use.

-- Enable vector preview features (required in SQL Server 2025 preview)
ALTER DATABASE SCOPED CONFIGURATION
SET PREVIEW_FEATURES = ON;
GO

-- Create a knowledge base table with native vector support
CREATE TABLE dbo.KnowledgeBase (
    ArticleID       INT             IDENTITY(1,1) PRIMARY KEY,
    DocumentName    NVARCHAR(500)   NOT NULL,
    DocumentType    NVARCHAR(50)    NOT NULL,   -- 'ticket', 'article', 'policy', 'runbook'
    ChunkText       NVARCHAR(MAX)   NOT NULL,
    ChunkIndex      INT             NOT NULL,
    Embedding       VECTOR(1536)    NOT NULL,   -- 1536 dims for text-embedding-3-small
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    UpdatedAt       DATETIME2       NOT NULL DEFAULT SYSDATETIME()
);

-- Create a DiskANN vector index for approximate nearest neighbor search
-- DiskANN uses a graph-based structure stored on SSD rather than RAM
-- making it practical for large vector collections on standard hardware
CREATE VECTOR INDEX IX_KnowledgeBase_Embedding
ON dbo.KnowledgeBase (Embedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');
-- METRIC options: cosine (semantic similarity), euclidean, dot
-- cosine is the standard choice for text embeddings

DiskANN vs Exact Search: When to Use Each

SQL Server 2025 provides two vector search approaches. VECTOR_SEARCH uses the DiskANN index for approximate nearest neighbor (ANN) search, which is fast but may occasionally miss the absolute nearest neighbor. VECTOR_DISTANCE performs exact search by computing distance against every row, which is precise but does not scale to large tables.

Function	Method	Speed	Accuracy	Use When
VECTOR_SEARCH	DiskANN approximate	Fast at scale	Approximate (99%+ recall typical)	Production RAG pipelines, large tables
VECTOR_DISTANCE	Exact brute force	Slow on large tables	Exact	Small tables, evaluation, testing

-- VECTOR_SEARCH: fast approximate search using DiskANN index
-- This is the production approach for RAG pipelines

DECLARE @QueryEmbedding VECTOR(1536);
-- (set from your embedding API call in application code)

SELECT TOP 10
    ArticleID,
    DocumentName,
    DocumentType,
    ChunkText,
    VECTOR_DISTANCE('cosine', Embedding, @QueryEmbedding) AS SemanticDistance
FROM VECTOR_SEARCH(
    TABLE dbo.KnowledgeBase,
    COLUMN Embedding,
    TOP_N => 10,
    VECTOR => @QueryEmbedding,
    METRIC => 'cosine'
)
ORDER BY SemanticDistance ASC;

-- VECTOR_DISTANCE: exact search for small tables or testing
SELECT TOP 10
    ArticleID,
    ChunkText,
    VECTOR_DISTANCE('cosine', Embedding, @QueryEmbedding) AS SemanticDistance
FROM dbo.KnowledgeBase
ORDER BY SemanticDistance ASC;

4 Embedding Generation and Text Chunking in T-SQL Intermediate

SQL Server 2025 can generate vector embeddings directly from T-SQL by calling external AI model endpoints including Azure OpenAI, OpenAI, and local models via Ollama and KServe. This means the embedding pipeline can live entirely inside the database without requiring Python middleware or external ETL processes.

-- SQL Server 2025: generate embeddings directly in T-SQL
-- Configure an external model endpoint first

-- Create an external model reference (Azure OpenAI example)
CREATE EXTERNAL MODEL AzureOpenAIEmbedding
    WITH (
        LOCATION = 'https://your-resource.openai.azure.com/',
        API_KEY  = 'your-api-key',
        MODEL    = 'text-embedding-3-small'
    );
GO

-- Generate embeddings inline with INSERT
-- SQL Server calls the embedding API and stores the result
INSERT INTO dbo.KnowledgeBase (DocumentName, DocumentType, ChunkText, ChunkIndex, Embedding)
SELECT
    'DBA Runbook v2.3',
    'runbook',
    chunk_text,
    chunk_index,
    AI_GENERATE_EMBEDDINGS(chunk_text USING AzureOpenAIEmbedding)
FROM dbo.DocumentChunks
WHERE DocumentName = 'DBA Runbook v2.3';

-- SQL Server 2025 also provides native text chunking
-- so you can go from raw document text to chunks to embeddings in T-SQL
SELECT
    DocumentName,
    chunk_ordinal,
    chunk_text,
    AI_GENERATE_EMBEDDINGS(chunk_text USING AzureOpenAIEmbedding) AS Embedding
FROM dbo.RawDocuments
CROSS APPLY AI_CHUNK_TEXT(
    DocumentText,
    CHUNK_SIZE    => 500,
    CHUNK_OVERLAP => 50
) AS chunks;

5 Hybrid Search: Vector Plus Traditional SQL Combined Intermediate

Pure vector search finds semantically similar content. But in most production AI applications you need more than semantic similarity. You need semantic similarity within a specific scope: only this customer’s tickets, only articles published in the last year, only documents the current user has permission to see. Hybrid search combines vector similarity with standard SQL predicates in the same query.

This is where SQL Server 2025’s approach is significantly more powerful than standalone vector databases. Because vector search and relational filtering run in the same query optimizer, SQL Server can apply scalar filters before the vector search to reduce the search space, rather than applying them after and discarding results.

-- Hybrid search: semantic similarity + relational filters in one query
-- Find the 5 most relevant support tickets for this customer from the last 90 days

DECLARE @QueryEmbedding   VECTOR(1536);
DECLARE @CustomerID       INT = 12345;
DECLARE @DaysBack         INT = 90;

SELECT TOP 5
    t.TicketID,
    t.Subject,
    t.Resolution,
    t.CreatedAt,
    VECTOR_DISTANCE('cosine', t.Embedding, @QueryEmbedding) AS Relevance
FROM VECTOR_SEARCH(
    TABLE dbo.SupportTickets,
    COLUMN Embedding,
    TOP_N  => 20,          -- search wider, then filter down
    VECTOR => @QueryEmbedding,
    METRIC => 'cosine'
) AS vs
JOIN dbo.SupportTickets t ON t.TicketID = vs.TicketID
WHERE t.CustomerID = @CustomerID
AND   t.CreatedAt  >= DATEADD(DAY, -@DaysBack, GETDATE())
AND   t.Status     = 'Resolved'
ORDER BY Relevance ASC;

-- Hybrid search combining vector similarity + full-text search
-- Useful when some queries are keyword-based and others are semantic
SELECT TOP 10
    k.ArticleID,
    k.ChunkText,
    VECTOR_DISTANCE('cosine', k.Embedding, @QueryEmbedding) AS VectorScore,
    ft.Rank                                                  AS KeywordScore
FROM VECTOR_SEARCH(
    TABLE dbo.KnowledgeBase,
    COLUMN Embedding,
    TOP_N  => 50,
    VECTOR => @QueryEmbedding,
    METRIC => 'cosine'
) AS vs
JOIN dbo.KnowledgeBase k  ON k.ArticleID = vs.ArticleID
LEFT JOIN CONTAINSTABLE(dbo.KnowledgeBase, ChunkText, @KeywordQuery) ft
    ON ft.[KEY] = k.ArticleID
ORDER BY
    -- Weighted combination of vector score and keyword rank
    (VECTOR_DISTANCE('cosine', k.Embedding, @QueryEmbedding) * 0.7)
    + (ISNULL(1.0 / NULLIF(ft.Rank, 0), 1.0) * 0.3)
ASC;

6 Model Integration Inside the Engine Intermediate

SQL Server 2025 integrates with AI model endpoints for more than just embedding generation. You can call chat completion models directly from T-SQL for text generation, classification, and summarization tasks that run as part of a SQL query or stored procedure. This capability connects to Azure OpenAI, OpenAI, Ollama for local models, and KServe for on-premises model serving.

-- Call a chat completion model directly from T-SQL
-- Useful for classification, summarization, or extraction tasks
-- that run on existing table data without leaving SQL Server

-- Example: classify support ticket severity using an LLM
CREATE EXTERNAL MODEL ChatCompletionModel
    WITH (
        LOCATION = 'https://your-resource.openai.azure.com/',
        API_KEY  = 'your-api-key',
        MODEL    = 'gpt-4o-mini'   -- use a fast, cost-efficient model for batch tasks
    );
GO

-- Classify ticket severity in bulk using the LLM
SELECT
    TicketID,
    Subject,
    AI_COMPLETE(
        CONCAT(
            'Classify the severity of this support ticket as Critical, High, Medium, or Low. ',
            'Return only the single word. Ticket: ', Subject, '. Description: ', Description
        )
        USING ChatCompletionModel
    ) AS ClassifiedSeverity
FROM dbo.SupportTickets
WHERE ClassifiedSeverity IS NULL
AND   CreatedAt >= DATEADD(DAY, -1, GETDATE());

-- Role-based access control for model usage
-- DBAs can control which database principals can invoke which models
GRANT EXECUTE ON EXTERNAL MODEL::ChatCompletionModel TO [ReportingUser];
DENY  EXECUTE ON EXTERNAL MODEL::ChatCompletionModel TO [ReadOnlyUser];

7 Designing the Knowledge Base Table Intermediate

The knowledge base is the retrieval foundation of any RAG system. It stores the document chunks, their embeddings, and the metadata needed for hybrid filtering. Schema design here directly determines retrieval quality and query performance.

-- Production knowledge base schema for a SQL Server AI application
-- Designed for efficient hybrid search: vector + relational filters

CREATE TABLE dbo.KnowledgeBase (
    -- Identity and source tracking
    ChunkID         BIGINT          IDENTITY(1,1) PRIMARY KEY,
    DocumentID      INT             NOT NULL,
    DocumentName    NVARCHAR(500)   NOT NULL,
    DocumentType    NVARCHAR(50)    NOT NULL,
    -- 'runbook', 'policy', 'incident', 'ticket', 'schema', 'procedure'

    -- Chunking metadata
    ChunkIndex      INT             NOT NULL,   -- position within source document
    ChunkText       NVARCHAR(MAX)   NOT NULL,
    TokenCount      INT             NULL,       -- track token usage for cost management

    -- Vector embedding
    Embedding       VECTOR(1536)    NOT NULL,

    -- Filtering metadata (used in hybrid search WHERE clauses)
    CategoryID      INT             NULL,       -- document category for scoped search
    TeamID          INT             NULL,       -- team ownership for access scoping
    IsActive        BIT             NOT NULL DEFAULT 1,
    EffectiveFrom   DATE            NULL,
    EffectiveTo     DATE            NULL,

    -- Audit
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    UpdatedAt       DATETIME2       NOT NULL DEFAULT SYSDATETIME()
);

-- DiskANN vector index for semantic search
CREATE VECTOR INDEX IX_KB_Embedding
ON dbo.KnowledgeBase (Embedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');

-- Covering index for the most common hybrid filter patterns
-- DocumentType + IsActive are almost always in the WHERE clause for scoped RAG
CREATE NONCLUSTERED INDEX IX_KB_Type_Active
ON dbo.KnowledgeBase (DocumentType, IsActive, TeamID)
INCLUDE (ChunkText, DocumentName, ChunkIndex);

-- Index for freshness filtering (date-scoped retrieval)
CREATE NONCLUSTERED INDEX IX_KB_Dates
ON dbo.KnowledgeBase (EffectiveFrom, EffectiveTo, IsActive)
WHERE IsActive = 1;

Chunking Strategy Matters as Much as the Schema

How you split documents into chunks directly affects retrieval quality. Chunks too small lose context. Chunks too large dilute relevance and waste the LLM context window. The right chunk size depends on your document type and retrieval pattern.

Document Type	Recommended Chunk Size	Overlap	Why
Support tickets	Whole ticket (no chunking)	None	Tickets are short and self-contained. Chunking loses the resolution context.
Policy documents	200 to 400 tokens per chunk	50 tokens	Policies have section-level meaning. Overlap prevents split sentences losing context.
Technical runbooks	300 to 500 tokens per chunk	75 tokens	Step sequences need enough context to be actionable. Too small loses procedure continuity.
SQL scripts and code	By logical unit (procedure, function)	None	Code chunks should map to complete executable units, not arbitrary token counts.
Long technical articles	400 to 600 tokens per chunk	100 tokens	Long articles need larger chunks to preserve the argument being made in each section.

8 Agent Memory: Storing Conversation History and Context Intermediate

LLMs have no memory between API calls. Every call starts from zero. The agent is responsible for supplying relevant history as context with each request. Your database stores that history and retrieves it efficiently on every conversation turn.

At Cosmos DB Conf 2026, Chander Dhall demonstrated that memory strategy directly affects cost, recall quality, and user experience, sometimes by a 20x difference in token cost between a poorly designed and well-designed memory implementation. The principle translates directly to SQL Server: how you store and retrieve conversation history determines how expensive and how effective your AI agent is.

-- Conversation history table for AI agent memory
-- Partitioned by SessionID for efficient per-session retrieval

CREATE TABLE dbo.ConversationHistory (
    MessageID       BIGINT          IDENTITY(1,1) PRIMARY KEY,
    SessionID       UNIQUEIDENTIFIER NOT NULL,    -- one session per user conversation
    UserID          INT             NOT NULL,
    Role            NVARCHAR(20)    NOT NULL,     -- 'user', 'assistant', 'system'
    MessageText     NVARCHAR(MAX)   NOT NULL,
    TokenCount      INT             NULL,         -- track token cost per message
    MessageOrder    INT             NOT NULL,     -- sequence within the session
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSDATETIME(),

    -- Optional: semantic embedding for memory-augmented retrieval
    -- Store embeddings on assistant messages to enable semantic recall
    -- of past conversations beyond the sliding window
    MessageEmbedding VECTOR(1536)   NULL
);

-- Index for the most common access pattern: get recent messages for a session
CREATE NONCLUSTERED INDEX IX_Chat_Session_Order
ON dbo.ConversationHistory (SessionID, MessageOrder DESC)
INCLUDE (Role, MessageText, TokenCount);

-- Index for user-level history queries
CREATE NONCLUSTERED INDEX IX_Chat_UserID_Time
ON dbo.ConversationHistory (UserID, CreatedAt DESC)
INCLUDE (SessionID, Role, MessageText);

-- Retrieve the sliding window of recent messages for a session
-- Used on every agent turn to supply recent context to the LLM
DECLARE @SessionID  UNIQUEIDENTIFIER = '...';
DECLARE @WindowSize INT = 20;  -- last 20 messages

SELECT TOP (@WindowSize)
    Role,
    MessageText,
    TokenCount,
    MessageOrder
FROM dbo.ConversationHistory
WHERE SessionID = @SessionID
ORDER BY MessageOrder DESC;

Three Memory Patterns and When to Use Each

• Sliding window memory. The simplest approach. Supply the last N messages as context on every call. Fast, predictable token cost, easy to implement. Limitation: loses earlier context when the window slides past it. Best for short, focused conversations.
• Hierarchical memory tiers. Recent messages in full detail (last 10 turns), older messages as summaries (LLM-generated compression), semantic recall of relevant past exchanges using vector similarity. More complex but handles long multi-session conversations where earlier context remains relevant.
• Entity memory. Extract key facts from conversations (customer preferences, confirmed decisions, established context) and store them as structured records. The agent loads relevant entity records alongside recent history. Most powerful for long-running relationships between a user and an agent.

9 Partitioning and Indexing Strategy for AI Workloads Advanced

AI agent workloads create query patterns that standard OLTP index strategies do not anticipate. Knowing what is different helps you design indexes that serve both workloads without creating unnecessary overhead.

What AI Workloads Do to Your Query Patterns

• High-frequency semantic search against the knowledge base, often many requests per second from concurrent agent sessions
• High-frequency short reads on conversation history, one read per conversation turn, highly selective by SessionID
• Periodic bulk writes when documents are ingested or updated and embeddings regenerated
• Occasional large analytical queries when the AI agent needs to reason across aggregate data rather than individual records

-- Partition the knowledge base by DocumentType for large deployments
-- Allows vector searches to be scoped to a partition automatically
-- when DocumentType appears in the WHERE clause

CREATE PARTITION FUNCTION pf_DocumentType (NVARCHAR(50))
AS RANGE RIGHT FOR VALUES ('incident', 'policy', 'runbook', 'ticket');

CREATE PARTITION SCHEME ps_DocumentType
AS PARTITION pf_DocumentType ALL TO ([PRIMARY]);

-- Partitioned knowledge base
CREATE TABLE dbo.KnowledgeBase_Partitioned (
    ChunkID         BIGINT          IDENTITY(1,1),
    DocumentType    NVARCHAR(50)    NOT NULL,
    ChunkText       NVARCHAR(MAX)   NOT NULL,
    Embedding       VECTOR(1536)    NOT NULL,
    IsActive        BIT             NOT NULL DEFAULT 1,
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT PK_KB_Partitioned PRIMARY KEY (ChunkID, DocumentType)
) ON ps_DocumentType (DocumentType);

-- Conversation history: partition by date for efficient archival
-- Active sessions on current partition, older sessions on archive partitions
CREATE PARTITION FUNCTION pf_ConvDate (DATETIME2)
AS RANGE RIGHT FOR VALUES (
    '2026-01-01', '2026-04-01', '2026-07-01', '2026-10-01'
);

-- Monitor vector index usage and health
-- DiskANN indexes need periodic maintenance as data grows
SELECT
    i.name                              AS IndexName,
    i.type_desc                         AS IndexType,
    ps.row_count,
    ps.reserved_page_count * 8 / 1024  AS ReservedMB,
    ius.user_seeks,
    ius.user_scans,
    ius.last_user_seek
FROM sys.indexes                i
JOIN sys.dm_db_partition_stats  ps  ON ps.object_id = i.object_id
                                   AND ps.index_id  = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON ius.object_id = i.object_id
    AND ius.index_id = i.index_id
WHERE OBJECT_NAME(i.object_id) IN ('KnowledgeBase', 'ConversationHistory')
ORDER BY ps.reserved_page_count DESC;

10 The Agent Memory Fabric Pattern Advanced

One of the most significant findings from production AI deployments in 2026 is that maintaining multiple separate systems for different data types (a relational database, a vector database, a cache, and a coordination layer) creates substantial operational complexity and cost. At Cosmos DB Conf 2026, Farah Abdou from SmartServe described replacing a four-system AI data stack with a single unified database fabric, cutting costs by 73% and latency by 65%.

The same pattern applies in SQL Server environments. Instead of maintaining SQL Server for relational data, a separate vector database (Pinecone, Chroma, Weaviate) for embeddings, and Redis for session caching, a SQL Server 2025 deployment can consolidate all three into a single database with appropriate table design.

-- The unified agent data layer in SQL Server 2025
-- One database serves relational data, vector search, and session state

-- Relational operational data (unchanged from traditional design)
-- dbo.Customers, dbo.Orders, dbo.SupportTickets etc.

-- Vector knowledge base (semantic retrieval)
-- dbo.KnowledgeBase with VECTOR column and DiskANN index

-- Conversation history (agent working memory)
-- dbo.ConversationHistory with session-scoped indexes

-- Semantic cache (avoid re-calling LLM for identical or near-identical questions)
CREATE TABLE dbo.SemanticCache (
    CacheID         BIGINT          IDENTITY(1,1) PRIMARY KEY,
    QueryEmbedding  VECTOR(1536)    NOT NULL,
    QueryText       NVARCHAR(MAX)   NOT NULL,
    CachedResponse  NVARCHAR(MAX)   NOT NULL,
    HitCount        INT             NOT NULL DEFAULT 0,
    CreatedAt       DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    LastHitAt       DATETIME2       NULL,
    ExpiresAt       DATETIME2       NULL
);

CREATE VECTOR INDEX IX_SemanticCache_Embedding
ON dbo.SemanticCache (QueryEmbedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');

-- Check semantic cache before calling the LLM
-- Returns a cached response if a very similar question was asked before
DECLARE @QueryEmbedding   VECTOR(1536);
DECLARE @SimilarityThreshold FLOAT = 0.05;  -- cosine distance below this = cache hit

SELECT TOP 1
    CacheID,
    CachedResponse,
    VECTOR_DISTANCE('cosine', QueryEmbedding, @QueryEmbedding) AS Distance
FROM VECTOR_SEARCH(
    TABLE dbo.SemanticCache,
    COLUMN QueryEmbedding,
    TOP_N  => 1,
    VECTOR => @QueryEmbedding,
    METRIC => 'cosine'
)
WHERE VECTOR_DISTANCE('cosine', QueryEmbedding, @QueryEmbedding) < @SimilarityThreshold
AND   (ExpiresAt IS NULL OR ExpiresAt > SYSDATETIME());
-- If this returns a row: return cached response, skip LLM call
-- If no row: call LLM, then insert result into SemanticCache

11 Performance: What Changes About Query Tuning Advanced

AI agent workloads introduce query patterns that standard performance tuning approaches do not fully cover. Here is what is different and what to watch.

Vector Search Performance

DiskANN index performance degrades over time as more vectors are inserted and the graph structure becomes less optimal. Monitor vector index health and rebuild periodically, especially on knowledge bases with frequent updates. The rebuild threshold depends on data volume and update frequency but a monthly rebuild on active knowledge bases is a reasonable starting point.

-- Monitor DiskANN index fragmentation and performance
-- Vector indexes do not have the same fragmentation metrics as B-tree indexes
-- Monitor via query performance and search quality degradation

-- Track semantic search query performance over time
SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count  AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.execution_count,
    qs.last_execution_time,
    LEFT(qt.query_sql_text, 200)                AS query_text
FROM sys.query_store_query           q
JOIN sys.query_store_query_text      qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan            p  ON q.query_id      = p.query_id
JOIN sys.query_store_runtime_stats   qs ON p.plan_id       = qs.plan_id
WHERE qt.query_sql_text LIKE '%VECTOR_SEARCH%'
OR    qt.query_sql_text LIKE '%VECTOR_DISTANCE%'
ORDER BY avg_elapsed_ms DESC;

-- Set a cost threshold for parallelism consideration
-- Vector searches benefit from parallelism on large tables
-- but too much parallelism hurts concurrent agent workloads
EXEC sp_configure 'max degree of parallelism', 4;
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

Token Budget Management

Every embedding generation call and every LLM completion call has a token cost. At scale, uncontrolled token consumption becomes a significant operational expense. Track token counts in your schema and implement query-level controls.

-- Monitor token consumption across your AI workload
SELECT
    DocumentType,
    COUNT(*)            AS ChunkCount,
    SUM(TokenCount)     AS TotalTokens,
    AVG(TokenCount)     AS AvgTokensPerChunk
FROM dbo.KnowledgeBase
WHERE IsActive = 1
GROUP BY DocumentType
ORDER BY TotalTokens DESC;

-- Session-level token consumption tracking
SELECT
    UserID,
    SessionID,
    COUNT(*)            AS MessageCount,
    SUM(TokenCount)     AS TotalTokens,
    MIN(CreatedAt)      AS SessionStart,
    MAX(CreatedAt)      AS SessionEnd
FROM dbo.ConversationHistory
WHERE CreatedAt >= DATEADD(DAY, -7, GETDATE())
GROUP BY UserID, SessionID
ORDER BY TotalTokens DESC;

12 Security and Governance for AI Database Access Intermediate

AI agents accessing your database through SQL Server create new security considerations on top of the standard access control model. The agent may have legitimate read access to the knowledge base but should not be able to read conversation history from other users or invoke expensive model calls without authorization.

-- Row-level security for multi-tenant AI applications
-- Each user sees only their own conversation history
-- The AI agent running as a service account sees all sessions it manages

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_ConversationFilter (@UserID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
    SELECT 1 AS fn_result
    WHERE @UserID = CAST(SESSION_CONTEXT(N'CurrentUserID') AS INT)
    OR IS_MEMBER('db_datareader') = 1;   -- admin role sees all
GO

CREATE SECURITY POLICY ConversationHistoryPolicy
ADD FILTER PREDICATE Security.fn_ConversationFilter(UserID)
ON dbo.ConversationHistory
WITH (STATE = ON, SCHEMABINDING = ON);

-- Dedicated low-privilege login for the AI agent service
CREATE LOGIN ai_agent_service WITH PASSWORD = 'StrongPassword2026!';
USE YourDatabase;
CREATE USER ai_agent_service FOR LOGIN ai_agent_service;

-- Grant minimum necessary permissions
GRANT SELECT ON dbo.KnowledgeBase       TO ai_agent_service;
GRANT SELECT, INSERT ON dbo.ConversationHistory TO ai_agent_service;
GRANT SELECT, INSERT, UPDATE ON dbo.SemanticCache TO ai_agent_service;
GRANT EXECUTE ON EXTERNAL MODEL::AzureOpenAIEmbedding TO ai_agent_service;

-- Deny access to operational tables
DENY SELECT ON dbo.Customers    TO ai_agent_service;
DENY SELECT ON dbo.PaymentData  TO ai_agent_service;

-- Audit all model invocations
CREATE SERVER AUDIT AI_Model_Audit
TO FILE (FILEPATH = 'D:\Audit\AIModelAudit\');

CREATE DATABASE AUDIT SPECIFICATION AI_Model_Spec
FOR SERVER AUDIT AI_Model_Audit
ADD (EXECUTE ON EXTERNAL MODEL::AzureOpenAIEmbedding BY ai_agent_service),
ADD (EXECUTE ON EXTERNAL MODEL::ChatCompletionModel  BY ai_agent_service)
WITH (STATE = ON);

13 The DBA Role in AI Application Architecture Beginner

The shift from rows to reasoning does not reduce the DBA’s role. It expands it into territory that application developers and ML engineers do not naturally own.

The patterns in this article, knowledge base design, chunking strategy, vector index management, conversation history storage, semantic caching, token cost tracking, row-level security for AI workloads, and model governance, are all data engineering and database administration problems. They require the same skills that make DBAs valuable for traditional workloads: understanding access patterns, designing schemas for performance, implementing security at the data layer, and monitoring for problems before users feel them.

What is new is that the workload consumer is an AI agent rather than a human application, and the query patterns include semantic similarity search alongside traditional SQL. SQL Server 2025 provides the engine capabilities. The DBA provides the architecture, governance, and operational discipline that makes those capabilities work reliably in production.

References

• Microsoft Docs: Vector Support in SQL Server 2025
• Microsoft Docs: CREATE VECTOR INDEX (DiskANN)
• Microsoft SQL Server Blog: Announcing SQL Server 2025 Preview
• SiliconANGLE: SQL Server 2025 Brings AI to the Data at Dell (Bob Ward, May 2026)
• Microsoft Developer Blog: Azure Cosmos DB Conf 2026 Recap: Lessons from Production
• Microsoft Azure Blog: Key Trends from Cosmos DB Conf 2026
• Microsoft Docs: Azure Cosmos DB Unified AI Database Overview
• Aleson ITC: Vector Search in SQL Server 2025 with DiskANN
• Microsoft Q&A: SQL Server 2025 Vector Database Benchmarking
• SQLYARD: What Every Senior DBA Needs to Know About LLMs, RAG, Agents, and MCP
• SQLYARD: MCP and SQL Server: What Every DBA Needs to Know
• SQLYARD: SQL Server Index Tuning: From Basics to AI-Assisted Recommendations