Home > SSIS > Rebuild Index using SSIS

Rebuild Index using SSIS


Dear SSIS/DBA folks,
In “SSIS for DBA’s” article series, Rebuild Index Task is the second article. In this article, I will be talking about Rebuild Index Task in SSIS.
Rebuilding indexes has various benefits in terms of improving performance of SQL Server and it is part of SQL Server data management activity. We have to keep the index to help the I/O to fetch the data much faster. There are two activities related to Index, they are Rebuilding and Reorganizing.
Please learn more about index rebuild from this site http://msdn.microsoft.com/en-us/library/ms187874.aspx
Implementation:
These are the steps involved in Index rebuilding using SSIS.
Step 1: create new SSIS project and add Rebuild Index Task in to control flow.
Step2: Edit the task and add new connection to connect with SQL Server instance
Step 3: select database(s) to rebuild index, you can also ignore the offline database by checking an option shown in the below picture
Step 4: select the database objects for index rebuild. You can choose table or views or both in this selection list. This selection option will be enable once you choose table or view object this this list. Select option will allow you to choose tables/views in the list for index rebuild.
Step 5: Free space option –
                Reorganize page with the default amount of free space – it drops and recreate index with default fill factor
                Change free space per page percentage to – it drops and recreate index with the user defined fill factor value
Step 6: Advanced options
                Sort result in tempdb – if checked, Index sort operation will be carried out in tempdb else in-memory
                Keep index online while reindexing – if checked, index will be available for use while rebuilding index else index will not available until rebuilding operation completed.
Step 7: Click View T-SQL to view script and click ok to complete configuration
To know more about this task, visit http://msdn.microsoft.com/en-us/library/ms180074.aspx
Thanks for reading this article; your comments are more valuable.
Advertisements
  1. January 28, 2014 at 3:54 pm

    what are the benefits of using the ssis task vs running something like this:

    TRUNCATE TABLE TRACKING..Ck_Idx_Frag

    DECLARE @DB INT
    DECLARE @OBJECT INT
    DECLARE @INDEX INT
    DECLARE @FRAG FLOAT
    DECLARE @ACTION VARCHAR(50) = NULL

    IF object_id(‘tempdb..#RPMemps’) IS NOT NULL
    BEGIN
    DROP TABLE #IDX_DFGR
    END

    SELECT /*looks at all db and tables, can be filtered to one db, to find what the frag % and select the ones larger than 10%*/
    ROW_NUMBER() OVER(ORDER BY database_id, object_id, index_id) AS ROW_ID,
    database_id DATABASEID,
    object_id OBJECTID,
    index_id INDEXID,
    avg_fragmentation_in_percent FRAG
    INTO #IDX_DFGR
    FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0

    DECLARE @OBJECTS INT = (SELECT COUNT(OBJECTID) FROM #IDX_DFGR)

    WHILE @OBJECTS > 0
    BEGIN
    SET @DB = (SELECT DATABASEID FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)
    SET @OBJECT = (SELECT OBJECTID FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)
    SET @INDEX = (SELECT INDEXID FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)
    SET @FRAG = (SELECT FRAG FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)

    IF(@FRAG >= 30) /*determine if a rebuild or reorganize is needed*/
    SET @ACTION = ‘REBUILD’
    ELSE
    SET @ACTION = ‘REORGANIZE’

    DECLARE @DBNAME VARCHAR(128) = (SELECT DB_NAME(@DB))

    EXEC(‘USE [‘+@DBNAME+’];

    DECLARE @schemaname nvarchar(130);
    DECLARE @objectname nvarchar(130);
    DECLARE @indexname nvarchar(130);
    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    JOIN sys.schemas as s ON s.schema_id = o.schema_id
    WHERE o.object_id = ‘+@OBJECT+’;

    BEGIN TRY
    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = ‘+@OBJECT+’ AND index_id = ‘+@INDEX+’;
    EXEC (”ALTER INDEX ”+@indexname + ” ON ” +@schemaname+”.”+@objectname+”’+@ACTION+”’)
    /*PRINT(”DB: ‘+ @DBNAME +’ IDX: ”+ @indexname + ” TABLE: ” + @schemaname + ”.”+@objectname+” ACTION: ‘+@ACTION+”’)*//*was used during testing*/
    END TRY
    BEGIN CATCH
    Insert TRACKING..Ck_Idx_Frag
    Select ”’+ @DBNAME +”’ as DBName, object_name(‘+@OBJECT+’) as Tablename, ERROR_MESSAGE() AS ErrorMessage
    END CATCH
    ‘)

    SET @OBJECTS = @OBJECTS – 1
    END

    DROP TABLE #IDX_DFGR

    Select *
    From TRACKING..Ck_Idx_Frag /*error trapping*/

    –Create TABLE TRACKING..Ck_Idx_Frag (DBName NVARCHAR(200), Tablename NVARCHAR(200), ErrorMessage NVARCHAR(MAX))

    • January 30, 2014 at 8:47 am

      I think this is you custom query to accomplish your task. SSIS is just another ways to send command to SQL server. It has real benefits in handling data processing. But for the admin tasks, it just execute commands.

      • Paul
        January 31, 2014 at 12:38 pm

        Hi there, I would like to ask if that is a good exercise if do you have millions of records in a table???

  2. Paul
    January 31, 2014 at 12:37 pm

    Hi there, I would like to ask if that is a good exercise if do you have millions of records in a table???

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: