sql server stored proc times out and query does not time out, why?

Question

I have a stored proc on sql server 2008 that excepts a int parameter. It does call other stored procs and have nested quires.

the problem i am facing is when i run the procedure from SQL server management studio it does not execute and times out.

If i run the queries in side the stored proc separately in another SQL server management studio it just executes all fine.

I am not able to debug the issue. will appreciate any help/pointers to dig this deep.

( i am useing the same credential when executing the proc or the query )

Thanks in advance.

Answer 1

Could be a case of parameter sniffing.

Try assigning the sproc parameters to local variables and using those in the queries within the sproc instead.

e.g.

CREATE PROCEDURE [TestSproc]
    @Param1 INTEGER
AS
BEGIN
DECLARE @Param1_LOCAL INTEGER
SET @Param1_LOCAL = @Param1

SELECT Something
FROM Somewhere
WHERE SomeField = @Param1_LOCAL 
END