Update take 1 hours to complete

emamuthu 2013-02-14 19:34:06

Same update on other machinces take less than 1 min to complete. This one take like 50mins to complete. Not sure what happening.

SQLkiwi 2013-02-14 21:22:26
The uploaded plan only shows estimates. To determine the reasons for the poor performance you are seeing, we would ideally have plans with execution information – use the Get Actual Plan feature to do this.

Also, an ideal session upload would include two examples: one where the query runs quickly, and one where it runs slowly. Even better if you have the Pro version of Plan Explorer which includes useful things like wait statistics.

I'll come back to this question later – if it's not possible to provide the information above, I'll make some general observations based on the estimated plan.

emamuthu 2013-02-14 21:44:31
whenever i run the update maunally it runs faster..but through sp is takes 1hours.
Anyhow i am uploading actual plan.
GregGonzalez 2013-02-14 22:04:30
A .sqlplan file leaves out some important details that can be helpful in determining exactly why it's running slowly, such as duration, CPU, table IO, etc. As Paul indicated, ideally you'll want to get two actual plans (both slow and fast) directly through Plan Explorer and save as .queryanalysis files.
SQLkiwi 2013-02-14 23:04:47
There doesn't seem too much desperately wrong with that plan (though there is always room for improvement).

You don't say whether that execution ran quickly or slowly, which is quite an important piece of information.

I do wonder if the stored procedure has the OPTION (RECOMPILE) hint, and also what SET options it is stored with.

Realistically, though, the fastest way to resolve this question requires a slow and a fast analysis session file produced as Greg has described.

SQLkiwi 2013-02-17 03:41:40
I promised you a reply even if you were unable to supply the additional information, so here goes. Bear in mind that these are only general observations, and the particular cause of the slowness in your case might be due to a factor that is not listed here.

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.

Memory Grant

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.

Thread Resources

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.

Cached Plans

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');

Slow I/O

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.

Paul

emamuthu 2013-02-17 14:14:31
Thanks Paul for your detailed & great explanation. This week we applied SP3 & CU8. SP is working as expected but this the first run after we applied SP3 & CU8. I will monitor & get back if I need anything else.

Note:
We have all recommended SET option on the sp as you said. Not sure if the update runs slower due to the lock bug mentioned in CU8.