How would you optimize a slow-running SQL query?

Optimizing a slow SQL query usually involves identifying bottlenecks in execution, indexing, joins, filtering, and data retrieval. Here’s a practical step-by-step approach for Microsoft SQL Server.

1. Check the Execution Plan

Use:

-- Show estimated execution plan
SET SHOWPLAN_ALL ON;
GO

SELECT *
FROM Employees
WHERE DepartmentID = 10;

GO
SET SHOWPLAN_ALL OFF;

Or in SQL Server Management Studio:

• Press Ctrl + M
• Execute the query

Review:

• Table scans
• Index scans
• Expensive joins
• Missing indexes

2. Create Proper Indexes

A missing index is one of the biggest causes of slow queries.

Example

-- Create index on frequently searched column
CREATE INDEX IX_Employees_DepartmentID
ON Employees(DepartmentID);

Use indexes on:

• WHERE
• JOIN
• ORDER BY
• GROUP BY

Avoid over-indexing

Too many indexes slow down:

• INSERT
• UPDATE
• DELETE

3. Avoid SELECT *

Bad:

SELECT *
FROM Employees;

Better:

SELECT EmployeeID, FirstName, Salary
FROM Employees;

Retrieving only required columns reduces:

• I/O
• Memory usage
• Network transfer

4. Filter Early

Reduce rows as soon as possible.

Bad:

SELECT *
FROM Orders O
JOIN Customers C
ON O.CustomerID = C.CustomerID;

Better:

SELECT *
FROM Orders O
JOIN Customers C
ON O.CustomerID = C.CustomerID
WHERE O.OrderDate >= '2025-01-01';

5. Avoid Functions on Indexed Columns

Bad:

SELECT *
FROM Employees
WHERE YEAR(HireDate) = 2025;

This prevents index usage.

Better:

SELECT *
FROM Employees
WHERE HireDate >= '2025-01-01'
AND HireDate < '2026-01-01';

6. Use Proper JOINs

Prefer explicit joins:

SELECT E.Name, D.DepartmentName
FROM Employees E
INNER JOIN Departments D
ON E.DepartmentID = D.DepartmentID;

Ensure joined columns are indexed.

7. Avoid Unnecessary Subqueries

Bad:

SELECT Name
FROM Employees
WHERE DepartmentID IN (
    SELECT DepartmentID
    FROM Departments
    WHERE Location = 'Delhi'
);

Better:

SELECT E.Name
FROM Employees E
INNER JOIN Departments D
ON E.DepartmentID = D.DepartmentID
WHERE D.Location = 'Delhi';

8. Use EXISTS Instead of IN for Large Datasets

SELECT Name
FROM Employees E
WHERE EXISTS (
    SELECT 1
    FROM Departments D
    WHERE D.DepartmentID = E.DepartmentID
);

EXISTS often performs better for large correlated datasets.

9. Reduce Use of Cursors

Cursors process row-by-row and are slow.

Bad:

• Cursor loops

Better:

• Set-based operations

Example:

UPDATE Employees
SET Salary = Salary * 1.10
WHERE DepartmentID = 5;

10. Use Pagination Properly

Bad:

SELECT TOP 100000 *
FROM Orders;

Better:

SELECT *
FROM Orders
ORDER BY OrderID
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY;

11. Update Statistics

Outdated statistics cause poor execution plans.

UPDATE STATISTICS Employees;

Or:

EXEC sp_updatestats;

12. Detect Blocking and Deadlocks

Use:

EXEC sp_who2;

Or monitor:

• Blocking sessions
• Long-running transactions

13. Partition Large Tables

Useful for millions/billions of rows:

• Improves query performance
• Reduces scan size

14. Use Covering Indexes

Example:

CREATE INDEX IX_Orders_CustomerID
ON Orders(CustomerID)
INCLUDE (OrderDate, TotalAmount);

This avoids extra lookups.

15. Identify Expensive Queries

Use Dynamic Management Views (DMVs):

SELECT TOP 10
    qs.execution_count,
    qs.total_elapsed_time,
    qs.total_logical_reads,
    qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_elapsed_time DESC;

Typical Performance Problems

Common Optimization Workflow

• Find slow query
• Check execution plan
• Add/adjust indexes
• Reduce scanned rows
• Rewrite inefficient joins/subqueries
• Test again
• Monitor continuously

For interviews, a concise answer is usually:

“I optimize slow SQL queries by analyzing the execution plan, creating proper indexes, reducing unnecessary scans, avoiding SELECT *, optimizing joins and filters, updating statistics, and rewriting inefficient subqueries or functions that prevent index usage.”