select count(*) from (select distinct KEY_SW_INSTANCE from DIM_N_SW_INSTANCE) DIM;
-- 10293847260
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.
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_...
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.
truncate table DIM_N_SW_INSTANCE$;
Mar 04, 2021 07:27 PM