Indexes, Include Properties, Filtered Indexes, Migrations, and SQL Server DDL Behavior

Learning outcomes

1. The problem: a LINQ query is fast only if the SQL Server access path is good

ServiceHub's dispatch queue filters open work orders by technician/status, orders by a timestamp, and projects a few display columns. EF can generate correct SQL and still force a large scan. Index design belongs to the relational workload: predicate selectivity, ordering, projected columns, update frequency, and cardinality. Provider Fluent APIs are simply a way to encode that SQL Server physical design in the migration model.

var queue = await db.SqlServerWorkOrders    .Where(w => w.AssignedTechnicianId == technicianId && w.Status == WorkOrderStatus.Open)    .OrderByDescending(w => w.OpenedUtc)    .Select(w => new    {        w.Id,        w.WorkOrderNumber,        w.CustomerName,        w.Priority,        w.OpenedUtc    })    .Take(50)    .ToListAsync(ct);

2. Build the index from predicate and order before adding includes

A reasonable starting key mirrors the selective equality predicates followed by the ordering column. Included columns can cover projected values without making them part of key ordering. Coverage is useful only if the plan actually uses the index and the write/storage cost is acceptable.

modelBuilder.Entity<SqlServerWorkOrder>()    .HasIndex(w => new { w.AssignedTechnicianId, w.Status, w.OpenedUtc })    .IncludeProperties(w => new    {        w.WorkOrderNumber,        w.CustomerName,        w.Priority    });

CREATE INDEX [IX_work_orders_AssignedTechnicianId_Status_OpenedUtc]ON [work_orders] ([AssignedTechnicianId], [Status], [OpenedUtc])INCLUDE ([WorkOrderNumber], [CustomerName], [Priority]);

Do not infer “covering” from DDL alone. Capture the actual/estimated plan and logical reads on representative data. A different filter distribution can make a scan cheaper.

3. Filtered indexes shrink the structure when a stable predicate matches the workload

SQL Server filtered indexes index only rows satisfying a SQL predicate. They are useful for sparse active subsets such as “external reference exists” or “not soft-deleted,” but the query predicate must be compatible with the filter. The filter is SQL text, so it is provider-specific and deserves migration review.

modelBuilder.Entity<SqlServerWorkOrder>()    .HasIndex(w => w.ExternalReference)    .IsUnique()    .HasFilter("[ExternalReference] IS NOT NULL");

The SQL Server provider also adds IS NOT NULL filters by convention for nullable columns participating in unique indexes. If you override that convention, do it because the domain/database semantics require it—not to make the migration look simpler.

4. Clustering changes the table's physical organization

SQL Server permits only one clustered index per table. By default a primary key is commonly clustered. If you choose a different clustered index, explicitly make the primary key nonclustered and understand every nonclustered index stores the clustering key as a row locator. This is a major physical decision, not a performance checkbox.

var entity = modelBuilder.Entity<SqlServerWorkOrder>();entity.HasKey(w => w.Id)    .IsClustered(false);entity.HasIndex(w => new { w.OpenedUtc, w.Id })    .IsClustered();

5. Fill factor is a maintenance tradeoff, not a magic performance percentage

Fill factor leaves free space on index pages during build/rebuild. It can reduce page splits for some random-insert/update workloads, but it also increases page count and can hurt reads/cache efficiency. EF exposes HasFillFactor; the number must come from SQL Server evidence such as page-split/fragmentation behavior, not a copied tutorial value.

modelBuilder.Entity<SqlServerWorkOrder>()    .HasIndex(w => w.ExternalReference)    .HasFillFactor(90);

For the mandatory lab, generate DDL with and without a fill factor and inspect the index. Do not claim a speedup unless you build a repeatable write workload and disclose data volume, cache state, server build, storage, and concurrency.

6. Online index creation can reduce blocking, but support/licensing/version matter

IsCreatedOnline() asks SQL Server to use the ONLINE = ON index creation option. The operational benefit and availability depend on SQL Server/Azure SQL capabilities, index type, edition/service tier, and the specific DDL operation. Developer edition is suitable for learning, not evidence that a production edition supports the same operation.

modelBuilder.Entity<SqlServerWorkOrder>()    .HasIndex(w => new { w.Status, w.OpenedUtc })    .IsCreatedOnline();

CREATE INDEX [IX_work_orders_Status_OpenedUtc]ON [work_orders] ([Status], [OpenedUtc])WITH (ONLINE = ON);

7. Evidence loop: migration DDL → query tag → plan → reads → write cost

Use a repeatable evidence loop. First inspect migration SQL. Next tag the queue query so it is easy to identify. Capture the SQL Server plan and STATISTICS IO/TIME for representative parameters. Then measure write overhead and storage before keeping the index.

var queue = await db.SqlServerWorkOrders    .TagWith("ServiceHub:DispatchQueue:v1")    .Where(w => w.AssignedTechnicianId == technicianId && w.Status == WorkOrderStatus.Open)    .OrderByDescending(w => w.OpenedUtc)    .Take(50)    .Select(w => new { w.Id, w.WorkOrderNumber, w.CustomerName, w.Priority })    .ToListAsync(ct);

SET STATISTICS IO ON;SET STATISTICS TIME ON;-- execute the parameterized query from SSMS/sqlcmd/Azure Data Studio equivalent-- inspect the actual execution plan and logical readsSET STATISTICS TIME OFF;SET STATISTICS IO OFF;

EF command duration is useful correlation, but SQL Server plan operators, estimated/actual rows, logical reads, waits, and server metrics answer database-side questions more directly.

8. Failure case: “index every filter” slows the write path

A team adds separate and composite indexes for every observed LINQ predicate. Reads improve in one synthetic test, but write latency and log volume rise because each INSERT/UPDATE must maintain many indexes. Similar indexes duplicate storage and maintenance.

Repair: inventory indexes and workload, consolidate overlapping structures, compare read/write benefit, and remove unused/duplicative indexes through a reviewed migration. Preserve a before/after plan and workload metric rather than relying on naming conventions.

9. Mandatory lab: prove one index decision end to end

1. Seed a disposable SQL Server database with a realistic skew of open/closed work orders and technicians; record row counts/distribution.
2. Run the dispatch queue query without the new covering index and capture tagged SQL, actual plan, logical reads, and elapsed time.
3. Add the covering index through an EF migration and inspect the generated CREATE INDEX ... INCLUDE DDL before applying.
4. Re-run the same parameter set and record the plan/read difference. Do not change data or cache disclosure between comparisons without noting it.
5. Add a filtered unique index on ExternalReference, insert NULL and duplicate non-NULL cases, and verify constraint semantics.
6. Generate—but do not automatically apply to any real database—an alternate clustering migration and identify the lock/rebuild/data-size risk.
7. Optionally generate online/fill-factor DDL in Developer edition and mark it as edition/version-sensitive rather than universal production guidance.

10. Production judgment and bridge

SQL Server index APIs are appropriate when they encode measured physical design. Keep them inside provider-specific configuration, review every migration, and validate with real plans/reads on production-like data. Avoid universal fill factors, index-count rules, or “online means no impact” claims. The final lesson ties provider-specific physical and query behavior together by inspecting SQL translations where the EF abstraction intentionally leaks.

Authoritative references

Index features are SQL Server physical design. Verify EF provider APIs and SQL Server/Azure SQL engine support before deployment.

• SQL Server provider indexes - EF Core — clustering, fill factor and online creation
• Indexes - EF Core — filters, included columns and relational index modeling
• SqlServerIndexBuilderExtensions - EF Core 10 API — provider index extension methods
• SQL Server index design guide — engine-side index design and tradeoffs
• Create indexes with included columns — INCLUDE semantics and limitations
• SQL Server filtered indexes — filtered-index behavior and design