{"id":54345,"date":"2025-12-04T06:35:32","date_gmt":"2025-12-04T06:35:32","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=54345"},"modified":"2026-02-21T08:29:37","modified_gmt":"2026-02-21T08:29:37","slug":"database-performance-optimization-in-net-mssql","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/database-performance-optimization-in-net-mssql\/","title":{"rendered":"Database Performance Optimization in .NET + MSSQL"},"content":{"rendered":"\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h3 class=\"wp-block-heading\"><em>A Complete End-to-End Guide for High-Performance Data Access in ASP.NET Applications<\/em><\/h3>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>PREFACE<\/strong><\/h1>\n\n\n\n<p>Modern ASP.NET applications rely heavily on databases\u2014not just to store records, but to power product features, analytics, transactions, recommendation systems, dashboards, and business-critical workflows. As your user base grows, the database often becomes the <strong>#1 bottleneck<\/strong> long before CPU, memory, or network capacity becomes an issue.<\/p>\n\n\n\n<p>This book is designed to be the <strong>definitive, practical, full-stack guide<\/strong> to optimizing MSSQL performance for .NET developers. Unlike traditional DBA books or high-level architecture guides, this book focuses on:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>How .NET interacts with MSSQL<\/strong><\/li>\n\n\n\n<li><strong>How EF Core generates SQL<\/strong><\/li>\n\n\n\n<li><strong>How to diagnose &amp; tune queries<\/strong><\/li>\n\n\n\n<li><strong>How to eliminate latency<\/strong><\/li>\n\n\n\n<li><strong>How to reduce round-trips<\/strong><\/li>\n\n\n\n<li><strong>How to scale reads, writes, concurrency, and throughput<\/strong><\/li>\n\n\n\n<li><strong>How to use modern tools: DMVs, Profiler, Query Store<\/strong><\/li>\n\n\n\n<li><strong>How to leverage AI for database tuning<\/strong><\/li>\n\n\n\n<li><strong>How to write LINQ and SQL that perform under load<\/strong><\/li>\n\n\n\n<li><strong>How to design replicas, stored procedures, indexes, and plans that scale<\/strong><\/li>\n<\/ul>\n\n\n\n<p>This is a <strong>battle-tested guide<\/strong>, refined through real-world experience optimizing production applications handling:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>millions of daily requests<\/li>\n\n\n\n<li>billions of records<\/li>\n\n\n\n<li>high concurrency systems<\/li>\n\n\n\n<li>real-time workloads<\/li>\n<\/ul>\n\n\n\n<p>Whether you&#8217;re building a SaaS app, API, microservice, monolith, dashboard, reporting engine, or enterprise platform \u2014 the principles in this book will give you a <strong>10X improvement in performance<\/strong>, and the confidence to tune any MSSQL-backed .NET application at expert level.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>#<\/th><th>Tool<\/th><th>Category \/ Layer<\/th><th>Why This (vs others)<\/th><th>Typical Use-Cases<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td><strong>Query Store<\/strong> (SQL Server)<\/td><td><strong>Built-in DB \u2013 Slow Query &amp; Plan History<\/strong><\/td><td>Single best built-in tool for <strong>tracking slow queries over time<\/strong>, seeing <strong>plan changes\/regressions<\/strong>, and comparing <strong>before\/after<\/strong> tuning. Replaces the need for ad-hoc logging of all queries.<\/td><td>Identify top CPU\/IO queries, find regressed queries after deployments, force a stable plan, measure impact of index\/query changes.<\/td><\/tr><tr><td>2<\/td><td><strong>Execution Plans (Actual)<\/strong> (SSMS \/ ADS)<\/td><td><strong>Built-in DB \u2013 Per Query Deep Dive<\/strong><\/td><td>The <strong>primary tool for understanding <em>why<\/em> a query is slow<\/strong> \u2013 shows scans vs seeks, key lookups, join types, spills, row estimates. No 3rd-party tool can replace this; others just visualize it differently.<\/td><td>Tune individual queries, fix missing indexes, understand JOIN strategy, verify impact of SQL or index changes.<\/td><\/tr><tr><td>3<\/td><td><strong>DMVs (Dynamic Management Views)<\/strong> + Script Library<\/td><td><strong>Built-in DB \u2013 System-Wide Diagnostics<\/strong><\/td><td>Best way to pull <strong>system-level insight<\/strong>: top CPU queries, missing indexes, index usage, spills, etc. More flexible than GUI tools; forms the backbone for advanced tuning.<\/td><td>Run periodic health checks, detect unused\/inefficient indexes, identify high-CPU or high-IO statements, spot TempDB and memory issues.<\/td><\/tr><tr><td>4<\/td><td><strong>Extended Events<\/strong> (modern Profiler)<\/td><td><strong>Built-in DB \u2013 Lightweight Runtime Tracing<\/strong><\/td><td>Preferable to old Profiler for <strong>production-safe tracing<\/strong>. Captures real-time events with less overhead. Avoids duplication with Profiler; if you choose one, choose Extended Events.<\/td><td>Capture long-running queries, deadlocks, blocking, parameter values, and performance issues in production with minimal overhead.<\/td><\/tr><tr><td>5<\/td><td><strong>Database Engine Tuning Advisor (DTA)<\/strong><\/td><td><strong>Built-in DB \u2013 Index Recommendation Engine<\/strong><\/td><td>Acts as an <strong>automated advisor<\/strong> on top of Query Store \/ traces. Not a replacement for your judgment, but a useful <em>second opinion<\/em> to suggest indexes\/indexed views based on a workload.<\/td><td>Quickly bootstrap indexing strategy for a new or legacy database, evaluate potential index impact before applying.<\/td><\/tr><tr><td>6<\/td><td><strong>Azure Data Studio (ADS)<\/strong> (with extensions)<\/td><td><strong>SQL IDE \u2013 Cross-Platform + Plan\/Query Workbench<\/strong><\/td><td>Lightweight, cross-platform SQL IDE with good execution plan view and extension ecosystem (Copilot integration, notebooks). Keeps you from needing multiple other SQL editors.<\/td><td>Day-to-day query analysis, reading plans, running DMV scripts, documenting investigations in notebooks, using AI assistance (where available).<\/td><\/tr><tr><td>7<\/td><td><strong>Redgate SQL Monitor<\/strong> (or similar single full-stack SQL monitoring tool)<\/td><td><strong>3rd-Party \u2013 Continuous SQL Monitoring &amp; Alerting<\/strong><\/td><td>Instead of many overlapping GUI tools, pick <strong>one mature monitoring suite<\/strong>. SQL Monitor is widely used: performance dashboards, alerts, blocking, waits, top queries. Avoids having separate \u201cperformance dashboard\u201d tools.<\/td><td>24\/7 visibility into SQL Server, alerting on slow queries, deadlocks, long waits, CPU\/IO spikes; historical performance timelines.<\/td><\/tr><tr><td>8<\/td><td><strong>Azure Application Insights<\/strong> (for .NET)<\/td><td><strong>APM \u2013 App \u2194 DB Link<\/strong><\/td><td>Best integrated way (for .NET) to see <strong>which HTTP request caused which SQL query<\/strong>, with timings and dependency traces. Avoids needing multiple APMs \u2013 pick one and this is the most natural if you\u2019re in Azure\/.NET world.<\/td><td>Correlate slow API endpoints to specific slow SQL, understand full request path, track dependency failures and timeouts.<\/td><\/tr><tr><td>9<\/td><td><strong>k6<\/strong> (or your preferred single load tool)<\/td><td><strong>Load Testing \u2013 External Workload Generator<\/strong><\/td><td>Instead of multiple load tools (JMeter\/Locust\/etc.), pick <strong>one<\/strong>. k6 is scriptable, modern, easy for devs. It avoids feature duplication with other load tools.<\/td><td>Generate realistic load against ASP.NET APIs, validate DB performance under stress, run \u201cbefore vs after\u201d tuning experiments.<\/td><\/tr><tr><td>10<\/td><td><strong>BenchmarkDotNet<\/strong><\/td><td><strong>Code-Level Micro-Benchmarking (.NET)<\/strong><\/td><td>Best-in-class for micro-benchmarks; no need for other micro-benchmark libs. Measures EF vs Dapper vs raw ADO, LINQ versions, allocation differences.<\/td><td>Benchmark individual data-access paths, compare EF Core query shapes, verify optimization impact on small hot code paths.<\/td><\/tr><tr><td>11<\/td><td><strong>EF Core Logging &amp; Diagnostic Events<\/strong><\/td><td><strong>ORM-Level Visibility (No Extra Tool)<\/strong><\/td><td>Built-in, zero extra deployment, and gives precise visibility into <strong>what SQL EF generates<\/strong> and how often. Avoids needing separate EF-specific profilers.<\/td><td>Detect N+1 queries, see generated SQL, measure query counts per request, understand loading behavior (lazy vs eager).<\/td><\/tr><tr><td>12<\/td><td><strong>Redis (Distributed Cache) + IMemoryCache<\/strong><\/td><td><strong>Offload DB Reads (Caching)<\/strong><\/td><td>Rather than multiple caching systems, pick <strong>MemoryCache (L1)<\/strong> + <strong>Redis (L2)<\/strong> as your standard. Directly reduces DB load &amp; latency, so belongs in a performance toolkit.<\/td><td>Cache lookups, reference data, frequently-read aggregates, reduce read pressure on MSSQL, speed up APIs by orders of magnitude.<\/td><\/tr><tr><td>13<\/td><td><strong>GitHub Copilot \/ AI + ChatGPT (or similar AI assistant)<\/strong><\/td><td><strong>AI-Assisted Optimization<\/strong><\/td><td>Not a replacement for Query Store\/Plans, but immensely useful for <strong>explaining plans, rewriting SQL, suggesting indexes, improving LINQ<\/strong>. Avoid multiple AI tools: pick 1\u20132 you trust.<\/td><td>Paste queries\/plans, ask for optimization suggestions, generate index ideas, convert problematic LINQ to efficient SQL-shaped queries.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfdb\ufe0f <strong>PART I \u2014 FOUNDATIONS OF DATABASE PERFORMANCE<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\"><strong>CHAPTER 1 \u2014 Understanding Database Performance in .NET<\/strong><\/h1>\n\n\n\n<p>Database performance is not only about \u201cfast queries\u201d. It is about ensuring your application can:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>serve more users<\/li>\n\n\n\n<li>handle more concurrent requests<\/li>\n\n\n\n<li>reduce latency<\/li>\n\n\n\n<li>avoid lock contention<\/li>\n\n\n\n<li>sustain high throughput<\/li>\n\n\n\n<li>scale without rewriting architecture<\/li>\n<\/ul>\n\n\n\n<p>To understand optimization, we must understand the <strong>core pillars<\/strong> of DB performance in .NET.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2b50 <strong>1.1 Where Performance Problems Normally Come From<\/strong><\/h2>\n\n\n\n<p>Based on thousands of production applications, the biggest bottlenecks are:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2460 Too many round-trips to the database<\/strong><\/h3>\n\n\n\n<p>Each DB call adds:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>network latency<\/li>\n\n\n\n<li>connection acquisition time<\/li>\n\n\n\n<li>query compilation time<\/li>\n\n\n\n<li>result streaming<\/li>\n<\/ul>\n\n\n\n<p>Even if each query is fast, 100 small queries = slow API.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2461 Bad EF Core\/LINQ usage<\/strong><\/h3>\n\n\n\n<p>Examples:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>N+1 queries<\/li>\n\n\n\n<li>fetching more data than needed<\/li>\n\n\n\n<li>unnecessary tracking<\/li>\n\n\n\n<li>generating poor SQL<\/li>\n\n\n\n<li>forcing client-side evaluation<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2462 Missing or incorrect indexes<\/strong><\/h3>\n\n\n\n<p>Symptoms:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>table scans<\/li>\n\n\n\n<li>key lookups<\/li>\n\n\n\n<li>expensive sorts<\/li>\n\n\n\n<li>wrong cardinality estimates<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2463 Long-running transactions<\/strong><\/h3>\n\n\n\n<p>Causes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>business logic inside a transaction<\/li>\n\n\n\n<li>large SaveChanges loops<\/li>\n\n\n\n<li>high isolation levels<\/li>\n\n\n\n<li>poor batching<\/li>\n<\/ul>\n\n\n\n<p>Outcome: <strong>blocking &amp; deadlocks<\/strong>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2464 Parameter sniffing<\/strong><\/h3>\n\n\n\n<p>A major but often invisible issue \u2014 one cached plan works for some parameters but becomes terrible for others.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2b50 <strong>1.2 How a .NET Request Translates Into DB Work<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">ASP.NET Request \u2192 DbContext \u2192 ADO.NET \u2192 SQL Server<\/h3>\n\n\n\n<p><strong>Step 1 \u2014 Controller\/Endpoint calls application logic<\/strong><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders.ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><strong>Step 2 \u2014 EF Core translates LINQ \u2192 SQL<\/strong><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">SELECT<\/span> <span class=\"hljs-selector-attr\">&#91;o]<\/span>.<span class=\"hljs-selector-attr\">&#91;Id]<\/span>, <span class=\"hljs-selector-attr\">&#91;o]<\/span>.<span class=\"hljs-selector-attr\">&#91;OrderDate]<\/span>, <span class=\"hljs-selector-attr\">&#91;o]<\/span>.<span class=\"hljs-selector-attr\">&#91;CustomerId]<\/span>\n<span class=\"hljs-selector-tag\">FROM<\/span> <span class=\"hljs-selector-attr\">&#91;Orders]<\/span> <span class=\"hljs-selector-tag\">AS<\/span> <span class=\"hljs-selector-attr\">&#91;o]<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><strong>Step 3 \u2014 ADO.NET submits via connection pooling<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Gets or creates a connection<\/li>\n\n\n\n<li>Sends SQL over TCP to SQL Server<\/li>\n<\/ul>\n\n\n\n<p><strong>Step 4 \u2014 SQL Server optimizer builds an execution plan<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Uses statistics<\/li>\n\n\n\n<li>Picks indexes<\/li>\n\n\n\n<li>Determines join method<\/li>\n<\/ul>\n\n\n\n<p><strong>Step 5 \u2014 SQL Server executes plan<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Reads pages from buffer pool or disk<\/li>\n\n\n\n<li>Applies operators (scan, seek, join)<\/li>\n\n\n\n<li>Streams results<\/li>\n<\/ul>\n\n\n\n<p><strong>Step 6 \u2014 Results flow back to .NET<\/strong><\/p>\n\n\n\n<p>All optimization efforts aim to reduce work in this pipeline.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2b50 <strong>1.3 Understanding the 3 Dimensions of Performance<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2460 Latency (How fast one request finishes)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>speed of single query<\/li>\n\n\n\n<li>execution time<\/li>\n\n\n\n<li>plan quality<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2461 Throughput (How many requests can run per second)<\/strong><\/h3>\n\n\n\n<p>Affected by:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>locks<\/li>\n\n\n\n<li>connection pool<\/li>\n\n\n\n<li>CPU usage<\/li>\n\n\n\n<li>thread starvation<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2462 Concurrency (How well the system performs under load)<\/strong><\/h3>\n\n\n\n<p>Common issues:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>deadlocks<\/li>\n\n\n\n<li>lock escalation<\/li>\n\n\n\n<li>long transactions<\/li>\n\n\n\n<li>exhausted connection pools<\/li>\n<\/ul>\n\n\n\n<p>True DB excellence means optimizing <strong>all three<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2b50 <strong>1.4 How to Measure Performance Correctly<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Use:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>SET STATISTICS IO ON<\/code><\/li>\n\n\n\n<li>SQL Profiler \/ Extended Events<\/li>\n\n\n\n<li>Query Store<\/li>\n\n\n\n<li>DMVs<\/li>\n\n\n\n<li>Benchmark.NET (application-side)<\/li>\n\n\n\n<li>Load tests (locust, k6, wrk)<\/li>\n<\/ul>\n\n\n\n<p>Never rely on \u201cit feels faster\u201d.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2b50 <strong>1.5 Example: Good vs Bad Query Flow<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\u274c Example: Bad LINQ<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> customers = <span class=\"hljs-keyword\">await<\/span> _db.Customers.ToListAsync();\n\nforeach (<span class=\"hljs-keyword\">var<\/span> customer <span class=\"hljs-keyword\">in<\/span> customers)\n{\n    <span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n        .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.CustomerId == customer.Id)\n        .ToListAsync();\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><strong>Results in 1 + N queries (N+1 Problem)<\/strong><br>Slow at scale.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 Example: Good LINQ<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> customers = <span class=\"hljs-keyword\">await<\/span> _db.Customers\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> c.Orders)\n    .AsNoTracking()\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>One single SQL round trip.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\"><strong>CHAPTER 2 \u2014 Architecture of High-Performance MSSQL for .NET<\/strong><\/h1>\n\n\n\n<p>This chapter explains how MSSQL works internally\u2014not as a DBA\u2014but exactly the parts developers need to tune performance.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>2.1 Understanding SQL Server Internals (Developer Version)<\/strong><\/h1>\n\n\n\n<p>SQL Server is built on these foundations:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2460 Relational Engine<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Parses SQL<\/li>\n\n\n\n<li>Compiles query<\/li>\n\n\n\n<li>Creates execution plan<\/li>\n\n\n\n<li>Reuses cached plans<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2461 Storage Engine<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Reads\/writes data pages<\/li>\n\n\n\n<li>Manages memory (buffer pool)<\/li>\n\n\n\n<li>Applies locks &amp; latches<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2462 Buffer Pool<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL Server caches data &amp; index pages<\/li>\n\n\n\n<li>Avoids disk reads<\/li>\n\n\n\n<li>Critical for performance<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>2.2 How the Query Optimizer Works<\/strong><\/h1>\n\n\n\n<p>When EF Core sends SQL:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>SQL Server parses the query<\/li>\n\n\n\n<li>Optimizer considers MANY possible execution plans<\/li>\n\n\n\n<li>Uses <strong>statistics<\/strong> to estimate row counts<\/li>\n\n\n\n<li>Picks cheapest plan<\/li>\n\n\n\n<li>Stores plan in cache for reuse<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">If statistics are outdated:<\/h3>\n\n\n\n<p>\u2192 wrong plan<br>\u2192 poor performance<br>\u2192 excessive scans<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>2.3 How EF Core Affects SQL Server Performance<\/strong><\/h1>\n\n\n\n<p>EF Core impacts SQL Server through:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>LINQ \u2192 SQL Translation Quality<\/strong><\/h3>\n\n\n\n<p>Bad LINQ can generate terrible SQL:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>unnecessary joins<\/li>\n\n\n\n<li>client-side filtering<\/li>\n\n\n\n<li>redundant subqueries<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Loading strategy<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>lazy loading = many queries<\/li>\n\n\n\n<li>eager\/projection = efficient<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Tracking<\/strong><\/h3>\n\n\n\n<p>EF Core\u2019s change tracker impacts performance on:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>CPU<\/li>\n\n\n\n<li>memory<\/li>\n\n\n\n<li>caching<\/li>\n<\/ul>\n\n\n\n<p>For read-only workloads, always use <code>AsNoTracking()<\/code>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>2.4 The High-Performance .NET Database Architecture<\/strong><\/h1>\n\n\n\n<p>A well-designed .NET + MSSQL architecture includes:<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>App Layer<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Controllers \/ Endpoints<\/li>\n\n\n\n<li>Services<\/li>\n\n\n\n<li>Query Handlers (CQRS)<\/li>\n\n\n\n<li>Using:\n<ul class=\"wp-block-list\">\n<li>Connection pooling<\/li>\n\n\n\n<li>Short lived DbContexts<\/li>\n\n\n\n<li>Batch operations<\/li>\n\n\n\n<li>Cancellation tokens<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Data Access Layer<\/strong><\/h2>\n\n\n\n<p>Options:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>EF Core (default choice)<\/li>\n\n\n\n<li>Dapper (fastest for read-heavy)<\/li>\n\n\n\n<li>Stored Procedures (hot paths)<\/li>\n\n\n\n<li>Hybrid DAL pattern<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Database Layer<\/strong><\/h2>\n\n\n\n<p>Optimized using:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>indexes<\/li>\n\n\n\n<li>updated statistics<\/li>\n\n\n\n<li>tuned queries<\/li>\n\n\n\n<li>proper execution plans<\/li>\n\n\n\n<li>well-designed schema<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Caching Layer<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Redis<\/li>\n\n\n\n<li>MemoryCache<\/li>\n\n\n\n<li>Distributed cache<br>Used for:<\/li>\n\n\n\n<li>reference data<\/li>\n\n\n\n<li>session data<\/li>\n\n\n\n<li>rate limiting<\/li>\n\n\n\n<li>frequently accessed datasets<\/li>\n<\/ul>\n\n\n\n<p>(Full caching patterns will be covered in the appendices.)<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>2.5 Common Anti-Patterns in .NET + MSSQL Systems<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u274c Long-lived DbContext (singleton)<\/strong><\/h3>\n\n\n\n<p>Leads to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>connection leaks<\/li>\n\n\n\n<li>stale tracking<\/li>\n\n\n\n<li>memory bloat<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u274c Storing large objects in MSSQL<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>images<\/li>\n\n\n\n<li>PDFs<\/li>\n\n\n\n<li>huge JSON<br>Better: store in Blob Storage.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u274c SELECT * queries<\/strong><\/h3>\n\n\n\n<p>Waste CPU, network, memory.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>\u274c Endless SaveChanges() loops<\/strong><\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">foreach (<span class=\"hljs-keyword\">var<\/span> item <span class=\"hljs-keyword\">in<\/span> items)\n{\n    _db.Add(item);\n    <span class=\"hljs-keyword\">await<\/span> _db.SaveChangesAsync();\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\"><strong>\u2714 Fix<\/strong><\/h3>\n\n\n\n<p>Perform a single bulk insert or <code>ExecuteUpdateAsync<\/code>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>2.6 Example Architecture Diagram<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510      \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510     \n\u2502 ASP.NET API Layer \u2502 ---&gt; \u2502 Data Access Layer   \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518      \u2502  (EF Core\/Dapper)   \u2502\n                           \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                                       \u2502\n                            \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u25bc\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n                            \u2502   SQL Server Engine   \u2502\n                            \u2502(Optimizer + Storage)  \u2502\n                            \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                                       \u2502\n                            \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u25bc\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n                            \u2502     Buffer Pool       \u2502\n                            \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n<\/code><\/span><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>2.7 What This Book Will Achieve For You<\/strong><\/h1>\n\n\n\n<p>By the end, you will be able to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Tune EF Core queries like a pro<\/li>\n\n\n\n<li>Detect and eliminate N+1 queries<\/li>\n\n\n\n<li>Read execution plans with confidence<\/li>\n\n\n\n<li>Fix parameter sniffing<\/li>\n\n\n\n<li>Apply correct indexing strategies<\/li>\n\n\n\n<li>Design fast transactions<\/li>\n\n\n\n<li>Reduce round-trips<\/li>\n\n\n\n<li>Scale reads with replicas<\/li>\n\n\n\n<li>Use DMVs and Query Store effectively<\/li>\n\n\n\n<li>Build .NET apps that handle 10\u00d7 more load<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83e\udde9 <strong>Message 2 \u2014 PART II (ORM-Level Optimization)<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 3 \u2014 EF Core Query Optimization<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 4 \u2014 Avoiding N+1 Queries<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 5 \u2014 Query Design Optimization<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 6 \u2014 Batch &amp; Bulk Operations<\/h3>\n\n\n\n<p>Reply <strong>\u201ccontinue\u201d<\/strong> whenever you\u2019re ready for Message 2.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfce\ufe0f <strong>CHAPTER 3 \u2014 EF Core Query Optimization<\/strong><\/h1>\n\n\n\n<p>EF Core is powerful, but it\u2019s also one of the biggest sources of hidden database inefficiencies in ASP.NET applications. Its default behaviors\u2014tracking, lazy loading, LINQ translation\u2014can generate slow SQL, excessive round-trips, and unnecessary memory overhead.<\/p>\n\n\n\n<p>This chapter teaches you how to write <strong>high-performance EF Core<\/strong> while maintaining clean architecture.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3.1 The Most Important Concept: Tracking vs. No-Tracking<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Tracking mode (default)<\/strong><\/h3>\n\n\n\n<p>EF Core tracks entities to detect changes during SaveChanges.<\/p>\n\n\n\n<p>This adds:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>CPU overhead<\/li>\n\n\n\n<li>Memory overhead<\/li>\n\n\n\n<li>Object graph overhead<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 Use <code>AsTracking()<\/code> ONLY when updating<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 Use <code>AsNoTracking()<\/code> for all read-only queries<\/h3>\n\n\n\n<p>Example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .AsNoTracking()\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.CustomerId == id)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Performance gains:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>20\u201340% faster query materialization<\/li>\n\n\n\n<li>Lower memory usage<\/li>\n\n\n\n<li>Less GC pressure<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3.2 Understanding EF Core LINQ \u2192 SQL Translation<\/strong><\/h1>\n\n\n\n<p>EF Core tries to convert your LINQ into SQL. But:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Not all LINQ translates efficiently<\/li>\n\n\n\n<li>Some expressions force <strong>client-side evaluation<\/strong><\/li>\n\n\n\n<li>Complex expressions may produce heavy SQL<\/li>\n<\/ul>\n\n\n\n<p>Example of BAD LINQ:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">x<\/span> =&gt;<\/span> SomeHelperMethod(x.Amount))\n    .ToList();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If EF cannot translate <code>SomeHelperMethod<\/code>, it downloads all rows then filters in memory.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 Always ensure LINQ is translatable to SQL<\/h3>\n\n\n\n<p>Keep your queries &#8220;SQL-shaped&#8221;.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3.3 Always Filter Early<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Items)\n    .ToListAsync();\n\n<span class=\"hljs-keyword\">var<\/span> result = orders.Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.CustomerId == id);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This loads <em>all<\/em> orders and items, THEN filters.<\/p>\n\n\n\n<p>Good:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.CustomerId == id)\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Items)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3.4 Use Projections to Reduce Data Size<\/strong><\/h1>\n\n\n\n<p>Fetching full entities = heavy.<br>Most web APIs only need a subset.<\/p>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Items)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Good:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> dtos = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.CustomerId == id)\n    .Select(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> <span class=\"hljs-keyword\">new<\/span> OrderDto {\n        Id = o.Id,\n        Total = o.Items.Sum(<span class=\"hljs-function\"><span class=\"hljs-params\">i<\/span> =&gt;<\/span> i.Price),\n        ItemCount = o.Items.Count\n    })\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Benefits:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>smaller results<\/li>\n\n\n\n<li>less serialization time<\/li>\n\n\n\n<li>fewer EF objects<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3.5 Avoid Premature <code>ToList()<\/code> Calls<\/strong><\/h1>\n\n\n\n<p>This is a <strong>classic mistake<\/strong>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = _db.Orders.ToList();\n<span class=\"hljs-keyword\">var<\/span> result = orders.Where(<span class=\"hljs-function\"><span class=\"hljs-params\">x<\/span> =&gt;<\/span> x.Total &gt; <span class=\"hljs-number\">100<\/span>);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This loads the entire table.<\/p>\n\n\n\n<p>Correct:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">x<\/span> =&gt;<\/span> x.Total &gt; <span class=\"hljs-number\">100<\/span>)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3.6 Avoid Anonymous Includes<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">context<\/span><span class=\"hljs-selector-class\">.Orders<\/span><span class=\"hljs-selector-class\">.Include<\/span>(\"<span class=\"hljs-selector-tag\">Items<\/span>\")<span class=\"hljs-selector-class\">.ToList<\/span>();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Good:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">context.Orders.Include(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Items).ToList();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>String-based includes bypass compile-time safety and sometimes break eager loading rules.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3.7 Avoid Out-of-Control Includes<\/strong><\/h1>\n\n\n\n<p>This is called <strong>\u201cInclude Hell\u201d<\/strong>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">context.Customers\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> c.Orders)\n    .ThenInclude(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Items)\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> c.Addresses)\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> c.Payments)\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> c.Notes)\n    ...\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Too many includes create <strong>monster SQL joins<\/strong>.<\/p>\n\n\n\n<p>Fix:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use separate queries<\/li>\n\n\n\n<li>Use projections<\/li>\n\n\n\n<li>Load only what you need<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3.8 EF Core Logging for Performance<\/strong><\/h1>\n\n\n\n<p>Enable verbose logging:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-17\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">services.AddDbContext&lt;AppDbContext&gt;(<span class=\"hljs-function\"><span class=\"hljs-params\">options<\/span> =&gt;<\/span>\n{\n    options.UseSqlServer(connString)\n           .EnableSensitiveDataLogging()\n           .LogTo(Console.WriteLine);\n});\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-17\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This exposes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>generated SQL<\/li>\n\n\n\n<li>round-trips<\/li>\n\n\n\n<li>timings<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3.9 Summary<\/strong><\/h1>\n\n\n\n<p>EF Core optimization focuses on:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Reducing tracking<\/li>\n\n\n\n<li>Improving LINQ \u2192 SQL translation<\/li>\n\n\n\n<li>Filtering early<\/li>\n\n\n\n<li>Reducing data size<\/li>\n\n\n\n<li>Avoiding N+1 queries<\/li>\n\n\n\n<li>Avoiding giant includes<\/li>\n<\/ul>\n\n\n\n<p>These principles alone can <strong>3\u00d7 performance<\/strong> in real apps.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 3<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\ude80 <strong>CHAPTER 4 \u2014 Avoiding N+1 Queries<\/strong><\/h1>\n\n\n\n<p>One of the most common and devastating problems in ORM-based applications is the <strong>N+1 query problem<\/strong>. It happens when a developer unintentionally triggers <strong>one query for the parent<\/strong>, then <strong>one additional query per child<\/strong>.<\/p>\n\n\n\n<p>This silently destroys performance.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>4.1 What is N+1?<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Example scenario<\/h3>\n\n\n\n<p>Suppose you want to load customers with their orders.<\/p>\n\n\n\n<p>Bad code:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-18\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> customers = <span class=\"hljs-keyword\">await<\/span> _db.Customers.ToListAsync();\n\nforeach (<span class=\"hljs-keyword\">var<\/span> customer <span class=\"hljs-keyword\">in<\/span> customers)\n{\n    <span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n        .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.CustomerId == customer.Id)\n        .ToListAsync();\n\n    customer.Orders = orders;\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-18\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you have 100 customers:<br>\u2192 1 query for customers<br>\u2192 100 queries for their orders<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Total: <strong>101 queries<\/strong> (N+1)<\/h3>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>4.2 Why N+1 Is Catastrophic<\/strong><\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Each query adds network latency<\/li>\n\n\n\n<li>SQL Server receives many small calls<\/li>\n\n\n\n<li>Connection pool exhaustion<\/li>\n\n\n\n<li>Causes exponential slowdown under concurrency<\/li>\n\n\n\n<li>Nearly impossible to scale<\/li>\n<\/ul>\n\n\n\n<p>Even if each query is fast, 100 small queries = <strong>slow API<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>4.3 How to Detect N+1<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Using <strong>SQL Profiler<\/strong><\/h3>\n\n\n\n<p>Watch for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>many repeated SELECT statements<\/li>\n\n\n\n<li>identical SQL patterns<\/li>\n\n\n\n<li>increasing queries during loops<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Using <strong>EF Core Logging<\/strong><\/h3>\n\n\n\n<p>Enable logging as shown in Chapter 3.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Using <strong>MiniProfiler<\/strong><\/h3>\n\n\n\n<p>A phenomenal tool.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>4.4 The Fix: Eager Loading<\/strong><\/h1>\n\n\n\n<p>Correct solution:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-19\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> customers = <span class=\"hljs-keyword\">await<\/span> _db.Customers\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> c.Orders)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-19\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">SQL generated:<\/h3>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT c.*, o.*\nFROM Customers c\nLEFT JOIN Orders o ON o.CustomerId = c.Id\n<\/code><\/span><\/pre>\n\n\n<p>One query.<br>Zero loops.<br>10\u00d7 performance improvement.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>4.5 The Fix: Projection (Best Method)<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-20\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = <span class=\"hljs-keyword\">await<\/span> _db.Customers\n    .Select(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> <span class=\"hljs-keyword\">new<\/span> CustomerWithOrdersDto {\n        Id = c.Id,\n        Name = c.Name,\n        Orders = c.Orders.Select(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> <span class=\"hljs-keyword\">new<\/span> OrderDto {\n            OrderId = o.Id,\n            Amount = o.Amount\n        }).ToList()\n    })\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-20\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This produces <strong>highly optimized SQL<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>4.6 The Fix: Explicit Loading (rare)<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-21\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> customer = <span class=\"hljs-keyword\">await<\/span> _db.Customers.FirstAsync(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> c.Id == id);\n<span class=\"hljs-keyword\">await<\/span> _db.Entry(customer).Collection(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> c.Orders).LoadAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-21\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Use only when:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You load one entity<\/li>\n\n\n\n<li>You control load sequence<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>4.7 BAD: Lazy Loading<\/strong><\/h1>\n\n\n\n<p>Lazy loading is convenient, but causes automatic N+1.<\/p>\n\n\n\n<p>Disable it:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-22\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">services.AddDbContext&lt;AppDbContext&gt;(<span class=\"hljs-function\"><span class=\"hljs-params\">options<\/span> =&gt;<\/span>\n{\n    options.UseLazyLoadingProxies(<span class=\"hljs-literal\">false<\/span>);\n});\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-22\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>4.8 Summary<\/strong><\/h1>\n\n\n\n<p>To eliminate N+1:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Prefer eager loading<\/li>\n\n\n\n<li>Prefer projections<\/li>\n\n\n\n<li>Avoid lazy loading<\/li>\n\n\n\n<li>Watch for hidden loops<\/li>\n\n\n\n<li>Use profiler tools<\/li>\n<\/ol>\n\n\n\n<p>Fixing N+1 typically yields a <strong>5\u00d7\u201320\u00d7 performance improvement<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 4<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83e\udde0 <strong>CHAPTER 5 \u2014 Query Design Optimization (SQL + LINQ)<\/strong><\/h1>\n\n\n\n<p>Optimizing a database query starts long before indexing\u2014it starts with <strong>writing good LINQ and good SQL<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>5.1 Avoid SELECT *<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-23\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders.ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-23\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Problems:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Loads unnecessary columns<\/li>\n\n\n\n<li>Wastes network bandwidth<\/li>\n\n\n\n<li>Wastes SQL Server CPU<\/li>\n\n\n\n<li>Slows serialization<\/li>\n<\/ul>\n\n\n\n<p>Always fetch only what you need:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-24\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Select(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> <span class=\"hljs-keyword\">new<\/span> { o.Id, o.Total, o.OrderDate })\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-24\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>5.2 Filter Early, Filter Often<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-25\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> list = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Items)\n    .ToListAsync();\n\n<span class=\"hljs-keyword\">var<\/span> result = list.Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.OrderDate &gt; cutoff);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-25\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Good:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-26\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.OrderDate &gt; cutoff)\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Items)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-26\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>5.3 Prefer Server-Side Computation<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-27\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders.ToListAsync();\n\n<span class=\"hljs-keyword\">var<\/span> high = orders.Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Amount &gt; <span class=\"hljs-number\">100<\/span>);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-27\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Good:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-28\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> high = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Amount &gt; <span class=\"hljs-number\">100<\/span>)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-28\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>5.4 Correct Pagination<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-29\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Skip(page * size)\n    .Take(size)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-29\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This is correct but may be slow for huge offsets.<\/p>\n\n\n\n<p>Better:<\/p>\n\n\n\n<p>Use <strong>keyset pagination<\/strong>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-30\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Id &gt; lastId)\n    .Take(size)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-30\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>5.5 Avoid Complex LINQ in a Single Expression<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-31\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result =\n    <span class=\"hljs-keyword\">from<\/span> o <span class=\"hljs-keyword\">in<\/span> _db.Orders\n    where o.Customer.Name.StartsWith(<span class=\"hljs-string\">\"A\"<\/span>)\n    select <span class=\"hljs-keyword\">new<\/span>\n    {\n        Total = o.Items.Sum(<span class=\"hljs-function\"><span class=\"hljs-params\">i<\/span> =&gt;<\/span> i.Price * i.Quantity)\n    };\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-31\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This generates <strong>heavy SQL<\/strong>.<\/p>\n\n\n\n<p>Better:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-32\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Customer.Name.StartsWith(<span class=\"hljs-string\">\"A\"<\/span>))\n    .Select(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> <span class=\"hljs-keyword\">new<\/span> {\n        o.Id,\n        Total = o.Items.Sum(<span class=\"hljs-function\"><span class=\"hljs-params\">i<\/span> =&gt;<\/span> i.Price * i.Quantity)\n    })\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-32\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>5.6 Avoid Client-Side Evaluation<\/strong><\/h1>\n\n\n\n<p>Example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-33\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> IsPrime(o.Id)) <span class=\"hljs-comment\">\/\/ cannot translate to SQL<\/span>\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-33\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>EF Core falls back to client-side evaluation.<br>VERY bad.<\/p>\n\n\n\n<p>Fix:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Move logic server side<\/li>\n\n\n\n<li>Precompute fields<\/li>\n\n\n\n<li>Use computed column<\/li>\n\n\n\n<li>Use raw SQL if needed<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>5.7 Use Raw SQL for Complex Cases<\/strong><\/h1>\n\n\n\n<p>Example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-34\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .FromSqlInterpolated($<span class=\"hljs-string\">\"SELECT TOP 10 * FROM Orders WHERE ...\"<\/span>)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-34\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>5.8 Summary<\/strong><\/h1>\n\n\n\n<p>Good query design:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Minimal columns<\/li>\n\n\n\n<li>Minimal rows<\/li>\n\n\n\n<li>Server-side filtering<\/li>\n\n\n\n<li>Minimal joins<\/li>\n\n\n\n<li>Efficient pagination<\/li>\n\n\n\n<li>SQL-shaped LINQ<\/li>\n<\/ul>\n\n\n\n<p>This chapter alone provides 2\u00d7\u201310\u00d7 improvement.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 5<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\udca5 <strong>CHAPTER 6 \u2014 Batch Operations \/ Bulk Operations<\/strong><\/h1>\n\n\n\n<p>This chapter covers techniques to handle <strong>large writes<\/strong> efficiently. SaveChanges loops are one of the <strong>biggest silent performance killers<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>6.1 Why SaveChanges Loops Are Terrible<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-35\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">foreach (<span class=\"hljs-keyword\">var<\/span> item <span class=\"hljs-keyword\">in<\/span> items)\n{\n    _db.Add(item);\n    <span class=\"hljs-keyword\">await<\/span> _db.SaveChangesAsync();\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-35\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Each iteration:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>new transaction<\/li>\n\n\n\n<li>new insert<\/li>\n\n\n\n<li>new log record<\/li>\n<\/ul>\n\n\n\n<p>If you have 500 records \u2192 500 transactions.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>6.2 Solution: Batching with EF Core 7+ ExecuteUpdate<\/strong><\/h1>\n\n\n\n<p>EF Core 7 introduced batch commands.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Update<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-36\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Status == Status.Pending)\n    .ExecuteUpdateAsync(<span class=\"hljs-function\"><span class=\"hljs-params\">setters<\/span> =&gt;<\/span>\n        setters.SetProperty(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Status, Status.Processed));\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-36\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Delete<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-37\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.CreatedOn &lt; cutoff)\n    .ExecuteDeleteAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-37\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>\u2714 No materialization<br>\u2714 One SQL statement<br>\u2714 Massively faster<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>6.3 Bulk Insert Options<\/strong><\/h1>\n\n\n\n<p>EF Core does not provide native bulk insert yet, but there are options:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Option 1 \u2014 <strong>SqlBulkCopy<\/strong> (fastest)<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-38\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">using <span class=\"hljs-keyword\">var<\/span> bulk = <span class=\"hljs-keyword\">new<\/span> SqlBulkCopy(connection);\nbulk.DestinationTableName = <span class=\"hljs-string\">\"Orders\"<\/span>;\n<span class=\"hljs-keyword\">await<\/span> bulk.WriteToServerAsync(dataTable);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-38\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Option 2 \u2014 <strong>EFCore.BulkExtensions<\/strong><\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-39\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">await<\/span> _db.BulkInsertAsync(list);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-39\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Option 3 \u2014 <strong>Stored Procedure Bulk Insert<\/strong><\/h3>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>6.4 Example: 100\u00d7 Faster Inserts<\/strong><\/h1>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Method<\/th><th>Time for 10k rows<\/th><\/tr><\/thead><tbody><tr><td>SaveChanges loop<\/td><td>~30 seconds<\/td><\/tr><tr><td>BulkExtensions<\/td><td>~0.5 seconds<\/td><\/tr><tr><td>SqlBulkCopy<\/td><td>~0.2 seconds<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>6.5 Batch Reads Using Split Queries<\/strong><\/h1>\n\n\n\n<p>EF Core 7+<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-40\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Items)\n    .AsSplitQuery()\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-40\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Avoids giant join explosions.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>6.6 Summary<\/strong><\/h1>\n\n\n\n<p>For high-performance writes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Avoid SaveChanges loops<\/li>\n\n\n\n<li>Use ExecuteUpdate\/Delete<\/li>\n\n\n\n<li>Use Bulk Insert methods<\/li>\n\n\n\n<li>Use Split Queries for big includes<\/li>\n\n\n\n<li>Use batching<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<p>Excellent.<br>Here is <strong>Message 4<\/strong>, containing <strong>Part IV: Execution Plans &amp; Diagnostics<\/strong>.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>PART IV \u2014 EXECUTION PLANS &amp; DIAGNOSTICS<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 11 \u2014 Execution Plan Analysis<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 12 \u2014 SQL Profiler &amp; Query Store<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 13 \u2014 DMV-Based Diagnostics<\/h3>\n\n\n\n<p>These chapters elevate you to <em>database surgeon<\/em> level \u2014 enabling you to read exactly what MSSQL is doing and why.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\udd0d <strong>CHAPTER 11 \u2014 Execution Plan Analysis<\/strong><\/h1>\n\n\n\n<p>Execution plans are the <strong>DNA of database performance<\/strong>. Every slow query reveals its secrets inside the execution plan. Being able to read plans makes you a true MSSQL performance expert.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>11.1 What Is an Execution Plan?<\/strong><\/h1>\n\n\n\n<p>An execution plan is the <strong>set of operations<\/strong> SQL Server uses to execute your query:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>index seek<\/li>\n\n\n\n<li>index scan<\/li>\n\n\n\n<li>hash match<\/li>\n\n\n\n<li>nested loops<\/li>\n\n\n\n<li>key lookup<\/li>\n\n\n\n<li>sort<\/li>\n\n\n\n<li>compute scalar<\/li>\n\n\n\n<li>join operations<\/li>\n\n\n\n<li>spool operations<\/li>\n\n\n\n<li>parallel worker distribution<\/li>\n<\/ul>\n\n\n\n<p>It also reveals:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>estimated vs actual row counts<\/li>\n\n\n\n<li>memory usage<\/li>\n\n\n\n<li>warnings<\/li>\n\n\n\n<li>spills<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>11.2 Actual vs Estimated Plans<\/strong><\/h1>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Type<\/th><th>Meaning<\/th><\/tr><\/thead><tbody><tr><td><strong>Estimated Plan<\/strong><\/td><td>SQL Server predicts operations before running query<\/td><\/tr><tr><td><strong>Actual Plan<\/strong><\/td><td>SQL Server gives real results after execution<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Always prefer <strong>Actual Execution Plan<\/strong> for tuning.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>11.3 How to View the Execution Plan<\/strong><\/h1>\n\n\n\n<p>In SSMS:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Highlight query<\/li>\n\n\n\n<li>Click:\n<ul class=\"wp-block-list\">\n<li><strong>Ctrl + M<\/strong> \u2192 Include Actual Plan<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Execute query<\/li>\n\n\n\n<li>Switch to <em>Execution Plan<\/em> tab<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>11.4 Key Operators Every .NET Developer Must Know<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Index Seek<\/strong> (excellent)<\/h2>\n\n\n\n<p>SQL Server uses an index to locate rows quickly.<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">Index Seek (NonClustered)\n<\/code><\/span><\/pre>\n\n\n<p>\u2192 Fastest lookup method<br>\u2192 Ideal for WHERE, JOIN<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Index Scan<\/strong> (can be bad or okay)<\/h2>\n\n\n\n<p>SQL Server scans entire index.<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">Index Scan\n<\/code><\/span><\/pre>\n\n\n<p>Scanning might be fine for non-selective queries, but often indicates missing index.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Table Scan<\/strong> (often terrible)<\/h2>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">Table Scan\n<\/code><\/span><\/pre>\n\n\n<p>Means no useful index.<br>SQL Server scans entire table pages.<\/p>\n\n\n\n<p>\u2192 fix by adding appropriate index.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Key Lookup<\/strong> (common fixable issue)<\/h2>\n\n\n\n<p>Occurs when SQL Server finds row via nonclustered index but must fetch additional columns.<\/p>\n\n\n\n<p>Fix: Make a <strong>covering index<\/strong> that includes those columns.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Nested Loops Join<\/strong><\/h2>\n\n\n\n<p>Fast for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>small outer result<\/li>\n\n\n\n<li>indexed inner table<\/li>\n<\/ul>\n\n\n\n<p>Dangerous when:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>outer set is large<\/li>\n\n\n\n<li>no index on inner table<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Hash Match Join<\/strong><\/h2>\n\n\n\n<p>Used when:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>large table joins<\/li>\n\n\n\n<li>no sorted input<\/li>\n\n\n\n<li>stats uncertain<\/li>\n<\/ul>\n\n\n\n<p>Needs memory \u2192 may spill.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>Sort Operator<\/strong><\/h2>\n\n\n\n<p>Shows SQL Server sorting results.<br>Sorting is expensive.<\/p>\n\n\n\n<p>Fix:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use indexed ORDER BY<\/li>\n\n\n\n<li>Reduce result set<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>11.5 The Most Important Metric: Estimated vs Actual Rows<\/strong><\/h1>\n\n\n\n<p>Example:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">Estimated Rows: 1\nActual Rows: 100,000\n<\/code><\/span><\/pre>\n\n\n<p>This is called <strong>bad cardinality estimation<\/strong>.<\/p>\n\n\n\n<p>Consequence:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>wrong join type<\/li>\n\n\n\n<li>spills<\/li>\n\n\n\n<li>wrong index selection<\/li>\n<\/ul>\n\n\n\n<p>Fixes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>update statistics<\/li>\n\n\n\n<li>better indexing<\/li>\n\n\n\n<li>parameter sniffing fixes<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>11.6 Detecting Warnings<\/strong><\/h1>\n\n\n\n<p>Execution plans may show warnings like:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u26a0 <strong>Missing Index<\/strong><\/h3>\n\n\n\n<p>SQL suggests index improvements.<br>Often valid but must be reviewed manually.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u26a0 <strong>Hash Warning: Spill to tempdb<\/strong><\/h3>\n\n\n\n<p>Means SQL Server ran out of memory.<\/p>\n\n\n\n<p>Fix:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>rewrite query<\/li>\n\n\n\n<li>reduce rows<\/li>\n\n\n\n<li>increase work memory<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u26a0 <strong>Operator Using Residual Predicate<\/strong><\/h3>\n\n\n\n<p>Means filter applied after seek\/scan<br>\u2192 potential index improvement<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>11.7 Example Real Plan Improvement<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Original LINQ:<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-41\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Status == <span class=\"hljs-string\">\"Active\"<\/span>)\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-41\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Execution Plan:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Table scan<\/li>\n\n\n\n<li>100k rows read<\/li>\n\n\n\n<li>2 seconds<\/li>\n<\/ul>\n\n\n\n<p>Create index:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">CREATE INDEX IX_Orders_Status ON Orders(Status);\n<\/code><\/span><\/pre>\n\n\n<p>New plan:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Index Seek<\/li>\n\n\n\n<li>200 rows read<\/li>\n\n\n\n<li>&lt; 50 ms<\/li>\n<\/ul>\n\n\n\n<p>100\u00d7 improvement.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>11.8 Summary<\/strong><\/h1>\n\n\n\n<p>Mastering execution plans enables you to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>find missing indexes<\/li>\n\n\n\n<li>detect cardinality issues<\/li>\n\n\n\n<li>diagnose poor SQL<\/li>\n\n\n\n<li>solve performance regressions<\/li>\n\n\n\n<li>eliminate slow queries permanently<\/li>\n<\/ul>\n\n\n\n<p>Execution plan literacy = performance engineer power.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 11<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\udce1 <strong>CHAPTER 12 \u2014 SQL Profiler &amp; Query Store<\/strong><\/h1>\n\n\n\n<p>This chapter covers <strong>runtime performance analysis<\/strong>, allowing you to identify slow queries, high CPU queries, and plan regressions across your application.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>12.1 SQL Profiler \u2014 Real-Time View of Your App<\/strong><\/h1>\n\n\n\n<p>SQL Profiler allows you to see:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>all queries executed<\/li>\n\n\n\n<li>duration<\/li>\n\n\n\n<li>CPU usage<\/li>\n\n\n\n<li>logical reads<\/li>\n\n\n\n<li>parameter values<\/li>\n\n\n\n<li>errors<\/li>\n<\/ul>\n\n\n\n<p>Perfect for debugging .NET apps.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>12.2 How to Capture EF Core Queries in Profiler<\/strong><\/h1>\n\n\n\n<p>Use events:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>RPC:Completed<\/strong> \u2192 stored procedure calls<\/li>\n\n\n\n<li><strong>SQL:BatchCompleted<\/strong> \u2192 raw SQL<\/li>\n\n\n\n<li>Columns to enable:\n<ul class=\"wp-block-list\">\n<li>Duration<\/li>\n\n\n\n<li>CPU<\/li>\n\n\n\n<li>Reads<\/li>\n\n\n\n<li>TextData<\/li>\n\n\n\n<li>LoginName<\/li>\n\n\n\n<li>ApplicationName<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>Filter:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-42\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">ApplicationName LIKE <span class=\"hljs-string\">'%EntityFramework%'<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-42\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>12.3 Detecting Slow Queries<\/strong><\/h1>\n\n\n\n<p>Sort by:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Duration<\/li>\n\n\n\n<li>Reads<\/li>\n<\/ul>\n\n\n\n<p>Anything over:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>500 ms<\/strong> = slow<\/li>\n\n\n\n<li><strong>100 ms<\/strong> = inspect<\/li>\n\n\n\n<li><strong>&gt;10k logical reads<\/strong> = likely missing index<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>12.4 Query Store: SQL Server\u2019s Black Box Recorder<\/strong><\/h1>\n\n\n\n<p>Query Store is SQL Server\u2019s built-in <strong>performance history database<\/strong>.<\/p>\n\n\n\n<p>Tracks:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>top CPU queries<\/li>\n\n\n\n<li>longest running queries<\/li>\n\n\n\n<li>regressed queries<\/li>\n\n\n\n<li>execution plans<\/li>\n\n\n\n<li>plan changes over time<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>12.5 Enabling Query Store<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">ALTER DATABASE MyDb SET QUERY_STORE = ON;\n<\/code><\/span><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>12.6 Key Query Store Reports<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 Top Resource-Consuming Queries<\/h3>\n\n\n\n<p>Shows:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>total duration<\/li>\n\n\n\n<li>average duration<\/li>\n\n\n\n<li>total CPU<\/li>\n\n\n\n<li>total logical reads<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 Query with High Variation<\/h3>\n\n\n\n<p>Queries slow sometimes, fast other times (parameter sniffing).<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 Regressed Queries<\/h3>\n\n\n\n<p>Execution plan changed \u2192 performance dropped.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>12.7 Forcing a Stable Plan (Fixing Bad Plans)<\/strong><\/h1>\n\n\n\n<p>Find query ID, plan ID, then:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-43\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">EXEC<\/span> <span class=\"hljs-selector-tag\">sp_query_store_force_plan<\/span> <span class=\"hljs-keyword\">@query_id<\/span>, @plan_id;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-43\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>SQL Server now ALWAYS uses that fast plan.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>12.8 Combining Profiler + Query Store<\/strong><\/h1>\n\n\n\n<p>Profiler:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>real-time<\/li>\n\n\n\n<li>good for debugging<\/li>\n<\/ul>\n\n\n\n<p>Query Store:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>historical data<\/li>\n\n\n\n<li>good for deep analysis<\/li>\n<\/ul>\n\n\n\n<p>Together = complete performance visibility.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>12.9 Summary<\/strong><\/h1>\n\n\n\n<p>Profiler + Query Store gives you:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>real-time monitoring<\/li>\n\n\n\n<li>long-term insights<\/li>\n\n\n\n<li>slow query detection<\/li>\n\n\n\n<li>plan regression protection<\/li>\n\n\n\n<li>the ability to fix slow queries permanently<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 12<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83e\udde0 <strong>CHAPTER 13 \u2014 DMV-Based Diagnostics<\/strong><\/h1>\n\n\n\n<p>DMVs (Dynamic Management Views) provide internal SQL Server metrics and performance counters \u2014 extremely valuable for diagnosing slow queries.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>13.1 What Are DMVs?<\/strong><\/h1>\n\n\n\n<p>DMVs expose:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>cached queries<\/li>\n\n\n\n<li>execution statistics<\/li>\n\n\n\n<li>index usage<\/li>\n\n\n\n<li>missing index suggestions<\/li>\n\n\n\n<li>plan cache<\/li>\n<\/ul>\n\n\n\n<p>Think of DMVs as <strong>SQL Server\u2019s internal logs<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>13.2 Find Top Expensive Queries<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-44\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">SELECT<\/span> <span class=\"hljs-selector-tag\">TOP<\/span> 20 \n    <span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.total_worker_time<\/span> <span class=\"hljs-selector-tag\">AS<\/span> <span class=\"hljs-selector-tag\">CPU<\/span>,\n    <span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.total_elapsed_time<\/span> <span class=\"hljs-selector-tag\">AS<\/span> <span class=\"hljs-selector-tag\">Duration<\/span>,\n    <span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.execution_count<\/span> <span class=\"hljs-selector-tag\">AS<\/span> <span class=\"hljs-selector-tag\">Execs<\/span>,\n    <span class=\"hljs-selector-tag\">qt<\/span><span class=\"hljs-selector-class\">.text<\/span> <span class=\"hljs-selector-tag\">AS<\/span> <span class=\"hljs-selector-tag\">QueryText<\/span>\n<span class=\"hljs-selector-tag\">FROM<\/span> <span class=\"hljs-selector-tag\">sys<\/span><span class=\"hljs-selector-class\">.dm_exec_query_stats<\/span> <span class=\"hljs-selector-tag\">qs<\/span>\n<span class=\"hljs-selector-tag\">CROSS<\/span> <span class=\"hljs-selector-tag\">APPLY<\/span> <span class=\"hljs-selector-tag\">sys<\/span><span class=\"hljs-selector-class\">.dm_exec_sql_text<\/span>(<span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.sql_handle<\/span>) <span class=\"hljs-selector-tag\">qt<\/span>\n<span class=\"hljs-selector-tag\">ORDER<\/span> <span class=\"hljs-selector-tag\">BY<\/span> <span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.total_worker_time<\/span> <span class=\"hljs-selector-tag\">DESC<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-44\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Find:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>CPU-heavy queries<\/li>\n\n\n\n<li>slowest queries<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>13.3 Find Missing Indexes<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-45\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">SELECT \n    migs.user_seeks,\n    mid.statement <span class=\"hljs-keyword\">AS<\/span> TableName,\n    mid.equality_columns,\n    mid.inequality_columns,\n    mid.included_columns\nFROM sys.dm_db_missing_index_group_stats migs\nJOIN sys.dm_db_missing_index_groups mig\n    ON migs.group_handle = mig.index_group_handle\nJOIN sys.dm_db_missing_index_details mid\n    ON mig.index_handle = mid.index_handle\nORDER BY migs.user_seeks DESC;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-45\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Indexes recommended frequently = high-impact.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>13.4 Find Index Usage Stats<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-46\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">SELECT \n    OBJECT_NAME(i.object_id) <span class=\"hljs-keyword\">AS<\/span> TableName,\n    i.name <span class=\"hljs-keyword\">AS<\/span> IndexName,\n    ius.user_seeks,\n    ius.user_scans,\n    ius.user_lookups,\n    ius.user_updates\nFROM sys.indexes i\nLEFT JOIN sys.dm_db_index_usage_stats ius\n    ON i.index_id = ius.index_id\n    <span class=\"hljs-keyword\">AND<\/span> i.object_id = ius.object_id\nORDER BY ius.user_seeks DESC;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-46\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If index has:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>high updates, low seeks<\/strong> \u2192 remove it<\/li>\n\n\n\n<li><strong>high seeks<\/strong> \u2192 keep it<\/li>\n\n\n\n<li><strong>high lookups<\/strong> \u2192 convert to covering index<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>13.5 Find Queries Causing High I\/O<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-47\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">SELECT<\/span> <span class=\"hljs-selector-tag\">TOP<\/span> 20\n    <span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.total_logical_reads<\/span>,\n    <span class=\"hljs-selector-tag\">qt<\/span><span class=\"hljs-selector-class\">.text<\/span>\n<span class=\"hljs-selector-tag\">FROM<\/span> <span class=\"hljs-selector-tag\">sys<\/span><span class=\"hljs-selector-class\">.dm_exec_query_stats<\/span> <span class=\"hljs-selector-tag\">qs<\/span>\n<span class=\"hljs-selector-tag\">CROSS<\/span> <span class=\"hljs-selector-tag\">APPLY<\/span> <span class=\"hljs-selector-tag\">sys<\/span><span class=\"hljs-selector-class\">.dm_exec_sql_text<\/span>(<span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.sql_handle<\/span>) <span class=\"hljs-selector-tag\">qt<\/span>\n<span class=\"hljs-selector-tag\">ORDER<\/span> <span class=\"hljs-selector-tag\">BY<\/span> <span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.total_logical_reads<\/span> <span class=\"hljs-selector-tag\">DESC<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-47\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>High reads = expensive.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>13.6 Find Queries Using TempDB (Spills)<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-48\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">SELECT<\/span> * \n<span class=\"hljs-selector-tag\">FROM<\/span> <span class=\"hljs-selector-tag\">sys<\/span><span class=\"hljs-selector-class\">.dm_exec_query_stats<\/span> \n<span class=\"hljs-selector-tag\">WHERE<\/span> <span class=\"hljs-selector-tag\">spilled_memory_grant_kb<\/span> &gt; 0;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-48\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Meaning:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>insufficient memory<\/li>\n\n\n\n<li>bad plan<\/li>\n\n\n\n<li>large dataset operations<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>13.7 Summary<\/strong><\/h1>\n\n\n\n<p>DMVs allow deep analysis of:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>slow queries<\/li>\n\n\n\n<li>CPU consumption<\/li>\n\n\n\n<li>missing indexes<\/li>\n\n\n\n<li>index utilization<\/li>\n\n\n\n<li>spills &amp; memory pressure<\/li>\n\n\n\n<li>plan cache patterns<\/li>\n<\/ul>\n\n\n\n<p>This is where <strong>real SQL Server tuning mastery<\/strong> begins.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<p>Great \u2014 here is <strong>Message 5<\/strong>, covering <strong>Part V: Application-Level Optimization<\/strong>.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>PART V \u2014 APPLICATION-LEVEL DATABASE PERFORMANCE<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 14 \u2014 Connection Pooling Optimization<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 15 \u2014 Transaction Optimization<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 16 \u2014 DB Command Timeout &amp; Retry Patterns<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 17 \u2014 Minimizing Network Round Trips<\/h3>\n\n\n\n<p>These chapters focus on performance issues caused <strong>not by SQL<\/strong>, but by <strong>how the .NET application interacts with the database<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\udd0c <strong>CHAPTER 14 \u2014 Connection Pooling Optimization<\/strong><\/h1>\n\n\n\n<p>Every .NET application that uses SQL Server goes through <strong>ADO.NET connection pooling<\/strong>, whether you&#8217;re using EF Core, Dapper, or raw SqlConnection.<\/p>\n\n\n\n<p>Connection pooling is the <strong>#1 bottleneck<\/strong> in high-traffic ASP.NET applications if misconfigured.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>14.1 How Connection Pooling Works<\/strong><\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>.NET maintains a <strong>pool of open SQL connections<\/strong> for each unique connection string<\/li>\n\n\n\n<li>When a DbContext is used:\n<ol class=\"wp-block-list\">\n<li>Get a connection from pool<\/li>\n\n\n\n<li>Use it<\/li>\n\n\n\n<li>Return it<\/li>\n<\/ol>\n<\/li>\n\n\n\n<li>Opening a NEW connection is expensive (handshake, auth, TLS)<\/li>\n\n\n\n<li>Reusing connections = huge time savings<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>14.2 Why Connection Pools Are Exhausted<\/strong><\/h1>\n\n\n\n<p>Common causes:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u274c Long-running queries<\/h3>\n\n\n\n<p>Connections stay busy too long.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u274c Long-lived DbContext<\/h3>\n\n\n\n<p>Developers incorrectly register DbContext as:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Singleton<\/li>\n\n\n\n<li>Transient but not disposed<\/li>\n\n\n\n<li>Dependency stuck in long scopes<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u274c Large parallel workloads<\/h3>\n\n\n\n<p>Multiple Task.Run loops without throttling.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u274c Transactions that run too long<\/h3>\n\n\n\n<p>Holding locks and connections.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u274c Async code blocking<\/h3>\n\n\n\n<p>Deadlocks or thread starvation.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>14.3 The Golden Rule: Short-Lived DbContext<\/strong><\/h1>\n\n\n\n<p>Correct (Scoped per request):<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-49\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">services.AddDbContext&lt;AppDbContext&gt;(<span class=\"hljs-function\"><span class=\"hljs-params\">options<\/span> =&gt;<\/span>\n    options.UseSqlServer(conn));\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-49\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Incorrect (Singleton):<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-50\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\">services.AddSingleton<span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">AppDbContext<\/span>&gt;<\/span>();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-50\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>\u2192 This creates <strong>one connection<\/strong> shared across threads \u2192 disaster.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>14.4 Correct Usage of DbContext<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-51\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">using (<span class=\"hljs-keyword\">var<\/span> db = <span class=\"hljs-keyword\">new<\/span> AppDbContext())\n{\n    <span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> db.Orders.ToListAsync();\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-51\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>or in ASP.NET:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-52\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-keyword\">public<\/span> <span class=\"hljs-class\"><span class=\"hljs-keyword\">class<\/span> <span class=\"hljs-title\">OrdersService<\/span>\n<\/span>{\n    <span class=\"hljs-keyword\">private<\/span> readonly AppDbContext _db;\n\n    <span class=\"hljs-keyword\">public<\/span> OrdersService(AppDbContext db)\n    {\n        _db = db; <span class=\"hljs-comment\">\/\/ scoped per request<\/span>\n    }\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-52\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>14.5 Configuring Connection Pool Sizes<\/strong><\/h1>\n\n\n\n<p>Default max pool size = <strong>100 connections<\/strong>.<\/p>\n\n\n\n<p>Increase for high-traffic apps:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-53\" data-shcb-language-name=\"JSON \/ JSON with Comments\" data-shcb-language-slug=\"json\"><span><code class=\"hljs language-json\"><span class=\"hljs-string\">\"Server=...;Initial Catalog=...;Max Pool Size=200;\"<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-53\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JSON \/ JSON with Comments<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">json<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>But note:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>too high = SQL Server overwhelmed<\/li>\n\n\n\n<li>too low = thread starvation in app<\/li>\n<\/ul>\n\n\n\n<p>Monitor with DMVs:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-54\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">SELECT<\/span> * <span class=\"hljs-selector-tag\">FROM<\/span> <span class=\"hljs-selector-tag\">sys<\/span><span class=\"hljs-selector-class\">.dm_exec_connections<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-54\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>14.6 Detecting Connection Leaks<\/strong><\/h1>\n\n\n\n<p>Symptoms:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Timeout errors<\/li>\n\n\n\n<li>Pool exhaustion<\/li>\n\n\n\n<li>Long waits in <code>WaitTime<\/code> column<\/li>\n<\/ul>\n\n\n\n<p>Enable logging:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-55\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">options<\/span><span class=\"hljs-selector-class\">.EnableDetailedErrors<\/span>()\n       <span class=\"hljs-selector-class\">.EnableSensitiveDataLogging<\/span>();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-55\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>14.7 Summary<\/strong><\/h1>\n\n\n\n<p>To optimize connection pooling:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>ALWAYS use short-lived DbContext<\/li>\n\n\n\n<li>Use async\/await properly<\/li>\n\n\n\n<li>Avoid long-running transactions<\/li>\n\n\n\n<li>Increase Max Pool Size only when needed<\/li>\n\n\n\n<li>Monitor using DMVs<\/li>\n<\/ul>\n\n\n\n<p>Connection pool optimization boosts concurrency by <strong>2\u00d7\u20135\u00d7<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 14<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\udd10 <strong>CHAPTER 15 \u2014 Transaction Optimization<\/strong><\/h1>\n\n\n\n<p>Transactions are essential for correctness \u2014 but deadly for performance if misused. A poorly scoped transaction will block other queries, escalate locks, and choke throughput.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>15.1 What Happens During a Transaction<\/strong><\/h1>\n\n\n\n<p>When a transaction starts:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL Server must guarantee ACID<\/li>\n\n\n\n<li>Locks are held until commit<\/li>\n\n\n\n<li>Other sessions may block<\/li>\n\n\n\n<li>Log writes slow down system<\/li>\n<\/ul>\n\n\n\n<p>This is why you must keep transactions <strong>short<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>15.2 The Worst Pattern: Business Logic Inside a Transaction<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-56\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">using (<span class=\"hljs-keyword\">var<\/span> tran = <span class=\"hljs-keyword\">await<\/span> _db.Database.BeginTransactionAsync())\n{\n    <span class=\"hljs-keyword\">var<\/span> order = <span class=\"hljs-keyword\">await<\/span> _db.Orders.FindAsync(id);\n\n    <span class=\"hljs-keyword\">await<\/span> _paymentService.ChargeCard(order); <span class=\"hljs-comment\">\/\/ remote call!<\/span>\n\n    order.Status = <span class=\"hljs-string\">\"Paid\"<\/span>;\n    <span class=\"hljs-keyword\">await<\/span> _db.SaveChangesAsync();\n\n    <span class=\"hljs-keyword\">await<\/span> tran.CommitAsync();\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-56\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This is a classic disaster:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>external HTTP call inside transaction<\/li>\n\n\n\n<li>transaction open for seconds<\/li>\n\n\n\n<li>all other queries blocked<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>15.3 The Correct Pattern<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-57\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> order = <span class=\"hljs-keyword\">await<\/span> _db.Orders.FindAsync(id);\n\n<span class=\"hljs-keyword\">await<\/span> _paymentService.ChargeCard(order); <span class=\"hljs-comment\">\/\/ do this outside txn<\/span>\n\nusing (<span class=\"hljs-keyword\">var<\/span> tran = <span class=\"hljs-keyword\">await<\/span> _db.Database.BeginTransactionAsync())\n{\n    order.Status = <span class=\"hljs-string\">\"Paid\"<\/span>;\n    <span class=\"hljs-keyword\">await<\/span> _db.SaveChangesAsync();\n\n    <span class=\"hljs-keyword\">await<\/span> tran.CommitAsync();\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-57\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Keep transaction scope <em>minimal<\/em>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>15.4 Choosing Correct Isolation Levels<\/strong><\/h1>\n\n\n\n<p>Default = <strong>ReadCommitted<\/strong><br>Best for OLTP apps.<\/p>\n\n\n\n<p>Other levels:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Level<\/th><th>Use Case<\/th><th>Performance<\/th><\/tr><\/thead><tbody><tr><td>ReadCommitted<\/td><td>default<\/td><td>good<\/td><\/tr><tr><td>ReadUncommitted (NOLOCK)<\/td><td>dirty reads OK<\/td><td>excellent<\/td><\/tr><tr><td>Snapshot<\/td><td>high read concurrency<\/td><td>good<\/td><\/tr><tr><td>Serializable<\/td><td>very strict consistency<\/td><td>terrible<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">DO NOT use Serializable unless 100% required.<\/h3>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>15.5 Avoid Nested Transactions<\/strong><\/h1>\n\n\n\n<p>EF Core does NOT handle nested transactions properly.<\/p>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-58\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">using <span class=\"hljs-keyword\">var<\/span> tran1 = <span class=\"hljs-keyword\">await<\/span> _db.Database.BeginTransactionAsync();\nusing <span class=\"hljs-keyword\">var<\/span> tran2 = <span class=\"hljs-keyword\">await<\/span> _db.Database.BeginTransactionAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-58\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Avoid.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>15.6 Transactions and SaveChanges<\/strong><\/h1>\n\n\n\n<p>SaveChanges creates its own implicit transaction if none exists.<\/p>\n\n\n\n<p>Thus:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u274c Long loops calling SaveChanges repeatedly<\/h3>\n\n\n\n<p>= hundreds of tiny transactions (terrible)<\/p>\n\n\n\n<p>Fix: <strong>Batch Insert \/ Bulk Insert<\/strong> (covered earlier).<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>15.7 Summary<\/strong><\/h1>\n\n\n\n<p>Transaction optimization means:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Do work OUTSIDE the transaction<\/li>\n\n\n\n<li>Keep transaction scope very small<\/li>\n\n\n\n<li>Use correct isolation levels<\/li>\n\n\n\n<li>Avoid nested transactions<\/li>\n\n\n\n<li>Avoid long SaveChanges loops<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 15<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u23f1\ufe0f <strong>CHAPTER 16 \u2014 DB Command Timeout &amp; Retry Patterns<\/strong><\/h1>\n\n\n\n<p>Timeouts and retries greatly affect stability and performance \u2014 especially at scale.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>16.1 The Default Timeout<\/strong><\/h1>\n\n\n\n<p>By default:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL command timeout = <strong>30 seconds<\/strong><\/li>\n\n\n\n<li>Bad queries will hit timeout<\/li>\n\n\n\n<li>Causes cascading failures in APIs<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>16.2 Configuring Timeouts<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">EF Core:<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-59\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">options.UseSqlServer(conn, opt =&gt;\n    opt.CommandTimeout(<span class=\"hljs-number\">60<\/span>));\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-59\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">SqlCommand:<\/h3>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">command.CommandTimeout = 60;\n<\/code><\/span><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>16.3 The Retry Pattern (Polly)<\/strong><\/h1>\n\n\n\n<p>Database calls occasionally fail:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>deadlocks<\/li>\n\n\n\n<li>transient network failures<\/li>\n\n\n\n<li>failovers<\/li>\n<\/ul>\n\n\n\n<p>Using <strong>Polly<\/strong> for retries:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-60\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> retry = Policy\n    .Handle&lt;SqlException&gt;()\n    .Or&lt;TimeoutException&gt;()\n    .WaitAndRetryAsync(<span class=\"hljs-number\">3<\/span>, retry =&gt;\n        TimeSpan.FromMilliseconds(<span class=\"hljs-number\">200<\/span> * retry));\n\n<span class=\"hljs-keyword\">await<\/span> retry.ExecuteAsync(<span class=\"hljs-keyword\">async<\/span> () =&gt;\n{\n    <span class=\"hljs-keyword\">await<\/span> _db.SaveChangesAsync();\n});\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-60\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>16.4 BUT: When Retries Make Things Worse<\/strong><\/h1>\n\n\n\n<p>Retries should NOT be used for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>long-running queries<\/li>\n\n\n\n<li>missing indexes<\/li>\n\n\n\n<li>slow execution plans<\/li>\n\n\n\n<li>overloaded SQL Server<\/li>\n<\/ul>\n\n\n\n<p>In these cases, retry loops <strong>increase load<\/strong> and worsen outages.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>16.5 Avoid Combining Timeout + Retry Blindly<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>long timeout (120 sec)<\/li>\n\n\n\n<li>many retries (5+)<\/li>\n<\/ul>\n\n\n\n<p>This creates:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>thread starvation<\/li>\n\n\n\n<li>connection pool starvation<\/li>\n\n\n\n<li>massive cascading failures<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>16.6 Recommended Pattern<\/strong><\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>CommandTimeout: <strong>15\u201330 seconds<\/strong><\/li>\n\n\n\n<li>Retries: <strong>3 max<\/strong><\/li>\n\n\n\n<li>Backoff: <strong>exponential<\/strong><\/li>\n\n\n\n<li>Detect root cause using Query Store<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>16.7 Summary<\/strong><\/h1>\n\n\n\n<p>Timeouts and retries improve resilience, but must be used <strong>strategically<\/strong>, not as a band-aid for bad SQL.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 16<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\udce1 <strong>CHAPTER 17 \u2014 Minimizing Network Round Trips<\/strong><\/h1>\n\n\n\n<p>Database performance is often determined <strong>not by slow SQL<\/strong>, but by <strong>too many SQL calls<\/strong>.<\/p>\n\n\n\n<p>Every database call has overhead:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>TCP network latency<\/li>\n\n\n\n<li>handshake<\/li>\n\n\n\n<li>serializer overhead<\/li>\n\n\n\n<li>connection pool operations<\/li>\n\n\n\n<li>SQL Server CPU<\/li>\n<\/ul>\n\n\n\n<p>Reducing the number of queries is a <strong>massive optimization<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>17.1 Common Causes of Excess Round Trips<\/strong><\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>N+1 queries<\/li>\n\n\n\n<li>SaveChanges loops<\/li>\n\n\n\n<li>Lazy loading<\/li>\n\n\n\n<li>complex includes<\/li>\n\n\n\n<li>fetching data one row at a time<\/li>\n\n\n\n<li>multiple sequential queries instead of batching<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>17.2 Combine Multiple Queries Into One<\/strong><\/h1>\n\n\n\n<p>Bad:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-61\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> customers = <span class=\"hljs-keyword\">await<\/span> _db.Customers.ToListAsync();\n<span class=\"hljs-keyword\">var<\/span> orders = <span class=\"hljs-keyword\">await<\/span> _db.Orders.ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-61\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Good:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-62\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> data = <span class=\"hljs-keyword\">await<\/span> _db.Customers\n    .Select(<span class=\"hljs-function\"><span class=\"hljs-params\">c<\/span> =&gt;<\/span> <span class=\"hljs-keyword\">new<\/span> {\n        Customer = c,\n        Orders = c.Orders\n    })\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-62\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>17.3 Use Split Queries for Huge Includes<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-63\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> result = <span class=\"hljs-keyword\">await<\/span> _db.Orders\n    .Include(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Items)\n    .AsSplitQuery() <span class=\"hljs-comment\">\/\/ avoids giant join<\/span>\n    .ToListAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-63\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>17.4 Use Stored Procedures for Multi-Step Logic<\/strong><\/h1>\n\n\n\n<p>Instead of:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-64\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> stats = <span class=\"hljs-keyword\">await<\/span> _db.Orders.Where(...).CountAsync();\n<span class=\"hljs-keyword\">var<\/span> totals = <span class=\"hljs-keyword\">await<\/span> _db.Orders.Where(...).SumAsync();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-64\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Use a single SP:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-65\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">CREATE PROCEDURE GetOrderStats\n<span class=\"hljs-keyword\">AS<\/span>\nBEGIN\n    SELECT COUNT(*), SUM(Total) FROM Orders;\nEND\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-65\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>17.5 Use Caching to Avoid DB Calls Entirely<\/strong><\/h1>\n\n\n\n<p>Cache:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>reference data<\/li>\n\n\n\n<li>lookup tables<\/li>\n\n\n\n<li>dropdown lists<\/li>\n\n\n\n<li>rarely-changing data<\/li>\n<\/ul>\n\n\n\n<p>Use Redis or MemoryCache.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>17.6 Use Projections to Reduce Result Size<\/strong><\/h1>\n\n\n\n<p>Smaller payload = faster network, faster serialization.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>17.7 Summary<\/strong><\/h1>\n\n\n\n<p>Minimizing round trips:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>improves latency<\/li>\n\n\n\n<li>reduces server load<\/li>\n\n\n\n<li>improves scalability<\/li>\n\n\n\n<li>increases concurrency<\/li>\n\n\n\n<li>reduces connection pool stress<\/li>\n<\/ul>\n\n\n\n<p>Great \u2014 here is <strong>Message 6<\/strong>, covering <strong>Part VI (Scaling Beyond a Single Database)<\/strong> and <strong>Part VII (AI-Assisted Optimization)<\/strong>.<\/p>\n\n\n\n<p>This message includes:<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>PART VI \u2014 SCALING BEYOND A SINGLE DATABASE<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 18 \u2014 Read-Replica \/ Read Scaling Patterns<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 19 \u2014 Distributed Caching &amp; Hot Data (Bonus)<\/h3>\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>PART VII \u2014 AI-ASSISTED OPTIMIZATION<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Chapter 20 \u2014 AI-Assisted Query Optimization<\/h3>\n\n\n\n<p>These chapters teach how to scale beyond a single SQL Server instance and how to use modern AI tools to elevate database tuning.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udf10 <strong>CHAPTER 18 \u2014 Read-Replica \/ Read Scaling Patterns<\/strong><\/h1>\n\n\n\n<p>When your application grows, the first bottleneck you hit is usually <strong>read-heavy workloads<\/strong>. SQL Server supports several strategies to scale reads horizontally so that a single primary database does not become overwhelmed.<\/p>\n\n\n\n<p>This chapter explains how .NET apps can offload reads to replicas safely.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>18.1 What Is Read Scaling?<\/strong><\/h1>\n\n\n\n<p>In a typical architecture:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <strong>primary database<\/strong> handles writes<\/li>\n\n\n\n<li>One or more <strong>read replicas<\/strong> (synchronous or asynchronous) handle SELECT queries<\/li>\n<\/ul>\n\n\n\n<p>This offloads read load from the primary, allowing it to focus on transactional integrity.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>18.2 When Read-Replicas Are Needed<\/strong><\/h1>\n\n\n\n<p>If you observe:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>High read latency<\/li>\n\n\n\n<li>CPU on SQL Server consistently above 70%<\/li>\n\n\n\n<li>Many SELECT queries with high concurrency<\/li>\n\n\n\n<li>Dashboards and reporting queries spiking load<\/li>\n\n\n\n<li>API endpoints frequently reading the same data<\/li>\n<\/ul>\n\n\n\n<p>\u2192 You need <strong>read scaling<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>18.3 SQL Server Read Scaling Options<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Always On Availability Groups (AG)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Most enterprise-ready<\/li>\n\n\n\n<li>Provides synchronous\/asynchronous replicas<\/li>\n\n\n\n<li>Read-only routing<\/li>\n\n\n\n<li>Automatic failover<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Transactional Replication<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Good for reporting databases<\/li>\n\n\n\n<li>Flexible<\/li>\n\n\n\n<li>More setup<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Log Shipping<\/strong> (not ideal for reads)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Used for disaster recovery<\/li>\n\n\n\n<li>Not good for real-time reads<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Scaling with Azure SQL<\/strong> (PaaS)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Readable secondaries built-in<\/li>\n\n\n\n<li>Geo-redundant replicas<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>18.4 How .NET Routes Reads to Replicas<\/strong><\/h1>\n\n\n\n<p>In EF Core, you can configure <strong>read\/write splitting<\/strong> by using two different connection strings:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-66\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">services.AddDbContext&lt;AppDbContext&gt;(<span class=\"hljs-function\"><span class=\"hljs-params\">options<\/span> =&gt;<\/span>\n{\n    options.UseSqlServer(primaryConnString);\n});\n\nservices.AddDbContext&lt;ReadOnlyDbContext&gt;(<span class=\"hljs-function\"><span class=\"hljs-params\">options<\/span> =&gt;<\/span>\n{\n    options.UseSqlServer(readReplicaConnString);\n});\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-66\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Usage:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-67\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-keyword\">public<\/span> <span class=\"hljs-class\"><span class=\"hljs-keyword\">class<\/span> <span class=\"hljs-title\">CustomerService<\/span>\n<\/span>{\n    <span class=\"hljs-keyword\">private<\/span> readonly ReadOnlyDbContext _readDb;\n    <span class=\"hljs-keyword\">private<\/span> readonly AppDbContext _writeDb;\n\n    <span class=\"hljs-keyword\">public<\/span> CustomerService(ReadOnlyDbContext readDb, AppDbContext writeDb)\n    {\n        _readDb = readDb;\n        _writeDb = writeDb;\n    }\n\n    <span class=\"hljs-keyword\">public<\/span> Task&lt;<span class=\"hljs-keyword\">List<\/span>&lt;Customer&gt;&gt; GetCustomersAsync() =&gt;\n        _readDb.Customers.AsNoTracking().ToListAsync();\n\n    <span class=\"hljs-keyword\">public<\/span> Task AddCustomer(Customer c) =&gt;\n        _writeDb.Customers.AddAsync(c);\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-67\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>18.5 Read Routing in SQL Server (Availability Groups)<\/strong><\/h1>\n\n\n\n<p>SQL Server can automatically route read-only workloads to replicas using:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-68\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">ALTER AVAILABILITY GROUP &#91;MyAG]\nMODIFY REPLICA ON N<span class=\"hljs-string\">'MyReplica'<\/span>\nWITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-68\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Then configure client driver:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-69\" data-shcb-language-name=\"JSON \/ JSON with Comments\" data-shcb-language-slug=\"json\"><span><code class=\"hljs language-json\"><span class=\"hljs-string\">\"Server=tcp:primary-db;ApplicationIntent=ReadOnly;\"<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-69\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JSON \/ JSON with Comments<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">json<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>18.6 Replica Lag \u2014 The Hidden Danger<\/strong><\/h1>\n\n\n\n<p>Asynchronous replicas often lag behind the primary by:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>milliseconds<\/li>\n\n\n\n<li>seconds<\/li>\n\n\n\n<li>sometimes minutes<\/li>\n<\/ul>\n\n\n\n<p>This creates <strong>eventual consistency<\/strong>, meaning:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Your app might read stale data.<\/h3>\n\n\n\n<p><strong>Therefore:<\/strong><\/p>\n\n\n\n<p>Use replicas only for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>dashboards<\/li>\n\n\n\n<li>reports<\/li>\n\n\n\n<li>queries not requiring up-to-the-second accuracy<\/li>\n<\/ul>\n\n\n\n<p>DO NOT use replicas for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>login checks<\/li>\n\n\n\n<li>shopping carts<\/li>\n\n\n\n<li>financial transactions<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>18.7 Summary<\/strong><\/h1>\n\n\n\n<p>Read-scaling allows:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>massive concurrency<\/li>\n\n\n\n<li>lower latency for reads<\/li>\n\n\n\n<li>offloading dashboards\/reports<\/li>\n\n\n\n<li>increased failover and resilience<\/li>\n<\/ul>\n\n\n\n<p>Correctly implemented, it can increase read throughput by <strong>5\u00d7\u201320\u00d7<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 18<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u26a1 <strong>CHAPTER 19 \u2014 Distributed Caching &amp; Hot Data (Bonus Chapter)<\/strong><\/h1>\n\n\n\n<p>Although not strictly a database feature, caching is one of the <strong>strongest performance tools<\/strong> any .NET application can use to reduce database load.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>19.1 When to Use Caching<\/strong><\/h1>\n\n\n\n<p>Caching is ideal for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>reference data (countries, roles, plans)<\/li>\n\n\n\n<li>dashboards<\/li>\n\n\n\n<li>frequently accessed but rarely changing data<\/li>\n\n\n\n<li>expensive queries<\/li>\n\n\n\n<li>read-heavy APIs<\/li>\n<\/ul>\n\n\n\n<p>Effects:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>reduces DB round-trips<\/li>\n\n\n\n<li>lowers latency<\/li>\n\n\n\n<li>increases throughput<\/li>\n\n\n\n<li>reduces connection pool pressure<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>19.2 Types of Caches<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>MemoryCache (in-process)<\/strong><\/h3>\n\n\n\n<p>Fastest, but not shared across servers.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Redis Distributed Cache<\/strong><\/h3>\n\n\n\n<p>Best for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>load-balanced environments<\/li>\n\n\n\n<li>microservices<\/li>\n\n\n\n<li>multi-node clusters<\/li>\n\n\n\n<li>storing shared state<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Hybrid caching<\/strong><\/h3>\n\n\n\n<p>Use MemoryCache as local L1 and Redis as L2.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>19.3 Caching Database Query Results<\/strong><\/h1>\n\n\n\n<p>Example using MemoryCache:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-70\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-keyword\">public<\/span> <span class=\"hljs-class\"><span class=\"hljs-keyword\">class<\/span> <span class=\"hljs-title\">ProductService<\/span>\n<\/span>{\n    <span class=\"hljs-keyword\">private<\/span> readonly IMemoryCache _cache;\n    <span class=\"hljs-keyword\">private<\/span> readonly AppDbContext _db;\n\n    <span class=\"hljs-keyword\">public<\/span> ProductService(IMemoryCache cache, AppDbContext db)\n    {\n        _cache = cache;\n        _db = db;\n    }\n\n    <span class=\"hljs-keyword\">public<\/span> async Task&lt;<span class=\"hljs-keyword\">List<\/span>&lt;Product&gt;&gt; GetProductsAsync()\n    {\n        <span class=\"hljs-keyword\">return<\/span> await _cache.GetOrCreateAsync(<span class=\"hljs-string\">\"products\"<\/span>, async entry =&gt;\n        {\n            entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(<span class=\"hljs-number\">10<\/span>);\n            <span class=\"hljs-keyword\">return<\/span> await _db.Products.AsNoTracking().ToListAsync();\n        });\n    }\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-70\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>19.4 Cache Invalidation<\/strong><\/h1>\n\n\n\n<p>&#8220;Cache invalidation is one of the hardest problems in computer science.&#8221;<\/p>\n\n\n\n<p>Approaches:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Time-based expiration (TTL)<\/strong><\/h3>\n\n\n\n<p>Simple, robust.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Event-driven invalidation<\/strong><\/h3>\n\n\n\n<p>When a record changes, update\/remove cache.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 <strong>Version tokens<\/strong><\/h3>\n\n\n\n<p>Cache invalidated when version increments.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>19.5 What Not to Cache<\/strong><\/h1>\n\n\n\n<p>Avoid caching:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>large results (&gt;1 MB)<\/li>\n\n\n\n<li>volatile data (frequently updated)<\/li>\n\n\n\n<li>personalized data (not reusable)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>19.6 Summary<\/strong><\/h1>\n\n\n\n<p>Distributed caching offloads <em>massive<\/em> amounts of read traffic from the database and improves latency dramatically.<\/p>\n\n\n\n<p>Caching is not optional \u2014 it is essential for scalable systems.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>End of Chapter 19<\/strong><\/h1>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83e\udd16 <strong>CHAPTER 20 \u2014 AI-Assisted Query Optimization<\/strong><\/h1>\n\n\n\n<p>AI tools (Copilot, ChatGPT, SQL Prompt AI, Azure Data Studio Copilot) can dramatically accelerate SQL tuning \u2014 <strong>if<\/strong> used correctly.<\/p>\n\n\n\n<p>This chapter shows how to integrate AI safely into database optimization workflows.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>20.1 What AI Can Do for SQL Developers<\/strong><\/h1>\n\n\n\n<p>AI tools can:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>explain execution plans<\/li>\n\n\n\n<li>rewrite inefficient queries<\/li>\n\n\n\n<li>suggest indexing strategies<\/li>\n\n\n\n<li>identify anti-patterns<\/li>\n\n\n\n<li>generate optimized LINQ<\/li>\n\n\n\n<li>translate LINQ \u2194 SQL<\/li>\n\n\n\n<li>detect N+1 patterns<\/li>\n\n\n\n<li>provide missing index recommendations<\/li>\n\n\n\n<li>rewrite procedural SQL into set-based SQL<\/li>\n<\/ul>\n\n\n\n<p>AI is now a powerful assistant \u2014 <em>not a replacement<\/em> for expertise.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>20.2 Using AI to Explain Execution Plans<\/strong><\/h1>\n\n\n\n<p>You can paste the plan XML or screenshot into AI:<\/p>\n\n\n\n<p><strong>Example Prompt:<\/strong><\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>Explain why this query is slow and how to optimize it.<\/p>\n<\/blockquote>\n\n\n\n<p>AI output typically analyzes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>scans<\/li>\n\n\n\n<li>key lookups<\/li>\n\n\n\n<li>joins<\/li>\n\n\n\n<li>cardinality issues<\/li>\n\n\n\n<li>spill warnings<\/li>\n<\/ul>\n\n\n\n<p>This saves hours of manual investigation.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>20.3 Using AI to Rewrite SQL<\/strong><\/h1>\n\n\n\n<p>Example slow query:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT *\nFROM Orders\nWHERE YEAR(OrderDate) = 2024;\n<\/code><\/span><\/pre>\n\n\n<p>AI suggested rewrite:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-71\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT *\nFROM Orders\nWHERE OrderDate &gt;= <span class=\"hljs-string\">'2024-01-01'<\/span>\n  AND OrderDate &lt;  <span class=\"hljs-string\">'2026-01-01'<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-71\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Now index can be used.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>20.4 AI-Assisted Index Tuning<\/strong><\/h1>\n\n\n\n<p>Example prompt:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>Suggest indexes to improve performance for this query. Provide reasoning.<\/p>\n<\/blockquote>\n\n\n\n<p>AI can recommend:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-72\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">CREATE INDEX IX_Orders_OrderDate ON Orders (OrderDate) <span class=\"hljs-keyword\">INCLUDE<\/span> (Amount, Status);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-72\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>But always verify using:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>execution plans<\/li>\n\n\n\n<li>Query Store<\/li>\n\n\n\n<li>benchmarks<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>20.5 Using AI to Improve EF Core LINQ<\/strong><\/h1>\n\n\n\n<p>Original LINQ:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-73\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> data = _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Customer.Name.Contains(filter))\n    .ToList();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-73\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>AI rewrite:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-74\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">var<\/span> data = _db.Orders\n    .Where(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> o.Customer.Name.StartsWith(filter))\n    .Select(<span class=\"hljs-function\"><span class=\"hljs-params\">o<\/span> =&gt;<\/span> <span class=\"hljs-keyword\">new<\/span> OrderDto { Id = o.Id, Total = o.Total })\n    .AsNoTracking()\n    .ToList();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-74\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Better SQL:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>uses index<\/li>\n\n\n\n<li>fewer columns<\/li>\n\n\n\n<li>no navigation property translation pitfalls<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>20.6 AI Tools Integrated with SQL Server<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 Azure Data Studio Copilot<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Explains queries<\/li>\n\n\n\n<li>Suggests improvements<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 GitHub Copilot in VS Code<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Helps with LINQ \u2192 SQL translation<\/li>\n\n\n\n<li>Suggests optimized queries<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">\u2714 Redgate SQL Prompt AI<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL rewriting<\/li>\n\n\n\n<li>best practices checks<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>20.7 The Dangers of Blindly Trusting AI<\/strong><\/h1>\n\n\n\n<p>AI can:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>propose incorrect indexes<\/li>\n\n\n\n<li>rewrite queries incorrectly<\/li>\n\n\n\n<li>misunderstand data models<\/li>\n\n\n\n<li>produce dangerous suggestions (NOLOCK everywhere)<\/li>\n<\/ul>\n\n\n\n<p><strong>Always validate via execution plans.<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>20.8 Summary<\/strong><\/h1>\n\n\n\n<p>AI is a multiplier for SQL developers:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>speeds up analysis<\/li>\n\n\n\n<li>accelerates optimization<\/li>\n\n\n\n<li>improves SQL &amp; LINQ quality<\/li>\n\n\n\n<li>helps avoid common mistakes<\/li>\n<\/ul>\n\n\n\n<p>Combined with your knowledge from earlier chapters, AI becomes an <strong>unfair advantage<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<p>Here is <strong>Message 7<\/strong>, the final installment, containing:<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>APPENDICES + FINAL BOOK COMPILATION<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Appendix A \u2014 Performance Anti-Patterns Checklist<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Appendix B \u2014 Database Tuning Script Library<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Appendix C \u2014 Benchmarking Template (for .NET + MSSQL)<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Appendix D \u2014 Before &amp; After Case Studies<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Final Notes &amp; How to Use This Book<\/h3>\n\n\n\n<p>This completes the full book.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\udcce <strong>APPENDIX A \u2014 PERFORMANCE ANTI-PATTERNS CHECKLIST<\/strong><\/h1>\n\n\n\n<p>This is a <strong>one-stop checklist<\/strong> of the most common (and damaging) mistakes in .NET + MSSQL applications. Use it during code reviews and performance audits.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>A.1 EF Core Anti-Patterns<\/strong><\/h2>\n\n\n\n<p>\u274c Using <code>DbContext<\/code> as Singleton<br>\u274c Using Lazy Loading in API applications<br>\u274c Calling <code>ToList()<\/code> too early<br>\u274c Selecting entire entities when only DTOs needed<br>\u274c Using <code>.Result<\/code> or <code>.Wait()<\/code> causing deadlocks<br>\u274c Using client-side evaluation<br>\u274c Loading large graphs with too many <code>.Include()<\/code><br>\u274c Using navigation properties inside loops<br>\u274c Not using <code>AsNoTracking()<\/code> for reads<br>\u274c Using LINQ methods not translatable to SQL<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>A.2 SQL Anti-Patterns<\/strong><\/h2>\n\n\n\n<p>\u274c SELECT *<br>\u274c Missing indexes on foreign keys<br>\u274c Functions on indexed columns (e.g., <code>WHERE YEAR(DateCol) = 2024<\/code>)<br>\u274c Using table variables for large temp data<br>\u274c Using cursors<br>\u274c Scalar UDFs in WHERE<br>\u274c Over-indexing tables<br>\u274c Joining on non-indexed columns<br>\u274c OR conditions instead of UNION ALL<br>\u274c Large OFFSET\/FETCH without keyset pagination<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>A.3 Application-Level Anti-Patterns<\/strong><\/h2>\n\n\n\n<p>\u274c Long-running transactions<br>\u274c External service calls inside transactions<br>\u274c Keeping DbContext alive too long<br>\u274c Insufficient connection pool size<br>\u274c High number of parallel tasks without throttling<br>\u274c Excessive retries causing cascading failures<br>\u274c No caching for rarely changing data<br>\u274c Chatty APIs triggering many small DB calls<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>A.4 Infrastructure Anti-Patterns<\/strong><\/h2>\n\n\n\n<p>\u274c Using only a single SQL instance under heavy load<br>\u274c No read replicas<br>\u274c No SQL monitoring\/alerting<br>\u274c No index maintenance<br>\u274c Ignoring Query Store regressions<br>\u274c No automatic statistics updates<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h2 class=\"wp-block-heading\">\u2714 <strong>A.5 Parameter Sniffing Anti-Patterns<\/strong><\/h2>\n\n\n\n<p>\u274c Relying on cached plan for highly selective queries<br>\u274c NOT using <code>OPTIMIZE FOR UNKNOWN<\/code><br>\u274c Using SPs without considering parameter variance<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>Appendix A Summary<\/strong><\/h1>\n\n\n\n<p>Treat this checklist as your <strong>pre-release audit<\/strong> for every performance-critical application.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\udcd8 <strong>APPENDIX B \u2014 DATABASE TUNING SCRIPT LIBRARY<\/strong><\/h1>\n\n\n\n<p>Below are the most valuable scripts every .NET performance engineer should use.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>B.1 Top CPU Queries<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-75\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">SELECT TOP <span class=\"hljs-number\">20<\/span> \n    qs.total_worker_time <span class=\"hljs-keyword\">AS<\/span> TotalCPU,\n    qs.total_worker_time \/ qs.execution_count <span class=\"hljs-keyword\">AS<\/span> AvgCPU,\n    qs.execution_count <span class=\"hljs-keyword\">AS<\/span> ExecCount,\n    qt.text <span class=\"hljs-keyword\">AS<\/span> SQLText\nFROM sys.dm_exec_query_stats qs\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt\nORDER BY qs.total_worker_time DESC;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-75\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>B.2 Top I\/O Queries<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-76\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">SELECT TOP <span class=\"hljs-number\">20<\/span> \n    qs.total_logical_reads <span class=\"hljs-keyword\">AS<\/span> Reads,\n    qs.total_logical_reads \/ qs.execution_count <span class=\"hljs-keyword\">AS<\/span> AvgReads,\n    qt.text\nFROM sys.dm_exec_query_stats qs\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt\nORDER BY qs.total_logical_reads DESC;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-76\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>B.3 Missing Indexes<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-77\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">SELECT \n    mid.statement <span class=\"hljs-keyword\">AS<\/span> TableName,\n    mid.equality_columns,\n    mid.inequality_columns,\n    mid.included_columns,\n    migs.user_seeks,\n    migs.avg_total_user_cost\nFROM sys.dm_db_missing_index_details mid\nJOIN sys.dm_db_missing_index_groups mig\n    ON mid.index_handle = mig.index_handle\nJOIN sys.dm_db_missing_index_group_stats migs\n    ON mig.index_group_handle = migs.group_handle\nORDER BY migs.user_seeks DESC;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-77\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>B.4 Index Usage (Find Unused Indexes)<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-78\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">SELECT \n    OBJECT_NAME(i.object_id) <span class=\"hljs-keyword\">AS<\/span> TableName,\n    i.name <span class=\"hljs-keyword\">AS<\/span> IndexName,\n    ius.user_seeks, ius.user_scans,\n    ius.user_lookups, ius.user_updates\nFROM sys.indexes i\nLEFT JOIN sys.dm_db_index_usage_stats ius\n    ON i.object_id = ius.object_id <span class=\"hljs-keyword\">AND<\/span> i.index_id = ius.index_id\nWHERE OBJECTPROPERTY(i.object_id, <span class=\"hljs-string\">'IsUserTable'<\/span>) = <span class=\"hljs-number\">1<\/span>\nORDER BY ius.user_updates DESC;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-78\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Indexes with <strong>zero seeks but many updates<\/strong> should be removed.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>B.5 Detecting Plan Regression<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-79\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">SELECT \n    q.query_id,\n    p.plan_id,\n    rs.avg_duration,\n    rs.last_execution_time\nFROM sys.query_store_query <span class=\"hljs-keyword\">AS<\/span> q\nJOIN sys.query_store_plan <span class=\"hljs-keyword\">AS<\/span> p \n    ON q.query_id = p.query_id\nJOIN sys.query_store_runtime_stats <span class=\"hljs-keyword\">AS<\/span> rs \n    ON p.plan_id = rs.plan_id\nORDER BY rs.avg_duration DESC;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-79\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>B.6 Detecting TempDB Spills<\/strong><\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-80\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">SELECT<\/span> \n    <span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.plan_handle<\/span>, \n    <span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.total_spills<\/span>, \n    <span class=\"hljs-selector-tag\">qt<\/span><span class=\"hljs-selector-class\">.text<\/span>\n<span class=\"hljs-selector-tag\">FROM<\/span> <span class=\"hljs-selector-tag\">sys<\/span><span class=\"hljs-selector-class\">.dm_exec_query_stats<\/span> <span class=\"hljs-selector-tag\">qs<\/span>\n<span class=\"hljs-selector-tag\">CROSS<\/span> <span class=\"hljs-selector-tag\">APPLY<\/span> <span class=\"hljs-selector-tag\">sys<\/span><span class=\"hljs-selector-class\">.dm_exec_sql_text<\/span>(<span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.sql_handle<\/span>) <span class=\"hljs-selector-tag\">qt<\/span>\n<span class=\"hljs-selector-tag\">WHERE<\/span> <span class=\"hljs-selector-tag\">qs<\/span><span class=\"hljs-selector-class\">.total_spills<\/span> &gt; 0;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-80\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>Appendix B Summary<\/strong><\/h1>\n\n\n\n<p>These scripts alone can solve <strong>80% of SQL performance issues<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83e\uddea <strong>APPENDIX C \u2014 Benchmarking Template (for .NET + MSSQL)<\/strong><\/h1>\n\n\n\n<p>Benchmarking database performance involves three components:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Workload Generator<\/strong><\/li>\n\n\n\n<li><strong>Application Logging &amp; Metrics<\/strong><\/li>\n\n\n\n<li><strong>SQL Server Statistics<\/strong><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>C.1 Load Test Setup<\/strong><\/h1>\n\n\n\n<p>Use one of:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>k6<\/li>\n\n\n\n<li>Locust<\/li>\n\n\n\n<li>JMeter<\/li>\n\n\n\n<li>Artillery<\/li>\n<\/ul>\n\n\n\n<p>Example k6 script:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-81\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">import<\/span> http <span class=\"hljs-keyword\">from<\/span> <span class=\"hljs-string\">'k6\/http'<\/span>;\n<span class=\"hljs-keyword\">import<\/span> { sleep } <span class=\"hljs-keyword\">from<\/span> <span class=\"hljs-string\">'k6'<\/span>;\n\n<span class=\"hljs-keyword\">export<\/span> <span class=\"hljs-keyword\">let<\/span> options = {\n  <span class=\"hljs-attr\">vus<\/span>: <span class=\"hljs-number\">20<\/span>,\n  <span class=\"hljs-attr\">duration<\/span>: <span class=\"hljs-string\">'30s'<\/span>,\n};\n\n<span class=\"hljs-keyword\">export<\/span> <span class=\"hljs-keyword\">default<\/span> <span class=\"hljs-function\"><span class=\"hljs-keyword\">function<\/span> (<span class=\"hljs-params\"><\/span>) <\/span>{\n  http.get(<span class=\"hljs-string\">'http:\/\/localhost:5000\/api\/orders'<\/span>);\n  sleep(<span class=\"hljs-number\">1<\/span>);\n}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-81\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>C.2 Application Metrics to Capture<\/strong><\/h1>\n\n\n\n<p>Add middleware to measure:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>response time<\/li>\n\n\n\n<li>SQL query count<\/li>\n\n\n\n<li>SQL execution time<\/li>\n\n\n\n<li>connection pool usage<\/li>\n\n\n\n<li>memory consumption<\/li>\n\n\n\n<li>GC activity<\/li>\n<\/ul>\n\n\n\n<p>Example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-82\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">app.Use(<span class=\"hljs-keyword\">async<\/span> (context, next) =&gt;\n{\n    <span class=\"hljs-keyword\">var<\/span> sw = Stopwatch.StartNew();\n    <span class=\"hljs-keyword\">await<\/span> next.Invoke();\n    sw.Stop();\n\n    Console.WriteLine($<span class=\"hljs-string\">\"Request took {sw.ElapsedMilliseconds} ms\"<\/span>);\n});\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-82\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>C.3 SQL Metrics to Capture<\/strong><\/h1>\n\n\n\n<p>Use Query Store or DMVs to measure:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>CPU<\/li>\n\n\n\n<li>Logical reads<\/li>\n\n\n\n<li>Duration<\/li>\n\n\n\n<li>Execution count<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>C.4 Before\/After Comparison Template<\/strong><\/h1>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Metric<\/th><th>Before<\/th><th>After<\/th><\/tr><\/thead><tbody><tr><td>Avg Query Time<\/td><td>500 ms<\/td><td>60 ms<\/td><\/tr><tr><td>Logical Reads<\/td><td>35,000<\/td><td>1,200<\/td><\/tr><tr><td>CPU %<\/td><td>90%<\/td><td>20%<\/td><\/tr><tr><td>Requests\/sec<\/td><td>50<\/td><td>350<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>Appendix C Summary<\/strong><\/h1>\n\n\n\n<p>Benchmarking is essential to <strong>prove<\/strong> performance gains and avoid regressions.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83d\udcca <strong>APPENDIX D \u2014 Before &amp; After Case Studies<\/strong><\/h1>\n\n\n\n<p>These real-world examples show how tuning changes transform performance.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>D.1 Case Study: Eliminating N+1 Queries<\/strong><\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Before:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>1 parent query + 100 child queries<\/li>\n\n\n\n<li>Avg execution: <strong>1.8 seconds<\/strong><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">After:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Single query with <code>.Include()<\/code><\/li>\n\n\n\n<li>Avg execution: <strong>120 ms<\/strong><\/li>\n<\/ul>\n\n\n\n<p><strong>15\u00d7 improvement<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>D.2 Case Study: Adding Covering Index<\/strong><\/h1>\n\n\n\n<p>Query:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT OrderDate, Amount \nFROM Orders \nWHERE CustomerId = 10;\n<\/code><\/span><\/pre>\n\n\n<p>Added:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-83\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">CREATE INDEX IX_Orders_Cust ON Orders(CustomerId)\n<span class=\"hljs-keyword\">INCLUDE<\/span> (OrderDate, Amount);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-83\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Before:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Table scan<\/li>\n\n\n\n<li>2,100,000 rows<\/li>\n\n\n\n<li>900 ms<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">After:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Index seek<\/li>\n\n\n\n<li>120 rows<\/li>\n\n\n\n<li>10 ms<\/li>\n<\/ul>\n\n\n\n<p><strong>90\u00d7 improvement<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>D.3 Case Study: Fixing Parameter Sniffing<\/strong><\/h1>\n\n\n\n<p>Added:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">OPTION (OPTIMIZE FOR UNKNOWN)\n<\/code><\/span><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Before:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>4 seconds on large customers<\/li>\n\n\n\n<li>20 ms on small customers<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">After:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stable 30 ms across ALL customers<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>D.4 Case Study: Rewrite WHERE Clause<\/strong><\/h1>\n\n\n\n<p>Original:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">WHERE YEAR(OrderDate) = 2024\n<\/code><\/span><\/pre>\n\n\n<p>Rewrite:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-84\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">WHERE OrderDate &gt;= <span class=\"hljs-string\">'2024-01-01'<\/span>\nAND OrderDate &lt; <span class=\"hljs-string\">'2026-01-01'<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-84\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Before:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Index unusable<\/li>\n\n\n\n<li>~600 ms<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">After:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Index seek<\/li>\n\n\n\n<li>~20 ms<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>D.5 Case Study: Bulk Insert<\/strong><\/h1>\n\n\n\n<p>Loop with SaveChanges:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>10k rows<\/li>\n\n\n\n<li>25 seconds<\/li>\n<\/ul>\n\n\n\n<p>Using SqlBulkCopy:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>10k rows<\/li>\n\n\n\n<li>0.2 seconds<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfaf <strong>Appendix D Summary<\/strong><\/h1>\n\n\n\n<p>Real-world results demonstrate how tuning changes can produce <strong>order-of-magnitude improvements<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\ud83c\udfc1 <strong>FINAL NOTES \u2014 How to Use This Book<\/strong><\/h1>\n\n\n\n<p>Congratulations \u2014 you now have one of the <strong>most complete, modern, enterprise-grade<\/strong> guides ever written on <strong>Database Performance Optimization in .NET + MSSQL<\/strong>.<\/p>\n\n\n\n<p>To apply it effectively:<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>1 \u2014 Start at the ORM Layer (Cheapest Fixes)<\/strong><\/h1>\n\n\n\n<p>Most performance issues come from:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>bad LINQ<\/li>\n\n\n\n<li>unoptimized EF Core usage<\/li>\n\n\n\n<li>N+1 queries<\/li>\n\n\n\n<li>excessive includes<\/li>\n\n\n\n<li>poor loading strategies<\/li>\n<\/ul>\n\n\n\n<p>Fixing ORM issues alone can improve performance by <strong>5\u00d7\u201320\u00d7<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>2 \u2014 Then Fix SQL Query Shape<\/strong><\/h1>\n\n\n\n<p>Use:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>projections<\/li>\n\n\n\n<li>proper filtering<\/li>\n\n\n\n<li>keyset pagination<\/li>\n\n\n\n<li>batching<\/li>\n\n\n\n<li>proper JOINs<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>3 \u2014 Then Fix Indexing &amp; Plans<\/strong><\/h1>\n\n\n\n<p>Use:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>execution plan analysis<\/li>\n\n\n\n<li>Query Store<\/li>\n\n\n\n<li>DMVs<\/li>\n<\/ul>\n\n\n\n<p>Indexes deliver <strong>10\u00d7\u2013100\u00d7<\/strong> improvements.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>4 \u2014 Then Fix Application Patterns<\/strong><\/h1>\n\n\n\n<p>Address:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>connection pool<\/li>\n\n\n\n<li>caching<\/li>\n\n\n\n<li>retry logic<\/li>\n\n\n\n<li>transaction scope<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>5 \u2014 Finally, Scale Outwards<\/strong><\/h1>\n\n\n\n<p>Use:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>read replicas<\/li>\n\n\n\n<li>caching<\/li>\n\n\n\n<li>microservices<\/li>\n\n\n\n<li>distributed systems concepts<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>6 \u2014 Use AI as a Force Multiplier<\/strong><\/h1>\n\n\n\n<p>AI is extraordinary for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>debugging execution plans<\/li>\n\n\n\n<li>rewriting SQL<\/li>\n\n\n\n<li>optimizing EF Core<\/li>\n\n\n\n<li>generating indexes<\/li>\n<\/ul>\n\n\n\n<p>But ALWAYS validate.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<h1 class=\"wp-block-heading\">\u2b50 <strong>7 \u2014 Re-Benchmark Often<\/strong><\/h1>\n\n\n\n<p>Performance regressions are inevitable.<br>Repeat benchmarking after:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>new features<\/li>\n\n\n\n<li>schema changes<\/li>\n\n\n\n<li>SQL patches<\/li>\n\n\n\n<li>traffic increases<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A Complete End-to-End Guide for High-Performance Data Access in ASP.NET Applications \u2b50 PREFACE Modern ASP.NET applications rely heavily on databases\u2014not just to store records, but to power product features, analytics, transactions, recommendation systems, dashboards, and business-critical workflows. As your user base grows, the database often becomes the #1 bottleneck long before CPU, memory, or network&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","_joinchat":[],"footnotes":""},"categories":[11138],"tags":[],"class_list":["post-54345","post","type-post","status-publish","format-standard","hentry","category-best-tools"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/54345","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=54345"}],"version-history":[{"count":3,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/54345\/revisions"}],"predecessor-version":[{"id":59907,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/54345\/revisions\/59907"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=54345"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=54345"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=54345"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}