sql server stored proc times out and query does not time out, why?
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 s开发者_Go百科eparately 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.
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
精彩评论