Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Unanswered: Reindexing script making my applications hang

    Hi All,

    I am using the following reindexing script to reindex my database

    -------------------------------------------------------------
    USE MASTER
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE Re_Index_Table
    @Db_Name as varchar(100),
    @Table_Name as varchar(100),
    @Index_Name as varchar(100),
    @Option_Name as varchar(100)
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    DECLARE @sSQL as Varchar(1000)
    SET NOCOUNT ON;

    SET @sSQL = 'ALTER INDEX ' + @Index_Name + ' ON ' + @Db_Name + '.dbo.' + @Table_Name + ' ' + @Option_Name
    Exec (@sSQL)

    END
    GO

    --------------------------------------------------------

    ------------------------------------------------------

    DECLARE @STABLE_NAME VARCHAR(100)
    DECLARE @SINDEX_NAME VARCHAR(100)
    DECLARE @SOPTION_NAME VARCHAR(100)
    DECLARE @Pages Int
    DECLARE @DATABASE_NAME VARCHAR(100)

    DECLARE Index_List CURSOR FORWARD_ONLY
    FOR
    SELECT
    --ps.database_id, ps.OBJECT_ID,
    --ps.index_id,
    --ps.avg_fragmentation_in_percent
    OBJECT_NAME(B.OBJECT_ID) as TableName, b.name as Index_Name,
    CASE
    WHEN ps.avg_fragmentation_in_percent > 30 THEN 'REBUILD WITH (FILLFACTOR =80,ONLINE=ON,SORT_IN_TEMPDB = ON)' --FOR ONLINE ADD USE THIS 'REBUILD WITH(ONLINE = ON)'
    ELSE 'REORGANIZE'
    END AS INDEX_OPTION,
    ps.Page_count as TotalPage
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
    AND ps.index_id = b.index_id
    WHERE ps.database_id = DB_ID() AND b.name IS NOT NULL AND ps.avg_fragmentation_in_percent > 10
    ORDER BY TotalPage desc

    SELECT @DATABASE_NAME=DB_NAME()
    PRINT @DATABASE_NAME

    OPEN Index_List

    FETCH NEXT FROM Index_List
    INTO @STABLE_NAME, @SINDEX_NAME, @SOPTION_NAME,@Pages
    Hi,



    WHILE @@FETCH_STATUS = 0
    BEGIN

    EXECUTE MASTER.DBO.Re_Index_Table @Db_Name=@DATABASE_NAME , @Table_Name=@STABLE_NAME, @Index_Name=@SINDEX_NAME, @Option_Name= @SOPTION_NAME

    PRINT @STABLE_NAME +' , ' + @SINDEX_NAME +' , '+ @SOPTION_NAME + ' INDEX COMPLETED'
    FETCH NEXT FROM Index_List
    INTO @STABLE_NAME, @SINDEX_NAME, @SOPTION_NAME,@Pages
    END

    CLOSE Index_List

    DEALLOCATE Index_List

    ----------------------------------


    But its making my applications hang while this script is executing, even if I have used the online option on during index rebuild.

    Could anyone please suggest , how to resolve this issue.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    Use SQL BI Development Tool and then create SSIS package then go to Tool and create REBUILD INDEXES task and then double click on it then set the connection and database then click on view script then just copy this whole script and then rebuild indexes at a time as you want,check attachement for steps

    and that is a script for rebuild indexes

    SQL Server Automate Indexes Rebuilding A|U|R|E|U|S – S|A|L|A|H

    Regards,

    Syed Jahanzaib Bin Hassan
    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    A|U|R|E|U|S – S|A|L|A|H
    Attached Thumbnails Attached Thumbnails SSIS_1.jpg   SSIS_2.jpg   SSIS_3.jpg  

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This code:
    "FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps"
    ..****ns an analysis against every single index in every table in your database. On a large database it will churn for quite a while and eat up a massive amount of resources. The underlying code for that system function is not efficient. You should provide specific table names to the function, and only run it against tables which are known to be performance bottlenecks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •