Home / Advanced SQL / High-Performance OLTP Database Design / T-SQL / Designing High-Performance OLTP Databases: A Real-World Guide for SQL Developers

Designing High-Performance OLTP Databases: A Real-World Guide for SQL Developers

On

Introduction: Why OLTP Database Design Matters More Than You Think

If you've ever watched a web application crawl to a stop during peak hours, or seen users complain that "the system is slow again," chances are the root cause lives in the database — specifically, in how the high-performance OLTP database was (or wasn't) designed from the start.

OLTP stands for Online Transaction Processing. These are the databases that power the real-time, day-to-day operations of a business: recording a customer order, updating inventory when a product ships, logging a payment. Unlike analytical (OLAP) databases that crunch historical data in batch, OLTP systems need to handle hundreds or thousands of small, fast transactions per second — simultaneously, reliably, and without locking each other out.

In this guide, we'll walk through the core principles of designing a high-performance OLTP database in Microsoft SQL Server, using real tables and realistic queries from the WideWorldImporters sample database. Whether you're building a new system from scratch or tuning an existing one, these patterns will serve you well.

What Makes an OLTP Database "High-Performance"?

Before diving into design, it helps to understand what we're optimizing for. A high-performance OLTP database should:

  • Respond quickly to individual INSERT, UPDATE, SELECT, and DELETE operations (think milliseconds, not seconds)
  • Handle concurrency — many users reading and writing simultaneously without blocking each other
  • Maintain data integrity — ACID compliance (Atomicity, Consistency, Isolation, Durability) is non-negotiable
  • Scale predictably as transaction volume grows

The decisions you make in schema design, indexing, and query structure determine whether your database achieves these goals — or buckles under real-world load.

Step-by-Step Guide to Designing a High-Performance OLTP Database

Step 1: Normalize Your Schema (But Know When to Stop)

Normalization eliminates data redundancy and protects integrity. For OLTP, Third Normal Form (3NF) is typically the target.

In WideWorldImporters, notice how Sales.Orders references Sales.Customers by CustomerID, rather than duplicating customer data on every order row. This is 3NF in action:

-- Good normalized design: Orders reference Customers by FK
SELECT
    o.OrderID,
    o.OrderDate,
    c.CustomerName,
    c.PhoneNumber,
    ct.CityName
FROM Sales.Orders o
INNER JOIN Sales.Customers c   ON o.CustomerID = c.CustomerID
INNER JOIN Application.Cities ct ON c.DeliveryCityID = ct.CityID
WHERE o.OrderDate >= '2016-01-01'
  AND o.OrderDate <  '2016-02-01';

Storing CustomerName directly in Sales.Orders would be a normalization violation — and a maintenance nightmare when a customer changes their name.

When to consider controlled denormalization: If a heavily queried report always joins 6 tables, adding a computed summary column or a pre-aggregated table may be justified. But in the core OLTP schema, keep it normalized.

Step 2: Choose Primary Keys and Surrogate Keys Wisely

Every OLTP table needs a primary key. In WideWorldImporters, most tables use an INT IDENTITY surrogate key (e.g., OrderID, CustomerID). This is the right call for OLTP because:

  • Integer comparisons in joins are fast
  • Sequential identity inserts avoid page splits (unlike GUIDs)
  • Foreign keys stay compact
-- WideWorldImporters uses clean integer PKs throughout
SELECT
    si.StockItemID,
    si.StockItemName,
    si.UnitPrice,
    sih.QuantityOnHand,
    sih.ReorderLevel
FROM Warehouse.StockItems si
INNER JOIN Warehouse.StockItemHoldings sih ON si.StockItemID = sih.StockItemID
WHERE sih.QuantityOnHand < sih.ReorderLevel
ORDER BY sih.QuantityOnHand ASC;

Developer Insight: Avoid using NEWID() (random GUIDs) as clustered index keys in OLTP tables. Random GUIDs cause constant page splits and dramatically hurt INSERT performance. If you need a GUID for external exposure, use NEWSEQUENTIALID() instead, or keep the GUID as a non-clustered secondary key.

Step 3: Index Strategically — Not Everything

Indexes are your primary performance lever in OLTP. But over-indexing is just as harmful as under-indexing — every index adds overhead on INSERT, UPDATE, and DELETE operations.

The golden rule: Index for your most frequent, most critical queries.

Covering Indexes

A covering index includes all columns a query needs, so SQL Server can satisfy the query entirely from the index without touching the base table (a "key lookup").

-- Frequent query: look up active orders for a specific customer
-- Create a covering index on CustomerID, include columns needed by the SELECT
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate
ON Sales.Orders (CustomerID, OrderDate DESC)
INCLUDE (ContactPersonID, SalespersonPersonID, ExpectedDeliveryDate);

-- Now this query is fully covered — no key lookup needed
SELECT
    o.OrderID,
    o.OrderDate,
    o.ExpectedDeliveryDate,
    p.FullName AS SalesPerson
FROM Sales.Orders o
INNER JOIN Application.People p ON o.SalespersonPersonID = p.PersonID
WHERE o.CustomerID = 1060
  AND o.OrderDate >= '2015-01-01'
ORDER BY o.OrderDate DESC;

Filtered Indexes

When a large portion of rows share a common value that queries almost never touch, a filtered index covers only the relevant subset — dramatically smaller and faster:

-- Index only unconfirmed purchase orders (a small active subset)
CREATE NONCLUSTERED INDEX IX_PurchaseOrders_UnconfirmedOnly
ON Purchasing.PurchaseOrders (SupplierID, OrderDate)
INCLUDE (ExpectedDeliveryDate, IsOrderFinalized)
WHERE IsOrderFinalized = 0;

Step 4: Write OLTP-Friendly T-SQL Queries

Schema design is half the battle. The other half is writing queries that SQL Server can execute efficiently.

Use SARGable Predicates

A SARGable predicate (Search ARGument ABLE) lets SQL Server use an index seek instead of a full scan. Avoid wrapping indexed columns in functions.

-- ❌ NOT SARGable — forces a full table scan
SELECT OrderID, OrderDate
FROM Sales.Orders
WHERE YEAR(OrderDate) = 2016;

-- ✅ SARGable — SQL Server can seek on OrderDate index
SELECT OrderID, OrderDate
FROM Sales.Orders
WHERE OrderDate >= '2016-01-01'
  AND OrderDate <  '2017-01-01';

Avoid SELECT *

Always list only the columns you need. SELECT * forces SQL Server to read every column, bloats network traffic, and breaks covering indexes.

-- ❌ Retrieves all 20+ columns from OrderLines
SELECT * FROM Sales.OrderLines WHERE OrderID = 12345;

-- ✅ Retrieves only what the application needs
SELECT
    ol.StockItemID,
    ol.Description,
    ol.Quantity,
    ol.UnitPrice,
    ol.TaxRate
FROM Sales.OrderLines ol
WHERE ol.OrderID = 12345;

Keep Transactions Short

Long-running transactions hold locks and block other sessions. Structure your T-SQL to minimize the work inside a transaction:

-- Process an order: look up data first, then transact
DECLARE @UnitPrice DECIMAL(18,2);
DECLARE @StockItemID INT = 225;

-- Read outside the transaction
SELECT @UnitPrice = UnitPrice
FROM Warehouse.StockItems
WHERE StockItemID = @StockItemID;

-- Then open and close the transaction quickly
BEGIN TRANSACTION;
    INSERT INTO Sales.OrderLines
        (OrderID, StockItemID, Description, PackageTypeID,
         Quantity, UnitPrice, TaxRate, PickedQuantity)
    VALUES
        (73820, @StockItemID, 'USB dongle (10 pack)',
         7, 5, @UnitPrice, 15.00, 0);
COMMIT TRANSACTION;

Step 5: Manage Foreign Keys and Constraints — Don't Skip Them

A common mistake is disabling foreign keys to "speed up inserts." This trades a minor INSERT overhead for catastrophic data integrity problems. Keep your referential integrity constraints in place.

-- Verify FK relationships are working as designed
SELECT
    po.PurchaseOrderID,
    po.OrderDate,
    s.SupplierName,
    p.FullName AS ContactPerson
FROM Purchasing.PurchaseOrders po
INNER JOIN Purchasing.Suppliers s  ON po.SupplierID = s.SupplierID
INNER JOIN Application.People  p  ON po.ContactPersonID = p.PersonID
WHERE po.IsOrderFinalized = 0
ORDER BY po.ExpectedDeliveryDate;

FK constraints also allow SQL Server's query optimizer to make smarter execution plans by knowing which joins are guaranteed to match.

Common Mistakes in OLTP Database Design

1. Using cursors for batch operations. Cursors process rows one at a time — painfully slow in OLTP. Replace them with set-based UPDATE or INSERT...SELECT statements whenever possible.

2. Clustered indexes on the wrong column. If you cluster on a GUID or a low-cardinality column like Status, you'll suffer fragmentation and poor range-scan performance. Cluster on the column most frequently used in range queries, typically the primary key or date field.

3. Missing indexes on foreign key columns. SQL Server doesn't automatically index foreign key columns. Every JOIN on an un-indexed FK column risks a scan. Always add an index on FK columns in child tables.

4. Implicit data type conversions. When a VARCHAR column is compared to an NVARCHAR parameter, SQL Server converts every row — destroying index seek efficiency. Match data types between columns and parameters.

5. Ignoring fill factor. For tables with heavy UPDATE activity (like Warehouse.StockItemHoldings), set a fill factor below 100% (e.g., 80–85%) on indexes to leave room for updates and reduce page splits.

-- Rebuild an index with a lower fill factor for a high-update table
ALTER INDEX IX_StockItemHoldings_StockItemID
ON Warehouse.StockItemHoldings
REBUILD WITH (FILLFACTOR = 80);

Summary / Key Takeaways

Designing a high-performance OLTP database in SQL Server is about deliberate, layered decisions — not a single magic fix. Here's what to carry with you:

  • Normalize to 3NF for the core schema; denormalize only with specific, measured justification.
  • Use integer identity surrogate keys as clustered primary keys to avoid fragmentation.
  • Index strategically: covering indexes for frequent queries, filtered indexes for selective subsets.
  • Write SARGable queries — keep indexed columns free of function wrappers in WHERE clauses.
  • Keep transactions short to minimize lock contention and blocking.
  • Never disable foreign key constraints — the performance gain isn't worth the integrity risk.
  • Match data types between columns and parameters to avoid implicit conversions.

These principles apply whether you're working on a small internal app or a system processing millions of rows per day.

FAQ

Q1: What's the difference between OLTP and OLAP databases?

OLTP (Online Transaction Processing) databases handle many small, real-time read/write transactions — like recording a sale or updating stock levels. OLAP (Online Analytical Processing) databases are optimized for complex analytical queries over large historical datasets, like monthly sales summaries or year-over-year trend analysis. They have opposite design priorities: OLTP favors normalization and fast single-row access; OLAP favors denormalization and fast full-table aggregations.

Q2: How many indexes should I put on a table?

There's no single right number, but a practical guideline for OLTP tables is 1 clustered index and 3–5 non-clustered indexes. Beyond that, the overhead on write operations (INSERT, UPDATE, DELETE) usually outweighs the read benefit. Use sys.dm_db_index_usage_stats in SQL Server to identify indexes that are never used by queries and can be safely dropped.

Q3: Should I use stored procedures in an OLTP database?

Yes — stored procedures are a best practice for OLTP systems. They reduce network round-trips (the entire logic executes server-side), promote plan reuse through parameterization, and centralize business logic. They also provide a clean security boundary — you can grant EXECUTE on a stored procedure without granting direct table access, which reduces your attack surface.

Tags: