Thanks Jack, All the Sp optimizatinon tecchnique had been applied with NOCOUNT ON , imposing NOLOCK hint on tables.

Here I am giving the sample code for the SP

CREATE Procedure Proc_test(@p1...