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.
Jump to a section
A simple way to picture Optimizing Database Query and Index Performance
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.
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'.
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.
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.
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.
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.
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.
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.
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.
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).
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.
These come up on the exam all the time. Here's how to tell them apart.
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
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
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
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
Mistake
Adding more indexes always makes queries faster.
Correct
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.
Mistake
A clustered index and a primary key are the same thing.
Correct
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.
Mistake
Statistics and indexes are the same concept.
Correct
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.
Mistake
If a query is slow, the first thing to do is rebuild all indexes.
Correct
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.
Mistake
An index seek is always faster than an index scan.
Correct
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.
Mistake
Full-text search indexes work the same as regular indexes.
Correct
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.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
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.
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.
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.
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.
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.
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.
You've finished Optimizing Database Query and Index Performance. Continue through the DP-300 study guide to build a complete picture of the exam.
Done with this chapter?