Seeking advice on deleting Indexes

Question

Re: Seeking advice on deleting Indexes

We have a nightly ETL with a table update that runs about 90 minutes that i want to optimize. There is a WHILE loop that executes 24 times (And no, there is no way to take the WHILE loop out of the processing and make it set based) and in each loop it (1) deletes rows from table dw_fact_GL3 where the DateKey equal a value (2) then inserts rows into dw_fact_GL3. This table is only updated during the nightly ETL. Otherwise it is read only.

Looking at the table I believe that there are several indexes that can be deleted. I highlighted those below in gray. Wanted to get input from others. I added writes to the log to see which is taking longer - the deletes or the inserts - but the ETL has not run yet with the new writes. I would guess that the inserts are the issue. We do not have any index hints. Also, all of the reporting during the day are done with schedules jobs, not much ad hoc reporting (reporting does not need to be optimized).

If I do drop one of the indexes, is there anything that i should know as a precaution?

IF OBJECT_ID('[dbo].[dw_fact_GL3]') IS NOT NULL 
DROP TABLE [dbo].[dw_fact_GL3] 
GO
CREATE TABLE [dbo].[dw_fact_GL3] ( 
[FactID]                         BIGINT           IDENTITY(1,1)   NOT NULL,
[dim_ChartOfAccounts_Key]        NUMERIC(8,0)                     NOT NULL,
[dim_BusinessUnits_Key]          VARCHAR(20)                      NOT NULL,
[DateKey]                        INT                              NOT NULL,
[AcctType]                       VARCHAR(1)                           NULL,
[DaysInMonth]                    INT                              NOT NULL,
[DaysInYear]                     INT                              NOT NULL,
[DaysInMonthPrior1]              INT                              NOT NULL,
[DaysInMonthFiscalPrior1]        INT                              NOT NULL,
[DaysInMonthPrior2]              INT                              NOT NULL,
[DaysInMonthFiscalPrior2]        INT                              NOT NULL,
[Average Balance]                DECIMAL(24,8)                        NULL,
[Average Balance Prior1]         DECIMAL(24,8)                        NULL,
[Average Balance Fiscal Prior1]  DECIMAL(24,8)                        NULL,
[Average Balance Prior2]         DECIMAL(24,8)                        NULL,
[Average Balance Fiscal Prior2]  DECIMAL(24,8)                        NULL,
[YTD Average Balance]            DECIMAL(24,8)                        NULL,
[Ending Balance]                 DECIMAL(24,8)                        NULL,
[Debit Amount]                   DECIMAL(24,8)                        NULL,
[Debit Count]                    DECIMAL(7,0)                     NOT NULL,
[Credit Amount]                  DECIMAL(24,8)                        NULL,
[Credit Count]                   DECIMAL(7,0)                     NOT NULL,
[Net Change]                     DECIMAL(24,8)                        NULL,
[Beginning Balance]              DECIMAL(24,8)                        NULL,
[Back-Date Adjusted Balance]     DECIMAL(24,8)                        NULL,
[Current Balance]                DECIMAL(24,8)                        NULL,
[Budget Amount]                  DECIMAL(24,8)                        NULL,
[Budget YTD Amount]              DECIMAL(24,8)                        NULL,
[Budget YTD Average]             DECIMAL(24,8)                        NULL,
[SliceID]                        INT                                  NULL,
[Has Bal]                        INT                                  NULL,
[ExtRecType]                     CHAR(1)                              NULL  CONSTRAINT [DF_dw_fact_GL3_ExtRecType] DEFAULT '0',
CONSTRAINT   [PK_dw_Fact_GL3]  PRIMARY KEY CLUSTERED    ([FactID] asc))
GO
CREATE NONCLUSTERED INDEX [IX_dw_Fact_GL3_AcctType] 
   ON [dbo].[dw_fact_GL3] ([AcctType] asc)
   INCLUDE ([Has Bal], [DateKey], [dim_BusinessUnits_Key], [SliceID], [dim_ChartOfAccounts_Key], [Average Balance], [YTD Average Balance], [Ending Balance], [Net Change], [DaysInMonth], [DaysInYear])
CREATE NONCLUSTERED INDEX [IX_dw_Fact_GL3_BUKey] 
   ON [dbo].[dw_fact_GL3] ([dim_BusinessUnits_Key] asc)
   INCLUDE ([Has Bal], [DateKey], [dim_ChartOfAccounts_Key], [SliceID], [AcctType], [Average Balance], [YTD Average Balance], [Ending Balance], [Net Change], [DaysInMonth], [DaysInYear])
CREATE NONCLUSTERED INDEX [IX_dw_Fact_GL3_ChartKey] 
   ON [dbo].[dw_fact_GL3] ([dim_ChartOfAccounts_Key] asc)
   INCLUDE ([Has Bal], [DateKey], [dim_BusinessUnits_Key], [SliceID], [AcctType], [Average Balance], [YTD Average Balance], [Ending Balance], [Net Change], [DaysInMonth], [DaysInYear])
CREATE NONCLUSTERED INDEX [IX_dw_Fact_GL3_Cover_BU] 
   ON [dbo].[dw_fact_GL3] ([dim_ChartOfAccounts_Key] asc)
   INCLUDE ([dim_BusinessUnits_Key], [DateKey], [AcctType], [Average Balance], [YTD Average Balance], [Ending Balance], [Net Change])
CREATE NONCLUSTERED INDEX [IX_dw_Fact_GL3_Cover_Chart] 
   ON [dbo].[dw_fact_GL3] ([dim_BusinessUnits_Key] asc)
   INCLUDE ([dim_ChartOfAccounts_Key], [DateKey], [AcctType], [Average Balance], [YTD Average Balance], [Ending Balance], [Net Change])
CREATE NONCLUSTERED INDEX [IX_dw_Fact_GL3_DateKey] 
   ON [dbo].[dw_fact_GL3] ([DateKey] asc)
   INCLUDE ([Has Bal], [dim_BusinessUnits_Key], [dim_ChartOfAccounts_Key], [SliceID], [AcctType], [Average Balance], [YTD Average Balance], [Ending Balance], [Net Change], [DaysInMonth], [DaysInYear])
CREATE NONCLUSTERED INDEX [IX_dw_Fact_GL3_HasBal] 
   ON [dbo].[dw_fact_GL3] ([Has Bal] asc)
   INCLUDE ([DateKey], [dim_BusinessUnits_Key], [dim_ChartOfAccounts_Key], [SliceID], [AcctType], [Average Balance], [YTD Average Balance], [Ending Balance], [Net Change], [DaysInMonth], [DaysInYear])

Answer 1

With zero knowledge about your system, there is no way we can give our blessing to dropping these indexes.

But if you want to improve the speed of the ETL process, you can disable indexes not needed to the ETL job:

ALTER INDEX ix ON tbl DISABLE

When the job is done, you rebuild these indexes to re-enable them

ALTER INDEX ix ON tbl REBUILD

This can definitely be a boost for your ETL process.

Just don't disable cluster indexes - that disables the table entirely and you cannot insert into it!