15 Common SQL Performance Pitfalls and Proven Fixes

Don’t Use UPDATE Instead of CASE

Replacing UPDATE with a CASE expression lets you evaluate each row’s condition once and write the result directly, avoiding double writes to the transaction log and yielding huge performance gains.

Don’t Blindly Reuse Code

Copy‑pasting code often brings in unnecessary columns or joins, inflating result sets and consuming resources. Trim the SELECT list to only the columns you truly need.

Select Only Needed Columns

Avoid SELECT * on wide tables; fetching all columns when only a few are used wastes I/O and memory, especially on tables with millions of rows.

Avoid Double‑Dip Queries

Instead of querying a large table twice and joining the results, extract the required subset into a temporary table once, then join to that smaller set to improve performance.

Know When to Use Temporary Tables

Temporary tables can prevent repeated scans of massive tables, reduce lock contention, and speed up reporting or complex stored procedures by persisting a small, filtered dataset.

Batch Delete and Update

Large delete or update statements run as a single transaction can block other work and cause long rollbacks. Solve this by:

Keeping each transaction small so only a few rows need to be rolled back.

Committing small batches to disk, allowing other transactions to proceed and greatly improving concurrency.

Use Temporary Tables to Improve Cursor Performance

When a cursor is unavoidable, load the data into a temporary table first and iterate over that smaller set, then apply a single UPDATE to the base table, reducing lock time and improving concurrency.

Use Table‑Valued Functions

Replace scalar functions in SELECT lists with table‑valued functions and use CROSS APPLY to avoid per‑row function calls, dramatically boosting query speed.

Don’t Perform Large Operations on Many Tables in One Batch

Break large multi‑table deletions into separate transactions so each transaction locks only one table, preventing blocking and improving overall throughput.

Don’t Use Triggers

Triggers execute in the same transaction as the original statement, locking both tables and extending lock duration. Instead, move such logic into separate stored procedures with their own short transactions.

Don’t Cluster on GUID

GUIDs are random 16‑byte values; clustering on them causes rapid fragmentation and severe performance degradation compared to identity columns. Benchmarks show GUID‑clustered tables lose thousands of percent performance within minutes.

If You Only Need to Check Existence, Don’t Count Rows

Replace SELECT COUNT(*) FROM dbo.T1 with IF EXISTS (SELECT 1 FROM dbo.T1) to get an instant true/false result, especially on tables with hundreds of millions of rows.

Don’t Perform Reverse Searches

Queries like WHERE RegionID <> 3 force table scans. Rewrite them to use index‑friendly predicates (e.g., WHERE RegionID < 3 UNION ALL SELECT ... WHERE RegionID > 3) to enable index usage and improve performance.

Remember, these tips are guidelines, not hard rules; always test in your environment before applying.