Post reply

Automatically rebuild large indexes

  • October 12, 2009 at 10:20 am

    #215440

    Hello,

    I have the following t-SQL code in which I am trying to loop though all indexes which are fragmented greater than 50% and rebuild them. It appears as if the alter index statement does not work because of how I am referencing the table. Is it not possible to automate this? Or is my syntax off?

    Error: Msg 1088, Level 16, State 9, Line 27

    Cannot find the object "@tblnm" because it does not exist or you do not have permissions.

    USE [mydb];

    declare

    @tblnm as varchar(150),

    @ndxnm as varchar(150),

    @fragrate as float;

    declare ndxs cursor fast_forward for

    SELECT

    TableName = OBJECT_NAME(s.[object_id])

    ,IndexName = i.name

    ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)

    FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

    INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

    AND s.index_id = i.index_id

    WHERE s.database_id = DB_ID()

    AND i.name IS NOT NULL -- Ignore HEAP indexes.

    and ROUND(avg_fragmentation_in_percent,2) > 50

    ORDER BY [Fragmentation %] DESC;

    open ndxs

    fetch next from ndxs into @tblnm, @ndxnm, @fragrate;

    while @@FETCH_STATUS = 0

    begin

    select @tblnm, @ndxnm, @fragrate;

    ALTER INDEX [@ndxnm] ON [@tblnm] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = NONE )

    fetch next from ndxs into @tblnm, @ndxnm, @fragrate;

    end

    close ndxs;

    deallocate ndxs;

    Thank you,

    Christine

  • October 12, 2009 at 10:38 am

    #1064824

    You need to use dynamic SQL here.

    Extract of code that needs to change

    ...

    SET @sSQL = 'ALTER INDEX [' + @ndxnm + '] ON [' + @tblnm + '] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = NONE )'

    EXEC (@sSQL)

    fetch next ....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 12, 2009 at 11:52 am

    #1064862

    Great! Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply