There are two ways, which can be used to improve the performance of Stored Procedure (SP) without making T-SQL changes in SP.
- Do not prefix your Stored Procedure with sp_.
In SQL Server, all system SPs are prefixed with sp_. When any SP is called which begins sp_ it is looked into masters database first before it is looked into the database it is called in.
- Call your Stored Procedure prefixed with dbo.SPName – fully qualified name.
When SP are called prefixed with dbo. or database.dbo. it will prevent SQL Server from placing a COMPILE lock on the procedure. While SP executes it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.
Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL