Database

SQL Server execution plan showing a Sort operator spilling to TempDB

TempDB Spill: The Silent Server Killer

Perfect index. Solid execution plan. You’ve reviewed the query line by line. And yet, sometimes everything crashes. If all basic metrics look normal… no CPU spikes, no memory pressure, no network bottleneck. You may be a spill-to-TempDB victim. Ok, but what is a TempDB spill? Some SQL Server operations, like Sort (especially with geospatial queries - my experience) and Hash Match, require memory. Before running a query, SQL Server estimates how much memory it needs. ...

A code snippet illustrating the syntax for BULK INSERT in SQL Server

How to Insert Millions of Records into SQL Server

If you’re trying to insert millions of rows using INSERT in a loop, you’re doing it wrong. A few days ago, I was asked about it. It’s funny how a tool you use usually can slip our mind. Anyway, BULK INSERT is one of the most efficient ways to load large datasets into SQL Server. It can increase the insertion speed by up to 10x to 50x, which means that it can significantly reduce the time required for the operation. ...

A visualization illustrating how Split Queries solve Cartesian Explosion in EF Core

Split Queries in EF Core: Mitigating Cartesian Explosion

Have you worked with Split Queries in EF Core before? When you incorporate related tables using EF Core, it typically generates a JOIN query, potentially leading to duplicated data. While this duplication is usually insignificant for small datasets, it can become problematic when dealing with larger volumes. When multiple collections are included, SQL JOINs multiply rows exponentially, not linearly. 1 2 3 4 var posts = context.Posts .Include(p => p.Comments) .Include(p => p.Tags) .ToList(); Imagine a scenario where you have a table with 1000 posts and each post has 1000 comments. If you use the query below, you will get 1000 * 1000 = 1,000,000 rows in the result. This phenomenon is known as Cartesian Explosion, causing unexpected spikes in data loads. ...

Code snippet demonstrating EF.Functions.Collate for accent-insensitive querying in EF Core

Handling Accents in SQL with EF Core

Have you ever needed to handle accents in SQL? If you are Latin, you have certainly done it many times. And how does it work in EF Core? It’s easy, you just have to use EF.Functions.Collate! Warning: Overriding case-sensitivity in a query via EF.Functions.Collate (or by calling string.ToLower) can have a very significant impact on your application’s performance. 🔗 Know more 👉 Adapted from my original post on LinkedIn . ...