Tip: Always Qualify Stored Procedure References

Qualifying the owner of an object when you reference it is always a good idea, even when SQL Server's default name-resolution rules don't require the qualification. When you use SQL Server Profiler to generate the SP:CacheMiss event, as we did in the Q&A "Should I Use the sp_ Prefix for Procedure Names?" page 21, you'll see a slight performance gain when you fully qualify stored procedure names as you execute them. To create an example that illustrates the value of qualifying object owners, follow these steps:

1. Create a login called Test that's a user in tempdb.



2. Create the dbo.Select1 procedure that Listing 1, page 21, shows.



3. Run the command grant exec on Select1 to test to set the appropriate permissions.



4. Use the Test login to log in to the server on a new connection, and change your current database context to tempdb.



5. Be sure that the Profiler trace is running. (You create the trace by following the steps in "Should I Use the sp_Prefix for Procedure Names?")



6. Press Ctrl+Shift+Delete to clear all events in the Profiler window.



7. Run Select1 and notice the generation of an SP:CacheMiss event.



8. Run dbo.Select1 and notice that it didn't generate an SP:CacheMiss event.

Because the procedure doesn't generate an SP:CacheMiss event, SQL Server doesn't need to create the compile lock that "Should I use the sp_ Prefix for Procedure Names?" describes, so performance isn't degraded. For more information about qualifying stored procedure names, see the Microsoft articles "INF: Troubleshooting Stored Procedure Recompilation" (http://support.microsoft.com/support/kb/articles/q243/5/86.asp) and "INF: SQL Blocking Due to COMPILE Locks" (http://support.microsoft.com/support/kb/articles/q263/8/89.asp).