SQL Server Execution Plan: A Hands-On Introduction
Umang Ahuja
Lead Engineer at Arcesium | My Newsletter - Joy Of Engineering | YouTuber @getsetpython
If you’ve worked with SQL in real-world applications, chances are you’ve encountered slow-running queries or overheard senior developers discussing query optimizations. But how do you approach such issues systematically?
To optimize a query, you first need to understand how SQL Server executes it—and that’s where execution plans come in.
Presenting: SQL Server Execution Plan
It’s simple, powerful, and incredibly insightful. Once you get the hang of it, you’ll start appreciating the inner workings of databases and how small changes can lead to big performance improvements.
Don’t worry if you’ve never used execution plans before or have only vaguely heard of them. By the end of this article, you’ll have a solid foundation to analyze and debug query performance like a pro. ??
What is an Execution Plan?
Think about how SQL works.
You write a query, hit Execute, and voilà! You get the results.
But what happens behind the scenes?
SQL Server doesn’t magically return your data—it figures out the most efficient way to retrieve it. This includes:
An execution plan is a step-by-step breakdown of how SQL Server executes your query.
It provides a visual representation of the exact operations performed, along with their costs in terms of time and memory.
Why Should You Care?
When you can see how SQL Server processes your query, debugging slow queries becomes data-driven instead of guesswork. No more tweaking random parts blindly—execution plans provide insights that guide real optimizations.
They help you:
? Identify slow operations
? Compare query costs before and after modifications
? Optimize queries with confidence
Now that you know what an execution plan is, let’s dive into real examples of slow queries and how to optimize them using execution plans.
Hands-on Practice with Execution Plans
For this example, you'll need an SQL Server connection. I assume you already have access to one—I'm personally running it on my localhost for free.
Most SQL clients, like SQL Server Management Studio (SSMS) and Azure Data Studio, don’t display execution plans by default when executing queries. So, we need to enable them explicitly to see the actual execution plan alongside our query results.
Enabling the Actual Execution Plan
In SQL Server Management Studio (SSMS):
1?? Connect to your database.
2?? Click on Query in the navigation bar.
3?? Select "Include Actual Execution Plan".
4?? Now, when you run a query, the execution plan will be displayed in a separate tab.
In Azure Data Studio:
1?? Open your SQL query editor.
2?? Look for the "Execution Plan" option right beside the editor.
3?? Check the box, and you're all set!
Now that we have execution plans enabled, let’s run some queries and analyze their plans!
Creating Sample Tables and Populating Data
First, let’s create a simple employee table:
CREATE TABLE employee
(
id INT,
name NVARCHAR(256)
)
Now, let’s insert some sample data:
INSERT INTO learning.dbo.employee (id, name) VALUES(1, N'Employee 1');
INSERT INTO learning.dbo.employee (id, name) VALUES(2, N'Employee 2');
INSERT INTO learning.dbo.employee (id, name) VALUES(3, N'Employee 3');
INSERT INTO learning.dbo.employee (id, name) VALUES(4, N'Employee 4');
INSERT INTO learning.dbo.employee (id, name) VALUES(5, N'Employee 5');
INSERT INTO learning.dbo.employee (id, name) VALUES(6, N'Employee 6');
INSERT INTO learning.dbo.employee (id, name) VALUES(7, N'Employee 7');
INSERT INTO learning.dbo.employee (id, name) VALUES(8, N'Employee 8');
INSERT INTO learning.dbo.employee (id, name) VALUES(9, N'Employee 9');
INSERT INTO learning.dbo.employee (id, name) VALUES(10, N'Employee 10');
Now that we have our data ready, let’s run a simple SELECT query while the Actual Execution Plan is enabled.
When you execute:
SELECT * FROM learning.dbo.employee;
Along with the results, you’ll also see the execution plan, which will help us analyze how SQL Server processes this query.
If you click on each element, it will show more details
Now, this might seem like a lot of information, but let’s focus on some key statistics that will help you analyze execution plans effectively.
Estimated vs. Actual Number of Rows
?? Estimated Rows → The number of rows SQL Server expects to process based on its internal estimates.
?? Actual Rows → The number of rows SQL Server actually processed when executing the query.
Why Does This Matter?
?? In simple queries, the difference is usually small. But in complex queries with joins and filters, if the estimated vs. actual row count differs significantly, it means:
This is one of the first things you should check when debugging slow queries!
Operator cost
Every step in the execution plan comes with a cost percentage, which represents how much that operation contributes to the total query cost.
?? In simple queries with just one operation, you’ll often see 100% cost assigned to that step.
Let’s take a more complex query and see how costs get distributed:
select * from learning.dbo.employee e1
join learning.dbo.employee e2
on e1.id < e2.id
Now, when you check the execution plan, you’ll notice different cost splits like:
?? 42% → One step of the execution
?? 52% → Another significant step
?? 6% → A minor operation
Why Does This Matter?
?? These percentages highlight the most expensive steps in your query execution.
?? The higher the percentage, the bigger the bottleneck—this is where optimizations will have the most impact.
Next time your query is slow, don’t guess—let the execution plan tell you where the problem is!
Memory Grant
When executing a query, SQL Server predicts the amount of memory it will need based on:
? Query complexity
? Expected data volume
However, sometimes the actual memory usage can be way higher or lower than the estimate.
Why Does This Matter?
?? Overestimated Memory Grant → SQL Server reserves too much memory, leading to resource contention. Other queries may wait unnecessarily, slowing down performance.
?? Underestimated Memory Grant → If SQL Server predicts too little memory, it might spill over to disk, causing performance bottlenecks.
Understanding memory grants helps you spot inefficiencies and avoid unnecessary slowdowns due to memory misallocation. ??
Warnings ??
Sometimes, you’ll see yellow warning signs in an execution plan. These highlight potential issues such as:
?? Implicit conversions (data type mismatches affecting performance)
?? Cardinality estimation errors (SQL Server making incorrect row predictions)
These warnings nudge you in the right direction to investigate and optimize queries effectively.
Final Thoughts & Bonus Tip
Execution plans offer a wealth of insights, and we’ve only scratched the surface today! There’s so much more to explore, but we’ll save that for another day.
?? Bonus Tip:
One of the simplest yet most impactful optimizations in large tables is adding indexes—a technique that can dramatically improve query speed!
Below signing off, let’s explore a live example of the same.
Execution plan in action
To truly see the execution plan in action, let’s work with a larger dataset. I’ve added 1 million rows to the employee table because query slowness becomes noticeable at scale.
Now, let’s run a simple query:
select * from learning.dbo.employee
where id = 100
At first glance, the query completes quickly—after all, it’s only retrieving one row. But when we check the execution plan, we get a warning ??
"Table does not have an index."
Understanding the Table Scan Operation
Since the table has no index, SQL Server has no choice but to scan all 1 million rows to find the one matching id = 100. This operation is called a Table Scan, as seen in the execution plan.
Key Observation:
?? Rows Read: 1 million
?? Actual Output: 1 row
Even though the query only returns one row, SQL Server had to scan the entire table, making it inefficient.
We've seen how a Table Scan can slow down queries on large datasets. But what if we could avoid scanning the entire table and directly jump to the required row?
How Does That Work?
If we understand how our tables are used—especially the columns frequently used in WHERE, JOIN, or GROUP BY clauses—we can create indexes.
Instead of scanning all rows, an index efficiently looks up the required data—just like how a HashMap lookup is faster than searching through a list in programming.
Applying an Index
Let’s create an index on the id column to optimize our previous query:
CREATE NONCLUSTERED INDEX idx_id
ON dbo.employee (id)
Observing the New Execution Plan
With the index in place, the execution plan changes significantly:
?? Before (Without Index): Table Scan → Reads 1 million rows
?? After (With Index): Index Seek → Reads only 1 row
?? Index Seek means SQL Server directly jumped to the required row, making the operation 100% efficient compared to scanning the entire table.
Wrapping Up
Today, we explored the fundamentals of SQL Server Execution Plans and saw firsthand how they can simplify debugging and optimization.
By understanding how SQL Server processes queries, you gain the power to write efficient, well-optimized queries instead of relying on guesswork. Beyond just fixing slow queries, this knowledge shapes your approach to database design and helps you avoid common pitfalls.
Now that you have this tool at your disposal, make it a habit to check the execution plan before deploying any new query. Every query tells a story—and the execution plan helps you read between the lines.
Who knows what new insights you’ll uncover next? ??
If you enjoyed this blog, leave a like ??, share your thoughts in the comments ??, and don’t forget to follow for more great content! ????
Until next time, keep the Joy of Engineering alive within you! ???
#SoftwareEngineering #Design #Java #JoyOfEngineering