SQL Server timeout in Profiler but executes ok in SSMS

Recently, I had a problem where I profiled a query which was timing out in my web application, but ran in under a second when I executed the same query in SQL Server Management Studio.

I found that forcing SQL Server to recompile the execution plan at runtime resolved the issue for the web application.

Here's a snippet of a CREATE PROCEDURE script that uses the WITH RECOMPILE option


CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
WITH RECOMPILE
AS
BEGIN