Performance issue with SQL Server
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 54
- Joined: Thu Oct 18, 2007 4:20 am
- Location: Chennai
Performance issue with SQL Server
DS Release: 8.5 Fix pack 1
DB: Microsoft SQL Server, version 11.00.5058 through driver VMsqls25.so.
issue: long running job
stage: ODBC Connector Stage
Write Mode: Insert
Generate SQL: yes
Table Action: Append
Record Count/Array size = 2000
isolation level: Read committed
Auto Commit mode : ON
Node:2
Grid enabled
Insert records: 9000
Description:-
Datastage job that loads to the DB started to perform badly. After analyzing we increased the table space
However, even after increasing the space, the job started to perform bad selectively
The issue is when the auto-generated sql kicks-in in the director.
For Node 0 the insert sql is generated immediately, however for Node1 the
insert sql takes minimum 1 hour to generate and sometime even more
Is there any known issue with Datastage 8.5 FP 1 and SQL server.
Another difficulty in analyzing the issue is that the other non production environment are in Fix pack 3 :( .
DB: Microsoft SQL Server, version 11.00.5058 through driver VMsqls25.so.
issue: long running job
stage: ODBC Connector Stage
Write Mode: Insert
Generate SQL: yes
Table Action: Append
Record Count/Array size = 2000
isolation level: Read committed
Auto Commit mode : ON
Node:2
Grid enabled
Insert records: 9000
Description:-
Datastage job that loads to the DB started to perform badly. After analyzing we increased the table space
However, even after increasing the space, the job started to perform bad selectively
The issue is when the auto-generated sql kicks-in in the director.
For Node 0 the insert sql is generated immediately, however for Node1 the
insert sql takes minimum 1 hour to generate and sometime even more
Is there any known issue with Datastage 8.5 FP 1 and SQL server.
Another difficulty in analyzing the issue is that the other non production environment are in Fix pack 3 :( .
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 54
- Joined: Thu Oct 18, 2007 4:20 am
- Location: Chennai
These jobs are there in Production server for some years now using the insert option.
Yesterday APT_DISABLE_COMBINATION was set to TRUE. The Job finished in 16 min.
However I still can see 8 min delay in generating the insert sql statement in the Node1.
Log Example:-
11/20/2014 5:11:45 AM
FCMGT_LBR_TRAN_ins,0: Generated INSERT statement: INSERT INTO FCMGT_LBR_TRAN(FCMGT_LBR_TRAN_I,JOB_I,BTCH_RUN_I,ETL_CRTE_TS,ORG_C,REF_GL_PROJ_I,GL_ACCT_I,CTCTR_I,GL_CHRT_ACCT_I,EXP_CTR_I,GL_PROJ_I,WORK_ORD_I,GL
11/20/2014 5:19:51 AM
FCMGT_LBR_TRAN_ins,1: Generated INSERT statement: INSERT INTO FCMGT_LBR_TRAN(FCMGT_LBR_TRAN_I,JOB_I,BTCH_RUN_I,ETL_CRTE_TS,ORG_C,REF_GL_PROJ_I,GL_ACCT_I,CTCTR_I,GL_CHRT_ACCT_I,EXP_CTR_I
Yesterday APT_DISABLE_COMBINATION was set to TRUE. The Job finished in 16 min.
However I still can see 8 min delay in generating the insert sql statement in the Node1.
Log Example:-
11/20/2014 5:11:45 AM
FCMGT_LBR_TRAN_ins,0: Generated INSERT statement: INSERT INTO FCMGT_LBR_TRAN(FCMGT_LBR_TRAN_I,JOB_I,BTCH_RUN_I,ETL_CRTE_TS,ORG_C,REF_GL_PROJ_I,GL_ACCT_I,CTCTR_I,GL_CHRT_ACCT_I,EXP_CTR_I,GL_PROJ_I,WORK_ORD_I,GL
11/20/2014 5:19:51 AM
FCMGT_LBR_TRAN_ins,1: Generated INSERT statement: INSERT INTO FCMGT_LBR_TRAN(FCMGT_LBR_TRAN_I,JOB_I,BTCH_RUN_I,ETL_CRTE_TS,ORG_C,REF_GL_PROJ_I,GL_ACCT_I,CTCTR_I,GL_CHRT_ACCT_I,EXP_CTR_I
-
- Premium Member
- Posts: 54
- Joined: Thu Oct 18, 2007 4:20 am
- Location: Chennai
The 8 minute delay between job log entries is just a possible misinterpretation. The detailed job log entries show up in the log ... eventually, when they feel like it. It does not mean that 8 minutes went by between a node 0 insert and a node 1 insert. I often see this situation where one node's SQL gets logged at the start and another node's same SQL gets logged at the end.
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Premium Member
- Posts: 54
- Joined: Thu Oct 18, 2007 4:20 am
- Location: Chennai
@qt_ky I did a detail analyzes and I am sure I am not looking at the wrong direction.
The director log just stops after Node 0 information and does not move. The job has only 4 stages. The source dataset and join with the target table in reference link mainly used for restart-ability and target odbc stage.
if the delay was there due to some other issue, I could have clearly seen that in the Designer or Job Monitor. However that is not the case
The director log just stops after Node 0 information and does not move. The job has only 4 stages. The source dataset and join with the target table in reference link mainly used for restart-ability and target odbc stage.
if the delay was there due to some other issue, I could have clearly seen that in the Designer or Job Monitor. However that is not the case
Read through this topic and check everything suggested: viewtopic.php?t=152044
To quote PaulVL from the above topic, "Remember that the entry in the log is NOT the time that the event happened.
It is the time that the event was written to the log. There is a difference."
What steps have you taken to narrow down the problem so far? Have you made any debug
copies of the job with each step being isolated into its own job and measured separately?
To quote PaulVL from the above topic, "Remember that the entry in the log is NOT the time that the event happened.
It is the time that the event was written to the log. There is a difference."
What steps have you taken to narrow down the problem so far? Have you made any debug
copies of the job with each step being isolated into its own job and measured separately?
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI