Performance and cost of AI features

By the end of the prototype, Ask Finch was useful, and Elena and the finance team asked the two questions every useful system eventually meets: how fast is it, and what will it cost when 23 stores use it every day? AI features have an unusual cost profile. The database work is modest and predictable; the model calls are where most of the time and most of the money go, and both depend on design choices that are easy to change early and hard to change later.

Where the time goes

Hana instrumented the answer procedure to record the duration of each step for every request, using SYSDATETIME before and after each part and writing the timings to the request log. Over 400 test requests, the picture was clear.

The chat call is about 80 in 100 of the median total of about 2 seconds, and nearly all of the slow tail. Database steps are small, and the largest of them, exact search over products, is the obvious candidate for a vector index if the catalog grows. Two cheap changes helped. The two searches do not depend on each other, so the application calls them in parallel; and the staff app shows the search results as soon as they arrive and fills in the written answer when it comes, so the associate is never looking at an empty screen. Nikolai's question about who reads the result and how fast they need it decided that layout: an associate with a customer needs something on screen within half a second.

Seeing model waits in the database

From the database's side, a session waiting for a model shows the HTTP_EXTERNAL_CONNECTION wait type. Elena added it to the wait statistics dashboard she built in MSAZ-281. A rise in that wait with no rise in request volume means the model service has slowed or is throttling; a rise in CPU or in PAGEIOLATCH waits means the database side has a problem, such as an exact vector search reading more data. Separating the two saved an argument in the first week of testing, when slow answers turned out to be throttling on a shared test deployment rather than the database.

-- Requests currently waiting on outbound calls
SELECT r.session_id, r.wait_type, r.wait_time AS wait_ms, r.cpu_time, t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.wait_type = 'HTTP_EXTERNAL_CONNECTION';

-- Cumulative waits since the last reset (Azure SQL Database)
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_db_wait_stats
WHERE wait_type IN ('HTTP_EXTERNAL_CONNECTION', 'SOS_SCHEDULER_YIELD', 'PAGEIOLATCH_SH')
ORDER BY wait_time_ms DESC;

Throughput and quotas

Model deployments have quotas measured in tokens per minute and requests per minute. A request over the quota returns HTTP 429, which the procedure's retry setting absorbs if it is brief. Planning for the pilot meant estimating peak load. Each answer request uses about 2,300 input tokens and 120 output tokens. With 23 stores averaging 60 questions a day and the busiest hour carrying about 15 in 100 of the day's questions, the peak is modest.

That is well within a modest quota. The embedding job is the heavier user of quota, because a full re-embedding sends about 5 million tokens, as lesson 3 calculated, so it runs at night, in batches with pauses, on a separate deployment of the embedding model so it can never slow the daytime question traffic.

What it costs

Model charges are per token, with separate prices for input and output tokens, and output usually costs several times more per token than input. Prices change and differ by model and region, so the team worked with illustrative figures and a spreadsheet anyone can update: 0.02 US dollars per million tokens for the embedding model, and for the small chat model 0.40 US dollars per million input tokens and 1.60 per million output tokens.

stores, per_day, days = 23, 60, 30
requests = stores * per_day * days

in_tokens, out_tokens, q_embed_tokens = 2_300, 120, 15
price_in, price_out, price_embed = 0.40, 1.60, 0.02   # US dollars per million tokens (illustrative)

chat_in = requests * in_tokens / 1e6 * price_in
chat_out = requests * out_tokens / 1e6 * price_out
embed = requests * q_embed_tokens / 1e6 * price_embed
print(f"requests per month: {requests:,}")
print(f"chat input:  {chat_in:7.2f} dollars")
print(f"chat output: {chat_out:7.2f} dollars")
print(f"question embeddings: {embed:5.2f} dollars")
print(f"total: {chat_in + chat_out + embed:7.2f} dollars per month")

hit = 0.30   # share of questions answered from the cache
print(f"with {hit * 100:.0f} in 100 served from cache: "
      f"{(chat_in + chat_out) * (1 - hit) + embed:7.2f} dollars per month")

requests per month: 41,400
chat input:    38.09 dollars
chat output:    7.95 dollars
question embeddings:  0.01 dollars
total:   46.05 dollars per month
with 30 in 100 served from cache:   32.24 dollars per month

About 46 US dollars a month for model calls surprised the finance team, who had expected far more. The database is the larger cost: the catalog database for the pilot moves from serverless to a provisioned General Purpose 2-vCore setting, which costs far more than the tokens. The lesson for estimates is that, for an internal assistant at this scale, model charges are small, and they grow in direct proportion to tokens per request, so a careless prompt that doubles the context doubles the bill.

Caching

Many store questions repeat: which primer for a stained ceiling comes up in every store every week. Hana added a cache table keyed by a hash of the normalized question and the store, holding the answer, its sources and the time it was produced. Answers are reused for 24 hours, and the cache entry is invalidated early when any cited product's description, price or stock status changes, found by a join on the source identifiers. In testing with the managers' 400 questions, 31 in 100 were exact repeats after normalization, which matched the 30 in 100 assumed above.

SELECT TOP (1) c.Answer, c.SourcesJson
FROM askfinch.AnswerCache AS c
WHERE c.QuestionHash = HASHBYTES('SHA2_256', LOWER(TRIM(@question)))
  AND c.StoreId = @storeId
  AND c.CreatedAt > DATEADD(hour, -24, SYSUTCDATETIME())
  AND NOT EXISTS (SELECT 1 FROM OPENJSON(c.SourcesJson) WITH (src_id varchar(20)) AS s
                  JOIN catalog.ProductChange AS pc ON CONCAT('P', pc.ProductId) = s.src_id
                  WHERE pc.ChangedAt > c.CreatedAt);

Nikolai suggested a further step for later: caching by meaning rather than exact text, so how do I stop a mirror fogging and stop mirror steaming up share an entry, found by vector distance between question embeddings with a tight threshold. The team decided to wait for real usage data before adding it, because a semantic cache that matches too loosely returns the right answer to the wrong question.

Database-side tuning

On the database side, the usual tuning applies, with a few vector-specific points. Exact vector search reads every candidate vector, so it benefits from filters on indexed columns and from keeping vectors in a separate narrow table, as Hana's ProductEmbedding table does, rather than in the wide product table, so scans read fewer pages. Half-precision vectors halve the pages read; Hana's test of float16 on the product embeddings showed no change in top-three accuracy on the 60 test questions and cut exact search time from 150 to about 95 milliseconds. Vector indexes trade build time and memory for fast queries, and their build can be limited with MAXDOP so it does not starve other work. And Query Store, from MSAZ-281, records the search procedures' plans and durations like any other query, which is how regressions after a change get noticed.

Capacity for the database itself

The database tier still has to be sized. Hana measured the database work per request on the test database: about 180 milliseconds of CPU for the two searches with float32 exact search and about 120 with float16, plus a few milliseconds for packaging, checking and logging. At the peak estimate of 3.5 requests a minute, that is a tiny load. Elena's concern was elsewhere: the nightly embedding job and the 15-minute maintenance job, which call the model and write vectors, and any future exact search over a larger table. She sized the pilot database at 2 vCores in General Purpose, set alerts on CPU and on log write use from the baseline work in MSAZ-281, and noted that the first sign of needing more would be the search procedures' durations in Query Store, not the model waits.

There is also the question of where the cost of a failure lands. A procedure that times out after five seconds holds a worker for five seconds. With concurrent outbound calls capped at 15 in 100 of workers, a slow model deployment could, in theory, use up that allowance during a burst and cause error 10928. The short timeout, the single retry and the fallback to search results keep that from cascading: slow answers degrade to search-only rather than to errors.

Finally, costs need an owner. Hana tagged the model resource and its deployments with the cost center for store operations, so Grace's budget, not the IT budget, sees the token charges, and a budget alert in Azure Cost Management warns at 80 dollars a month, nearly twice the estimate, which would mean something unexpected is happening.

One cost is easy to forget: the people who review answers. The weekly review of 30 sampled answers takes a store manager about an hour, which at pilot scale costs more than the tokens. The team decided that was the right place to spend money during the pilot, because human review is what tells them whether the assistant is helping, and planned to reduce the sample once the not helpful rate settled below an agreed level.