Stored Procedure and Trigger Execution

Microsoft® SQL Server™ 2000 stores only the source for stored procedures and triggers. When a stored procedure or trigger is first executed, the source is compiled into an execution plan. If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. If the plan has aged out of memory, a new plan is built. This process is similar to the process SQL Server 2000 follows for all SQL statements. The main performance advantage that stored procedures and triggers have in SQL Server 2000 is that their SQL statements are always the same; therefore, the relational engine matches them with any existing execution plans.

Stored procedures had a more pronounced performance advantage over other SQL statements in earlier versions of SQL Server. Earlier versions of SQL Server did not attempt to reuse execution plans for batches that were not stored procedures or triggers. The only way to reuse execution plans was to encode the SQL statements in stored procedures.

The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. This allows for greater reuse of the stored procedure and trigger execution plans. For more information, see Batch Processing.