SQL Lab: Benchmark Tables & Test Setup

Why 10 million synthetic rows?

Professional discretion is absolute: Client production data never leaves client infrastructure. To provide engineering teams and leadership with rigorous empirical proof, I generated a synthetic corporate transactional dataset (orders and ledger events).

10 million rows force the SQL Server engine to traverse real physical I/O paths. The clustered index covers roughly 350,000 8-KB data pages (~2.8 GB). Here we can observe exactly how the query optimizer behaves when data volume exceeds CPU cache limits.

The test table and schema

The schema mirrors a typical enterprise transactional record:

CREATE TABLE dbo.KundenBewegung (
    Id             bigint IDENTITY(1,1) NOT NULL,
    Nachname       varchar(50)  NOT NULL,
    Vorname        varchar(50)  NOT NULL,
    Stadt          varchar(50)  NOT NULL,
    GeschaeftsJahr smallint     NOT NULL,
    Umsatz         decimal(12,2) NOT NULL,
    Bemerkung      varchar(200) NULL,
    CONSTRAINT PK_KundenBewegung PRIMARY KEY CLUSTERED (Id)
);

Data distribution: Common surnames (~270k matches for top names), realistic city cardinalities, and financial years spanning 2018 to 2026.

The “Tipping Point”: Why Level 1 is ignored

In Level 1, an index is created on only one column: CREATE INDEX IX_Nachname ON KundenBewegung(Nachname). Many engineers expect the query to speed up. Yet SQL Server completely ignores the index and continues executing a full Table Scan!

The mathematical reason: Searching for a common name produces 271,384 matching rows. Seeking on IX_Nachname would locate the pointers quickly, but would then require 271,384 random I/O bookmark lookups into the clustered index to fetch Vorname, Stadt, and Umsatz. Because the entire table only spans 350,000 pages, the optimizer calculates: 271,000 random page lookups cost more CPU and disk time than scanning all 350,000 pages sequentially in one go. This is the Tipping Point.

Why “Logical Reads” are central alongside execution plan, CPU and wait stats

Latency in milliseconds fluctuates depending on CPU clock rate, virtual machine throttling, background jobs, and operating system scheduling.

For a query’s data access, Logical Reads (8-KB pages accessed) provide a reproducible metric for a given execution plan and dataset – complemented by execution plan operators, CPU time, memory grants and lock/wait statistics. Whether pages reside in RAM or on NVMe storage: the number of buffer pool page accesses remains the same. An optimization that slashes logical reads from 350,000 down to 11 was faster by orders of magnitude in the lab. Fewer reads mean less I/O work; the exact time factor depends on the hardware.

Collation & Implicit Data Type Conversion

When .NET / Entity Framework passes parameters as Unicode (nvarchar) against a varchar column:

Root cause: Under SQL Server Data Type Precedence, nvarchar outranks varchar. The engine cannot convert the parameter; it must convert every single table row on the fly (Plan warning: CONVERT_IMPLICIT). Index seeks become impossible; CPU burns for 2.4 seconds.

Set Passing (IN Lists) & Plan Cache Bloat

Comparing dynamic concatenated strings with Table-Valued Parameters (TVP) for 2,000 IDs:

Root cause: Dynamically constructed IN (1, 2, 3...) queries flood the plan cache with single-use plans. Across 50,000 queries, ad-hoc execution seized 781 MB of RAM in the plan cache, while parameterized TVP required only 0.1 MB.

Natural Composite Key vs. Surrogate int Key

A 3-part natural text key (56 characters) compared to a slim int surrogate key:

Root cause: The natural composite key duplicates into every foreign key and secondary index across all child tables. Storage expands by 4.4×, exerting persistent buffer pool pressure.

SARGability: Leading Wildcards (LIKE '%berg')

Suffix search on company names: Standard scan vs. computed column with REVERSE():

Root cause: LIKE '%berg' cannot seek B-trees. Adding a computed column FirmaRev AS REVERSE(Firma) with an index converts a non-SARGable suffix search into a high-speed prefix seek LIKE 'greb%': ×617 faster.

Database boundaries: collation mismatch

Cross-database queries:

Root cause: If the collation of a remote database differs, SQL Server pulls all rows locally and compares them row-by-row. Remote index seeks become impossible.

Normalisation (3NF) & Data Quality

Raw string columns in transactional tables vs. indexed lookup tables:

Root cause: Filtering on a compact lookup table (5,000 cities) executes in near-zero time before joining via a lean int key – 46.4× faster, and the model prevents differing spellings of the same city.

Stored procedure vs. view: where the filter is applied

We look for the revenue of the 100 customers in one city, with suitable indexes. The logic sits once in a view and once in a procedure without parameters whose result is filtered afterwards (INSERT … EXEC):

Root cause: The view is expanded into the query, so the city filter moves inside and uses the index. The procedure computes all 10 million customers first and is filtered only afterwards. The procedure itself is not the problem: with a parameter it takes 54 ms, as fast as the view. Client times with a cold cache.

Removing one month: DELETE vs. truncating a partition

The same bookings over 120 months, stored by booking number, by date, and partitioned by month. One month (833,333 rows) is removed:

Root cause: DELETE logs every row and maintains every index. Truncating a partition only deallocates its pages. Prerequisite: the month lies in exactly one partition and all indexes are aligned. Only the delete statement was measured, inside a transaction that was rolled back afterwards.

Partitioning does not make queries faster by itself

The same three tables as in benchmark 8, this time two queries: the total of one month and the total of one account across all years:

Root cause: For the monthly total an index by date is enough; in this measurement partitioning was even slightly slower. The account query does not know the month and has to search all 120 partitions. Partitioning is a tool for data maintenance (benchmark 8), not a substitute for the right index.

Three-table join at 100 million customers

Customer, order and line item; we look for the 400 line items of the customers in one city. No index on the foreign keys vs. indexes on city and foreign keys (with INCLUDE):

Root cause: Without an index SQL Server reads all 400 million line items to find 400 – 2,177 times as many pages. Three subqueries per customer instead of the join: 50,737 ms vs. 232 ms (factor ×219). Forced nested loops without an index were not measured at this size.