# Optimizing Database Query and Index Performance

> Chapter 11 of the Courseiva DP-300 curriculum — https://courseiva.com/learn/dp-300/optimize-database-performance

**Official objective:** 4.2 — Optimize database performance

## Introduction

Optimising database query and index performance is the art of making your database answer questions as fast as possible. For the DP-300 exam, you need to understand how queries work under the hood and how indexes act like roadmaps to speed up data retrieval. This concept matters because slow queries can cripple a business application, and Azure gives you tools to diagnose and fix them.

## The Kitchen Order Station Analogy

A busy restaurant kitchen during Friday night dinner rush. The head chef stands at the order station, a board where tickets from waiters get pinned in a long row. Each ticket is a request from a table: 'Table 4: grilled salmon, mashed potatoes, green beans'. The chef grabs a ticket, reads it, and starts cooking. If the tickets are piled up randomly—some for Table 4, some for Table 9, some for Table 2—the chef has to shuffle through the stack to find the next one that's ready. That takes time, and the fries get cold. 

Now imagine the waiters organise the tickets by table number. The chef simply reads them in order. But when Table 9 shouts for extra sauce mid-service, the chef rips off that part of the ticket and forces the sous chef to run and fetch the sauce before the next ticket can be processed. That interruption slows everything down. 

An index in a database is like a well-organised order board with a map of where each ingredient is kept. Without it, the chef (the database engine) has to read every single ticket (every row) to find what's needed. With an index, the chef skips straight to the right ticket—but too many indexes, or messy ones, are like a board covered in sticky notes that overlap and confuse. The goal is to keep the board clean, ordered, and quick to read so the kitchen (the database) serves dishes (query results) fast.

## Core explanation

When you run a query in a database—like 'SELECT * FROM Orders WHERE CustomerID = 42'—the database engine must find the rows that match 'CustomerID = 42'. Without any help, the engine performs a 'table scan', which means it starts at the first row of the 'Orders' table and reads every single row until it finds all rows where CustomerID equals 42. If the table has a million rows, that's a million reads. This is slow, especially for large tables. 

An 'index' is a separate data structure that stores a subset of the table's data in a sorted order, along with pointers to the actual rows. Think of it like the index at the back of a textbook: instead of reading every page to find a topic, you look up the topic in the index, get the page number, and jump straight there. In a database, a 'clustered index' physically reorders the table's rows to match the index order. A table can have only one clustered index because the rows can only be sorted one way. A 'non-clustered index' is a separate copy of a subset of columns, sorted, with pointers to the full rows. A table can have many non-clustered indexes. 

When you create an index, the database engine updates it automatically whenever data is inserted, updated, or deleted. That maintenance costs time and disk space, so adding too many indexes can actually slow down write operations. The trick is to add indexes only on columns that are frequently used in WHERE clauses, JOIN conditions, or ORDER BY clauses. 

Azure SQL Database and SQL Server on Azure VMs provide tools to analyse query performance. The 'query execution plan' shows you exactly how the database engine ran your query—whether it used a table scan or an index seek. An 'index seek' is fast because the engine uses the index tree to jump directly to the matching rows. An 'index scan' is slower because it reads the entire index structure. The 'missing index' feature analyses your workload and suggests indexes that would speed up frequently run queries. 

Another key concept is 'index fragmentation'. Over time, as data is modified, the index pages become scattered on disk, making them less efficient. You can reorganise or rebuild indexes to reduce fragmentation. Rebuilding is more thorough but takes more resources and can lock the table. Reorganising is lighter and can be done online. 

You also need to understand 'statistics'. Statistics are metadata that describe the distribution of values in a column. The database engine uses statistics to estimate how many rows a query will return, which helps it choose the best execution plan. Outdated statistics can lead to bad plans. You can update statistics manually or let the database do it automatically. 

Finally, 'parameter sniffing' is a behaviour where the database engine optimises a query based on the parameter values from the first execution. That first plan may be bad for other parameter values later. You can resolve this by using query hints like 'OPTION (RECOMPILE)' or 'OPTIMIZE FOR UNKNOWN'.

## Real-world context

Picture yourself as a junior database administrator for an online retail company that sells furniture. The company uses Azure SQL Database for its product catalogue. One Monday, the customer service team complains that searching for a product by name takes over 30 seconds. You open Azure Monitor and look at the query performance insights. 

You find a query that runs hundreds of times a day: 'SELECT ProductID, ProductName, Price FROM Products WHERE ProductName LIKE '%chair%''. The execution plan shows a clustered index scan, meaning the database is reading every product row. The table has 500,000 rows. That's the problem. 

Your first step is to create a non-clustered index on the 'ProductName' column. But because the query uses a wildcard at the start ('%chair%'), a standard index can't be used efficiently—the engine still needs to scan the entire index. You decide to implement a 'full-text index' instead, which is designed for searching words inside text columns. After creating the full-text index and rewriting the query to use 'CONTAINS(ProductName, 'chair')', the search time drops to under one second. 

Next, you check for index fragmentation. You run 'sys.dm_db_index_physical_stats' and discover that the index on the 'CategoryID' column is 45% fragmented. You schedule a maintenance window to rebuild that index during low-traffic hours. 

You also notice that the database autogrows in size by 1 GB every time it runs out of space, which causes brief performance hiccups. You pre-allocate space by setting a larger initial size and a more appropriate autogrowth increment (like 512 MB). 

Finally, you set up a weekly job to update statistics on all tables. You also enable 'Query Store', a feature that permanently tracks query performance over time. Now, if a query slows down after an index change, you can roll back to a previous execution plan. The result: customer service searches are now instant, and the business is happy. Your manager asks you to document the process, which you do in the company wiki.

## Exam focus

The DP-300 exam tests your ability to diagnose and fix slow queries using indexes. Expect multiple-choice questions that describe a slow query and ask which index type or maintenance operation will fix it. The exam loves 'missing index' recommendations and 'clustered vs non-clustered' trade-offs. 

Key topics you must memorise: 
- Clustered index: sorts and stores the data rows in order. One per table. Speeds up range queries (e.g., BETWEEN). Best on columns that are often searched for ranges or sorted. 
- Non-clustered index: a separate structure containing key columns and a pointer to the full row. Many per table. Best on columns used in WHERE and JOIN but not for large data retrieval. 
- Covered index: a non-clustered index that includes all columns needed by a query, so the engine doesn't need to touch the table at all. This is called a 'covering' index. 
- Index seek vs scan: seek is fast (direct access via tree), scan is slow (reads all leaf pages). 
- Fragmentation: caused by insert/update/delete. Rebuild when fragmentation > 30%, reorganise when between 5% and 30%. 
- Statistics: outdated statistics cause bad plans. Update statistics after significant data changes. 
- Parameter sniffing: know that the first plan may be cached and reused. Use 'RECOMPILE' or 'OPTIMIZE FOR UNKNOWN' to fix it. 

Common traps in the exam: 
- They will ask: 'Which index type should you create?' and list options like 'Clustered', 'Non-clustered', 'Columnstore'. Columnstore indexes are for data warehousing and analytics, not OLTP. Know the difference. 
- They may describe a query that uses 'SELECT *' with a WHERE clause on a non-indexed column. The correct answer is to create a non-clustered index on that column. But if the query also returns many columns, a covering index might be better. 
- They will test 'index maintenance' scenarios. If a question says an index is 40% fragmented, the correct answer is 'rebuild'. If it's 15%, the answer is 'reorganise'. 
- They might give a scenario where a query is slow because of parameter sniffing. The solution is 'OPTION (RECOMPILE)' or 'OPTIMIZE FOR UNKNOWN'. 
- They will ask about 'Query Store'—know that it captures historical query plans and allows you to force a specific plan. 
- You will see questions about 'sys.dm_db_index_physical_stats' and 'sys.dm_db_missing_index_details'. Know what they return. 
- Watch out for questions that mention 'Azure SQL Database' vs 'SQL Server on Azure VM'. The maintenance options differ (some operations can be done automatically in PaaS).

## Step by step

1. **Identify the slow query** — Use Azure Monitor, Query Store, or sys.dm_exec_query_stats to find queries with high duration, CPU usage, or logical reads. Focus on the queries that run most frequently or are reported as slow by users.
2. **Analyse the execution plan** — Retrieve the 'actual execution plan' for the query. Look for operators like 'Table Scan', 'Clustered Index Scan', or 'Key Lookup'—these indicate missing or poorly designed indexes. An 'Index Seek' is ideal. Note the estimated vs actual rows: a large discrepancy suggests outdated statistics.
3. **Check for missing indexes** — Examine sys.dm_db_missing_index_details to see which columns the database engine estimates would benefit from indexing. The Dynamic Management View returns the equality columns, inequality columns, and included columns. Create a non-clustered index based on these recommendations.
4. **Create or modify the index** — Use the CREATE INDEX statement. Decide between clustered or non-clustered. For queries returning many columns, add them as included columns to create a covering index. Test the new index on a non-production environment first to ensure it speeds up the query without degrading other operations.
5. **Monitor and maintain the index** — After deploying the index, check fragmentation levels using sys.dm_db_index_physical_stats. If fragmentation exceeds 5%, schedule a maintenance job: reorganise for 5-30%, rebuild for >30%. Also update statistics on the table to ensure the optimiser has accurate data distribution information.
6. **Review parameter sniffing issues** — If the same query performs well for some parameter values but poorly for others, it may be suffering from parameter sniffing. Apply the query hint OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN to test. Consider forcing a plan via Query Store if the optimal plan is known.

## Comparisons

### Clustered Index vs Non-Clustered Index

**Clustered Index:**
- Physically reorders data rows in the table
- Only one per table
- Faster for range queries and sorting

**Non-Clustered Index:**
- Creates a separate structure with key columns and row pointers
- Up to 999 per table
- Faster for equality searches on key columns

### Index Seek vs Index Scan

**Index Seek:**
- Navigates the B-tree to find specific rows
- Efficient for small result sets
- Uses the index hierarchy to jump directly

**Index Scan:**
- Reads all leaf-level pages of the index
- Efficient for large result sets (e.g., >50% of rows)
- Can be caused by missing predicates or wildcards

### Index Rebuild vs Index Reorganise

**Index Rebuild:**
- Drops and recreates the index
- More thorough defragmentation
- Can be resource-intensive and lock the table (offline)

**Index Reorganise:**
- Defragments leaf-level pages only
- Lighter operation, runs online
- Best for fragmentation between 5% and 30%

### Query Store vs Traditional DMV Querying

**Query Store:**
- Persistent storage of query plans and runtime stats
- Automatic capture with configurable retention
- Allows plan forcing and regression detection

**Traditional DMV Querying:**
- Requires manual querying of sys.dm_exec_query_stats
- Data resets on server restart or plan cache flush
- No built-in plan forcing capability

## Diagram

_A flowchart showing the step-by-step process of diagnosing and resolving a slow database query._

```mermaid
flowchart TD
    A[Slow Query Identified] --> B[Get Execution Plan]
    B --> C{Missing Index?}
    C -- Yes --> D[Create Non-Clustered Index]
    C -- No --> E{High Fragmentation?}
    E -- Yes --> F[Rebuild or Reorganise Index]
    E -- No --> G{Outdated Statistics?}
    G -- Yes --> H[Update Statistics]
    G -- No --> I{Parameter Sniffing?}
    I -- Yes --> J[OPTION (RECOMPILE)]
    I -- No --> K[Review Query Design]
```

## Common misconceptions

- **Misconception:** Adding more indexes always makes queries faster. **Reality:** Each new index slows down write operations (INSERT, UPDATE, DELETE) because the index must be maintained. Too many indexes can actually degrade overall performance. (Beginners see indexes as pure speed boosts, not as trade-offs. The cost of index maintenance is invisible until it causes problems.)
- **Misconception:** A clustered index and a primary key are the same thing. **Reality:** By default, SQL Server creates a clustered index on the primary key, but you can create a primary key as a non-clustered index. A primary key enforces uniqueness, while a clustered index defines the physical order of rows. (The default behaviour creates the confusion. Most people never change the default, so they assume the two are inseparable.)
- **Misconception:** Statistics and indexes are the same concept. **Reality:** Indexes are structures that speed up data retrieval. Statistics are metadata about data distribution that help the query optimiser choose a good execution plan. Both are separate and necessary. (Both are used for query performance, so beginners lump them together. But the optimiser uses statistics even when no index exists.)
- **Misconception:** If a query is slow, the first thing to do is rebuild all indexes. **Reality:** First, examine the query execution plan to understand why it's slow. It might be missing an index, using outdated statistics, or suffering from parameter sniffing. Rebuilding indexes is a maintenance step, not a fix for all performance problems. (Rebuilding indexes feels like a clear action, so beginners jump to it. But it's often a waste of time if the real issue is a missing index.)
- **Misconception:** An index seek is always faster than an index scan. **Reality:** An index seek is faster when retrieving a small number of rows. For queries that return a large percentage of rows (say 50% of the table), a scan can be more efficient because seek operations involve many individual lookups. (The rule of thumb 'seek is good, scan is bad' is oversimplified. The optimiser considers the row count estimate to decide.)
- **Misconception:** Full-text search indexes work the same as regular indexes. **Reality:** Full-text indexes are built by the Full-Text Engine for tokenised text searches (e.g., words and phrases). They support operations like stemming and proximity, but cannot be used with simple equality or range queries like a regular index. (The word 'index' in both names makes beginners assume they are interchangeable, but they serve completely different purposes.)

## Key takeaways

- A clustered index determines the physical order of table rows, and a table can have only one clustered index.
- A non-clustered index is a separate structure that contains key columns and pointers to the full rows, and a table can have up to 999 non-clustered indexes.
- Index fragmentation measured above 30% requires a rebuild, while fragmentation between 5% and 30% can be fixed with a reorganise.
- Query Store in Azure SQL Database permanently captures query execution plans and allows you to force a specific plan to fix regressions.
- Parameter sniffing causes cached query plans to be reused for different parameter values, and using OPTION (RECOMPILE) forces the engine to generate a fresh plan each time.
- Outdated statistics can lead to poor query execution plans, so updating statistics after significant data changes is critical for performance.
- A covering index includes all columns referenced in a query, eliminating the need to access the table and improving read performance dramatically.

## FAQ

**How do I find out which index is missing in Azure SQL Database?**

Query sys.dm_db_missing_index_details. It returns the columns that the database engine recommends for indexing. You can also use the 'Missing Index' feature in Azure Monitor or the Query Store reports.

**What is the difference between reorganising and rebuilding an index?**

Reorganising defragments the leaf-level pages of an index and is an online operation that doesn't require full locks. Rebuilding drops and recreates the index, which can be done online or offline, and is more thorough but consumes more resources.

**Can I create more than one clustered index on a table?**

No, a table can have only one clustered index because the clustered index determines the physical order of the data rows. You can, however, create up to 999 non-clustered indexes.

**How do I update statistics manually in Azure SQL Database?**

Use the UPDATE STATISTICS statement followed by the table or indexed view name. For example: UPDATE STATISTICS Sales.Orders. You can also use sp_updatestats to update all statistics in the database.

**What is parameter sniffing and how do I fix it?**

Parameter sniffing is when the query optimiser uses the parameter values from the first execution to create a plan, which may be suboptimal for later values. Fix it by adding OPTION (RECOMPILE) to the query or by using OPTIMIZE FOR UNKNOWN.

**How do I check if an index is being used?**

Query sys.dm_db_index_usage_stats. It shows the number of seeks, scans, and lookups performed on each index. If an index has never been used, consider dropping it to reduce maintenance overhead.

## Check your understanding

1. **What type of index physically reorders the data rows in a table and can only be created once per table?**

   Answer: A clustered index.

2. **If a non-clustered index contains all columns needed by a query, what is this called?**

   Answer: A covering index.

3. **What tool in Azure SQL Database permanently stores historical query execution plans and runtime statistics?**

   Answer: Query Store.

4. **What option do you add to a query to force the optimiser to generate a new plan every time, bypassing parameter sniffing?**

   Answer: OPTION (RECOMPILE).

5. **If an index has 45% fragmentation, should you reorganise or rebuild it?**

   Answer: Rebuild it, because fragmentation above 30% requires a rebuild for full defragmentation.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/dp-300/optimize-database-performance