IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

BIML Script To Disable Indexes And Later Rebuild Indexes

In an earlier post I described the necessity to drop non-clustered indexes before loading data in your data warehouse and rebuilding them afterwards. Later I found a better approach: Instead of dropping the indexes you can just disable them with:
ALTER INDEX <IndexName> ON <TableName> DISABLE
The loading process will now perform faster because SQL Server won’t update these indexes when you are loading. After completion of loading you can rebuild them with:
ALTER INDEX <IndexName> ON <TableName> REBUILD
 
The BIML code at the bottom of this post is part of a larger script in which I loop through a dataset that describe a series of packages. In relevant packages this script is added and executed. In the image of the package you can see what happens:
  • With the Execute SQL Task ‘SQL GetIndexList’ I will get a list of statements I need to disable and rebuild the non-clustered indexes of the target table. The result (Full result set) of this SQL statement is stored in an object variable with the name IndexList
  • In the For Each Loop Container ‘FELC Disable Index’ I loop through the rows of this IndexList and use the Execute SQL Task ‘SQL Disable Index’ for each row and thus disabling the relevant non-clustered indexes.
  • Next is the DataFlow Task … which I have kept empty for this purpose.
  • I end with a For Each Loop Container ‘FELC Rebuild Indexes’ in which I rebuild every index I disabled earlier.

HandleIndexesWithBiml

WARNING

In most data warehouse scenarios this is a viable pattern. However in a scenario were you load relatively few rows in a large table with many non-clustered indexes, the rebuild of these indexes can take very long.

The Biml Script to create this package:

  <Biml xmlns="http://schemas.varigence.com/biml.xsd">
   
    <# string TableName="[Production].[ProductCategory]";#>
    <Connections>
      <OleDbConnection Name="TargetDatabase"
                  ConnectionString="Data Source=.\SQL2012; Initial Catalog=AdventureWorks2012; Provider=SQLNCLI10.1; Integrated Security=SSPI;"
                  CreatePackageConfiguration="true">
      </OleDbConnection>
    </Connections>
 
    <Packages>
      <Package Name="PKG Handle Indexes Gracefully" ConstraintMode="Linear">
        <Variables>
          <Variable Name="IndexList" DataType="Object" />
          <Variable Name="DisableStatement" DataType="String" />
          <Variable Name="RebuildStatement" DataType="String" />
        </Variables>
        <Tasks>
          <ExecuteSQL Name="SQL GetIndexList" ConnectionName="TargetDatabase" ResultSet="Full">
            <DirectInput>
SELECT
   DisableStatement = 'ALTER INDEX ' + QUOTENAME(i.Name) + ' ON '
    + QUOTENAME(SCHEMA_NAME (o.SCHEMA_ID)) + '.'
      + QUOTENAME(o.name) + ' DISABLE'
   ,RebuildStatement = 'ALTER INDEX ' + QUOTENAME(i.Name) + ' ON '
    + QUOTENAME(SCHEMA_NAME (o.SCHEMA_ID)) + '.'
      + QUOTENAME(o.name) + ' REBUILD'
FROM sys.indexes i
JOIN sys.objects o
    ON o.object_id=i.object_id
WHERE i.is_primary_key = 0       
AND i.is_unique_constraint = 0  
AND i.is_disabled = 0    
AND i.type_desc = N'NONCLUSTERED'       
AND o.TYPE = N'U'  
AND QUOTENAME(SCHEMA_NAME (o.SCHEMA_ID)) + '.'
    + QUOTENAME(o.name)='<#=TableName#>'
            </DirectInput>
            <Results>
              <Result Name="0" VariableName="User.IndexList" />
            </Results>
          </ExecuteSQL>
         
          <!--Loop through indexes and disable them-->
          <ForEachAdoLoop Name="FELC Disable Indexes"
                          SourceVariableName="User.IndexList"
                          ConstraintMode="Linear" >
            <VariableMappings>
              <VariableMapping Name="0"
                               VariableName="User.DisableStatement" />
            </VariableMappings>
            <Tasks>
              <ExecuteSQL Name="SQL Disable Index"
                          ConnectionName="TargetDatabase"
                          ResultSet="None" >
                <VariableInput VariableName="User.DisableStatement" />
              </ExecuteSQL>
            </Tasks>
          </ForEachAdoLoop>

          <Dataflow Name="DFT YourDataFlowTask" />
          <!--DataFlow Logic-->
         
         
          <!--Loop through indexes and rebuild them-->
          <ForEachAdoLoop Name="FELC Rebuild Indexes"
                          SourceVariableName="User.IndexList"
                          ConstraintMode="Linear" >
            <VariableMappings>
              <VariableMapping Name="1"
                               VariableName="User.RebuildStatement" />
            </VariableMappings>
            <Tasks>
              <ExecuteSQL Name="SQL Rebuild Index"
                          ConnectionName="TargetDatabase"
                          ResultSet="None" >
                <VariableInput VariableName="User.RebuildStatement" />
              </ExecuteSQL>
            </Tasks>
          </ForEachAdoLoop>
         
         
        </Tasks>
      </Package>
  </Packages>
</Biml>

ABOUT BIML

You can leverage Business Intelligence Markup Language – or BIML – to automate the creation of Microsoft SQL Server Integration Services (SSIS) Packages. BIML is a creation of Varigence, Inc. and is available in proprietary products, open source projects, and has been published as an open language specification. The popular open source BIDSHelper project includes Biml functionality, enabling anyone to write and execute Biml code for free.

Do you need a head start with the automation of SSIS packages: consider my BIML Workshop.

» Similar Posts

  1. Logging in SSIS with BIML 2 – Logging Package Execution in the Control Flow
  2. SSIS Pattern: Drop- and rebuild indexes dynamically
  3. Logging in SSIS with BIML 1 – Logging Task Execution

» Trackbacks & Pingbacks

    No trackbacks yet.

» Comments

    There are no comments.

Comments are closed