Performance Tuning Stored Procedures

Naming Conventions
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
The stored procedure in the master database.
The stored procedure based on any qualifiers provided (database name or owner).
The stored procedure using dbo as the owner, if one is not specified.
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name. Important: If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.
Schema prefixing
Always reference your database objects using the schema name as a prefix. For the same reason as in the Naming Conventions tip, when looking for a database object for the first time, SQL Server will look in a specific order. But by prefixing the database object in question, it can find it straight away.
e.g.
Select name from tableA where Id = 2
Becomes,
Select name from dbo.tableA where Id = 2
Using database functions become,
Select dbo._fn_GetName(@ID, dbo.tableA)
Using joins become,
Select Id, Name, Address From dbo.Customer Left Outer Join dbo.Address on Customer.Id = Address.Id