Drop and Recreate Indexes in SSIS with SMO

Posted: March 30, 2012 in SSIS

There are an infinite number of ways to load a table with data however I believe most people would agree with me in saying that a table without indexes will load faster than one with indexes.  Microsoft talks about performance in loading tables in the guide (The Data Loading Performance Guide).  In that guide they say the following: “Before you load data into an empty table, it is always a good idea to drop or disable all indexes on the table. After the load is done, re-create or re-enable the indexes.

In SSIS there are a couple of ways you can do this, the easiest way is to use an Execute SQL Task to drop the index(s) before your load and then use a second task after your load to re-create the index(s).  While this is certainly a good option it causes a problem in production.  A code change is required for each change to an existing index or new index is created.  In order to get around this you need to drop all indexes on a table and re-create them after the load.  The question becomes how do you know what indexes exist to drop and after you drop them how do you re-create them.  Finally you might ask why don’t we just disable the indexes. 

We drop and re-create the indexes vs. disabling them because if a clustered index is disabled it will prevent an Insert (Guidelines for Disabling Indexes and Constraints).

To drop and recreate the indexes we can use SMO (SQL Management Objects) to obtain a list of indexes for a particular table along with a SQL script to create them.  Using an object variable in SSIS we can store the SQL scripts for re-creation after the data load.  To do this your package would look something like this:

image

In this example we have a completion constraint between the Data Flow Task and the second script that re-creates the indexes because regardless if the Data Flow ends in success or failure we want to put the indexes back. 

When you build your script task you will need the SMO Libraries to access the SQL Server via SMO and the generic collections library to build the list of SQL objects.

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using System.Collections.Generic;

 

The code below will script out the indexes and drop them.

public void Main()

{

    Boolean bProcessLive = Convert.ToBoolean(Dts.Variables["etlProcessLive"].Value);

    int ErrorNumber = 9000;

    try

    {

        string sSQLServer = this.Dts.Connections["OLEDB_Connection"].Properties["ServerName"].GetValue(Dts.Connections["OLEDB_Connection"]).ToString();

        if (bProcessLive == false)

            MessageBox.Show("Server Name: " + sSQLServer, "SQL Objects");

        string sSQLDatabase = this.Dts.Connections["OLEDB_Connection"].Properties["InitialCatalog"].GetValue(Dts.Connections["OLEDB_Connection"]).ToString();

        if (bProcessLive == false)

            MessageBox.Show("Database Name: " + sSQLDatabase, "SQL Objects");

        

        // Connect to the instance of SQL Server. 

        Server oSQLServer = new Server(sSQLServer);

        

        // Reference the database.  

        Database db = oSQLServer.Databases[sSQLDatabase];

 

        // Reference the table

        String sDestTable = Dts.Variables["etlDestinationTable"].Value.ToString();

        // Bug in SMO doesn't return schema data. ConnectID:696411

        // The following line won't work

        // Table table = db.Tables["dimCustomer","dbo"];

 

        // Create a list to store SQL code in.

        List<string> objSQL = new List<string>();

 

        // Define a Scripter object and set the required scripting options. 

        Scripter scrp = new Scripter(oSQLServer);

        scrp.Options.ScriptDrops = false;

        scrp.Options.WithDependencies = false;

        scrp.Options.Indexes = true;   // Include indexes

        scrp.Options.DriAllConstraints = true;   // Include referential constraints

 

        // Iterate through the tables in database and script each one. Display the script.

        foreach (Table tb in db.Tables)

        {

            Boolean bResult = tb.ToString() == sDestTable.ToString();

            if (bProcessLive == false)

                MessageBox.Show("Enumerated Table: " + tb.ToString() + "Destination Table: " + sDestTable + " Result: " + bResult.ToString(), "SQL Objects");

            // Check if the table is our destination table and that it is not a system table

            if (bResult == true && tb.IsSystemObject == false)

            {

                if (bProcessLive == false)

                    MessageBox.Show("Scripting indexes for table: " + tb.Name, "SQL Objects");

                // Count the indexes on the table

                int ixCount = tb.Indexes.Count;

                if (bProcessLive == false)

                    MessageBox.Show("Index Count: " + ixCount.ToString(), "Index Count");

 

                // Script out each index

                while (ixCount > 0)

                {

                    ixCount--;

                    System.Collections.Specialized.StringCollection ic = scrp.Script(new Urn[] { tb.Indexes[ixCount].Urn });

                    foreach (string st in ic)

                    {

                        if (bProcessLive == false)

                            MessageBox.Show(st, "Index SQL");

                        objSQL.Add(st);

                    }

                    // Drop the index

                    tb.Indexes[ixCount].Drop();

                }

            }

        }

        Dts.Variables["User::etlSQLObjects"].Value = objSQL;

    }

    catch (Exception ex)

    {

        if (bProcessLive == false)

            MessageBox.Show(ex.Message, "Script Task Failure");

        Dts.Events.FireError(ErrorNumber, "Script Task Failure", ex.Message, ex.HelpLink, 0);

        Dts.TaskResult = (int)ScriptResults.Failure;

    }

 

    Dts.TaskResult = (int)ScriptResults.Success;

}

The script below will re-create the Indexes:

public void Main()

{

    Boolean bProcessLive = Convert.ToBoolean(Dts.Variables["etlProcessLive"].Value);

    int ErrorNumber = 9000;

 

    // Create new ADO connection

    SqlConnection sqlConn = new SqlConnection();

    sqlConn = (SqlConnection)(Dts.Connections["ADO_Connection"].AcquireConnection(Dts.Transaction) as SqlConnection);

    if (bProcessLive == false)

        MessageBox.Show(sqlConn.ConnectionString, "ADO.net Connection");

    if (bProcessLive == false)

        MessageBox.Show(sqlConn.ConnectionTimeout.ToString(), "Connection Timeout");

 

    try

    {

        // Populate the list

        object objSQL = Dts.Variables["User::etlSQLObjects"].Value;

        List<string> list;

        list = (List<string>)objSQL;

 

        // Loop through each object to create each index

        foreach (string SQL in list)

        {

            if (bProcessLive == false)

                MessageBox.Show(SQL, "SQL Statement");

            // Create the index

            SqlCommand sqlCmd = new SqlCommand(SQL,sqlConn);

            sqlCmd.CommandTimeout = sqlConn.ConnectionTimeout;

            sqlCmd.ExecuteNonQuery();

        }

    }

    catch (Exception ex)

    {

        if (bProcessLive == false)

            MessageBox.Show(ex.Message, "Script Task Failure");

        Dts.Events.FireError(ErrorNumber, "Script Task Failure", ex.Message, ex.HelpLink, 0);

        Dts.TaskResult = (int)ScriptResults.Failure;

    }

    finally

    {

        if (sqlConn.State == ConnectionState.Open)

        {

            sqlConn.Close();

            sqlConn.Dispose();

        }

    }

    Dts.TaskResult = (int)ScriptResults.Success;

}

About these ads

Comments are closed.