I have been working with relational databases for more than 30 years – from nightly runs in mid-sized businesses to billion-row, terabyte-scale databases in semiconductor manufacturing at NXP. This page presents my approach, case studies and measurements from my SQL lab, each with a route to the raw data.
Database development, performance tuning and data modelling in corporate IT, manufacturing, FDA-regulated medical studies and mid-sized companies – focussed on MS SQL Server (T-SQL, SSIS, SSRS) as well as Oracle (PL/SQL), MySQL, PostgreSQL and Redis.
At NXP Semiconductors: Approx. 1.4 billion incoming serial numbers checked in real time for duplicate prevention against 64 billion records (4 TB) – leveraging insert-only tables, decoupled Redis queues, distributed satellite databases and zero-downtime batch migration in 24/7 continuous production.
Instead of guesswork, I use an automated benchmark lab for execution plans, logical reads, plan cache and locking – see the index matrix and ten further lab benchmark series.
These examples are anonymised (I never disclose confidential client data). Values from memory indicate realistic orders of magnitude – the exact technical causes and effects are measured and benchmarked reproducibly on 10 million rows in the lab section below.
A customer in Austria experienced transaction blocking in live operation that could never be reproduced in the test system. The cause was a stored procedure that concatenated its query as text depending on the case and executed it dynamically.
I split it into a dispatcher (delegator) and two procedures with static, parameterised T-SQL. SQL Server now validates the queries in advance and uses a stable execution plan in the plan cache for each branch. Until the end of my engagement, the error did not recur.
A developer had spent several days trying to speed up a slow query in C# code. The cause was in the database: due to a missing covering index, SQL Server had to read millions of rows via scan and key lookups.
Once the right index was created, the query ran roughly a hundred times faster (order of magnitude, from memory), without changing a single line of application code.
Nightly reports and ETL jobs that are not finished by morning rarely come alone: a long-running job holds exclusive and page locks (`LCK_M_*`) and slows down the processes next to it – which in turn block others.
I have regularly brought such nightly runs down from several hours to just over an hour by breaking up oversized transactions, replacing loops with set-based SQL, and removing the few bottlenecks in the execution plan.
A client stored session variables shared across sessions in a NoSQL database; the application was sluggish and the matter was urgent. A test environment could not be spun up quickly, and neither logs nor metrics were available.
I read through the source code, traced the execution paths mentally, and isolated the bottleneck: a lookup table tracking which session used which variables was being scanned repeatedly on every request – adding 1 to 2 seconds of delay per run.
The fix was a NoSQL key-referencing pattern instead of relational searches: the session directly stores the references to its variable IDs under its known key. The client’s team grasped the concept immediately and implemented it: response time dropped to sub-milliseconds with zero noticeable latency.
The same search query (`WHERE Nachname = … AND Stadt = … AND GeschaeftsJahr >= …`) on a table with 1 and 10 million rows, each time with a better index level. The benchmark runs fully automated via PowerShell and T-SQL scripts and is repeatable at any time.
Clustered Index Scan: Scans all 350,000 data pages from start to finish. Maximum I/O and CPU load.
Tipping Point: The optimizer ignores the index because key lookups for 271,000 rows would cost more than a full table scan.
2× Seek + Merge Join + Key Lookup: Faster, but thousands of random page lookups saturate disk I/O and buffer cache.
Index Seek + Key Lookup: Search columns optimally ordered, but missing payload columns still force 2,692 key lookups.
Pure Index Seek (INCLUDE): All needed data resides directly in the B-tree leaf pages. Zero access to base table.
A missing index is only the beginning. For my consulting and tuning work I built a dedicated test framework that measures typical architectural decisions on 1 to 100 million rows, including Showplan XML, CPU time and memory clerks.
When .NET/EF sends a Unicode parameter (@p nvarchar) against a varchar column, type precedence forces a row-by-row scan under default SQL collation:
Under SQL collations, SQL Server has to convert every single row (plan warning: CONVERT_IMPLICIT, 3,011 ms CPU time). 3 page reads turn into a massive scan. View lab benchmarks & collation matrix →
How do you pass hundreds or thousands of IDs from C# to SQL Server? Four methods benchmarked at 10 to 2,000 IDs and with 50,000 single calls:
Individual parameters inflate the plan cache; 50,000 ad-hoc queries occupy 781 MB memory vs. 0.1 MB parameterised. Table-valued parameters (TVP) maintain a single reusable plan. View lab benchmarks & 4-method comparison →
A three-part business key (Mandant + Bereich + Artikelnummer, ~56 bytes) repeats in every child table and foreign key index:
The text key inflates storage 4.4-fold and makes child joins 2.5 times slower (1,142 ms vs. 471 ms). The business key is preserved as a UNIQUE constraint. View lab benchmarks & metadata script →
LIKE '%berg')A suffix search such as WHERE Firma LIKE '%berg' is not SARGable – even with an index, SQL Server scans all 49,022 pages:
With a computed column FirmaRev AS REVERSE(Firma) and an index on it, the search becomes a prefix seek. Reads drop by ×1,140, and runtime drops from 4 seconds to 6.3 ms. View lab benchmarks & computed column →
Queries across server and database boundaries produce radically different execution plans depending on syntax and collation:
For large joins, OPENQUERY forces thousands of nested-loop accesses. If target collation differs, implicit conversion kills the seek (factor ×119 I/O). View lab benchmarks & remote join tests →
When attributes like Stadt and Zahlungsart are stored as plain text instead of compact foreign keys:
Pattern matching on the compact lookup table costs almost nothing before joining via lean keys – 46.4 times faster. Contradiction checks drop from 21,584 to 32 reads. View lab benchmarks & schema modelling →
In the measured case (synthetic data, cold cache) the same question – revenue of the customers in one city – once through a view and once through a procedure without parameters whose result is filtered afterwards:
The view lets the filter reach the index; the procedure computes all 10 million customers. A multi-statement function sits in between at 53,095 ms. With a parameter the procedure is as fast as the view (54 ms). View lab benchmarks →
One month (833,333 rows) is removed from 100 million bookings – with DELETE or by truncating the month's partition:
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. View lab benchmarks →
The same bookings, once with a plain index by date, once partitioned by month:
The account query does not know the month and searches all 120 partitions. The monthly total was not faster when partitioned either: 244 ms vs. 173 ms with a plain date index. Partitioning serves data maintenance; for queries it does not replace the right index. View lab benchmarks →
We look for the 400 line items of the customers in one city – without and with indexes on city and foreign keys:
With three subqueries per customer instead of the join: 50,737 ms vs. 232 ms (×219). View lab benchmarks →
In databases that have grown over the years, the patterns repeat. These are the six causes I come across most often.
One index per column helps little. What matters is column order (equality before range), covering columns (INCLUDE to eliminate key lookups) and filtered indexes – including handling parameter sniffing and OPTION (RECOMPILE).
If data type or collation of column and parameter do not match (e.g. varchar column against nvarchar from Entity Framework), SQL Server converts every row on read – falling back from an Index Seek to a full scan.
Queries concatenated as text flood the plan cache with single-use plans, burn CPU compile time on every call and prevent stable execution plans. Parameterised calls and Table-Valued Parameters (TVP) eliminate plan cache flooding and compilation load; against parameter sniffing, OPTION (RECOMPILE) or separate plans help in a targeted way.
Conditions like LIKE '%berg' or functions on indexed columns (YEAR(DateCol) = …, ISNULL(Col, …)) blind existing indexes. Rewritten predicates or indexed computed columns restore the Index Seek.
Composite text primary keys are duplicated in every foreign key and every nonclustered index of referencing tables. Surrogate keys (int/bigint) often cut buffer pool memory usage in half.
Large transactions and cursor loops hold locks and trigger blocking chains – especially in nightly runs. Set-based SQL, smaller transaction batches and appropriate isolation levels break the chain.
In grown databases, every change has side effects: which stored procedure, view, SQL Agent job or SSIS package will break if you touch a table or key? That is why I rely on deterministic tooling rather than guesswork.
My diagnostic scripts query system catalogs (sys.dm_db_partition_stats, sys.dm_exec_query_stats, sys.index_columns, Showplan XML) without scanning large tables. This means the memory saving of a key or index change can be calculated from the catalog data before any intervention.
With COMPIDDA, I developed a dedicated analysis toolset (backed by over 4,000 automated tests) that parses up to 19 source types – T-SQL (procedures, views, triggers, dynamic SQL, OPENQUERY, linked servers via ScriptDom), SSIS packages, SQL Agent jobs, SSRS reports and C#/.NET data access – and presents them as a typed dependency graph (R/W/X) with exact source locations. For entire grown or orphaned IT landscapes, this is available as a dedicated, separately priced on-site assessment including toolset deployment (Details on the COMPIDDA assessment →).
Every measure is given a measurable success criterion in advance – run time, reads, wait time – and is re-measured afterwards under the same conditions; I compare result sets with the previous state using checksums. I use AI tools to accelerate scripts and test cases; you decide in advance whether code or metadata from your organisation may be processed in doing so. Nothing is guessed on your database.
Where there are not enough tests, I do not rewrite code on a large scale. I target the places that make the biggest difference – often indexes, statistics and individual stored procedures, without touching the application. Where deeper changes are needed, we secure the dependent paths upfront with automated tests and checksums.
Index optimisation often takes effect within hours. Changes to the code take days to months. New hardware costs procurement time, server downtime and money – and usually achieves only a fraction of what the right index does (in the lab at 100M rows, a SQL Server throttled to 2 cores and 1,300 MB with a covering index was 14,864 times as fast as an 8-core server with 13,000 MB without an index; lab value with synthetic data, times around 1 ms are coarse – at 2 ms it would still be about 8,900 times; disk speed and memory bandwidth are not emulated). That is why this is my preferred diagnostic sequence.
When database queries slow down, the default corporate reflex is often: “We need a bigger server, more RAM, and faster NVMe drives.” For a query without a matching index, more hardware yields only a limited factor: in my lab at 100M rows, the server with 8 cores and ten times the memory was only 1.7 times as fast as the one with 2 cores – at high Microsoft licensing costs and long lead times. In my lab, a matching index made the query ×358 faster at 10M rows – work of a few hours.
More CPU cores and faster NVMe drives only accelerate the transfer pipe, but leave the root cause intact: the database engine must still retrieve the exact same massive volume of data.
Indexing does not change the wire speed; it changes how much data must be fetched: the query pinpoints and reads only the exact matching rows.
Microsoft licenses SQL Server on a per-core basis (2-core pack per the Microsoft SQL Server 2025 price list: Standard $3,945, Enterprise $15,123, estimated retail price). Upgrading from 8 to 32 cores to brute-force inefficient queries adds over $180,000 in Enterprise core licensing alone – plus Windows Server licenses and, where applicable, licenses for further nodes.
As the global AI boom diverts wafer capacity into High Bandwidth Memory (HBM), TrendForce forecast in February 2026 a server DRAM price increase of around 90% quarter-on-quarter for Q1 2026 (source). For server components such as BMC chips, TrendForce reported lead times of 21 to 26 weeks in April 2026 (source). The old management assumption that “buying hardware is faster than tuning software” is made harder in 2026 by prices and lead times for individual components.
Computed display strings (such as concatenating title, first name, last name or formatting dates) belong in the C#/.NET application tier, not inside the database engine. SQL Server is the most expensive and hardest-to-scale tier. String formatting inside views burns licensed CPU cycles and can obstruct parallelism and index seeks.
Trustworthy engineering avoids silver bullets. There are distinct scenarios where hardware upgrades are indeed necessary:
The 4-step decision sequence for managers: 1. Measure where time is actually spent (wait stats: I/O, CPU, locking, or network). 2. Audit and optimize indexes (hours of work, zero licensing costs, highest multiplier). 3. Refactor queries and application code (days to weeks). 4. Purchase hardware only as the final step – when profiling objectively proves that physical disk or RAM limits are the actual bottleneck.
Thousands of lines of T-SQL, historical nesting, row-by-row cursors instead of set-based operations – and the original authors left years ago. Nobody on the team dares touch the code because any modification risks breaking production or month-end billing. I bring calm, transparency and speed to deeply entrenched legacy procedure landscapes.
At an international spirits producer, a historically grown landscape of around 130 MSSQL stored procedures handled nightly data consolidation and error reconciliation: deeply nested logic led to long run times and blocked downstream systems.
I analysed the entire procedure landscape step by step, disentangled it and largely replaced it with robust, maintainable and significantly faster T-SQL solutions – in continuous live production, without operational downtime, secured by checksum tests.
“Mr. Hagen has exceptionally profound technical expertise in the areas of databases, application architecture, and performance optimization. He works in a very structured, analytical, and self-directed manner, quickly understanding and sustainably improving even complex, historically grown system landscapes. His work significantly enhanced the transparency, maintainability, stability, and performance of the IT systems in use.”
— from the customer's written reference letter
Before touching a single line of code, my analysis tool COMPIDDA reads the codebase, T-SQL via the official SQL syntax tree (ScriptDom). You see it as an interactive dependency diagram: Which procedure calls which? Where do views, triggers, SQL Agent jobs or C# services attach? Anything that cannot be resolved unambiguously is shown to you as a marked open point. For whole IT landscapes, this is also available as a dedicated, separately priced on-site assessment.
To guard against regressions, we record real input and output data in advance – in your environment and with your approval; recordings never leave your premises. For every recorded case, NUnit automated test harnesses and SHA-256 result checksums (integrated directly into your Azure DevOps CI/CD pipeline) show before deployment that the refactored procedure returns exactly the same data as the legacy code.
I replace slow row-by-row cursors and WHILE loops with high-throughput set-based logic (window functions, CTEs), break up deadlock-prone oversized transactions, and convert dynamic SQL string concatenation into parameterized stored procedures with stable query plans.
Is a nightly batch run blowing past deadlines or a 2,000-line procedure locking your entire production database? Let us review the execution plan.
I am often asked which client query I sped up by what exact factor. Measurement logs, schemas and production data from client systems stay with the client; I do not take them with me and I do not publish them. That is why I only state values from earlier projects as orders of magnitude – and I do not state invented figures at all.
Instead, I have reproduced typical cases in my own SQL lab: automated, on 1 to 100 million rows of synthetic test data, with disclosed T-SQL, execution plans and exact page reads. The proof on your own database is delivered by the performance check.
The performance check is my entry offer. The lab figures on this page are a demonstration on test data; in the performance check you see the effect on your own system before deciding on a larger engagement. We agree scope and effort in the initial call and put them in writing in advance.
VIEW SERVER STATE); table contents are not needed.View a complete sample report (PDF in German, based on lab data) before you commit: including an executive summary for management, technical before-and-after measurements, a ranked list of measures, rollback scripts and ROI comparison.
“… quickly understanding and sustainably improving even complex, historically grown system landscapes. His work significantly enhanced the transparency, maintainability, stability, and performance of the IT systems in use.”
Excerpt from written reference · international spirits producer
“Mr. Hagen was exceptionally reliable … consistently demonstrating total dedication when ensuring round-the-clock operational continuity.”
Excerpt from official reference · NXP Semiconductors
More references on the home page; full documents available on request.