Multi instance Job gets Deadlocked on windows platform
Posted: Fri Mar 16, 2007 8:58 am
We have a Multi instance Multi invocation ID Server job wich runs on SQL server 2005, most of the time following SQL gets dead locked. This is a select statement with a LOCK. I have included the SQL server trace. if I execute couple of following SQL select statement through SQL server Managment Studio it runs fine. Any budy has any idea why?
SQL:
SELECT SITE, ltrim(rtrim(cast(customer_no as varchar)))+'~'+ltrim(rtrim(cast(item_div as varchar))), current_salesperson_SK, EFFECTIVE_FROM_DT FROM #CatalogSrc#."dbo".#DTbl# "(nolock)" WHERE SITE = #DSJobInvocationId# order by customer_no desc ,item_div,salesperson_sk,EFFECTIVE_FROM_DT;
SQL SERVER Trace :
StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [CURRENT_IND] AS [SC0] FROM [dbo].[D_CURRENT_SALESPERSON] WITH (READUNCOMMITTED,SAMPLE 2.044260e+000 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
Thanks
SQL:
SELECT SITE, ltrim(rtrim(cast(customer_no as varchar)))+'~'+ltrim(rtrim(cast(item_div as varchar))), current_salesperson_SK, EFFECTIVE_FROM_DT FROM #CatalogSrc#."dbo".#DTbl# "(nolock)" WHERE SITE = #DSJobInvocationId# order by customer_no desc ,item_div,salesperson_sk,EFFECTIVE_FROM_DT;
SQL SERVER Trace :
StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [CURRENT_IND] AS [SC0] FROM [dbo].[D_CURRENT_SALESPERSON] WITH (READUNCOMMITTED,SAMPLE 2.044260e+000 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
Thanks