SQL Server Performance Tuning: A Guide for .NET Developers

A structured learning guide covering indexes, execution plans, joins, normalization, code review checklists, anti-patterns, and real-world before/after optimization examples.

1. Introduction

Performance problems are among the most common — and costly — issues in production .NET applications. Slow queries, blocking transactions, and inefficient stored procedures can degrade user experience and increase infrastructure costs dramatically.

This guide provides a structured path for .NET developers to master SQL Server query optimization and stored procedure tuning. Whether you are writing LINQ queries that generate T-SQL, hand-crafting stored procedures, or debugging slow reports, the principles here will help you deliver faster, more reliable data access.

Why This Matters: A single unoptimized query on a busy table can hold locks, block other operations, and cause cascading timeouts across your entire application stack.

1.1 What You Will Learn

How SQL Server processes and optimizes queries internally
How to read and interpret execution plans
Index design strategies for common query patterns
Join types and when each performs best
Normalization trade-offs for OLTP vs OLAP workloads
A repeatable code-review checklist for SQL artifacts
Common anti-patterns and how to fix them
Real-world before/after optimization scenarios

2. Core Concepts

2.1 How SQL Server Processes a Query

Understanding the lifecycle of a SQL query helps you predict where bottlenecks arise. When you submit a T-SQL statement, SQL Server follows these steps:

Parse — syntax check and tokenization
Algebrize — semantic check, name resolution, type derivation
Optimize — the Query Optimizer generates a cost-based execution plan
Execute — the Storage Engine retrieves or modifies data using the plan
Return — results are sent to the client

Key Insight: The Query Optimizer makes decisions based on statistics (row counts, data distribution). Stale or missing statistics are a leading cause of bad execution plans.

2.2 Reading Execution Plans

An execution plan is a visual or XML description of how SQL Server will physically retrieve your data. It is your most powerful diagnostic tool.

How to View Execution Plans

Include Actual Execution Plan: Ctrl+M in SSMS, then run your query
Estimated Plan: Ctrl+L (no query execution required)
sys.dm_exec_query_plan: retrieves plan for a cached query via DMVs

Key Operators to Recognize

Operator	What It Means
Table Scan	Reading every row in a heap (no clustered index). Almost always bad on large tables.
Clustered Index Scan	Reading all rows via the clustered index. May be fine for small tables or full scans.
Clustered Index Seek	Efficient targeted lookup via clustered index. This is what you want.
Index Seek	Efficient lookup via a non-clustered index. Excellent for selective queries.
Key Lookup	Extra trip to the base table to fetch columns not in the index. Indicates a covering index opportunity.
Hash Match	Used for joins/aggregations when no useful index exists. High memory usage.
Nested Loops	Efficient for small result sets and indexed inner tables.
Merge Join	Efficient when both inputs are pre-sorted. Look for Sort operators feeding it.
Sort	Expensive operation. Indicates a missing ORDER BY-friendly index.
Spool	Temporary storage for intermediate results. Can signal re-use inefficiency.

Warning Signs in Execution Plans

Thick arrows between operators — large data movement, possible missing index
Yellow exclamation marks — implicit conversion warnings, missing statistics
Table Scan or RID Lookup on large tables
Sort operators with high estimated cost
Parallelism (yellow arrows) — may indicate under-indexed queries

2.3 Indexes: Design Principles

Indexes are the single biggest lever for query performance. Understanding when and how to create them is essential.

Clustered Index

A clustered index determines the physical order of rows on disk. A table can have only one clustered index. Best candidates:

Integer primary keys (IDENTITY or SEQUENCE)
Narrow columns — every non-clustered index includes the clustered key
Monotonically increasing values to avoid page splits

Anti-pattern: Using a GUID (uniqueidentifier) as a clustered index key causes severe page fragmentation due to random insertion order. Use NEWSEQUENTIALID() if you must use GUIDs.

Non-Clustered Indexes

Non-clustered indexes are separate structures pointing back to the base row. A table can have up to 999 non-clustered indexes (though far fewer is recommended in practice).

Covering Indexes

A covering index contains all columns a query needs, eliminating Key Lookups:

-- Query
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = 42 AND OrderDate >= '2024-01-01';

-- Covering index satisfies this query entirely
CREATE INDEX IX_Orders_Customer_Date
ON Orders (CustomerID, OrderDate)
INCLUDE (OrderID, TotalAmount);

Filtered Indexes

Filtered indexes index only a subset of rows, reducing index size and maintenance cost:

-- Only index active, high-value orders
CREATE INDEX IX_Orders_Active_HighValue
ON Orders (CustomerID, OrderDate)
WHERE IsActive = 1 AND TotalAmount > 1000;

Index Column Ordering Rule (EII Pattern)

Follow the Equality-Inequality-Include pattern:

Equality columns first (WHERE Col = value)
Inequality / range columns next (WHERE Col > value, BETWEEN, LIKE 'x%')
Additional SELECT columns in the INCLUDE clause

Index Maintenance

Task	Guidance
Rebuild Index	Fragmentation > 30%. Rebuilds statistics. ONLINE option minimizes blocking.
Reorganize Index	Fragmentation 10–30%. Less resource-intensive than rebuild.
Update Statistics	Run after large data loads. SQL Server auto-updates stats but may lag on large tables.
Unused Index Removal	Query sys.dm_db_index_usage_stats. Unused indexes waste write performance.

2.4 Join Types and Performance

Join Algorithms (Physical Operators)

Algorithm	Best Used When
Nested Loop Join	Outer input is small; inner input has an efficient index seek available.
Hash Match Join	No useful indexes on join columns; large unsorted inputs. High memory cost.
Merge Join	Both inputs are sorted on the join key (or can be via index). Very efficient.

Common Join Anti-Patterns

Joining on nullable columns without handling NULLs explicitly
Implicit cross joins (missing WHERE clause between tables in old-style joins)
Joining on computed or cast columns that prevent index seeks
Using functions on join columns: WHERE YEAR(OrderDate) = 2024 prevents seek

-- ANTI-PATTERN: function on column prevents index seek
SELECT * FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE YEAR(o.OrderDate) = 2024;

-- OPTIMIZED: sargable range predicate
SELECT * FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2024-01-01'
  AND o.OrderDate < '2025-01-01';

2.5 Normalization and Denormalization

Normalization reduces redundancy and ensures data integrity. But in performance-critical scenarios, strategic denormalization can significantly reduce join complexity.

Normal Forms — Quick Reference

Normal Form	Rule
1NF	Atomic values, no repeating groups, each row uniquely identifiable.
2NF	1NF + no partial dependency (every non-key attribute depends on the whole key).
3NF	2NF + no transitive dependency (non-key attributes depend only on the key).
BCNF	Every determinant is a candidate key. Stricter than 3NF.

OLTP vs OLAP Design Guidance

Aspect	OLTP (Transactional)	OLAP (Analytical)
Normalization	High (3NF/BCNF) — reduces write amplification	Denormalized star/snowflake schema
Joins	More joins — acceptable for single-row lookups	Fewer joins — pre-joined wide fact tables
Indexes	Selective non-clustered indexes on FK and filter columns	Columnstore indexes for aggregations
Denormalization	Avoid — causes update anomalies	Embrace for query simplicity

3. Code Review Checklist

Use this checklist when reviewing SQL queries, stored procedures, or Entity Framework-generated T-SQL in pull requests.

Category	Review Item
Columns	Avoid SELECT * — always list required columns explicitly
Columns	No implicit type conversions on indexed columns (check for yellow warnings in plan)
Predicates	All filter predicates are sargable (no functions wrapping indexed columns)
Predicates	Date range filters use >= and < rather than BETWEEN for half-open intervals
Predicates	NULL handling is explicit (IS NULL / IS NOT NULL, ISNULL / COALESCE)
Indexes	Execution plan reviewed — no Table Scans on large tables
Indexes	Key Lookups identified and resolved with covering indexes
Indexes	Index usage confirmed via sys.dm_db_index_usage_stats (existing indexes leveraged)
Indexes	No unnecessary indexes added (balance read gain vs write cost)
Stored Procs	SET NOCOUNT ON present in all stored procedures
Stored Procs	Parameter sniffing issues mitigated (OPTION RECOMPILE or OPTIMIZE FOR where needed)
Stored Procs	No dynamic SQL without parameterization (prevents injection and enables plan reuse)
Stored Procs	TRY/CATCH blocks with proper transaction rollback on error
Anti-patterns	Row-by-row cursor logic replaced with set-based operations
Anti-patterns	No scalar UDFs in WHERE clauses or JOIN conditions
Anti-patterns	No correlated subqueries that re-execute per row (use JOIN or window functions)
Transactions	Transactions are short — no user interaction or external calls inside a transaction
Transactions	Appropriate isolation level used (READ COMMITTED SNAPSHOT for OLTP)
Statistics	Statistics are current for tables touched by query
Testing	Query tested with realistic production-scale data volumes

4. Best Practices

4.1 Query Writing Best Practices

Always Write Sargable Predicates

A sargable predicate can use an index seek. The term comes from Search ARGument ABLE. Non-sargable predicates force full scans.

-- NOT SARGABLE (forces scan)
WHERE CONVERT(VARCHAR, OrderDate, 103) = '01/01/2024'
WHERE LEFT(LastName, 3) = 'Smi'
WHERE OrderAmount * 1.1 > 1000

-- SARGABLE alternatives
WHERE OrderDate = '2024-01-01'
WHERE LastName LIKE 'Smi%'
WHERE OrderAmount > 909.09

Use EXISTS Instead of COUNT for Existence Checks

-- Inefficient: scans all matching rows to get count
IF (SELECT COUNT(*) FROM Orders WHERE CustomerID = @ID) > 0

-- Efficient: stops at first match
IF EXISTS (SELECT 1 FROM Orders WHERE CustomerID = @ID)

Prefer CTEs for Readability, Not Performance

Common Table Expressions (CTEs) improve readability but are not materialized by default — they are inlined into the query. For reuse and performance, consider temp tables or indexed views.

Use Window Functions Over Self-Joins

-- Self-join approach (expensive — correlated subquery per row)
SELECT a.OrderID, a.CustomerID, a.OrderDate, a.TotalAmount,
       (SELECT SUM(b.TotalAmount)
        FROM Orders b
        WHERE b.CustomerID = a.CustomerID
          AND b.OrderDate <= a.OrderDate) AS RunningTotal
FROM Orders a;

-- Window function approach (single pass — much more efficient)
SELECT OrderID, CustomerID, OrderDate, TotalAmount,
       SUM(TotalAmount) OVER (
         PARTITION BY CustomerID
         ORDER BY OrderDate
         ROWS UNBOUNDED PRECEDING
       ) AS RunningTotal
FROM Orders;

4.2 Stored Procedure Best Practices

Always use SET NOCOUNT ON to suppress row-count messages that can confuse ADO.NET
Use schema prefix on all object references: dbo.Orders, not just Orders
Handle parameter sniffing with OPTION (RECOMPILE) on plans that vary widely, or use local variable copies of parameters
Use TRY/CATCH with XACT_STATE() to detect and rollback transactions correctly

CREATE PROCEDURE dbo.usp_GetCustomerOrders
  @CustomerID INT,
  @StartDate  DATE,
  @EndDate    DATE
AS
BEGIN
  SET NOCOUNT ON;

  -- Local copies mitigate parameter sniffing
  DECLARE @LocalCustomerID INT  = @CustomerID;
  DECLARE @LocalStart      DATE = @StartDate;
  DECLARE @LocalEnd        DATE = @EndDate;

  BEGIN TRY
    SELECT o.OrderID, o.OrderDate, o.TotalAmount,
           c.FirstName + ' ' + c.LastName AS CustomerName
    FROM   dbo.Orders o
    JOIN   dbo.Customers c ON c.CustomerID = o.CustomerID
    WHERE  o.CustomerID = @LocalCustomerID
      AND  o.OrderDate >= @LocalStart
      AND  o.OrderDate <  DATEADD(DAY, 1, @LocalEnd)
    ORDER BY o.OrderDate DESC;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
  END CATCH
END;

4.3 Transaction Best Practices

Keep transactions as short as possible — long transactions cause blocking
Never perform external calls (HTTP, file I/O, email) inside a transaction
Use READ COMMITTED SNAPSHOT ISOLATION (RCSI) to reduce reader-writer blocking
Avoid SELECT inside a transaction unless updating based on the result
Access tables in consistent order across procedures to prevent deadlocks

5. Common Mistakes & Anti-Patterns

5.1 The N+1 Problem

This is the most common performance anti-pattern in .NET applications using ORM frameworks. It occurs when code loads a collection and then queries for related data per item.

// C# N+1 example (Entity Framework)
var customers = context.Customers.ToList(); // 1 query
foreach (var c in customers)
{
    var orders = context.Orders                // N queries!
                        .Where(o => o.CustomerID == c.ID)
                        .ToList();
}

// Fix: eager load with Include()
var customers = context.Customers
                       .Include(c => c.Orders)
                       .ToList(); // 1 query with JOIN

5.2 Implicit Conversions

When column data types and parameter types differ, SQL Server must cast one of them. This can invalidate index seeks and generate full scans.

-- CustomerCode is VARCHAR(20) in the table
-- Passing NVARCHAR causes implicit conversion
-- The yellow warning in execution plan says: Type conversion in expression
WHERE CustomerCode = N'ABC123'  -- N prefix = NVARCHAR

-- Fix: match parameter type exactly
WHERE CustomerCode = 'ABC123'   -- VARCHAR literal

-- In stored procedure: declare parameter correctly
CREATE PROCEDURE dbo.FindCustomer
  @CustomerCode VARCHAR(20)  -- NOT NVARCHAR

5.3 Scalar User-Defined Functions in Queries

Scalar UDFs called per-row prevent parallelism and run row-by-row, negating set-based optimization.

-- ANTI-PATTERN: scalar UDF called for every row
SELECT OrderID, dbo.fn_GetDiscountedPrice(OrderID) AS Price
FROM Orders;

-- BETTER: inline the logic with a JOIN
SELECT o.OrderID,
       o.UnitPrice * (1 - COALESCE(d.DiscountRate, 0)) AS Price
FROM Orders o
LEFT JOIN Discounts d ON d.CustomerID = o.CustomerID
                      AND d.ProductID = o.ProductID;

5.4 Cursor-Based Row Processing

Cursors process one row at a time. In almost all cases, a set-based alternative is faster by orders of magnitude.

-- ANTI-PATTERN: cursor update
DECLARE @OrderID INT;
DECLARE cur CURSOR FOR SELECT OrderID FROM Orders WHERE Status = 0;
OPEN cur;
FETCH NEXT FROM cur INTO @OrderID;
WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE Orders SET Status = 1 WHERE OrderID = @OrderID;
  FETCH NEXT FROM cur INTO @OrderID;
END;
CLOSE cur; DEALLOCATE cur;

-- OPTIMIZED: single set-based UPDATE
UPDATE Orders SET Status = 1 WHERE Status = 0;

5.5 NOLOCK / READ UNCOMMITTED Overuse

NOLOCK (READ UNCOMMITTED) is rarely the right fix for blocking. Its risks:

Risk of reading uncommitted (dirty) data — values that may be rolled back
Risk of reading the same row twice or missing rows during scans
Does NOT prevent all blocking — intent locks are still taken

Better Alternative: Enable Read Committed Snapshot Isolation (RCSI) at the database level. Readers never block writers and vice versa, with no dirty reads.

5.6 Over-Indexing

More indexes does not mean better performance. Every index has a write cost:

Each INSERT adds a row to every index on the table
Each UPDATE of an indexed column modifies those indexes
Each DELETE must remove the row from every index

Rule of thumb: review sys.dm_db_index_usage_stats regularly and drop indexes with zero seeks and low scans that are not used for uniqueness enforcement.

6. Real-World Before / After Examples

Example 1 — Missing Index on Foreign Key

Scenario: Order history page in an e-commerce .NET app loads slowly for customers with many orders.

BEFORE

-- Original query (no index on CustomerID)
SELECT TOP 20
    o.OrderID, o.OrderDate, o.TotalAmount, o.Status,
    p.Name AS ProductName
FROM Orders o
JOIN OrderItems oi ON oi.OrderID = o.OrderID
JOIN Products p   ON p.ProductID = oi.ProductID
WHERE o.CustomerID = @CustomerID
ORDER BY o.OrderDate DESC;

-- Execution plan shows:
--   Table Scan on Orders (500,000 rows)
--   Nested Loop with Hash Match on OrderItems
--   Duration: ~1,800ms on production

AFTER

-- Step 1: Add covering index on Orders
CREATE INDEX IX_Orders_CustomerID_Date
ON dbo.Orders (CustomerID, OrderDate DESC)
INCLUDE (TotalAmount, Status);

-- Step 2: Add index on OrderItems (FK + covered columns)
CREATE INDEX IX_OrderItems_OrderID
ON dbo.OrderItems (OrderID)
INCLUDE (ProductID, Quantity, UnitPrice);

-- Query unchanged — optimizer now uses Index Seek + Nested Loop
-- Execution plan: Index Seek on Orders, Index Seek on OrderItems
-- Duration: ~12ms on production  (150x improvement)

Result: 1,800ms reduced to 12ms. No application code changes required. Index adds 2.1MB storage and negligible write overhead for this low-write, high-read table.

Example 2 — Parameter Sniffing in a Stored Procedure

Scenario: A report procedure runs fast most of the time but occasionally takes 30+ seconds for the same parameters.

Root Cause: SQL Server compiled the plan when a low-selectivity customer (1,000 orders) ran the proc first. The cached plan uses a Hash Match. A customer with 5 orders gets the same bad plan.

BEFORE

CREATE PROCEDURE dbo.usp_OrderSummary
  @CustomerID INT
AS
BEGIN
  SET NOCOUNT ON;
  SELECT OrderID, OrderDate, SUM(TotalAmount) AS Total
  FROM Orders
  WHERE CustomerID = @CustomerID
  GROUP BY OrderID, OrderDate;
END;
-- Plan cached on first execution; may be wrong for subsequent calls

AFTER — Local Variable Technique

CREATE PROCEDURE dbo.usp_OrderSummary
  @CustomerID INT
AS
BEGIN
  SET NOCOUNT ON;

  -- Local copy breaks sniffing; optimizer uses average statistics
  DECLARE @LocalID INT = @CustomerID;

  SELECT OrderID, OrderDate, SUM(TotalAmount) AS Total
  FROM Orders
  WHERE CustomerID = @LocalID
  GROUP BY OrderID, OrderDate;
END;

-- Alternative for highly variable queries:
-- Add OPTION (OPTIMIZE FOR UNKNOWN) or OPTION (RECOMPILE)

Example 3 — Replacing a Scalar UDF with an Inline Calculation

Scenario: Product listing query takes 4 seconds; profiler shows 90% of time in dbo.fn_GetFinalPrice.

BEFORE

-- Scalar UDF called once per row
SELECT p.ProductID, p.Name,
       dbo.fn_GetFinalPrice(p.ProductID, @CustomerTier) AS FinalPrice
FROM Products p
WHERE p.CategoryID = @CategoryID;

-- fn_GetFinalPrice internally does:
--   SELECT BasePrice FROM Products WHERE ProductID = @pid
--   SELECT DiscountPct FROM TierDiscounts WHERE Tier = @tier
--   RETURN BasePrice * (1 - DiscountPct)
-- This executes 2 extra queries per row!

AFTER

SELECT p.ProductID, p.Name,
       p.BasePrice * (1 - COALESCE(td.DiscountPct, 0)) AS FinalPrice
FROM Products p
LEFT JOIN TierDiscounts td
       ON td.Tier = @CustomerTier
      AND td.ProductID = p.ProductID
WHERE p.CategoryID = @CategoryID;

-- Single query, set-based, parallelism-eligible
-- Duration: 4,100ms -> 55ms

Example 4 — Eliminating an N+1 in Entity Framework

Scenario: Dashboard loading invoices with customer names causes 500+ database round-trips.

BEFORE

// Generates 1 query for invoices + N queries for customers
var invoices = await context.Invoices
    .Where(i => i.DueDate < DateTime.Today)
    .OrderByDescending(i => i.DueDate)
    .Take(100)
    .ToListAsync();

var viewModels = invoices.Select(i => new InvoiceVM {
    InvoiceID    = i.InvoiceID,
    CustomerName = context.Customers  // N extra queries!
        .First(c => c.ID == i.CustomerID).FullName
}).ToList();

AFTER

// Single query with JOIN via Include
var viewModels = await context.Invoices
    .Include(i => i.Customer)          // generates single JOIN
    .Where(i => i.DueDate < DateTime.Today)
    .OrderByDescending(i => i.DueDate)
    .Take(100)
    .Select(i => new InvoiceVM {
        InvoiceID    = i.InvoiceID,
        CustomerName = i.Customer.FullName
    })
    .ToListAsync();

// Or use projection to avoid loading full entities:
// .Select(i => new { i.InvoiceID, i.Customer.FullName })

7. Useful Diagnostic Queries

These T-SQL queries help you identify performance problems using SQL Server's Dynamic Management Views (DMVs).

Top 10 Most Expensive Queries by CPU

SELECT TOP 10
    qs.total_worker_time / qs.execution_count  AS AvgCPU,
    qs.total_elapsed_time / qs.execution_count AS AvgDuration,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset END
         - qs.statement_start_offset)/2)+1) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgCPU DESC;

Missing Index Suggestions

SELECT TOP 20
    migs.avg_total_user_cost * migs.avg_user_impact
        * (migs.user_seeks + migs.user_scans) AS ImpactScore,
    mid.statement       AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid
    ON mid.index_handle = mig.index_handle
ORDER BY ImpactScore DESC;

Note: Missing index suggestions are hints, not mandates. Always review proposed indexes against your existing index set to avoid duplicates or redundant coverage.

Unused Indexes (Candidates for Removal)

SELECT
    OBJECT_NAME(i.object_id)    AS TableName,
    i.name                      AS IndexName,
    i.type_desc,
    ISNULL(s.user_seeks,   0)   AS Seeks,
    ISNULL(s.user_scans,   0)   AS Scans,
    ISNULL(s.user_lookups, 0)   AS Lookups,
    ISNULL(s.user_updates, 0)   AS Updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
       ON s.object_id    = i.object_id
      AND s.index_id     = i.index_id
      AND s.database_id  = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.index_id > 1   -- exclude clustered
  AND ISNULL(s.user_seeks,   0)
    + ISNULL(s.user_scans,   0)
    + ISNULL(s.user_lookups, 0) = 0
ORDER BY ISNULL(s.user_updates, 0) DESC;

8. Structured Learning Path

Follow this sequence to build competency progressively. Each phase has measurable outcomes.

Phase	Topics & Outcomes
Phase 1 — Foundations (Weeks 1–2)	Understand query lifecycle. Read basic execution plans. Create clustered and non-clustered indexes. Identify and fix table scans. Outcome: Can diagnose and fix simple missing-index problems.
Phase 2 — Intermediate (Weeks 3–4)	Master covering and filtered indexes. Understand join algorithms. Write sargable predicates. Fix parameter sniffing. Outcome: Can tune stored procedures in a development environment.
Phase 3 — Advanced (Weeks 5–6)	Window functions, CTEs vs temp tables. Statistics management. Locking, blocking, and isolation levels. Execution plan operators in depth. Outcome: Can investigate and resolve production performance incidents.
Phase 4 — Mastery (Ongoing)	DMV-based diagnostics. Query Store analysis. In-memory OLTP (Hekaton). Columnstore indexes. Benchmarking and regression testing. Outcome: Proactive performance governance across the team.

9. Quick Reference Card

Do	Avoid
SELECT only needed columns	SELECT *
Use sargable predicates	Functions on indexed columns in WHERE
Cover queries with INCLUDE columns	Key Lookups on large result sets
SET NOCOUNT ON in stored procs	Omitting NOCOUNT (extra round-trips)
Use set-based operations	Row-by-row cursors or loops
Keep transactions short	Long transactions with external calls
Use EXISTS for existence checks	COUNT(*) > 0 for existence checks
Match parameter and column types	Implicit type conversion on filter columns
Enable RCSI for OLTP databases	NOLOCK as a blanket blocking fix
Review execution plans before merge	Shipping queries without plan review

10. Summary

SQL Server performance tuning is a skill built through systematic practice and a disciplined habit of reviewing execution plans. The most impactful improvements usually come from:

Adding the right indexes — especially covering indexes that eliminate Key Lookups
Making predicates sargable — removing functions from WHERE and JOIN columns
Eliminating N+1 patterns — use eager loading or projection in EF queries
Keeping transactions short — reduce lock duration and deadlock risk
Replacing scalar UDFs and cursors — embrace set-based SQL

Performance work is iterative. Establish a baseline, make one change at a time, measure, and repeat. Use the DMV queries in Section 7 to find your highest-impact targets before writing a single index.

Final Thought: The best query is the one you do not execute. Cache wisely, paginate results, and design indexes that let SQL Server do the minimum work necessary to satisfy each request.

Happy Learning