Some users may have issues creating a community account. For more information, please click here.

Normalize task keeps failing at Analyze Step or Normalize performance slow down significantly

Normalize task keeps failing at Analyze Step or Normalize performance slow down significantly

Symptoms:

  1. Normalize task timeout at Analyze ETL step
  2. Normalize task takes much longer than before

Diagnosis:

  1. Please make sure “Keep History” is disabled
  2. Run below SQL query in BDNA_PUBLISH database, it will return a huge number -
    select count(*) from (select distinct KEY_SW_INSTANCE from DIM_N_SW_INSTANCE) DIM;
    
    -- 10293847260​
  3. Per BDNA.log, ETL step will consume the most time of the whole access, like-
    2021-03-03 01:45:38,793 0 INFO Call remoting service=tcp://xx.xx.xx.xx:8084/NormalizeBIUpdateServer/DataService, method=EtlInventory...
    2021-03-03 22:18:00,260 0 INFO Return message from Analyze: Success.​
  4. Check logs in BDNA_A_Logger table in BDNA_PUBLISH database, and you will see all of those SQL statements which slow down the ETL process badly are all the “DIM_N_SW_INSTANCE$” related, like-
    3/3/2021 2:40:42 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = exec NBI_CREATE_TAB_CONS_SP @p_table_name = 'DIM_N_SW_INSTANCE$'; ]
    3/3/2021 4:34:00 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = exec NBI_CREATE_TAB_INDEXES_SP @p_table_name = 'DIM_N_SW_INSTANCE$'; ]
    3/3/2021 8:48:13 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str ...
    
    
    3/3/2021 9:09:47 INFO : [NBI.DM_ENABLE_TAB_INDEXES_SP].[Executing stmt_str = ALTER INDEX DIM_N_SWINS_KEY_SW_IDX ON [dbo].[DIM_N_SW_INSTANCE$] REBUILD WITH (MAXDOP = 15, FILLFACTOR = 100)]
    3/3/2021 10:27:09 INFO : [NBI.DM_ENABLE_TAB_INDEXES_SP].[Executing stmt_str = ALTER INDEX DIM_N_SWINS_SWLTUSRDMN_IDX ON [dbo].[DIM_N_SW_INSTANCE$] REBUILD WITH (MAXDOP = 15, FILLFACTOR = 100)]
    3/3/2021 11:23:37 INFO : [NBI.DM_ENABLE_TAB_INDEXES_SP].[Executing stmt_str = ALTER INDEX DIM_N_SWINS_SWLTUSRLGN_IDX ON [dbo].[DIM_N_SW_INSTANCE$] REBUILD WITH (MAXDOP = 15, FILLFACTOR = 100)]
    3/3/2021 12:21:18 INFO : [NBI.DM_ENABLE_TAB_INDEXES_SP].[Executing stmt_str = ALTER INDEX DIM_N_SWINS_SWLTUSRNAME_IDX ON [dbo].[DIM_N_SW_INSTANCE$] REBUILD WITH (MAXDOP = 15, FILLFACTOR = 100)]
    3/3/2021 13:19:31 INFO : [NBI.NBI_UTIL_UPDATE_STATISTICS].[Running update statistics for DIM_N_SW_INSTANCE$ table]
    
    
    3/3/2021 13:46:33 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = SELECT * INTO DIM_N_SW_INSTANCE$_K FROM DIM_N_SW_INSTANCE$ D WHERE EXISTS ( SELECT 1 FROM FACT_N_IT_ASSETS F WHERE F.KEY_SW = D.KEY_SW AND D.KEY_SW > 0 ); INSERT INTO DIM_N_SW_INSTANCE$_K SELECT * FROM DIM_N_SW_INSTANCE$ WHERE KEY_SW=0; DROP TABLE DIM_N_SW_INSTANCE$; EXEC SP_RENAME DIM_N_SW_INSTANCE$_K, DIM_N_SW_INSTANCE$;]
    3/3/2021 15:35:44 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = exec NBI_CREATE_TAB_CONS_SP @p_table_name = 'DIM_N_SW_INSTANCE$'; ]
    3/3/2021 17:44:27 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = exec NBI_CREATE_TAB_INDEXES_SP @p_table_name = 'DIM_N_SW_INSTANCE$'; ]
    3/3/2021 21:57:48 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_...​

Root Cause:

The current normalize procedure will not clean the table - “DIM_N_SW_INSTANCE$”, which makes the table get bigger and bigger. However, the index of the table “DIM_N_SW_INSTANCE$” will be dropped and re-built for each normalize run, and the date set size has a significantly impact on the index creation. Thus, a huge “DIM_N_SW_INSTANCE$” table will slow down the performance badly.

 

Note:

The unclean data in “DIM_N_SW_INSTANCE$” will have not any impact on Analyze Reports.

 

Solution:

  1. The temporary solution is to run below SQL statement to truncate the "DIM_N_SW_INSTANCE$" tabel :
    truncate table DIM_N_SW_INSTANCE$;​
  2. The permanent fix will be included in 5.5.39 release.

 

 

Labels (3)
Was this article helpful? Yes No
No ratings
Version history
Revision #:
2 of 2
Last update:
‎Mar 04, 2021 07:26 PM
Updated by:
 
Contributors