Indexing & Performance Interview Questions

An index is a data structure that improves the speed of data retrieval operations by providing quick access to rows in a database table. It creates a pointer to data based on the values of specific columns, similar to a book's index, reducing the need for full table scans.

A clustered index determines the physical order of data in a table and can only exist once per table. Non-clustered indexes create a separate structure that points to the data and multiple can exist per table. Clustered indexes are typically faster for retrievals but slower for inserts.

Index selectivity is the ratio of unique values to total rows in an indexed column. High selectivity (many unique values) makes an index more effective as it better narrows down the result set. Low selectivity indexes might be ignored by the query optimizer.

Composite indexes include multiple columns in a specific order. They're useful for queries that filter or sort by multiple columns, following the leftmost principle. The order of columns should match common query patterns and consider column selectivity.

NULL values in indexed columns can affect performance by increasing index size and complexity. Some databases store NULL values in the index, while others don't. Understanding NULL handling is crucial for optimal index design and query performance.

Index fragmentation occurs when the logical order of index pages doesn't match their physical order, or when pages have empty space. It can degrade performance by causing extra I/O operations. Regular maintenance (rebuilding or reorganizing) helps maintain optimal performance.

Execution plans show how SQL Server processes a query, including index usage, join types, and estimated costs. Analyze plans to identify full table scans, inefficient joins, or missing indexes. Use this information to optimize queries through index creation or query restructuring.

Covering indexes include all columns needed by a query in the index itself, eliminating the need to access the table. They improve performance by reducing I/O but increase storage space and maintenance overhead. Use them for frequently run queries that access a limited set of columns.

Index maintenance operations (rebuilding, reorganizing) can impact performance by consuming resources and blocking operations. Schedule maintenance during low-usage periods, consider online operations, and balance frequency against database performance needs.

Filtered indexes include only a subset of rows based on a predicate. They're smaller and more efficient for queries matching the filter condition. Use them when queries frequently access a specific subset of data or for implementing row-level security.

Monitor blocking using dynamic management views, identify long-running transactions or lock escalation issues. Solutions include optimizing transaction duration, using appropriate isolation levels, implementing row versioning, or adjusting index design.

Parameter sniffing occurs when SQL Server reuses an execution plan optimized for specific parameter values. It can lead to poor performance when data distribution varies significantly. Solutions include using RECOMPILE hints or local variables.

Statistics provide the query optimizer with data distribution information to choose efficient execution plans. Outdated or missing statistics can lead to poor plan choices. Regular updates and appropriate sampling rates are crucial for optimal performance.

Index foreign key columns to improve JOIN performance and maintain referential integrity efficiently. Consider column order in composite indexes, include frequently queried columns, and evaluate the impact on write operations.

Strategies include partitioning, batch processing, minimizing logging, using appropriate isolation levels, and considering index impact. For maintenance operations, use minimal logging, tempdb optimization, and parallel execution when possible.

Index intersection occurs when the query optimizer uses multiple indexes to satisfy a query. While it can be efficient for some queries, too many index intersections might indicate the need for a better composite index.

Temporal table indexing requires consideration of both current and history tables. Index historical columns based on query patterns, consider filtered indexes for active records, and maintain appropriate statistics for both tables.

Bitmap indexes use bit arrays to track row locations for specific values. They're efficient for low-cardinality columns and complex AND/OR operations but perform poorly with frequent updates. Common in data warehousing scenarios.

Use covering indexes for common report queries, consider indexed views, implement partitioning for large tables, and evaluate materialized views. Balance real-time needs against data freshness requirements.

GUID clustered keys can cause page splits and fragmentation due to random value insertion. This impacts performance through increased I/O and maintenance overhead. Consider sequential GUIDs or alternative key designs for better performance.

Use appropriate indexing for parent-child relationships, consider materialized path or nested sets models, implement covering indexes for common traversal patterns, and evaluate graph database features for complex hierarchies.

Full-text indexes for text search, filtered indexes for non-NULL values, and careful evaluation of included columns. Consider partial indexing strategies and impact on maintenance operations.

Monitor deadlocks using trace flags or extended events, analyze deadlock graphs, optimize transaction patterns, adjust isolation levels, and ensure consistent access order for resources. Consider index design impact on lock types.

Index key compression reduces storage space by eliminating redundant key values. It's beneficial for indexes with many duplicate values or long key values, but increases CPU usage. Evaluate compression benefits against performance impact.

Use appropriate indexes for join conditions, consider batch processing, implement proper transaction handling, and evaluate MERGE statement alternatives. Monitor lock escalation and consider impact on existing indexes.

Align indexes with partition scheme, consider local vs. global indexes, implement filtered indexes for partition elimination, and maintain statistics at the partition level. Balance maintenance overhead against query performance needs.

Implement proper parameter handling, consider filtered indexes for common conditions, use dynamic SQL carefully, and evaluate index impact of different search patterns. Consider using OPTION (RECOMPILE) for highly variable queries.

Include date/time columns in appropriate index position, consider partitioning for historical data, implement sliding window maintenance, and evaluate impact of timezone handling on query performance.

Consider elastic resources, monitor DTU/vCore usage, implement appropriate scaling strategies, evaluate cost-based optimization, and understand cloud-specific indexing limitations. Balance performance against cloud resource costs.