Update take 1 hours to complete
Apply SQL Server 2008 Service Pack 3
I notice you are running SQL Server 2008 Service Pack 2 (released September 2010). I recommend you apply Service Pack 3 (released October 2011). There have been nine Cumulative Updates issued since Service Pack 3, which you should also check to see if any fixes there apply to you. These updates may not fix the issue you are reporting here, but it would be remiss of me not to mention it.
The query plan you uploaded requires a memory grant of 1.6GB. If this is not available at the time the plan starts executing, the query may wait in a queue for the memory it needs before it can start executing.
It is quite possible the delay you see is caused by this issue. It is worth knowing that SQL Server 2012 reports much more detail about memory grants than SQL Server 2008 does. You may see extra information about these waits if you run the query directly from Plan Explorer PRO; an alternative is to check the sys.dm_exec_query_memory_grants DMV while the query is executing.
The query plan also includes a number of parallel branches. Each branch that may execute concurrently requires four worker threads (at DOP 4). It seems your query plan might have eight or so such concurrently-executing branches, needing 32 threads. It is possible the query is waiting to reserve enough worker threads to begin, or possibly to acquire extra threads that turn out to be required at runtime. This is less likely, given the delays quoted, but it is worth checking the sys.dm_os_wait_stats DMV during execution for waits like THREADPOOL or RESOURCE_SEMAPHORE_MUTEX.
It may also be that the stored procedure is generating a different execution plan from the one you captured using SSMS. One possible cause of this is a difference in SET options. Your query does not appear to access any indexed views, indexes on computed columns…or any of the many features that now require specific SET options to work. Nevertheless, SET options can still affect the execution plan, so you should check your stored procedure runs with the following recommended settings:
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT OFF;
The ANSI_NULLS and QUOTED_IDENTIFIER options are stored with the procedure and override any connection-level settings. Script the procedure to check these are both set ON. You can check the other settings using the SQL Server Profiler event Existing Connection – the SET options are in the Text Data column.
You can also use the following query to see the SET values for plan cache entries associated with the procedure:
SELECT deqp.query_plan, [ANSI_NULLS] = CASE WHEN CONVERT(integer, depa.[value]) & 32 = 32 THEN 'ON' ELSE 'OFF' END, [ANSI_PADDING] = CASE WHEN CONVERT(integer, depa.[value]) & 1 = 1 THEN 'ON' ELSE 'OFF' END, [ANSI_WARNINGS] = CASE WHEN CONVERT(integer, depa.[value]) & 16 = 16 THEN 'ON' ELSE 'OFF' END, [ARITHABORT] = CASE WHEN CONVERT(integer, depa.[value]) & 4096 = 4096 THEN 'ON' ELSE 'OFF' END, [CONCAT_NULL_YIELDS_NULL] = CASE WHEN CONVERT(integer, depa.[value]) & 8 = 8 THEN 'ON' ELSE 'OFF' END, [QUOTED_IDENTIFIER] = CASE WHEN CONVERT(integer, depa.[value]) & 64 = 64 THEN 'ON' ELSE 'OFF' END, [NUMERIC_ROUNDABORT] = CASE WHEN CONVERT(integer, depa.[value]) & 8192 = 8192 THEN 'ON' ELSE 'OFF' END FROM sys.dm_exec_procedure_stats AS deps CROSS APPLY sys.dm_exec_plan_attributes(deps.plan_handle) AS depa CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp WHERE depa.attribute = N'set_options' AND deps.[object_id] = OBJECT_ID(N'dbo.YourProcedureName', N'P');
It is also possible the query that runs for an hour does so because the particular machine is under extreme I/O pressure. This is much less likely to be the cause if the SSMS query and stored procedure tests were run on the same machine – it is unclear from your question exactly what the arrangement was here.