Recompilation and Coding Owner Prefixes on a Stored Procedure

In June 2001, you mentioned that to avoid recompilations, thereby improving performance, you can "try coding the object owner for referenced tables, views, and procedures inside your stored procedures" when submitting a query (e.g., select col1 from dbo.table1). Does SQL Server recompile if two tables with the same name exist in the database (e.g., dbo.table1, fred.table1)? Further, does SQL Server recompile the stored procedure if only dbo.table1 exists in the database?

SQL Server recompiles a stored procedure or a cached query plan even if only one table1 exists because through the recompilation process, SQL Server checks the catalog for the appropriate object that the connection context issuing the query should use. When you don't qualify the owner name, SQL Server enters the compile code and acquires a COMPILE lock on the procedure. SQL Server eventually determines that a new plan isn't required, so at that point, SQL Server doesn't recompile the plan. However, when SQL Server takes the extra step of acquiring a COMPILE lock on the procedure, in situations of heavy load, blocking can occur. For more details about blocking contention, see the Microsoft article "INF: SQL Blocking Due to COMPILE Locks" at http://support.microsoft.com/default.aspx?scid=kb;en-us;q263889.

Qualifying the table or view and the columns you use is also good practice. Qualifying those entities ensures that the query will continue to work as you expect—even if the underlying tables are altered—because the code explicitly names the tables that hold the columns you need. Otherwise, adding mycol1 to Table2 would cause a namespace clash and break the query. The following code snippet qualifies a table and columns:

SELECT a.mycol1, b.othercol1FROM dbo.Table1       AS a INNER JOIN dbo.Table2       AS b ON a.myid=b.otherid