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