What happens when SQL Server executes a stored procedure or query


Interview questions

SQL Server - use of the execution plan when SP is executed

Next>>         SQL Server tutorial  

SQL Server - use of the execution plan when SP is executed - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

Describe what happens when SQL Server executes a stored procedure or query.

SQL Server performs compilation and execution while executing a stored procedure or a query. If the execution plan for the stored procedure/query is not present in memory then SQL Server needs to compile the stored procedure/query before executing it.

The compilation is a 4 step process:

  • Parsing: Checks for syntax error and transforms it into a compiler ready structure.
  • Normalization: Checks for all references to objects in the stored procedure/query.
  • Compilation: Starts building the execution plan. A sequence tree is created followed by view definition is the stored procedure/query uses views, followed by creation of query graph.
  • Optimization: It comes up with the cheapest possible execution plans involving resources like memory, CPU etc.

SQL Server - use of the execution plan when SP is executed - May 05, 2009 at 22:00 PM by Rajmeet Ghai

Describe what happens when SQL Server executes a stored procedure or query.

When the SQL server needs to execute a query, it makes use of the execution plan available in the memory. If not, the query is compiled in 4 stages:

Parsing - Here, the query is checked for any syntactical errors. It does not check for any object or column names. This stage basically transforms query into a form that can be used for optimization.

Normalization - Here, references of all objects present in the query is checked. If some object is not found, “Object no found” error is returned.

Compilation - Here, the execution plan is prepared by starting with creation of a sequence tree. This tree is normalized by performing some implicit conversions if necessary. View definitions, if references are also placed. Query graphs may be prepared for DML statements. This query graph is used by the optimizer for creation of the execution plan which is then stored in the procedure cache for reuse.

Optimization - in this stage, the query optimizer optimizes the query by choosing the best and cheapest execution plan. The cheapest plan is chosen based on the least number of resources consumed by the plan. Joining queries, index usage helps determine the cheapest plan.

SQL Server - use of the execution plan when SP is executed - June 21, 2009 at 09:00 AM by Amit Satpute

Describe what happens when SQL Server executes a stored procedure or query.

Before the execution of a stored procedure or a query, the process of compilation takes place.

The SQL Server performs the following operations in the compilation process:

1. Parsing

  • Query is checked for syntax errors
  • It is then transformed into a complier-ready structure.

2. Normalization

  • References to objects in the query are checked.
  • Errors are thrown if the references are not found.
  • Checks are also performed related to the syntax and other rules
  • Optimization is performed on select, insert, and update statements.

3. Compilation

  • The execution plan for the query is built.
  • A sequence tree is created, normalized, added with implicit conversions.
  • For a query that references views, a view definition is placed in the query.
  • For a DML statement, a query graph object is created to generate an optimized plan for the query.
  • This is the compiled plan that is stored in the procedure cache for reuse.

4. Optimization

  • Cost-based Optimizer (Least usage of resources to get the desired output)
  • The best query execution plan is obtained by testing the indexes and join orders as the indexes help in the process of optimization.
  • It is important to determine the best path to be taken for the execution of a particular query as there can be multiple plans/paths.


Also read

What are the advantages of using Stored Procedures?

Answer - Stored procedures provide performance benefits through local storage, precompiling the code, and caching......

What are the differences among batches, stored procedures, and triggers?

Answer - A batch is a group of one or more SQL statements. SQL Server compiles the statements......

What security features are available for stored procedures?

Answer - Database users can have permission to execute a stored procedure without being......

Write your comment - Share Knowledge and Experience


Latest placement tests
Latest links
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring