Optimizing SQL Queries - Business Applications

1. Cardinality Estimation Optimization

Modern SQL engines rely heavily on accurate row estimates to choose the best execution plan. Bad estimates lead to slow joins and huge memory grants.

SELECT *
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'CardinalityEstimationModelVersion';

2. Solving Parameter Sniffing Problems

SQL caches execution plans based on first-run parameters; bad sniffing severely hurts performance. Use OPTIMIZE FOR or RECOMPILE.

OPTION (OPTIMIZE FOR (@customer_id UNKNOWN));

3. Adaptive Join Algorithms

SQL Server/Oracle adaptive joins dynamically choose hash or nested-loop at runtime depending on actual row counts.

SELECT * FROM sales s
JOIN customers c ON s.cust_id = c.id;

4. Batch Mode on Rowstore

Enable high-performance batch mode even on non-columnstore tables for analytics workloads.

ALTER DATABASE SCOPED CONFIGURATION
SET BATCH_MODE_ON_ROWSTORE = ON;

5. Memory-Optimized Temp Tables

Move temp tables to in-memory OLTP for extreme speed and elimination of TempDB contention.

CREATE TABLE #temp
WITH (MEMORY_OPTIMIZED = ON)
AS SELECT * FROM orders;

6. Multi-Column Selectivity Optimization

Choose correct index column order based on selectivity. Place most selective column first.

CREATE INDEX idx_multi
ON orders (customer_id, order_date);

7. Avoiding Hotspot Contention

High-write tables may repeatedly lock the same rows. Use hash partitioning or randomized keys.

INSERT INTO logs (bucket, message)
VALUES (ABS(CHECKSUM(NEWID())) % 16, @msg);

8. Deep Columnstore Optimization

Use columnstore indexes for analytically heavy workloads; massively reduces IO and boosts aggregation performance.

CREATE CLUSTERED COLUMNSTORE INDEX cci_sales
ON sales;

9. Preventing Table-Level Lock Escalation

Large batch operations automatically escalate to table locks — disable or break into smaller chunks.

ALTER TABLE orders SET (LOCK_ESCALATION = DISABLE);

10. Maintaining Stable Query Shapes

Slight code changes or parameter types can alter the entire execution plan. Keep query shapes predictable for reliable performance.

DECLARE @cust INT = CAST(@customer_id AS INT);
-- Prevent implicit conversion performance issues

11. Table Partitioning for Massive Data

Partition very large tables to improve read performance, reduce scan sizes, and optimize data management without impacting queries.

CREATE PARTITION FUNCTION pfOrders (DATE)
AS RANGE RIGHT FOR VALUES ('2024-01-01','2025-01-01');

12. Reducing Index Fragmentation

Regularly REBUILD or REORGANIZE indexes to avoid fragmentation which slows seeks and scans.

ALTER INDEX idx_orders_date_customer ON orders REBUILD;

13. Using Covering Indexes

Include extra columns in an index to avoid key lookups and improve SELECT performance.

CREATE INDEX idx_orders_cover
ON orders (order_date)
INCLUDE (customer_id, amount);

14. Replacing Cursors with Set-Based Logic

Cursors are slow — rewrite to set-based operations for millions of rows.

UPDATE t
SET processed = 1
FROM transactions t
WHERE amount > 50000;

15. Do Not Apply Functions on Indexed Columns

Functions block index usage causing full scans. Rewrite conditions to be SARGable.

-- Bad
WHERE YEAR(order_date) = 2025

-- Good
WHERE order_date >= '2025-01-01'
AND order_date < '2026-01-01';

16. Using Query Hints Carefully

Force index or join type only when necessary; overuse harms performance.

SELECT * FROM orders WITH (INDEX(idx_orders_date_customer));

17. Optimizing Join Order

Join smallest or most filtered result sets first to reduce intermediate result sizes.

SELECT *
FROM small_table s
JOIN large_table l ON s.id = l.id;

18. Using Materialized Views

Pre-calculate expensive aggregations or joins for instant performance on large datasets.

CREATE MATERIALIZED VIEW mv_sales_summary
AS SELECT product_id, SUM(amount) AS total_sales
FROM sales GROUP BY product_id;

19. Data Compression for I/O Reduction

Row or page-level compression increases cache efficiency and reduces IO for heavy read workloads.

ALTER TABLE orders REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

20. Avoiding Triggers for Heavy Logic

Triggers slow insert/update operations; move logic to procedures or queues.

-- Instead of trigger, use stored procedure for controlled validation

21. Using Query Store for Performance Regression

Identify slow-running queries, execution plan regressions, and optimize based on real workloads.

SELECT * FROM sys.query_store_query_text;

22. Updating Statistics Regularly

Outdated statistics cause poor plan choices. Maintain them for accurate estimates.

UPDATE STATISTICS orders WITH FULLSCAN;

23. Fixing Excessive Memory Grants

Large joins or sorts may request too much memory; tune stats or indexes to fix.

SELECT * FROM sys.dm_exec_query_memory_grants;

24. Reducing TempDB Usage

Rewrite queries that spill to TempDB; avoid big sorts, hashes, or unnecessary temp tables.

SELECT * FROM sys.dm_db_file_space_usage;

25. Designing Transactions to Prevent Deadlocks

Use consistent locking order, shorter transactions, and proper isolation levels.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

26. Breaking Large Updates into Batches

Avoid table locks and log overload by processing millions of rows in batches.

WHILE 1=1
BEGIN
  DELETE TOP (5000) FROM logs WHERE created < '2024-01-01';
  IF @@ROWCOUNT = 0 BREAK;
END;

27. FETCH Only Required Rows

Pagination improves performance on large result sets.

SELECT * FROM orders
ORDER BY order_date
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

28. Choosing Correct Join Algorithm

Nested loop for small sets, merge for sorted, hash for huge unsorted datasets.

-- SQL chooses automatically, but tune with indexes

29. Designing Efficient Row-Level Security

Use inline table-valued functions and avoid heavy logic in security predicates.

CREATE SECURITY POLICY OrderFilter
ADD FILTER PREDICATE dbo.fnUserAccess(user_id)
ON orders;

30. Parallel Execution Tuning

Control parallelism to avoid CPU pressure. Use MAXDOP and Cost Threshold for Parallelism.

SELECT * FROM orders OPTION (MAXDOP 4);