Editor's Note: Send your SQL Server questions to Richard Waymire, Microsoft's group program manager for SQL Server management tools, at questions@sqlmag.com.
A sentence in your June 2001 column caught my eye: "Recompilations might be the source of the slower stored procedure speed." You recommend coding the owner prefix on all reference tables, views, and procedures. Should I incorporate this step into my coding standards to avoid suffering a performance problem? Can you give me more information about the circumstances under which recompilation occurs?
Yes, you should always code the owner prefix if you know it. Let's consider an example of why coding the owner prefix can improve query performance. When user Fred calls stored procedure dbo.foo, and dbo.foo executes the query
SELECT col1 FROM table1
the query optimizer must decide whether to retrieve fred.table1 or dbo.table1. Then, when user Mark calls the same stored procedure, the query optimizer must recompile the query plan to determine whether the user wants mark.table1 or dbo.table1. If you code the SELECT statement as
SELECT col1 FROM dbo.table1
the query optimizer encounters no ambiguity and avoids recompilation.