Ways to Improve Stored Procedures

There are multiple ways to improve the SP few of them without doing any code modification are

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.