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
Answer - Stored procedures provide performance benefits through
local storage, precompiling the code, and caching......
Answer - A batch is a group of one or more SQL statements. SQL
Server compiles the statements......
Answer - Database users can have permission to execute a stored
procedure without being......
|
