Database optimization techniques with T-SQL examples

Picture this: Your application was running smoothly last month, but now users are complaining about slow page loads. Your database server's CPU is spiking during business hours, and what used to be instant queries are now taking seconds or, in worst cases, minutes to complete. If you’ve ever waited just a bit too long for a query to return results, you’re not alone. SQL Server is a powerful engine, but without optimization, even basic queries can perform poorly. Whether you’re tuning a slow report or squeezing more from your database compute power, knowing how to optimize your SQL is essential.

In this blog post, we’ll cover practical SQL optimization techniques with real T-SQL examples that can help reduce execution time, lower CPU usage, and therefore make your applications more responsive.

Setting Up Our Test Environment

For this demonstration, Microsoft SQL Server 2022 was used. The examples are based on the following table structure:

	
	CREATE TABLE Orders (
	    OrderID INT IDENTITY(1,1) PRIMARY KEY,
	    CustomerID INT NOT NULL,
	    OrderDate DATETIME NOT NULL,
	    TotalAmount DECIMAL(10,2) NOT NULL,
	    Status VARCHAR(20) NOT NULL,
	    ShippingAddress VARCHAR(200) NULL,
	    OrderPriority TINYINT DEFAULT 3, _-- 1=High, 2=Medium, 3=Standard
	    LastModified DATETIME DEFAULT GETDATE()
	);
	

	
	-- Create OrderDetails table with a relationship to Orders
	CREATE TABLE OrderDetails (
	    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,
	    OrderID INT NOT NULL,
	    ProductID INT NOT NULL,
	    Quantity INT NOT NULL,
	    UnitPrice DECIMAL(10,2) NOT NULL,
	    Discount DECIMAL(5,2) DEFAULT 0,
	    IsGift BIT DEFAULT 0,
	    CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY (OrderID)
	    REFERENCES Orders (OrderID)
	);
	

For populating the tables, the following script was used:

	
	DECLARE @i INT = 1
	DECLARE @customerCount INT = 1000
	DECLARE @startDate DATETIME = '2010-01-01'
	DECLARE @endDate DATETIME = '2025-05-01'
	DECLARE @daySpan INT = DATEDIFF(DAY, @startDate, @endDate)
	WHILE @i <= 600000 -- 600,000 orders
	BEGIN
	    DECLARE @customerId INT = CAST(RAND() * @customerCount AS INT) + 1
	    DECLARE @orderDate DATETIME = DATEADD(DAY, CAST(RAND() * @daySpan AS INT), @startDate)
	    DECLARE @totalAmount DECIMAL(10,2) = CAST((RAND() * 1000) + 10 AS DECIMAL(10,2))
	    DECLARE @status VARCHAR(20) = CASE
	        WHEN RAND() < 0.7 THEN 'Completed'
	        WHEN RAND() < 0.85 THEN 'Processing'
	        WHEN RAND() < 0.95 THEN 'Shipped'
	        ELSE 'Cancelled'
	    END
	    DECLARE @priority TINYINT = CASE
	        WHEN RAND() < 0.1 THEN 1 -- High
	        WHEN RAND() < 0.3 THEN 2 -- Medium
	        ELSE 3 -- Standard
	    END
	    INSERT INTO Orders (CustomerID, OrderDate, TotalAmount, Status, OrderPriority, LastModified)
	    VALUES (@customerId, @orderDate, @totalAmount, @status, @priority,
	        DATEADD(MINUTE, CAST(RAND() * 43200 AS INT), @orderDate))
	SET @i = @i + 1
	END
	

	
	-- Insert dummy data into OrderDetails_
	DECLARE @maxOrderId INT = (SELECT MAX(OrderID) FROM Orders)
	DECLARE @minOrderId INT = (SELECT MIN(OrderID) FROM Orders)
	DECLARE @productCount INT = 2000
	SET @i = 1
	WHILE @i <= 150000 -- 150,000 order detail records_
	BEGIN
	    DECLARE @orderId INT = CAST(RAND() * (@maxOrderId - @minOrderId) AS INT) + @minOrderId
	    DECLARE @productId INT = CAST(RAND() * @productCount AS INT) + 1
	    DECLARE @quantity INT = CAST(RAND() * 10 AS INT) + 1
	    DECLARE @unitPrice DECIMAL(10,2) = CAST((RAND() * 100) + 5 AS DECIMAL(10,2))
	    DECLARE @discount DECIMAL(5,2) = CASE WHEN RAND() < 0.3 THEN CAST(RAND() * 20 AS DECIMAL(5,2)) ELSE 0 END
	    DECLARE @isGift BIT = CASE WHEN RAND() < 0.1 THEN 1 ELSE 0 END
	    INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice, Discount, IsGift)
	    VALUES (@orderId, @productId, @quantity, @unitPrice, @discount, @isGift)
	    SET @i = @i + 1
	END
	

Importance of indexes

What is an Index?

An index in a database is a data structure that enhances the speed of data retrieval operations on a table, much like an index in a book. It works by creating a reference to the location of data based on one or more columns.

How Database Indexes Work

A database index is like the index at the back of a textbook. When you’re looking for information about “SQL joins,” you don’t have to search through every page of the book. Instead, you can go to the index, find “SQL joins,” and it tells you exactly which pages contain that specific information. Indexes work similarly.

Without an index, the database must perform a full table scan, which means going through every single row in a table to find the data you’re looking for. This is like reading every page of a book to find mentions of “SQL joins.” With an index, the database can directly access the relevant rows, thereby significantly reducing the time it takes to retrieve the data.

The Structure Behind the Speed

Database indexes are typically implemented using B-tree (balanced tree) data structures, although other types, such as hash indexes and bitmap indexes, also exist. For efficient searching, insertion, and deletion, the B-tree keeps the data sorted.

Once an index is created on a column, the database creates a different structure that contains:

• The sorted indexed column values
• Pointers to the rows in the table

This structure is much smaller than a whole table and can be searched much faster.

Let's say there’s a report that uses the following query to retrieve the completed orders of a given customer from a specific date. For performance measurement, SET STATISTICS TIME was used.

	
	SET  STATISTICS  TIME  ON;
	GO
	SELECT CustomerID, OrderDate, TotalAmount, Status
	FROM Orders
	WHERE CustomerID =  66
	ORDER BY OrderDate DESC;
	GO
	SET  STATISTICS  TIME  OFF;
	GO
	

The above query takes 32ms to run. Let’s create a standard non-clustered index on the orders table on the CustomerID field.

	
	CREATE  NONCLUSTERED  INDEX IDX_Orders_CustomerID
	ON Orders (CustomerID);
	

With the standard index, our previously created query takes 2ms to run. But can we make it faster? The answer is yes. Let's delete the standard index and check the execution plan. By checking the execution plan, we can see a recommendation to create an index for this query, as it could reduce our execution time by over 99%.

	
	**Missing Index (Impact 99.6483): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]ON [dbo]. [Orders] ([CustomerID]) INCLUDE ( [OrderDate],
	[TotalAmount], [Status])**
	

By creating the following index with additional columns, we can further enhance the efficiency of our query.

	
	CREATE  NONCLUSTERED  INDEX IDX_Orders_CustomerID_OrderDate_Covering ON Orders (CustomerID, OrderDate DESC) INCLUDE (TotalAmount,  Status, OrderPriority);
	

Now, the query takes less than 1ms to run.

Date Range Queries (SARGable Predicates)

To fully utilize the index created beforehand, we need to ensure that our queries are SARGable. SARGable stands for Search ARGument ABLE - meaning the SQL Server query optimizer can use indexes to efficiently locate rows instead of scanning the entire table.

-- Non-SARGable queries

	
	SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;
	SELECT * FROM Orders WHERE DATEDIFF(day, OrderDate, GETDATE()) <= 30;
	SELECT * FROM Orders WHERE CONVERT(VARCHAR(10), OrderDate, 120) = '2024-06-15';
	--SARGable queries
	SELECT * FROM Orders
	WHERE OrderDate >= '2024-01-01'
	AND OrderDate < '2025-01-01';
	

	
	SELECT * FROM Orders
	WHERE OrderDate >= DATEADD(day, -30, GETDATE());
	

A predicate is SARGable when:

• The column is isolated on one side of the comparison operator
• No functions or calculations are applied to the indexed column
• The query optimizer can determine a specific range of values to search

By avoiding built-in functions and calculations on indexed columns and using direct comparisons, we can create SARGable queries.

Join performance

Indexes can also significantly speed up joins; therefore, it's recommended to use indexes on columns that are joined. This query retrieves all orders from 2024 onward, along with their related order details.

	
	-- Poor performance - no proper indexing on JOIN columns SELECT o.OrderID, o.CustomerID, o.TotalAmount, od.ProductID, od.Quantity, od.UnitPrice FROM Orders o LEFT JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE o.OrderDate >= '2024-01-01'; 
	

What is wrong with the following query?

• No Indexes on JOIN Columns: If OrderID in Orders and OrderDetails is not indexed, the database engine must perform a full table scan and hash join—this gets worse as data grows.
• LEFT JOIN Usage: If you don’t need unmatched rows from Orders, consider using INNER JOIN to reduce result size and improve performance.

-- Create an index on the foreign key

	
	CREATE NONCLUSTERED INDEX IDX_OrderDetails_OrderID
	ON OrderDetails (OrderID)
	INCLUDE (ProductID, Quantity, UnitPrice);
	

By creating an index on the foreign key column, you can significantly improve the performance of the JOIN operation. Additionally, switching from a LEFT JOIN to an INNER JOIN improves efficiency if the logic requires listing orders that have matching order details. INNER JOIN is faster in most cases; therefore, if the logic allows it, use INNER JOIN instead of LEFT JOIN.

	
	SELECT o.OrderID, o.CustomerID, o.TotalAmount,
	od.ProductID, od.Quantity, od.UnitPrice
	FROM Orders o
	INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
	WHERE o.OrderDate >= '2024-01-01';
	

Pre-computed columns

Let’s enhance the OrderDetails table by adding a computed column for the total line amount.

ALTER TABLE OrderDetails ADD LineTotal AS (Quantity * UnitPrice * (1 - Discount/100)) PERSISTED;

Computed columns can be a powerful feature in SQL Server when used wisely. They allow you to calculate a value based on other columns in the same table. Ideal for frequently used expressions. The persisted keyword makes sure the value is stored in the table, not just calculated on the fly. Indexing computed columns improves performance, especially for filtering and sorting.

-- Index the computed column

CREATE INDEX IDX_OrderDetails_LineTotal ON OrderDetails (LineTotal);

Summary

Optimizing SQL Server queries doesn’t always require complex changes. It’s more about making small changes that have a significant impact on performance. If a specific query is too slow, review the execution plans and look for missing index suggestions.

Index Strategy - Standard indexes can reduce query execution time by 90%+ (32ms to 2ms in our example) - Covering indexes with INCLUDE columns can push performance even further (down to <1ms) - Always index foreign key columns used in JOINs - Review execution plans for missing index recommendations; they often provide the exact CREATE INDEX statement needed.

Query Optimization Best Practices - Write SARGable predicates by avoiding functions on indexed columns (YEAR(), DATEDIFF(), etc.) - Use direct comparisons and range operators instead of functions - Choose INNER JOIN over LEFT JOIN when you don’t need unmatched rows and the logic allows it. Isolating columns on one side of comparison operators enables index usage.

Advanced Techniques - Use computed columns for frequently calculated expressions - Use the PERSISTED keyword to store computed values physically - Index computed columns when they’re used in WHERE clauses or ORDER BY statements for faster execution time.

References

B-Trees https://www.cs.cornell.edu/courses/cs3110/2012sp/recitations/rec25-B-trees/rec25.html

How Database B-Tree Indexing Works https://builtin.com/data-science/b-tree-index

SET STATISTICS TIME (Transact-SQL) https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql?view=sql-server-ver17

CREATE INDEX (Transact-SQL) https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver17

Indexing in databases https://www.geeksforgeeks.org/dbms/indexing-in-databases-set-1/

How to use sargable expressions in T-SQL queries; performance advantages and examples https://www.sqlshack.com/how-to-use-sargable-expressions-in-t-sql-queries-performance-advantages-and-examples/

ALTER TABLE computed_column_definition (Transact-SQL) https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql?view=sql-server-ver17