May 12, 2010 at 2:25 am
My team encountered one scenario, we created a SQL job via sql server agent to load data.
And it is normal if we run the job separately. It includes many scripts. We wants to merge the jobs.
Such as one is for April 2010, the other is for May 2010.
We see the first step for April 2010 is normal. but hangs on one UPDATE May 2010. The UPDATE is one script of the job and the two steps' scripts are all the same, only parameters different.
Actions we already done.
1. First find the CXPACKET waittype on the UPDATE, so we reduce the parallelism via option(maxdop 1). But no useful.
2. We find no wait type on the UPDATE session. that says the session is running, right?
3. Check the sys.dm_tran_locks, request_status is grant, no wait and covert
4. Check IO status, no IO pending
5. Normally the UPDATE should complte in 10 minutes, but now it lasts more then 10 hours, Why the UPDATE hangs up?
I think threads switch may be the point, but i am not sure.
Server: DELL PowerEdge M713
Processor: Intel(R) Xeon(R) CPU E5520 @ 2.27 GHz(16 CPUs)
Memory:32G
Could you help to give me some advice or clue on the case?
May 12, 2010 at 3:17 am
And we use the SQL Server 2005 Enterprise Edition with(sp3)
May 12, 2010 at 3:24 am
Can you post the UPDATE statement that it is hanging on ?
Sounds like a bad query plan....
Please also see this http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2010 at 3:28 am
The UPDATE statement is the following, the UPDATE only last about 5 minutes first time.
And we already check the index and it's ok.
UPDATE WC_PERSON_F_M_CN
SET SALE_REVENUE_BLYEAR = TMP.SALE_REVENUE_BLYEAR ,
SALE_TRANS_BLYEAR = TMP.SALE_TRANS_BLYEAR
FROM (
SELECT A.MONTH_WID AS MONTH_WID ,
W_PERSON_D.ROW_WID AS PERSON_WID ,
W_ORG_D.INTEGRATION_ID AS VIS_PR_BU_ID ,
SUM(WC_ORDERITEM_F_M_CN.U_NET_PRI * WC_ORDERITEM_F_M_CN.QTY_REQ) AS SALE_REVENUE_BLYEAR ,
COUNT(DISTINCT (CASE WHEN WC_ORDERITEM_F_M_CN.X_ORDER_TYPE_I = 'Sale Order'
THEN WC_ORDERITEM_F_M_CN.ORDER_WID
ELSE NULL --0
END)) AS SALE_TRANS_BLYEAR
FROM #W_MONTH_D_TMP A ,
W_ORG_D ,
WC_ORDERITEM_F_M_CN ,
W_PERSON_D
WHERE
WC_ORDERITEM_F_M_CN.ORDER_DT_WID < A.LST_YEAR_DT_WID
AND WC_ORDERITEM_F_M_CN.ORDER_DT_WID >= A.BLST_YEAR_DT_WID
AND W_ORG_D.INTEGRATION_ID = W_PERSON_D.VIS_PR_BU_ID
AND W_PERSON_D.ROW_WID > 0
AND W_PERSON_D.ROW_WID = WC_ORDERITEM_F_M_CN.CONTACT_WID
GROUP BY A.MONTH_WID ,
W_ORG_D.INTEGRATION_ID ,
W_PERSON_D.ROW_WID
) TMP
WHERE WC_PERSON_F_M_CN.MONTH_WID = TMP.MONTH_WID
AND WC_PERSON_F_M_CN.PERSON_WID = TMP.PERSON_WID
AND WC_PERSON_F_M_CN.VIS_PR_BU_ID = TMP.VIS_PR_BU_ID
OPTION(maxdop 1);
May 12, 2010 at 3:39 am
Have you tried comparing the execution plans of the hanging and non hanging statements ?
May 12, 2010 at 4:00 am
Dave Ballantyne (5/12/2010)
Have you tried comparing the execution plans of the hanging and non hanging statements ?
Yes, it's same.
May 12, 2010 at 4:47 am
Try to update table statistics after update:
UPDATE STATISTICS WC_PERSON_F_M_CN
May 13, 2010 at 12:20 am
elutin (5/12/2010)
Try to update table statistics after update:
UPDATE STATISTICS WC_PERSON_F_M_CN
Thanks,
Yes, we had check the index and statistics , both are fine. no use even though we update statistics Tables with fullscan;
I think this issue caused by one temp table. The parent thread hold the temp table X lock, and the child thread wanna to get X lock on the same temp table. This is similiar the deadlock on the thread.
We just tested it again, now it works well. the test will last another 1 or 2 hours.
May 14, 2010 at 8:59 am
1) you are using non-ansi joins. Note that these are no longer supported in newer editions of SQL Server
2) AND WC_ORDERITEM_F_M_CN.ORDER_DT_WID >= A.BLST_YEAR_DT_WID
that is known as a 'triangular join', and the more rows between the two sets the MUCH worse performance gets.
3) have you checked for foreign key issues? those can really screw updates in some cases.
curious - non of the above would allow the query to run fast on one iteration but then slow on another. And you said there is no blocking, no IO stalls and the same query plan. if all of those things are REALLy true, you have an unexplainable scenario here - and that bothers me quite a bit! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2010 at 1:26 am
TheSQLGuru (5/14/2010)
1) you are using non-ansi joins. Note that these are no longer supported in newer editions of SQL Server2) AND WC_ORDERITEM_F_M_CN.ORDER_DT_WID >= A.BLST_YEAR_DT_WID
that is known as a 'triangular join', and the more rows between the two sets the MUCH worse performance gets.
3) have you checked for foreign key issues? those can really screw updates in some cases.
curious - non of the above would allow the query to run fast on one iteration but then slow on another. And you said there is no blocking, no IO stalls and the same query plan. if all of those things are REALLy true, you have an unexplainable scenario here - and that bothers me quite a bit! :w00t:
Yes, this also bothers me; but now it can works well. We made 2 changes.
1. enable the with recompile on the procs
2. drop the temp tables at the end of the procs, because the developer use the same name temp tables in several procs.
BTW: we find the temp table cannot be dropped when the session lost. check via object_id.
such as we used one temp table named #W_MONTH___________________________00000000013D,
the temp table rename to #7CD98669 when the session lost but not dropped.
May 18, 2010 at 2:07 am
And the related topic
http://msdn.microsoft.com/en-us/library/ms174979.aspx
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
SQL statements reference the temporary table by using the value specified for table_name in the CREATE TABLE statement, for example:
Copy Code
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);
[highlight=#ffff11]If more than one temporary table is created inside a single stored procedure or batch, they must have different names.[/highlight]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply