SQL Server Interview Questions & Answers

Topic 13: Indexing & Query Optimization Scenarios

Questions 111–120 | Advanced Scenario-Based Questions

This section focuses on real-world SQL Server indexing and query optimization scenarios including Key Lookups, missing indexes, excessive indexes, fragmentation, statistics, plan changes, application-vs-SSMS performance differences, parallelism and duplicate indexes.

Effective indexing is a balancing exercise. The objective is not to create as many indexes as possible, but to maintain the smallest effective index set that supports the important workload without introducing excessive write, storage and maintenance overhead.

111. A frequently executed query performs a Key Lookup thousands of times. How would you optimize it?

Answer:
A Key Lookup is not automatically a problem. I would first determine how many rows require the lookup and how much of the query's overall cost and I/O it represents.

If thousands of repeated lookups are responsible for significant reads or execution time, I would investigate whether the nonclustered index can be modified to cover the query.

For example, consider:

SELECT
    CustomerID,
    OrderDate,
    OrderStatus,
    TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID;

If the existing index contains only:

CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders(CustomerID);

SQL Server may need a Key Lookup to retrieve OrderDate, OrderStatus and TotalAmount.

A possible covering index could be:

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

Before implementing it, I would compare:

Logical reads before and after
Execution duration
CPU consumption
Index size
INSERT, UPDATE and DELETE overhead
Existing overlapping indexes

Senior DBA Tip:
Do not eliminate every Key Lookup. A lookup performed five times may be cheaper than maintaining a much wider index. Focus on expensive, frequently executed lookups.

112. SQL Server recommends many missing indexes. Would you create all of them?

Answer:
No. Missing-index recommendations should be treated as tuning clues, not automatic implementation instructions.

Creating every recommendation can result in:

Duplicate indexes
Overlapping indexes
Excessive storage usage
Slower INSERT, UPDATE and DELETE operations
More transaction log generation
Longer index maintenance windows
Additional backup overhead

I would first compare each recommendation with the existing indexes.

For example, SQL Server might independently suggest:

INDEX 1
(CustomerID)
INCLUDE (OrderDate)

INDEX 2
(CustomerID)
INCLUDE (OrderDate, TotalAmount)

INDEX 3
(CustomerID)
INCLUDE (OrderStatus)

Instead of blindly creating three indexes, I would evaluate whether one carefully designed index can support all three workloads.

I would validate:

Query frequency
Business importance
Read improvement
Write overhead
Existing indexes
Key order
Included columns
Actual execution plans

Interview Tip:
Missing-index DMVs do not understand your complete workload or automatically consolidate similar recommendations. Always review the existing index design before creating a recommended index.

113. A table has more than 30 nonclustered indexes. What problems could this cause?

Answer:
Thirty indexes are not automatically wrong, but such a table deserves careful investigation because every index has a maintenance cost.

Possible problems include:

Slower INSERT operations
Slower UPDATE operations
Slower DELETE operations
More transaction log generation
Higher storage consumption
Longer backup duration
Larger Buffer Pool footprint
Longer index maintenance
More statistics to maintain
Duplicate or overlapping index definitions

I would review index usage using sys.dm_db_index_usage_stats and compare the index definitions.

However, I would not immediately drop an index simply because its usage counters are zero.

An index might support:

Month-end processing
Quarterly reports
Weekend jobs
Rare but business-critical queries

DBA Tip:
Capture usage over a representative business cycle before dropping production indexes. DMV usage counters can also reset after SQL Server restarts and other events.

114. Index fragmentation reaches 80% on a large table. Would you immediately rebuild the index?

Answer:
No. I would not rebuild an index based only on the fragmentation percentage.

I would first check:

Index page count
Page density
Workload type
Whether the index is actually used
Whether queries perform range scans
Maintenance window
Available transaction log space
Availability Group impact
Expected benefit

For example:

Index A
Fragmentation = 80%
Pages = 12

Index B
Fragmentation = 45%
Pages = 5,000,000

Index B may be far more important operationally even though its fragmentation percentage is lower.

I would inspect:

SELECT
    OBJECT_SCHEMA_NAME(ips.object_id) AS SchemaName,
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'LIMITED'
) AS ips
INNER JOIN sys.indexes AS i
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.index_id > 0
ORDER BY ips.page_count DESC;

Interview Tip:
Avoid blindly applying rules such as "fragmentation above 30% always means rebuild." Fragmentation percentage is only one input into an index-maintenance decision.

115. An index rebuild causes blocking and transaction log growth in production. How would you handle it?

Answer:
Index rebuilds can be resource-intensive operations and should be planned according to database size, edition/features, workload, recovery model and HA/DR architecture.

I would assess:

Whether the rebuild is actually necessary
Size of the index
Transaction log free space
Disk free space
Maintenance window
Availability Group send/redo queues
Concurrent workload
Whether an online rebuild is supported
Whether resumable index operations are supported and appropriate

Where supported and appropriate, options can include:

ALTER INDEX IX_Orders_CustomerID
ON dbo.Orders
REBUILD
WITH
(
    ONLINE = ON
);

Or, on supported versions and configurations, a resumable online rebuild may provide additional operational control.

Other approaches can include:

Scheduling maintenance during lower activity
Rebuilding only indexes that genuinely require maintenance
Managing transaction-log capacity before maintenance
Using reorganize where appropriate
Performing partition-level maintenance where applicable
Monitoring HA replica lag during maintenance

Senior DBA Tip:
Never start a very large production rebuild without checking transaction-log capacity and HA/DR impact. A maintenance operation that improves an index but fills the log drive is not successful maintenance.

116. A query remains slow even after creating the recommended index. What would you check next?

Answer:
I would not assume that an index recommendation guarantees a faster execution plan.

I would check:

Whether SQL Server is actually using the new index
Actual execution plan
Estimated vs actual rows
Statistics
Parameter sensitivity
Implicit conversions
Non-SARGable predicates
Key Lookups
Sort or Hash spills
Memory grant
Blocking
Wait types

I would also measure:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Execute the query

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

If logical reads remain extremely high, the new index may not be addressing the actual access pattern.

DBA Principle:
Creating an index is not the end of tuning. Always compare the execution plan, reads, CPU and duration before and after the change.

117. A query performs well in SSMS but runs slowly from the application. How would you troubleshoot the difference?

Answer:
I would compare the application execution with the SSMS execution rather than assuming SQL Server behaves identically for both.

I would compare:

Exact query text
Parameter values
Parameter data types
SET options
Database context
Login and permissions
Execution plan
Application connection settings
Whether the application is inside a transaction
Whether application-side processing contributes to elapsed time

Different SET options can result in different cached execution plans.

An important example historically encountered when comparing SSMS and applications is:

SET ARITHABORT ON;

However, I would compare the complete relevant session environment instead of assuming ARITHABORT is always the cause.

I would use Query Store or Extended Events to capture the actual application execution and compare it with the SSMS test.

Interview Tip:
"Fast in SSMS" does not prove that the application is running the same query under the same conditions. Compare the exact execution context.

118. A query plan suddenly changes after statistics are updated. Why can this happen?

Answer:
Statistics contain information used by the Query Optimizer to estimate row counts.

Updating statistics can change those estimates. A subsequent compilation may therefore choose a different execution strategy.

The new plan may change:

Join order
Join type
Seek vs Scan selection
Parallelism
Memory grant
Index selection

For example:

Old Estimate   = 100 rows
Updated Estimate = 2,000,000 rows

The optimizer may reasonably select a completely different plan for two million rows.

If the new plan causes regression, I would investigate:

Statistics sampling
Data skew
Parameter values
Old vs new plans
Query Store history
The first major cardinality-estimation difference

Interview Tip:
Updating statistics is generally beneficial, but it can trigger recompilation and expose a different plan. Query Store is particularly useful for identifying such regressions.

119. A query uses excessive parallelism and consumes most of the server CPU. How would you address it?

Answer:
I would first determine why the query requires so much work before changing server-wide parallelism settings.

I would review:

Actual execution plan
Logical reads
Cardinality estimates
Indexes
Statistics
Join strategies
Large scans
Query frequency
Current MAXDOP
Cost Threshold for Parallelism
CPU and NUMA architecture

If the query is doing unnecessary work, query and index optimization should come before simply suppressing parallelism.

For an individual query, where justified, an option such as:

OPTION (MAXDOP 4);

may provide more targeted control than changing the entire SQL Server instance.

On supported versions, Query Store hints may also provide a way to apply query-level behavior without changing the application code.

Common Interview Trap:
Do not immediately set MAXDOP = 1 because one query is consuming too much CPU. That could negatively affect unrelated workloads that benefit from parallel execution.

120. You discover duplicate and overlapping indexes on a large production database. How would you rationalize them safely?

Answer:
I would treat index consolidation as a controlled production change rather than immediately dropping apparently duplicate indexes.

My approach would be:

Inventory every index definition.
Compare key columns and their order.
Compare included columns.
Identify exact duplicates.
Identify partially overlapping indexes.
Review index usage over a representative workload period.
Check Query Store and important application queries.
Check whether indexes support constraints or unique requirements.
Estimate storage and DML overhead.
Consolidate only where one index can safely support multiple workloads.
Remove redundant indexes through controlled change management.
Monitor query performance and write performance after the change.

Consider:

INDEX A
(CustomerID)

INDEX B
(CustomerID, OrderDate)

INDEX C
(CustomerID, OrderDate)
INCLUDE (OrderStatus, TotalAmount)

These indexes overlap, but that does not automatically mean Index A and Index B can safely be dropped. Key width, workload patterns, selectivity, query ordering and write cost should all be evaluated.

Senior DBA Tip:
Index consolidation is a workload decision, not a text-matching exercise. Two similar index definitions can still serve materially different workload requirements.

Useful DBA Script: Check Index Usage

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.index_id,
    ISNULL(us.user_seeks, 0) AS UserSeeks,
    ISNULL(us.user_scans, 0) AS UserScans,
    ISNULL(us.user_lookups, 0) AS UserLookups,
    ISNULL(us.user_updates, 0) AS UserUpdates,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
FROM sys.indexes AS i

LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON i.object_id = us.object_id
    AND i.index_id = us.index_id
    AND us.database_id = DB_ID()

WHERE
    i.index_id > 0
    AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1

ORDER BY
    OBJECT_SCHEMA_NAME(i.object_id),
    OBJECT_NAME(i.object_id),
    i.index_id;

Important:
Usage counters are not permanent history. Before dropping an index, understand when the counters were reset and whether the observation window includes month-end, quarter-end and other important workloads.

Useful DBA Script: Review Missing Index Suggestions

SELECT TOP (50)
    DB_NAME(mid.database_id) AS DatabaseName,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS SchemaName,
    OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle

WHERE mid.database_id = DB_ID()

ORDER BY
    (
        migs.user_seeks + migs.user_scans
    ) *
    migs.avg_total_user_cost *
    (
        migs.avg_user_impact / 100.0
    ) DESC;

Useful DBA Script: List Index Definitions

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.index_id,
    i.type_desc AS IndexType,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    c.name AS ColumnName,
    ic.key_ordinal,
    ic.is_included_column
FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id

INNER JOIN sys.columns AS c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id

WHERE
    i.index_id > 0
    AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1

ORDER BY
    SchemaName,
    TableName,
    i.index_id,
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;

Index Optimization Decision Flow


      Slow / Expensive Query

         ↓

      Capture Actual Execution Plan

         ↓

      Measure Reads + CPU + Duration

         ↓

      Review Cardinality Estimates

         ↓

      Review Seek / Scan / Lookup Pattern

         ↓

      Review Existing Indexes

         ↓

      Review Missing Index Suggestions

         ↓

      Look for Duplicate / Overlapping Indexes

         ↓

      Design Smallest Effective Index Set

         ↓

      Test with Representative Workload

         ↓

      Compare Before vs After

         ↓

      Measure Read Benefit vs Write Cost

Senior DBA Indexing Principle

Every index has two sides.

Benefit:
Faster reads and potentially better execution plans.

Cost:
Additional storage, memory footprint, transaction log generation, backup size, DML overhead and maintenance.

The goal is not the maximum number of indexes.

The goal is the smallest effective index set that supports the business workload.

Interview Quick Recap

Frequent expensive Key Lookups may justify a covering index.
Do not automatically create every missing-index recommendation.
Too many indexes can significantly increase write and maintenance overhead.
Fragmentation percentage alone should not determine whether an index is rebuilt.
Large index rebuilds require planning for blocking, transaction-log growth, disk space and HA/DR impact.
If a recommended index does not improve performance, return to the execution plan and workload evidence.
When SSMS is fast but the application is slow, compare the actual execution environment rather than only the query text.
Statistics updates can alter cardinality estimates and produce a new execution plan.
Excessive parallelism should be investigated at query level before changing server-wide MAXDOP.
Duplicate and overlapping indexes should be consolidated only after representative workload analysis.

← Previous Topic
# Topic 12: Production Performance Troubleshooting

Next Topic →

# Topic 14: Transaction Log an Recovery Scenarios

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning

Search This Blog

Technical Architects Zone

SQL Server Q&A Topic 13: Indexing & Query Optimization Scenarios