---
title: "How would you optimize a slow-running SQL query?"  
description: "How would you optimize a slow-running SQL query?"  
author: "Anubhav Sharma"  
published: 2026-05-14  
updated: 2026-05-20  
canonical: https://answers.mindstick.com/qa/116619/how-would-you-optimize-a-slow-running-sql-query  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 4 minutes  

---

# How would you optimize a slow-running SQL query?

## Answers

### Answer by Ravi Vishwakarma

Optimizing a slow SQL query usually involves identifying bottlenecks in execution, indexing, joins, filtering, and [data retrieval](https://www.mindstick.com/forum/160123/how-do-you-handle-asynchronous-data-retrieval-in-a-dot-net-core-api-for-improved-performance). Here’s a practical step-by-step approach for Microsoft SQL Server.

## 1. Check the Execution Plan

Use:

```plaintext
-- 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](https://www.mindstick.com/forum/159668/what-is-the-difference-between-sql-server-and-sql-management-studio):

- Press `Ctrl + M`
- Execute the query

Review:

- Table scans
- Index scans
- Expensive joins
- [Missing indexes](https://www.mindstick.com/interview/34512/how-do-you-identify-missing-indexes)

## 2. Create Proper Indexes

A missing index is [one of the biggest](https://yourviews.mindstick.com/view/84809/judicial-reforms-lead-to-one-of-the-biggest-protests-in-israel) causes of slow queries.

### Example

```plaintext
-- 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:

```plaintext
SELECT *
FROM Employees;
```

Better:

```plaintext
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:

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

Better:

```plaintext
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:

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

This prevents index usage.

Better:

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

## 6. Use Proper JOINs

Prefer explicit joins:

```plaintext
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:

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

Better:

```plaintext
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

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

`EXISTS` often [performs better](https://answers.mindstick.com/qa/95774/which-performs-better-google-adsense-mediavine-or-ezoic) 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:

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

## 10. Use Pagination Properly

Bad:

```plaintext
SELECT TOP 100000 *
FROM Orders;
```

Better:

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

## 11. Update Statistics

Outdated statistics cause poor execution plans.

```plaintext
UPDATE STATISTICS Employees;
```

Or:

```plaintext
EXEC sp_updatestats;
```

## 12. Detect Blocking and Deadlocks

Use:

```plaintext
EXEC sp_who2;
```

Or monitor:

- Blocking sessions
- Long-running transactions

## 13. Partition Large Tables

Useful for millions/billions of rows:

- Improves [query performance](https://www.mindstick.com/forum/160277/how-execution-plan-can-help-identify-query-performance-issues)
- Reduces scan size

## 14. Use Covering Indexes

Example:

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

This avoids extra lookups.

## 15. Identify Expensive Queries

Use Dynamic Management Views (DMVs):

```plaintext
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

| Problem | Solution |
| --- | --- |
| Table Scan | Add indexes |
| High CPU | Rewrite joins/subqueries |
| High I/O | Select fewer columns |
| Slow sorting | Index ORDER BY columns |
| Blocking | Shorter transactions |
| [Parameter sniffing](https://www.mindstick.com/forum/160280/describe-the-concept-of-parameter-sniffing-and-its-impact-on-query-performance) | Use `OPTION (RECOMPILE)` carefully |

## Common Optimization Workflow

- Find slow query
- Check [execution plan](https://www.mindstick.com/forum/160276/what-is-an-execution-plan-in-sql-server)
- 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.”


---

Original Source: https://answers.mindstick.com/qa/116619/how-would-you-optimize-a-slow-running-sql-query

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.