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

Should I Use the sp_ Prefix for Procedure Names?

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!

Labels: ,

Comments: Post a Comment

Links to this post:

Create a Link

<< Home

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]