Script related to DB Index Tune

 

When the data is modified (INSERT, UPDATE or DELETE) the table fragmentation can occurs. To rebuild the index again we used the DBCC DBREINDEX statements can used to rebuilds all the indexes of the table.

The syntax is mentioned bellow

DBCC DBREINDEX

(

        table_name

    [ , index_name [ , fillfactor ] ]

)

    [ WITH NO_INFOMSGS ]

 

table_name

Is the name of the table for which to rebuild the specified index or indexes.

Index_name

Is the name of the index to rebuilds. If the index is not specified it means all the index is going to rebuild.

fillfactors

 is the percentage (%) of space on each index page used storing data when index is rebuild.

WITH NO_INFOMSGS

Suppresses all informational messages that have severity levels from 0 through 10.

sp_updatestats

is used to run the update statistics against all user-defined and internal tables of the current database.

Using this technique, I am trying to create a stored procedure that retunes your index. The stored procedure is ready to use.

The parameters @P_TBLNAME

 If you supply the specified table name, it retunes all the index of the supplied table objects and updates the statistics. If not then it works with all table objects of the current database.

/*

   To Tune the Database. Reindexing The Entire DB

  

   Date: 31-March-2012

   by : joydeep Das

  

  

   EXEC up_RUN_REINDEXTUEN

        @P_TBLNAME=''

       

       

*/

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_RUN_REINDEXTUEN')

      BEGIN

            DROP  PROCEDURE  up_RUN_REINDEXTUEN

      END

 

GO

 

CREATE Procedure [dbo].[up_RUN_REINDEXTUEN]

      (

            @P_TBLNAME  VARCHAR(MAX) =    NULL   

    )

AS

    DECLARE  @v_TblName VARCHAR(MAX)

BEGIN

        -- Cursor Declerations

        IF ISNULL(@P_TBLNAME,'')=''

           BEGIN

                        DECLARE db_cursor CURSOR FOR

                                    SELECT name

                                    FROM    sys.sysobjects

                                    WHERE   xtype='U'

                                                AND name LIKE @P_TBLNAME+'%'

               END

            ELSE

               BEGIN

                    DECLARE db_cursor CURSOR FOR

                                    SELECT name

                                    FROM    sys.sysobjects

                                    WHERE   xtype='U'

                                                AND name = @P_TBLNAME

               END                                   

        -- Cursor Open

        OPEN db_cursor

        -- Fatching the Cursor 

        FETCH NEXT FROM db_cursor

                   INTO @v_TblName

 

        WHILE @@FETCH_STATUS = 0  

                    BEGIN

                    PRINT 'Reindexing Table:  ' + @v_TblName

                    DBCC DBREINDEX(@v_TblName, '', 80)

                   

                    FETCH NEXT FROM db_cursor

                               INTO @v_TblName

              END

                      

       CLOSE db_cursor  

         DEALLOCATE db_cursor

        

         -- update teh Statistics of the Table After Reindex.

         EXEC sp_updatestats

END

 

GO

 

To execute

EXEC up_RUN_REINDEXTUEN

        @P_TBLNAME='My_Table'

 

 

OR

 

EXEC up_RUN_REINDEXTUEN

        @P_TBLNAME=''

 

 

Hope you like that.

 

Posted by: MR. JOYDEEP DAS