Performance issue with SQL Server

thanush9sep · Post by **thanush9sep** » Wed Nov 19, 2014 5:53 pm

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 :( .

ray.wurlod · Post by **ray.wurlod** » Wed Nov 19, 2014 7:43 pm

Any reason you're not using the bulk loader?

thanush9sep · Post by **thanush9sep** » Thu Nov 20, 2014 8:43 am

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

chulett · Post by **chulett** » Thu Nov 20, 2014 9:09 am

There's no reason to use BBCode for stuff like bold or italics and then have the "Disable BBCode in this post" option checked. #JustSayin

Fixed.

thanush9sep · Post by **thanush9sep** » Thu Nov 20, 2014 9:35 am

Thanks Chulett. I am sorry . Did not notice those option

qt_ky · Post by **qt_ky** » Thu Nov 20, 2014 3:20 pm

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.

thanush9sep · Post by **thanush9sep** » Thu Nov 20, 2014 9:22 pm

@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

qt_ky · Post by **qt_ky** » Thu Nov 20, 2014 9:54 pm

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?

priyadarshikunal · Post by **priyadarshikunal** » Fri Nov 21, 2014 2:23 am

Also did you check in database when the connection is established for both nodes?