Calling a common Stored Procedure from multiple stored procedure performance issues

Question

Hi,

A common procedure is called inside the  multiple stored procedures based on different parameter values.Some times it gets dead slow.

Could you please provide the best solution for that.

Example:

-> Create procedure SPCommon

{

Here only select statement.

}

-> Create procedure TestA

{

here also select statements

exec SPCommon

}

->

-> Create procedure TestB

{

Here also select statements

exec SPCommon

}

Cheers,

Answer 1

Moved because this isn't a database design question, but the problem you mention sounds (without any sort of other evidence at this point) like a parameter sniffing issue. Basically, when it compiles a plan, it does it with a parameter value optimal for the majority of values that you might use.

It caches the sub optimal plan, and then every user suffers.  Here is an article written by a respected colleague over at Simple-Talk on the subject: https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/ for more detail.

Others may chime in, and one method of fixing this is to use the WITH RECOMPILE procedure option, and another may be to use OPTIMIZE for on the offending slow statement that gets the bad plan if you can narrow it down.

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

Answer 2

Thanks for valuable information.

Answer 3

Have you implemented any transaction isolation level within the SPs.? Response might be slow due to locking.

Try using WITH(NOLOCK) hint with the any big table that is queried in the common SP.

Remember, NOLOCK hint is equalent to READUNCOMMITED.

You can use this as;

Select col1, col2 
from myTable1 WITH (NOLOCK)
Inner Join myTable2
where col1 = 123

Here, I am assuming myTable1 is a big table that is involving in Locking.

---

Please visit my Blog for some easy and often used t-sql scripts
My BizCard