Should I Use the sp_ Prefix for Procedure Names?

  • Article

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

https://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html