SQL Server Tuning: Indexes and Query Plans for .NET Applications

Why .NET developers cannot ignore SQL Server tuning

Entity Framework Core hides SQL until it does not. A demo runs fine on ten rows; in production with two million orders, the same LINQ becomes a table scan that holds locks and melts p95 latency. We see strong C# developers blame "the database" when the fix is an index, a narrower projection, or splitting a report query from the OLTP path. Toolliyo's SQL Server curriculum exists because backend interviews still ask you to read a plan and explain why a seek beat a scan.

This article focuses on practical tuning for apps you build with ASP.NET Core and SQL Server: indexes, statistics, plans, and EF-specific patterns that actually ship.

Clustered vs non-clustered indexes in one minute

The clustered index is the table row order—usually the primary key. Non-clustered indexes are separate structures with keys and pointers (or included columns) back to the clustered leaf. You get at most one clustered index per table. Choose the clustered key carefully: narrow, ever-increasing integers (IDENTITY or sequential GUID strategies) reduce fragmentation compared to random GUID defaults on high-insert tables.

Covering indexes include all columns the query needs in the INCLUDE list so SQL Server can satisfy the query without key lookups—often the biggest win for read-heavy APIs.

How to read an execution plan without drowning

In SSMS or Azure Data Studio, enable actual execution plan. Look for:

• Table Scan / Clustered Index Scan on large tables—often missing or wrong index.
• Key Lookup—consider INCLUDE columns or a different index order.
• Sort on large rows—can you support ORDER BY with an index?
• Hash Match—sometimes fine; on huge builds watch memory grants and spills to tempdb.
• Warnings—implicit conversions kill index use when comparing nvarchar column to varchar parameter.

Compare estimated vs actual rows. Big gaps mean outdated statistics or parameter sniffing issues.

Parameter sniffing and .NET apps

SQL Server compiles a plan for the first parameter values it sees. Sometimes that plan is perfect for one tenant and terrible for another. Mitigations include OPTIMIZE FOR UNKNOWN, recompile hints for volatile reports, or Query Store plan forcing after you identify a regression. In EF Core, literal constants from different LINQ branches can cause plan cache bloat—another reason to keep queries predictable.

EF Core patterns that generate bad SQL

N+1 queries

Loading a list of parents then lazy-loading children in a loop is the classic failure. Fix with .Include(), explicit loading, or—often best—projection:

var dtos = await db.Orders
  .Where(o => o.CustomerId == id)
  .Select(o => new OrderDto {
    Id = o.Id,
    Total = o.Lines.Sum(l => l.Quantity * l.Price)
  })
  .ToListAsync();

Client evaluation

Filtering with methods EF cannot translate pulls data into memory. Watch logs in development; use ToQueryString() in EF Core 5+ to inspect SQL before shipping.

Tracking overhead

Read-only APIs should use AsNoTracking(). Tracking tens of thousands of entities for a dashboard is wasted CPU and memory.

Indexing workflow we recommend

1. Capture the slow query from Application Insights or extended events with duration and logical reads.
2. Run it in SSMS with actual plan and SET STATISTICS IO ON.
3. Check existing indexes—avoid duplicates that differ only slightly.
4. Propose an index; test on staging with realistic data volume.
5. Measure before/after logical reads and CPU time.
6. Document the index purpose in a migration comment or team wiki.

Statistics and maintenance

Out-of-date statistics lead to wrong cardinality estimates and bad joins. Ensure auto-update statistics is on; for large tables, consider manual updates after big ETL loads. Index maintenance: reorganize for light fragmentation, rebuild for heavy—schedule off-peak. Do not rebuild every index nightly on modern SSDs without measuring need.

Tempdb and locking basics for API developers

Spills to tempdb from sorts and hashes show as warnings in plans—often memory grant issues or missing indexes. Blocking chains appear when long transactions hold locks during API requests—keep transactions short, avoid user think time inside a transaction, and use appropriate isolation level; READ COMMITTED is default for good reason.

When to bypass EF for hot paths

Dapper or raw SQL for bulk inserts, heavy reporting, or TVP batch operations is not a failure of EF—it is engineering judgment. Keep EF for most domain logic; use specialized paths where measured benefit is clear.

Monitoring in production

Enable Query Store on supported editions. Set baselines for top CPU and duration queries. Alert on regressions after deploys. Correlate SQL spikes with deployment markers. For Azure SQL, use Intelligent Insights and performance recommendations as hints, not blind autopilot.

Interview questions you should practice

Explain clustered index choice for a GUID primary key table. Design indexes for a query filtering on Status and CreatedDate ordered by Id descending. Describe how you would diagnose a sudden slowdown after a deployment (Query Store, plan comparison, rollback). Walk through fixing an N+1 in EF with real method names.

Hands-on lab on Toolliyo

Take a sample ASP.NET Core API with EF Core, seed a few hundred thousand rows, capture a slow endpoint, add a covering index, and document logical reads before and after. That artifact proves performance skills better than claiming "optimized SQL" without numbers.

SQL Server tuning is a habit: measure, index with intent, keep ORMs honest about the SQL they emit, and treat execution plans as part of your debugging toolkit—not a DBA-only mystery.