When you run a query or call a stored procedure in SQL Server, a lot happens under the hood. We'll break down how SQL Server processes both, with a unique visual model.
The 4 Main Steps Every Query Goes Through
Whether it's an Normal SQL query or a stored procedure, SQL Server follows the same general flow.
- Parsing: SQL Server checks for typos and turns your query into a tree structure it can understand.
- Algebrizing: It links your query to actual tables and columns, and checks for things like permissions.
- Optimization: SQL Server builds a plan for how it thinks the query should run choosing indexes, join types, etc.
- Execution: It runs the plan and returns the results.
The Plan Cache: SQL Server’s Memory Trick
To save time, SQL Server stores execution plans in memory (called the plan cache). If a query or procedure has already been run, SQL Server can reuse that plan instead of making a new one.
- Raw SQL Query: SQL Server saves each unique query. Even small changes (like a space or value) create a new plan.
- Stored procedures: These are compiled once and reused, so they’re better at plan reuse.
Inside the SQL Server Engine
Let’s break down the key parts involved in running queries.
- Plan Cache: Stores execution plans.
- Buffer Pool: Stores data pages from disk in memory so SQL Server can access them quickly.
- Execution Engine: The part that actually runs the query plan and processes the data.
Normal SQL query and stored procedures both use all these pieces, but stored procedures often make better use of caching.
Examples
Raw SQL Query.
SELECT * FROM Orders WHERE CustomerID = 'ALFKI';
Equivalent Stored Procedure.
CREATE PROCEDURE sp_GetOrdersByCustomer
@CustomerID NVARCHAR(5)
AS
BEGIN
SELECT * FROM Orders WHERE CustomerID = @CustomerID;
END;
Running this many times with different values.
- The Normal SQL query version creates multiple plans.
- The stored procedure reuses the same plan with different values.
Side-by-Side Execution Flow
![Execution Process]()
Note. Stored procedures skip parsing, name resolution, and optimization after the first execution unless schema changes or recompilation triggers.
Key Differences
| Step |
Raw SQL Query |
Stored Procedure |
| Parsing |
Every time |
Once |
| Algebrizing (name resolution) |
Every time |
Once |
| Optimization |
Every time |
Once (plan is cached) |
| Plan Cache Usage |
Optional (based on parameterization) |
Always cached |
| Security |
Less secure (SQL injection risk) |
More secure |
| Performance |
Slower for repeated queries |
Faster due to plan reuse |
Conclusion
| Scenario |
Best Approach |
| Dynamic filters or one-off queries |
Raw SQL Query |
| Repeated logic, reporting, automation |
Stored Procedure |
| Need security, performance, versioning |
Stored Procedure |
Normal SQL queries and stored procedures both help us get data from the database, but they work differently behind the scenes.
A normal query goes through parsing, checking, and planning every time it's run. A stored procedure does this only once and saves the plan, which makes it faster when used again.
Stored procedures are better when you need to run the same logic many times or want more control and security. Knowing how both are processed helps you write better, faster, and safer SQL code.