Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Read Execution Plans to Find Performance Bottlenecks

- 22.03.26 - ErcanOPAK

🔍 See What Database Actually Does

Query slow? Don’t guess. Execution plans show exactly how database executes query. Find bottlenecks, fix performance.

Get Execution Plan

-- SQL Server
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Show estimated plan (doesn't run query)
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM orders WHERE user_id = 123;
GO
SET SHOWPLAN_TEXT OFF;

-- Show actual plan (runs query)
SET STATISTICS PROFILE ON;
SELECT * FROM orders WHERE user_id = 123;
SET STATISTICS PROFILE OFF;

-- Or use SSMS: Ctrl + M (actual plan)

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

-- MySQL
EXPLAIN
SELECT * FROM orders WHERE user_id = 123;

🚨 Red Flags to Look For

Problem What It Means Fix
Table Scan Reading entire table Add index
Index Scan (large) Reading whole index Better WHERE clause
Nested Loops Join with no index Add index on join column
Sort (high cost) ORDER BY without index Index on ORDER BY column
Key Lookup Extra data fetch Covering index
Implicit Conversion Data type mismatch Match column types

Real Example: Before & After

❌ Before (Slow)

SELECT *
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;

-- Execution Plan Shows:
-- Table Scan on orders (500,000 rows)
-- No index on status column
-- Sort operation (expensive)
-- Execution time: 8 seconds

✅ After (Fast)

-- Add index
CREATE INDEX idx_orders_status_date 
ON orders(status, created_at DESC);

-- Same query
SELECT *
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;

-- Execution Plan Shows:
-- Index Seek (2,000 rows - only pending orders)
-- No sort needed (already sorted in index)
-- Execution time: 0.05 seconds
-- 160x faster!

Reading The Numbers

Execution Plan Output:

|--Index Seek  (Cost: 5%)
   Rows: 100
   Estimated Rows: 95
   
|--Nested Loops  (Cost: 30%)
   Rows: 10,000
   Estimated Rows: 100  ← Problem! Estimate way off
   
|--Table Scan  (Cost: 65%)  ← Most expensive operation
   Rows: 500,000

Key Metrics:
- Cost %: Higher = slower (focus on > 20%)
- Rows: Actual rows processed
- Estimated Rows: Database guess (way off = update stats)

🎯 Quick Wins

  • Table Scan → Index Seek: Add index on WHERE columns
  • Sort → No Sort: Index on ORDER BY columns
  • Nested Loops → Hash Match: Update statistics
  • Key Lookup: Create covering index (include SELECT columns)
  • Parameter Sniffing: Use OPTION (RECOMPILE)

💡 Pro Tips

  • Use actual plan: Estimated can be wrong
  • Look at percentages: Focus on operations > 20% cost
  • Check row counts: Estimated vs Actual mismatch? Update stats
  • Read right to left: Execution flows from right (data source) to left (result)
  • Save plans: Compare before/after optimization

“Query took 30 seconds. Execution plan showed table scan. Added one index. Query now takes 0.1 seconds. 300x faster. Execution plans are X-ray vision for databases.”

— Database Administrator

Related posts:

SQL Index Exists but Not Used

SQL Index Exists but Not Used

SQL Server: Why a Temp Table Inside a Stored Procedure Triggers Constant Recompilation

SQL Server: Why a Temp Table Inside a Stored Procedure Triggers Constant Recompilation

SQL Server: Fix a CTE That Gets Re-Evaluated Once Per Reference Instead of Once, Doubling the Work f...

SQL Server: Fix a CTE That Gets Re-Evaluated Once Per Reference Instead of Once, Doubling the Work f...

Views: 17

Post navigation

SQL: Use CTEs (WITH Clause) for Readable Complex Queries
C#: Use Pattern Matching for Cleaner Type Checks and Switches

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com