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

T-SQL to list processes and their running sql

select sp.spid, sp.status, sp.blocked, sp.hostname, db_name(sp.dbid), object_name(objectid), qt.text
from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) qt
where sp.status <> 'sleeping'

Credits to Phil Hendry