SQL Server Interview Questions: Performance Optimization

Analyze an Execution Plan

Obtain the actual execution plan for a slow query and identify at least two performance bottlenecks.

Expected output: A list of bottlenecks with explanations and suggestions for improvement.

Hint: Look for table scans, expensive operators, and missing index warnings.

Create and Use an Index

Create a non-clustered index on a frequently queried column and demonstrate the performance improvement with and without the index.

Expected output: Evidence of reduced logical reads and faster query execution after indexing.

Hint: Use SET STATISTICS IO ON to compare logical reads.

A clustered index defines the physical order of data in a table, meaning the table data is stored in the order of the clustered index key. A non-clustered index is a separate structure that points to the data rows and does not affect the physical order.

You can identify slow queries by using SQL Server Profiler, Extended Events, or querying Dynamic Management Views like sys.dm_exec_query_stats to find queries with high execution time or resource usage.

An execution plan shows how SQL Server executes a query, detailing the operations and their order. It is important because it helps identify inefficient operations like table scans or missing indexes that cause poor performance.

You can improve performance by rewriting queries for efficiency, such as avoiding SELECT *, using proper joins, filtering early, and ensuring predicates are SARGable to leverage existing indexes.