 |
|
SQL Server Tips by Burleson |
Execution of Extended Stored
Procedures
XP’s calls and interaction with TSQL are exactly like SP’s. On the
surface, their usage is identical but they have a very different
nature. Internally there is a “compilation” of SP’s, called plan
creation, which is the process of finding the optimal plan from a
statistical analysis of the data distribution. This plan resides in
the Master database, in the system table syscacheobjects. Triggers,
UDF’s and views also have their execution plans in that table and
the plan creation works exactly the same way for all these objects.
The query optimizer is the part of SQL Server responsible for
optimizing the execution plans. The query optimizer requests the
execution plans for use, reuse or recycling.
SP’s help reduce traffic and latency when compared with client query
submissions because of the code reuse and query optimization. XP’s
have that plus extremely fast execution not only because of the
compiled code but also because they do not need to follow some of
the steps that precede the query optimizer. Those steps are the
Parsing Process and the Standardization Process. It is possible to
have very simple SP’s causing performance degradation because of a
poor execution plan or frequent recompilation and an XP could solve
the problem.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
