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:
- Create a login called Test that's a user in tempdb.
- Create the dbo.Select1 procedure that Listing 1, page 21, shows.
- Run the command grant exec on Select1 to test to set the appropriate permissions.
- Use the Test login to log in to the server on a new connection, and change your current database context to tempdb.
- 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?")
- Press Ctrl+Shift+Delete to clear all events in the Profiler window.
- Run Select1 and notice the generation of an SP:CacheMiss event.
- 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).