Operational Analytics, Hybrid Workloads, Resource Isolation, and When to Use a Warehouse

Learning outcomes

ServiceHub wants dashboards that are seconds old, but the same database handles dispatch writes, technician assignment and customer status changes. Adding a nonclustered columnstore can make analytics much cheaper than repeated rowstore scans, yet it also adds write maintenance, memory/CPU demand and concurrency competition. Hybrid Transactional/Analytical Processing (HTAP) is therefore an operating model, not merely “create NCCI.” This lesson builds acceptance criteria for co-locating analytics and for deciding when a dedicated warehouse/lakehouse is the safer architecture.

1. HTAP begins with a shared consistency requirement

Operational analytics is attractive when users need current transactional state and the analytical query can safely share the OLTP engine. NCCI lets the base rowstore preserve transactional indexes while a columnstore copy serves wide scans. The data is transactionally maintained with the base table, so the analytical representation is not an asynchronously replicated cache. That consistency benefit must be weighed against extra storage, log volume and write CPU.

SET STATISTICS IO ON;SET STATISTICS TIME ON;GOSELECT region_code,status,       COUNT_BIG(*) AS orders,       SUM(labor_minutes) AS labor_minutes,       SUM(amount) AS amountFROM lab19.LiveWorkOrderWHERE opened_at >= '2026-03-01'GROUP BY region_code,statusORDER BY region_code,status;GOSET STATISTICS IO OFF;SET STATISTICS TIME OFF;GO

Capture the actual plan, segment IO and elapsed/CPU on your own machine. Then record the same query after disabling the NCCI in a disposable test only, or use an isolated clone, to quantify whether the index changes the plan/work materially. Do not publish a single laptop timing as a universal speedup.

2. Every analytical copy creates a write tax

An NCCI must be maintained as base rows are inserted, updated and deleted. Frequent status/labor/amount changes affect the analytical index columns and can create delta/delete pressure. The correct question is not “does the dashboard run faster?” but “does dashboard benefit exceed additional write latency, log, storage, memory and maintenance under peak transactional concurrency?”

-- Run in a disposable test window and compare with your own baseline.DECLARE @start datetime2(3)=SYSUTCDATETIME();BEGIN TRAN;UPDATE lab19.LiveWorkOrderSET labor_minutes=labor_minutes+1,    amount=amount+0.50WHERE work_order_id BETWEEN 2005000 AND 2014999;COMMIT;SELECT DATEDIFF(millisecond,@start,SYSUTCDATETIME()) AS local_elapsed_ms;GOSELECT row_group_id,state_desc,total_rows,deleted_rows,trim_reason_descFROM sys.dm_db_column_store_row_group_physical_statsWHERE object_id=OBJECT_ID(N'lab19.LiveWorkOrder')ORDER BY row_group_id;GO

The elapsed value is explicitly a local observation. Repeat under representative concurrency/cache/storage conditions, capture transaction-log growth and waits, and compare against the same workload in a controlled clone without the NCCI. One test run cannot establish causation or production capacity.

3. Resource isolation can protect OLTP, but it cannot create capacity

Resource Governor classifies incoming sessions into workload groups/resource pools and can govern CPU/memory and related resources. SQL Server 2025 makes Resource Governor available in Standard and Standard Developer as well as Enterprise; Express remains unsupported. This widens the ability to isolate reporting sessions in Standard production, but governance only allocates finite resources—it does not make an overloaded server large enough.

SELECT CAST(SERVERPROPERTY('Edition') AS nvarchar(128)) AS edition,       CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128)) AS product_version;SELECT is_enabled FROM sys.resource_governor_configuration;SELECT pool_id,name,min_cpu_percent,max_cpu_percent,       min_memory_percent,max_memory_percentFROM sys.resource_governor_resource_pools;SELECT group_id,name,pool_id,importance,request_max_memory_grant_percentFROM sys.resource_governor_workload_groups;GO

The mandatory lab is inspection-only because a classifier/resource-pool change affects instance-wide workload admission. In a dedicated test instance, you can classify reporting logins/application names into a constrained group, then validate both OLTP latency and dashboard SLA before adopting it.

4. Readable secondaries move reads, but also change consistency/operability

An Enterprise Availability Group readable secondary can offload analytical reads, but Chapter 16 established the boundaries: secondary data can lag while redo catches up, read-only routing requires a listener/routing list and ApplicationIntent=ReadOnly, and a readable secondary is not a zero-lag cache. Standard Basic AG does not provide readable-secondary scale-out. If your dashboard requires read-your-own-write semantics immediately after a transaction, routing it to an asynchronous secondary can violate the product requirement even if the query is fast.

5. Define the warehouse/lakehouse exit criteria before the incident

A dedicated analytical system becomes compelling when the analytical data model diverges from OLTP, retention greatly exceeds operational needs, joins span multiple source systems, scans compete persistently with customer transactions, report SLAs require independent scaling, or transformation/history semantics require a curated fact/dimension/lake model. Moving analytics also creates new contracts: change capture, replay, retention, schema evolution and reconciliation from Chapter 18 become first-class.

DROP TABLE IF EXISTS lab19.AnalyticsDecision;GOCREATE TABLE lab19.AnalyticsDecision(  decision_id int IDENTITY PRIMARY KEY,  criterion varchar(80) NOT NULL,  observed_value nvarchar(200) NOT NULL,  threshold_or_requirement nvarchar(200) NOT NULL,  disposition varchar(40) NOT NULL,  recorded_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME());INSERT lab19.AnalyticsDecision(criterion,observed_value,threshold_or_requirement,disposition)VALUES('freshness','dashboard requires current committed status','< 5 seconds','keep candidate on primary/NCCI'),('cross-source joins','none today','if CRM + telemetry joins become mandatory','warehouse trigger'),('OLTP interference','must be measured under peak load','p95 dispatch SLA cannot regress','test before rollout'),('retention','12 months operational','multi-year curated history','warehouse trigger');SELECT * FROM lab19.AnalyticsDecision ORDER BY decision_id;GO

The example values are architectural placeholders to demonstrate a decision register, not measurements of your production system. Replace them with measured latency/resource evidence and business requirements. A clear exit criterion prevents the team from trying to “tune harder” after the shared engine is already a bottleneck.

6. Chapter cleanup and production checklist

USE ServiceHubLab;GODROP TABLE IF EXISTS lab19.AnalyticsDecision;DROP TABLE IF EXISTS lab19.LiveWorkOrder;DROP TABLE IF EXISTS lab19.WorkOrderFact;GOIF SCHEMA_ID(N'lab19') IS NOT NULLAND NOT EXISTS(  SELECT 1 FROM sys.objects WHERE schema_id=SCHEMA_ID(N'lab19'))  DROP SCHEMA lab19;GO

The cleanup does not touch the established ops course schema, Query Store, HA settings, Resource Governor, or instance configuration. Production adoption should record engine build/edition, compatibility level, dataset and rowgroup profile, query mix, write rate, peak concurrency, MAXDOP, memory, Query Store baseline, storage/tempdb behavior, maintenance plan, rollback and the criteria that would trigger migration to a separate analytical platform.

7. Production judgment and bridge

Keep analytics on the transactional system only when freshness, consistency and operational simplicity outweigh the resource coupling and you can prove transactional SLAs remain healthy. Use NCCI/ordered NCCI, workload governance and readable secondaries as tools with explicit boundaries—not as excuses to keep unlimited BI beside OLTP. Chapter 20 moves to another specialized engine: In-Memory OLTP, where memory-optimized tables, checkpoint files and optimistic concurrency require an entirely different durability/index model from “ordinary tables cached in RAM.”

Authoritative references

• Get started with columnstore for real-time operational analytics
• Columnstore indexes - Design guidance
• SQL Server 2025 editions and supported features
• What's new in SQL Server 2025
• Resource Governor