Query Optimization

Effective query optimization is essential for achieving high performance in SQL Server. This guide covers best practices, tools, and techniques to help you write efficient queries and troubleshoot performance issues.

1. Understand Execution Plans

Execution plans reveal how SQL Server executes a query. Use SET SHOWPLAN_XML ON or the graphical plan in SSMS to analyze cost distribution.

SET SHOWPLAN_XML ON;
GO
SELECT OrderID, CustomerID FROM Orders WHERE OrderDate > '2024-01-01';
GO
SET SHOWPLAN_XML OFF;
GO

2. Indexing Recommendations

Proper indexing can reduce I/O dramatically. Follow these guidelines:

Use covering indexes for frequently accessed columns.
Avoid over-indexing; each index adds write overhead.
Consider filtered indexes for sparse data.

3. Query Rewrites

Sometimes rewriting a query yields better plans:

-- Prefer EXISTS over IN for subqueries
SELECT p.ProductID, p.Name
FROM Products p
WHERE EXISTS (
    SELECT 1 FROM OrderDetails od
    WHERE od.ProductID = p.ProductID
);

4. Parameter Sniffing

When a stored procedure performs poorly for certain parameters, use OPTION (RECOMPILE) or create multiple plans with OPTIMIZE FOR.

CREATE PROCEDURE GetOrders @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate
    FROM Orders
    WHERE CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR(@CustomerID = 123));
END;

5. Use Query Store

Enable Query Store to capture query performance history and force good plans:

ALTER DATABASE AdventureWorks2022
SET QUERY_STORE = ON;
GO
EXEC sp_query_store_force_plan
    @query_id = 12345,
    @plan_id = 67890;

6. Common Pitfalls

Missing statistics – run UPDATE STATISTICS regularly.
Implicit conversions – ensure data types match in joins/filters.
Scalar UDFs – replace with inline table‑valued functions when possible.

7. Further Reading

Explore more topics in the Performance Tuning section: