{"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&#8230; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_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}]}}