You’re looking at a SQL Server execution plan that looks like a spider web of boxes and arrows. Your EF Core query takes 800ms, but you have no idea where to start optimizing.

Execution plans contain overwhelming detail, but EF Core developers only need to focus on specific elements. Here’s what actually matters for optimizing your LINQ queries.

Getting Execution Plans from EF Core

First, you need to capture the SQL that EF Core generates:

// Enable query logging to see generated SQL
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging(); // Shows parameter values
}

Run your EF Core query and copy the logged SQL:

var expensiveOrders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .Where(o => o.OrderDate >= DateTime.Now.AddDays(-30))
    .ToListAsync();

EF Core generates SQL like this:

SELECT [o].[Id], [o].[CustomerId], [o].[OrderDate], [o].[Total],
       [c].[Id], [c].[Name], [c].[Email],
       [o0].[Id], [o0].[OrderId], [o0].[ProductId], [o0].[Quantity],
       [p].[Id], [p].[Name], [p].[Price]
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
LEFT JOIN [OrderItems] AS [o0] ON [o].[Id] = [o0].[OrderId]
LEFT JOIN [Products] AS [p] ON [o0].[ProductId] = [p].[Id]
WHERE [o].[OrderDate] >= @__AddDays_0
ORDER BY [o].[Id], [c].[Id], [o0].[Id]

Paste this into SQL Server Management Studio with “Include Actual Execution Plan” enabled.

Reading Execution Plans: The Essentials

1. Flow Direction and Cost Percentages

Execution plans flow from right to left. The rightmost operations execute first:

Table Scan (45%) → Nested Loop Join (25%) → Sort (20%) → Select (10%)

Focus on operations with the highest cost percentages. In this example, the Table Scan at 45% is your biggest opportunity for optimization.

2. Table Scans vs Index Seeks

Table Scan (BAD): Reads every row in the table

Table Scan
Cost: 45% of total query
Estimated Rows: 1,000,000

This happens when EF Core queries don’t use indexes:

// Causes table scan - no index on OrderDate
var recentOrders = await context.Orders
    .Where(o => o.OrderDate >= DateTime.Now.AddDays(-30))
    .ToListAsync();

Index Seek (GOOD): Uses an index to find specific rows

Index Seek (NonClustered)
Cost: 5% of total query  
Estimated Rows: 1,500

This happens when your WHERE clause matches an index:

// Uses index seek - CustomerId is indexed as foreign key
var customerOrders = await context.Orders
    .Where(o => o.CustomerId == 123)
    .ToListAsync();

3. Join Types and Performance

Nested Loop Join: Good for small datasets

Nested Loop (Inner Join)
Cost: 15%
Estimated Rows: 1,000

Hash Match Join: Good for large datasets

Hash Match (Inner Join)  
Cost: 35%
Estimated Rows: 100,000

Merge Join: Best when both inputs are sorted

Merge Join (Inner Join)
Cost: 10%
Estimated Rows: 50,000

EF Core’s Include() operations often generate nested loop joins, which can be expensive for large datasets.

Common EF Core Performance Patterns

N+1 Query Detection

Look for execution plans with repeated patterns:

-- Main query
SELECT * FROM Orders WHERE CustomerId = 1

-- Then for each order...
SELECT * FROM OrderItems WHERE OrderId = 101
SELECT * FROM OrderItems WHERE OrderId = 102  
SELECT * FROM OrderItems WHERE OrderId = 103
-- ...hundreds more identical queries

In execution plans, you’ll see the same index seek pattern repeated many times. Fix with proper Include():

// Bad: Causes N+1 queries
var orders = await context.Orders.ToListAsync();
foreach (var order in orders)
{
    var items = order.OrderItems.ToList(); // Lazy loading hits database
}

// Good: Single query with join
var orders = await context.Orders
    .Include(o => o.OrderItems)
    .ToListAsync();

Cartesian Product Problems

When you Include() multiple collections, you might see massive row estimates:

Nested Loop (Inner Join)
Cost: 85%
Estimated Rows: 10,000,000  ← Red flag!
Actual Rows: 2,500

This happens with multiple includes:

// Problematic: Creates cartesian product
var customer = await context.Customers
    .Include(c => c.Orders)
    .Include(c => c.Addresses)  // Each order × each address
    .FirstAsync();

Use split queries or separate calls:

// Better: Split query to avoid cartesian product
var customer = await context.Customers
    .AsSplitQuery()
    .Include(c => c.Orders)
    .Include(c => c.Addresses)
    .FirstAsync();

Parameter Sniffing Issues

You might see drastically different performance for the same query with different parameters:

-- Fast with CustomerId = 1 (few orders)
SELECT * FROM Orders WHERE CustomerId = @p0

-- Slow with CustomerId = 999 (many orders)  
SELECT * FROM Orders WHERE CustomerId = @p0

The execution plan optimizes for the first parameter value it sees. Use query hints when necessary:

var orders = await context.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE CustomerId = {0} OPTION (OPTIMIZE FOR UNKNOWN)", customerId)
    .ToListAsync();

Practical Optimization Examples

Example 1: Missing Index

Problem: Table scan on OrderDate filter

// EF Core query
var recentOrders = await context.Orders
    .Where(o => o.OrderDate >= DateTime.Now.AddDays(-30))
    .ToListAsync();

Execution Plan Shows:

Table Scan on [Orders]
Cost: 78% of total query
Estimated Rows: 1,200,000

Solution: Add index

// In your DbContext configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasIndex(o => o.OrderDate)
        .HasDatabaseName("IX_Orders_OrderDate");
}

After Index:

Index Seek on [IX_Orders_OrderDate]
Cost: 12% of total query
Estimated Rows: 15,000

Example 2: Inefficient Join Order

Problem: EF Core generates joins in suboptimal order

// Large table joined first
var result = await context.Orders
    .Include(o => o.Customer)
    .Where(o => o.Customer.Country == "USA" && o.OrderDate >= DateTime.Now.AddDays(-7))
    .ToListAsync();

Execution Plan Shows:

Orders Table Scan → Customer Join → Filter
Cost breakdown: 60% + 30% + 10%

Solution: Filter early with better query structure

// Filter customers first, then join orders
var result = await context.Customers
    .Where(c => c.Country == "USA")
    .SelectMany(c => c.Orders)
    .Where(o => o.OrderDate >= DateTime.Now.AddDays(-7))
    .Include(o => o.Customer)
    .ToListAsync();

Optimized Plan:

Customer Index Seek → Orders Join → Date Filter
Cost breakdown: 15% + 20% + 5%

Example 3: Over-fetching Data

Problem: Selecting unnecessary columns and rows

// Fetches all columns for all orders
var orderTotals = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .Select(o => new { o.Id, o.Total, CustomerName = o.Customer.Name })
    .ToListAsync();

Execution Plan Shows:

Multiple joins with large data transfer
I/O Cost: Very High

Solution: Project only needed data

// Fetches only required columns
var orderTotals = await context.Orders
    .Select(o => new 
    { 
        o.Id, 
        o.Total, 
        CustomerName = o.Customer.Name 
    })
    .ToListAsync();

Optimized Plan:

Simplified joins with reduced I/O
I/O Cost: Much Lower

Using SQL Server Tools

SQL Server Management Studio

Enable these options for better analysis:

  • Include Actual Execution Plan
  • Include Live Query Statistics
  • Client Statistics

Extended Events

Capture expensive queries automatically:

CREATE EVENT SESSION [EF_Core_Performance] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text)
    WHERE ([duration] > 1000000) -- More than 1 second
) 
ADD TARGET package0.event_file(SET filename=N'EFCorePerformance')

Query Store

Enable Query Store to track performance over time:

ALTER DATABASE YourDatabase 
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO
);

Automated Performance Monitoring

Monitor EF Core query performance in production:

public class EFCorePerformanceInterceptor : DbCommandInterceptor
{
    private readonly ILogger<EFCorePerformanceInterceptor> logger;
    
    public override async ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command, 
        CommandExecutedEventData eventData, 
        DbDataReader result,
        CancellationToken cancellationToken = default)
    {
        var duration = eventData.Duration;
        
        if (duration.TotalMilliseconds > 1000) // Log slow queries
        {
            logger.LogWarning(
                "Slow EF Core query detected: {Duration}ms - {CommandText}",
                duration.TotalMilliseconds,
                command.CommandText);
        }
        
        return await base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
    }
}

Key Optimization Checklist

When analyzing execution plans for EF Core queries:

  1. Look for Table Scans: Should be Index Seeks instead
  2. Check Join Costs: High percentages indicate problems
  3. Verify Row Estimates: Massive differences between estimated and actual rows indicate statistics issues
  4. Identify Missing Indexes: Green text suggestions or high-cost scans
  5. Watch for Cartesian Products: Unreasonably high row counts
  6. Monitor I/O Costs: Large data transfers indicate over-fetching

Pro Tip: I spend most of my time looking at three things in execution plans: operations with cost > 20%, table scans that should be index seeks, and row count estimates that are wildly wrong. These three patterns solve 80% of EF Core performance problems.

Key Takeaway

SQL Server execution plans contain vast amounts of information, but EF Core developers should focus on cost percentages, scan vs seek operations, and row estimates. Most performance problems stem from missing indexes, inefficient LINQ expressions, or over-fetching data.

Learn to read these key indicators quickly, and you’ll be able to optimize EF Core queries effectively without getting lost in execution plan complexity.