Parameter Sniffing

Sometimes maybe you find out your SP (Store Procedure) doesn't response in reasonable time despite Select statement, which you used in that SP, has good performance and result is shown you immediately. What's wrong with your SP?
There are different answers for that problem but when I faced on this problem, I realized it's strongly related to Parameter Sniffing.

You can solve Parameter Sniffing easily with follow this example:
If you have a SP such as GetOrderForCustomers that has Parameter Sniffing issue:

Create procedure GetOrderForCustomers (@CustID Varchar (20))
As
Begin
Select * from orders
Where customerid = @CustID
End

To solve problem you should change your SP similar:

Create procedure GetOrderForCustomers (@CustID Varchar (20))
As
Begin
Declare @LocCustID Varchar (20)
Set @LocCustID = @CustID
Select * from orders
Where customerid = @LocCustID
End

Good luck.