I have been working with SQL databases for around 30 years – from controlling reports to billions of records in semiconductor production. Over that time I have sped up hundreds of queries, stored procedures and nightly runs. This page shows how I work, what I have seen and what can be measured.
Database development, performance tuning and data modelling in corporate IT, manufacturing, clinical trials (FDA-compliant) 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, around 1.4 bn new serial numbers per month were checked for duplicates against 64 bn existing rows – using insert-only tables, decoupled Redis queues, distributed satellite databases and live batch migration in 24/7 production.
Instead of guesswork, I use an automated benchmark lab for execution plans, logical reads, plan cache and locking – see the index matrix and 6 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 Vienna 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. The error never occurred again.
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 – from minutes of waiting down to milliseconds, 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.
| Index level | Runtime 1M rows |
Runtime 10M rows |
Logical reads 10M rows |
Execution plan operators (10M rows) |
|---|---|---|---|---|
| 0 · No index (Heap / PK scan) | 173 ms | 1,684 ms | 350,018 | Clustered Index Scan, Parallelism |
| 1 · Index on one column only | 165 ms ×1.05 | 1,670 ms ×1.01 | 350,167 ×1.00 | Clustered Index Scan (tipping point) |
| 2 · One single-column index each | 61 ms ×2.8 | 96 ms ×17.5 | 3,872 ×90 | 2× Index Seek + Merge Join + Key Lookup |
| 3 · Composite index | 30 ms ×5.7 | 53 ms ×32 | 2,628 ×133 | Index Seek + Key Lookup (Nested Loops) |
| 4 · Covering index (INCLUDE) | 2.3 ms ×75 | 2.7 ms ×624 | 11 ×31,820 | Pure Index Seek (no Key Lookup) |
| 5 · Filtered covering index | 1.3 ms ×133 | 3.3 ms ×510 | 4 ×87,505 | Pure Index Seek (index size 86 MB vs. 1,159 MB) |
Method: Microsoft SQL Server, 10M rows (approx. 2.7 GB base table), cold buffer pool before each run (CHECKPOINT; DBCC DROPCLEANBUFFERS), execution plan created in advance and measured separately from parse and compile time via SET STATISTICS IO, TIME, XML ON, mean of at least three runs. Runtimes in the single-digit millisecond range fluctuate; logical reads are the exact, repeatable comparison metric.
Two details from the lab that are often overlooked in practice:
First, in level 1 the Query Optimizer completely ignores the single-column index on Nachname (271,384 matches out of 10M rows exceed the tipping point for key lookups) and falls back to a full Clustered Index Scan.
Second, the filter in level 5 shrinks the index from 1,159 MB to just 86 MB (and cuts INSERT time from 9.4 s to 1.5 s) – however, if the query is called parameterised without OPTION (RECOMPILE), SQL Server cannot use the filtered index due to parameter sniffing and drops back to 349,977 reads (1,657 ms)!
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 10 million rows, including Showplan XML, CPU time and memory clerks.
When an application sends a Unicode parameter (@p nvarchar, default in .NET/EF) against a varchar column, the outcome depends on collation precedence:
Under a SQL collation or with varchar = int, SQL Server must convert every single row (plan warning: CONVERT_IMPLICIT). From 3 page reads and 0 ms CPU to 27,469 reads and over 2 seconds of burned CPU time per single query.
How should hundreds or thousands of IDs be passed from C# to SQL Server? I benchmarked four approaches across 10 to 2,000 IDs and across 50,000 individual calls:
Individual parameters (@p1 … @p1000) degrade at 1,000 IDs due to Merge Interval / Concatenation to 565 ms and 7.1 MB plan size. Table-Valued Parameters (TVP) consistently deliver a reusable plan with zero compile overhead.
A three-column business key (Tenant + Area + ItemNumber, averaging 56 characters) is duplicated in every child table and every foreign-key index:
The technical 4-byte key saves nearly two thirds of storage and buffer pool memory (while the business key remains protected by a UNIQUE constraint on the parent table). For existing customer databases I wrote a catalog script that calculates this exact saving upfront purely from sys.dm_db_partition_stats and sys.index_columns without touching user data.
LIKE '%berg'): from 4.2 s to 3.3 msA suffix search such as WHERE Company LIKE '%berg' is not SARGable – even with an index on Company, SQL Server scans all 49,062 index pages (21,297 ms CPU time, 4,231 ms runtime in a parallel scan).
With a computed column CompanyRev AS REVERSE(Company) and an index on it, the suffix search becomes a prefix search (LIKE REVERSE(@p)). Logical reads drop from 49,062 to 45 reads (factor ×1,090 less I/O), runtime from 4,231 ms to 3.3 ms (over ×1,200 faster).
Queries across database and server boundaries flip into completely different execution plans depending on selectivity and collation:
For point lookups, OPENQUERY pushes the filter to the remote server; for full joins, it forces thousands of local nested-loop seeks. And if the child database collation differs, an implicit conversion prevents the index seek (factor ×119 more reads).
Storing values such as City and PaymentMethod as plain text in the transaction table instead of an int foreign key to a lookup table hurts both speed and data integrity:
On the small lookup table (5,000 cities), even a LIKE '%berg' costs almost nothing before joining via the lean CityId. At the same time, the data model prevents contradictory spellings of the same city for a given postcode.
After hundreds of optimisations, the patterns repeat. I find these six bottlenecks in almost every database that has grown over the years.
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 procedures and Table-Valued Parameters (TVP) solve this permanently.
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. That makes it possible to predict the exact megabyte savings of a key or index change before touching anything.
With COMPIDDA, I developed a dedicated analysis tool (backed by over 4,000 automated tests) that parses T-SQL procedures, views, triggers, dynamic SQL, OPENQUERY, linked servers, SSIS packages, jobs and C# data access via the official SQL Server syntax tree (ScriptDom) into a dependency graph with exact source locations.
I use modern AI tools specifically to accelerate analysis scripts and test cases – guided by 30 years of knowledge of SQL Server internals and verified by strict, deterministic before/after benchmarks (checksum verification, logical reads, execution plans). 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, a SQL Server throttled to 2 cores and 512 MB RAM with a covering index beats an unthrottled 8-core server without an index by a factor of over a hundred). That is why I check strictly in this order.
When database queries slow down, the default corporate reflex is often: “We need a bigger server, more RAM, and faster NVMe drives.” For relational databases, hardware tuning typically yields only a modest factor (1.2× to 3×) – at extreme Microsoft licensing costs and months of lead time. An optimal index delivers improvements of 10× to over 600× – in just 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 packs; Standard ~$3,945, Enterprise ~$15,123). 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 duplicated costs for secondary Always-On nodes.
As the global AI boom diverts wafer capacity into High Bandwidth Memory (HBM), server DRAM prices surged up to 90% in early 2026 with enterprise SSDs following suit. Server delivery lead times now sit at 20 to 24+ weeks. The old management assumption that “buying hardware is faster than tuning software” is dead.
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.
In tenders I am often asked which customer query I sped up years ago by what exact percentage. The honest answer: On the one hand, after more than 30 years in IT, I do not recall every single case and its exact metrics from memory. On the other hand, as a matter of professional ethics, I never disclose confidential client data, internal schemas or proprietary production data. Historical logs from client systems belong to the client – a reputable architect does not take them home, nor do I invent polished fantasy numbers for my CV.
That is why I reconstructed typical remembered cases with provable, real measurements in my own SQL lab: fully automated, on 1 to 10 million rows, with transparent T-SQL, Showplan XML and exact page reads. What I provide for your decision is tangible and solid – and I am glad to deliver the direct before-and-after proof on your own database.
I analyse the execution plan, logical reads and locking behaviour, propose the fix and measure before and after with a script – under the same strict conditions as in my SQL lab above. You see the exact effect on your own system before deciding on a larger engagement.
„Er arbeitet sehr strukturiert, analytisch und eigenverantwortlich …“
Excerpt from a written reference (German original) · STOCK SPIRITS
„Herr Hagen war in ganz besonders hohem Maße zuverlässig.“
Excerpt from a written reference (German original) · NXP Semiconductors
More references on the home page; full documents available on request.