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:

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).