SQL Server Performance

Reindexing script making my applications hang

Discussion in 'SQL Server 2005 General DBA Questions' started by rahul_in05, Apr 27, 2011.

  1. rahul_in05 New Member

    I am using the following reindexing script to reindex my database
    -------------------------------------------------------------USE MASTERGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE Re_Index_Table @Db_Name as varchar(100),@Table_Name as varchar(100),@Index_Name as varchar(100),@Option_Name as varchar(100) ASBEGIN-- 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)ENDGO--------------------------------------------------------------------------------------------------------------DECLARE @STABLE_NAME VARCHAR(100)DECLARE @SINDEX_NAME VARCHAR(100)DECLARE @SOPTION_NAME VARCHAR(100)DECLARE @Pages IntDECLARE @DATABASE_NAME VARCHAR(100)DECLARE Index_List CURSOR FORWARD_ONLYFOR SELECT --ps.database_id, ps.OBJECT_ID,--ps.index_id, --ps.avg_fragmentation_in_percentOBJECT_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 TotalPageFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS psINNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_IDAND ps.index_id = b.index_idWHERE ps.database_id = DB_ID() AND b.name IS NOT NULL AND ps.avg_fragmentation_in_percent > 10ORDER BY TotalPage desc SELECT @DATABASE_NAME=DB_NAME()PRINT @DATABASE_NAMEOPEN Index_ListFETCH NEXT FROM Index_ListINTO @STABLE_NAME, @SINDEX_NAME, @SOPTION_NAME,@PagesHi,WHILE @@FETCH_STATUS = 0BEGINEXECUTE 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_ListINTO @STABLE_NAME, @SINDEX_NAME, @SOPTION_NAME,@PagesENDCLOSE Index_ListDEALLOCATE 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. Luis Martin Moderator

    Could you reformat your query please?
  3. rahul_in05 New Member

    Hi,I am unable to reformat. It's formatted actually, displaying properly in the compose textbox but not displaying properly after posting.
  4. FrankKalis Moderator

  5. Jahanzaib Member

    what is the default fillfactor of the Server configuration
    select * from sys.configurations
    or execute
    exec sp_configure 'show advanced option',1
    go
    reconfigure
    go
    exec sp_configure
    go
    what you are providing fillfactor for the specific index in your script
  6. satya Moderator

    How big is the user database(s)?
    How big is the TEMPDB?
    What is the available free space on the drives where user databases and TEMPDB is located?

Share This Page