Database Reference
In-Depth Information
From the Extended Events output, you can see that the existing plan is found in the procedure cache. On
searching the cache, SQL Server finds the executable plan for the stored procedure
BasicSalesInfo
causing an
sp_cache_hit
event. Once the existing execution plan is found, SQL reuses the plan to execute the stored procedure.
One interesting note: The
sp_cache_miss
event just prior is for the SQL batch calling the procedure. Because of the
change to the parameter value, that statement was not found in the cache, but the procedure's execution plan was.
This apparently “extra” cache miss event can cause confusion.
These other aspects of stored procedures are worth considering:
•
Stored procedures are compiled on first execution.
•
Stored procedures have other performance benefits, such as reducing network traffic.
•
Stored procedures have additional benefits, such as the isolation of the data.
Stored Procedures Are Compiled on First Execution
The execution plan of a stored procedure is generated when it is executed the first time. When the stored procedure
is created, it is only parsed and saved in the database. No normalization and optimization processes are performed
during the stored procedure creation. This allows a stored procedure to be created before creating all the objects
accessed by the stored procedure. For example, you can create the following stored procedure, even when table
NotHere
referred to in the stored procedure does not exist:
IF (SELECT OBJECT_ID('dbo.MyNewProc')
) IS NOT NULL
DROP PROCEDURE dbo.MyNewProc
GO
CREATE PROCEDURE dbo.MyNewProc
AS
SELECT MyID
FROM dbo.NotHere; --Table no_tl doesn't exist
The stored procedure will be created successfully since the normalization process to bind the referred object to
the query tree (generated by the command parser during the stored procedure execution) is not performed during the
stored procedure creation. The stored procedure will report the error when it is first executed (if table
NotHere
is not
created by then) since the stored procedure is compiled the first time it is executed.
Other Performance Benefits of Stored Procedures
Besides improving the performance through execution plan reusability, stored procedures provide the following
performance benefits:
•
Business logic is close to the data
: The parts of the business logic that perform extensive
operations on data stored in the database should be put in stored procedures since SQL
Server's engine is extremely powerful for relational and set theory operations.
•
Network traffic is reduced
: The database application, across the network, sends just the name
of the stored procedure and the parameter values. Only the processed result set is returned to
the application. The intermediate data doesn't need to be passed back and forth between the
application and the database.
