Post reply

Index defragmentation

  • July 13, 2005 at 10:13 am

    #152467

    Hi, I have one question.

    I would like to defrag indexes not one by one, but all at once by running one script or command.

  • July 13, 2005 at 10:24 am

    #574008

    Search the script section on this site... you'll find what you need.

  • July 13, 2005 at 10:26 am

    #574015

    I assume you mean one command vs multiple commands ?  I'd really suggest you follow the route shown in BOL for showcontig which shows how to do selective rebuilds.

    I use this proc - placed in master - call from any user database to rebuild indexes - for smaller databases.

    create procedure sp_IndexRebuild

    -- ============================================================================ 

    --  Stored Procedure:  sp_IndexRebuild                         

    --  Written by:  Colin Leversuch-Roberts

    --   Kelem Consulting Limited

    --   http://www.kelemconsulting.co.uk

    --   (c) november 2003                                

    --                                                               

    --  Purpose:            To be called from a maint task to enable the rebuilding of indexes on user tables

    --   Rebuilds index with current fill factor ( of index )

    --                                                               

    --  System:  Master Database

    --

    --  Input Paramaters none

    --

    --  Output Parameters:  None                                     

    --  Return Status:  None                                     

    --                                                               

    --  Usage:  Exec dbo.sp_IndexRebuild

    --                                                    

    --  Calls:  dbcc dbreindex

    --

    --  Notes:  By placing this procedure in master it may be called from any database

    --   The procedure will rebuild the user tables in the database the proc is CALLED FROM

    --   See also sp_IndexRebuild100 which forces a 100% fillfactor on index rebuild

    --                                                               

    --  VERSION HISTORY

    --  Version No  Date   Description

    --  1   12th Nov 2003  Initial Release

    --=======================================================================================

    as

    set nocount on

    declare @table1 table (Numkey int identity(1,1),Tname sysname)

    declare @count int, @cmdstring varchar(100)

    --

    -- look no cursor !!

    --

    insert into @table1(Tname)

    select name from dbo.sysobjects with (nolock) where xtype='U'

    set @count=1

    while @count<=(select max(Numkey) from @table1)

    BEGIN

     select @cmdstring=Tname from @table1 where numkey=@count

     print @cmdstring

     dbcc dbreindex(@cmdstring)

     set @count=@count+1

    END

    --endwhile

    GO

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 13, 2005 at 10:26 am

    #574013

    Here Ya go, Thx to mikedouglas

    http://qa.sqlservercentral.com/scripts/contributions/1260.asp

    I'm sure your not interested in reading this but for all to enjoy

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

  • July 14, 2005 at 8:53 am

    #574434

    What about this?:

    exec master..sp_MSforeachtable 'dbcc dbreindex ("?")'



    Bye
    Gabor

  • July 14, 2005 at 3:31 pm

    #574608

    Yup .. but creates a cursor underneath the covers. I did the proc(s) so I could bind it into maint jobs ( without the maint plans ) and you know what they say about undocumented procs < grin >

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 15, 2005 at 1:35 am

    #574686

    Even the maintenance plan is doing a kind of cursor.

    Anyhow you cann't make a reindex without any cursor. Of cours you can generate a script in one shot (what I'm doing in my big databases (SAP) where I cannot run a reindex on all tables at once because there is over 26,000 tables in the database and the DB size itself is over 600GB.

    As far the unducumented features you are absolutly right. It is better to avoid them. I just wanted to show eica a very short and elegant solution for a specific problem



    Bye
    Gabor

  • July 18, 2005 at 12:37 am

    #575144

    thanx to all guys ! And especially to Gabor Nyul.

    I am begginer in sql administering and in SQL using. Each hint is very important for me. thanx

  • July 19, 2005 at 10:55 pm

    #575712

    Hello,

    I inherited a Database where someone set it to reindex all the tables. At least in my db many tables contained static data and reindexing was a waste of time. That was the root of the problem, time. It took so long that it bumped into other tasks that we ran in the off hours. I had to change it to only reindex our volatile tables. Also keep in mind that reindexing a clustered index reindexes all non-clustered indexes at the same time. My predecessor was running 9 reindexes on a very large volatile table when one reindex on the clustered index did it all. We went from hundreds taking many hours to 7 which ran much more quickly.

    Best wishes,

    Barry O'Connell

  • July 20, 2005 at 7:05 am

    #575779

    yup - check out BOL for a starting implementation of how to do selective re-indexing - as you say it's a matter of availble time and complexity of putting a job together which may save you time. I have a selective job which only re-indexes after a %age of fragmentation and ignores the secondary indexes if the clustered index needs a rebuild .. it does save time, about 50%, but also requires a total dbcc showcontig ( which takes time ) - you win some , you lose some < grin >

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 10 posts - 1 through 9 (of 9 total)

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