Database Reference
In-Depth Information
Note that an SP:CacheMiss event is fired before SQL Server tries to locate the plan for the stored procedure in
the procedure cache. The SP:CacheMiss event is caused by SQL Server looking in the master database for the stored
procedure, even though the execution of the stored procedure is properly qualified with the user database name.
This aspect of the sp_ prefix becomes more interesting when you create a stored procedure with the name of an
existing system stored procedure.
CREATE PROC sp_addmessage @param1 NVARCHAR(25)
AS
PRINT '@param1 = ' + @param1 ;
GO
EXEC AdventureWorks2012.dbo.[sp_addmessage] 'AdventureWorks';
The execution of this user-defined stored procedure causes the execution of the system stored procedure
sp_addmessage from the master database instead, as you can see in Figure 19-13 .
Figure 19-13. Execution result for stored procedure showing the effect of the sp_ prefix on a stored procedure name
Unfortunately, it is not possible to execute this user-defined stored procedure. You can see now why you should
not prefix a user-defined stored procedure's name with sp_ . Use some other naming convention.
Reducing the Number of Network Round-Trips
Database applications often execute multiple queries to implement a database operation. Besides optimizing the
performance of the individual query, it is important that you optimize the performance of the batch. To reduce the
overhead of multiple network round-trips, consider the following techniques:
Execute multiple queries together.
SET NOCOUNT .
Use
Let's look at these techniques in a little more depth.
Execute Multiple Queries Together
It is preferable to submit all the queries of a set together as a batch or a stored procedure. Besides reducing the
network round-trips between the database application and the server, stored procedures also provide multiple
performance and administrative benefits, as described in Chapter 15. This means the code in the application needs to
be able to deal with multiple result sets. It also means your T-SQL code may need to deal with XML data or other large
sets of data, not single-row inserts or updates.
 
Search WWH ::




Custom Search