Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter