In this lesson, you will learn how to identify performance bottlenecks by inspecting how your Entity Framework Core queries interact with the SQL Server plan cache.
Understanding the Plan Cache
When you execute a LINQ-to-Entities query, the database engine must decide on the most efficient way to retrieve your data, known as the Execution Plan. To avoid the expensive overhead of calculating this plan every time, the database stores it in the Plan Cache.
A common performance bottleneck occurs when EF Core generates queries that vary slightly—for example, by including dynamic values directly in the SQL string rather than using parameters. This causes "Plan Cache Pollution," where the database compiles a unique plan for every query variant, filling up memory and forcing the engine to constantly re-compile plans instead of reusing them.
Inspecting Cache Interaction
You can identify these bottlenecks by using Query Tagging to label your queries and then querying the database’s dynamic management views (DMVs) to see if plans are being reused.
// Tagging a query for easy identification in the plan cache
var users = context.Users
.TagWith("FindUserById_Query")
.Where(u => u.Id == userId)
.ToList();
By querying sys.dm_exec_cached_plans and sys.dm_exec_sql_text in SQL Server, you can search for your tag. If you see hundreds of entries for the same query structure, it means your EF Core queries are not being parameterized correctly, and the database is treating every call as a brand-new, unique request.
Performance Impact
When the plan cache is full of unique, non-reusable plans, your application will experience high CPU usage on the database server during the "Compilation" phase. To maintain performance, ensure that your LINQ queries rely on EF Core’s built-in parameterization. If you find your queries are not being cached, check if you are inadvertently using string interpolation—which creates literal values—instead of variable references in your Where clauses.
Key Takeaway: Monitoring the plan cache allows you to verify that EF Core is correctly parameterizing your queries, preventing redundant compilations and ensuring consistent database performance.