Query Optimization & Plans
7 questions foundWhat is an execution plan in SQL Server, and how does it help you understand query performance?
Beginner An execution plan shows exactly how SQL Server intends to carry out a query, including which indexes it plans to use, how it will join tables together, and in what order operations will happen, giving you visibility into the actual strategy behind a query rather than just seeing its final results.
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Orders WHERE CustomerId = 123;
GO
Real-world example A developer views the execution plan for a slow running query and immediately spots a full table scan happening where an index seek should have been used, pointing directly to a missing index as the root cause.
Common follow-ups: What is the difference between an estimated and an actual execution plan?;How do you read an execution plan visually in SQL Server Management Studio?
Indexes;Query Store
What is the difference between a table scan and an index seek in an execution plan, and why does this distinction matter for performance?
Beginner A table scan means SQL Server reads through every single row in a table to find the ones it needs, which becomes slow on large tables, while an index seek means SQL Server uses an index to jump directly to the specific rows that match a query's condition, generally making it significantly faster, especially as table size grows.
-- Without an index, this causes a table scan
SELECT * FROM Orders WHERE CustomerId = 123;
-- With an index on CustomerId, this becomes an index seek
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);
Real-world example A reporting query that used to scan an entire million row orders table runs almost instantly after adding an appropriate index, changing the execution plan from a slow table scan to a fast index seek.
Common follow-ups: Is a table scan always a bad thing to see in an execution plan?;How do you know when adding an index will actually convert a scan into a seek?
Indexes;Data Types & Schema Design
How do outdated or missing statistics affect the query optimizer's ability to choose an efficient execution plan?
Intermediate SQL Server relies on statistics about the distribution of data within your tables to estimate how many rows a query will return and choose the most efficient plan accordingly, and if those statistics become outdated after significant data changes, the optimizer can make poor estimates, leading to inefficient plans, such as choosing the wrong join type or failing to use a helpful index.
UPDATE STATISTICS Orders;
-- Or check when statistics were last updated
SELECT name, STATS_DATE(object_id, stats_id) AS LastUpdated
FROM sys.stats WHERE object_id = OBJECT_ID('Orders');
Real-world example A team notices query performance suddenly degrading after a large data import, and updating the table's statistics immediately resolves the issue by giving the optimizer accurate information to choose a better execution plan.
Common follow-ups: How often does SQL Server automatically update statistics on its own?;What is the performance cost of manually updating statistics on a very large table?
Indexes;Query Store
What is parameter sniffing, and how can it sometimes lead to unexpectedly poor query performance?
Intermediate Parameter sniffing happens when SQL Server caches an execution plan based on the specific parameter values used the first time a query or stored procedure runs, and if a very different, less typical parameter value is used later, that same cached plan might be poorly suited for the new value, leading to unexpectedly slow performance for that particular execution.
-- A plan optimized for a common, small parameter value
-- might perform poorly when reused for a very different, large value
CREATE PROCEDURE GetOrdersByCustomer @CustomerId INT
AS
SELECT * FROM Orders WHERE CustomerId = @CustomerId;
Real-world example A stored procedure that runs quickly for most customers suddenly performs terribly for one specific large customer, and the team discovers this is caused by parameter sniffing, where the cached plan was optimized for a smaller, more typical customer's data volume.
Common follow-ups: What are some common techniques for mitigating parameter sniffing issues?;How do you force a stored procedure to recompile its execution plan?
Stored Procedures & Functions;Query Store
How would you use query hints to influence the optimizer's behavior when you have determined the default execution plan is genuinely suboptimal?
Advanced You can add specific query hints, such as forcing a particular join type, a specific index, or a maximum degree of parallelism, directly in your query using the OPTION clause, though this should be done carefully and only after confirming through testing that the hint genuinely produces better performance, since hints can also prevent the optimizer from adapting to future changes in your data.
SELECT * FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId
OPTION (HASH JOIN);
Real-world example A database administrator forces a specific join type using a query hint after confirming through careful testing that the optimizer's default choice was consistently less efficient for this particular, unusually shaped query.
Common follow-ups: What are the risks of relying too heavily on query hints?;How do you know when a query hint is genuinely still necessary after data or indexes have changed?
Joins;Indexes
How do you use the SQL Server Query Store feature to identify and address query performance regressions over time?
Advanced Query Store automatically captures a history of execution plans and their performance metrics over time, letting you compare how a specific query has performed across different plans and time periods, quickly spot when a regression occurred, such as after a deployment, and even force SQL Server to use a previously known good execution plan if the current one has become a performance problem.
ALTER DATABASE MyDatabase SET QUERY_STORE = ON;
-- Later, identify regressed queries
SELECT * FROM sys.query_store_query_text
WHERE query_text_id IN (SELECT query_text_id FROM sys.query_store_query);
Real-world example A team uses Query Store to quickly identify that a specific report's performance regressed right after a recent deployment, and forces SQL Server back to the previously known good execution plan while they investigate the actual root cause.
Common follow-ups: How do you force a specific historical plan using Query Store?;What is the storage overhead of enabling Query Store on a busy database?
Query Store;Stored Procedures & Functions
What are the most common causes of poor query performance that you should check first when troubleshooting a slow query?
Intermediate Common causes include missing or unused indexes on columns used in WHERE clauses and joins, outdated statistics leading to poor row estimates, functions applied directly to columns in a WHERE clause which prevent index usage, implicit data type conversions caused by mismatched column types, and queries that return far more columns or rows than are actually needed by the application.
-- A function on the column prevents index usage
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026;
-- Rewritten to allow index usage
SELECT * FROM Orders WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';
Real-world example A developer rewrites a slow query that was applying a function directly to a date column, allowing SQL Server to use an existing index on that column and dramatically improving the query's performance.
Common follow-ups: Why does applying a function to a column in a WHERE clause prevent index usage?;What tools help quickly identify these common performance mistakes in existing queries?
Indexes;Data Types & Schema Design