Friday, July 11, 2008
Should I Use the sp_ Prefix for Procedure Names?
A little gem: SQL Server gives name-resolution preference to the master database for procedures that have the sp_ prefix. SQL Server looks for a compiled plan for the procedure associated with the master database and doesn't find it because, in this case, the sp_Select1 procedure exists in tempdb. SQL Server assumes the procedure isn't in cache (and thus must be recompiled) and acquires an exclusive compile lock on the stored procedure for a short time. However, the short time that the lock exists is enough to cause performance problems...
Using a prefix like usp_ should circumvent this quite nicely. Apart from that, having a naming convention (or nomenclature) in place is a great big ol' must have!
Subscribe to Posts [Atom]