Top 10 T-SQL Performance Tips Every Data Engineer Should Know

Why T-SQL Performance Matters

In enterprise data environments, a poorly written query can consume 10x more CPU and I/O than its optimised equivalent. At scale — millions of rows, dozens of concurrent users — the difference between a 200ms query and a 2-second query is the difference between a responsive BI dashboard and a frustrated team.

Here are the 10 practices that consistently deliver the biggest gains.

1. Avoid SELECT *

Always specify the columns you need. SELECT * forces the engine to read every column, preventing index-only scans and increasing I/O.

-- ❌ Bad
SELECT * FROM orders WHERE status = 'completed';

-- ✅ Good
SELECT order_id, customer_id, amount, created_at
FROM orders
WHERE status = 'completed';

2. Use SARGable Predicates

A predicate is SARGable (Search ARGument ABLE) when SQL Server can use an index to satisfy it. Wrapping a column in a function breaks SARGability.

-- ❌ Bad — function on column prevents index seek
WHERE YEAR(created_at) = 2024

-- ✅ Good — range scan uses index
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

3. Prefer EXISTS over IN for Subqueries

EXISTS short-circuits on the first match; IN evaluates the full subquery result set.

-- ❌ Slower with large subquery
WHERE customer_id IN (SELECT id FROM customers WHERE region = 'EU')

-- ✅ Faster
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = orders.customer_id AND c.region = 'EU')

4. Use Covering Indexes

A covering index includes all columns referenced in a query, eliminating the need for a key lookup.

-- Query: SELECT order_id, amount FROM orders WHERE status = 'completed'
-- Covering index:
CREATE INDEX IX_orders_status_covering
ON orders (status) INCLUDE (order_id, amount);

5. Avoid Implicit Conversions

Mixing data types forces SQL Server to convert one side, often making the index unusable.

-- ❌ Bad — customer_id is INT, '12345' is VARCHAR → implicit conversion
WHERE customer_id = '12345'

-- ✅ Good
WHERE customer_id = 12345

6. Limit the Use of Cursors

Cursors process row-by-row and are orders of magnitude slower than set-based operations. Replace them with window functions, CTEs, or batch updates.

7. Use SET NOCOUNT ON in Stored Procedures

Suppresses the "rows affected" message, reducing network traffic in loops and batch operations.

CREATE PROCEDURE usp_ProcessOrders
AS
BEGIN
  SET NOCOUNT ON;
  -- procedure body
END

8. Partition Large Tables

For tables exceeding 100M rows, table partitioning on a date column allows partition elimination — queries only scan relevant partitions.

9. Monitor with sys.dm_exec_query_stats

Identify the top 10 most expensive queries by CPU or logical reads before optimising:

SELECT TOP 10
  qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
  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 query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_logical_reads DESC;

10. Keep Transactions Short

Long-running transactions hold locks, blocking other sessions. Move non-transactional work outside the transaction boundary.

*SQL Querywise Advisor automatically identifies all 10 of these patterns in your queries and provides specific, actionable recommendations.*