Enable & Disable Index - SSIS

• gugan_ta

  SSCommitted

  Points: 1649

  More actions

  January 20, 2012 at 9:35 pm

  #267001

  Hi,

  We have developed a below ETL packages.

  Master packages - 1

  Child packages - 10

  Containers in each package - 5

  Each container has a data flow from source to destination.Target table has indexing (both clustered and non clustered). We have planned to disbale index in the target table during dataflow and enable the same after completion of dataflow. The process has been designed based on source records per day.Also, the process is an increamental load on a daily basis. So the entire data loading process will be done inside a for loop container. The date will be the input for the loop. Now i had below queries while designing.

  1. If we execute disable index at master package, it will drop all the table index irrespective of data load (i.e., some table will not get the data load on that particular day. but still we will disable & enable index for these table at master package level which will take additional time)

  2. If we handle at container level(child package), i can disable & enable index if date variable contains value and then start the loop. This will avoid the above issue and indexing will handled only for the dataload available.

  We had different views in above points and i suggested the 2nd method as it will avoid the indexing(enable/disable) for the tables which will not have the data load.

  I thought to get the expert guidance to know which one will be the best approach.

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  January 22, 2012 at 1:19 am

  #1436865

  gugan_ta (1/20/2012)

  ---

  I thought to get the expert guidance to know which one will be the best approach.

  The second option will create the least churn for your database, which is good. However, to that point, a question. Are you disabling or dropping/creating these indexes? You can't disable the clustered (well, you can, but you lock the table out), and a mere disabling of the non-clustered will leave your statistics quite stale.

  The common reasons for disabling are found in this article on msdn:

  http://msdn.microsoft.com/en-us/library/ms177406.aspx

  Note, one of them is to rebuild the index without using extra space, an extra step.

  What are your maintenance intentions if you are doing a direct disable of all non-clustered indexes?

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA