SQL Server Performance

Drop and Recreate all indexes

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by JerrySommerville, Oct 20, 2006.

  1. JerrySommerville New Member

    I have a need to drop and then recreate all indexes in a database. I have tried DBCC DBREINDEX to clean up fragmentation, but now I have been asked to drop and recreate all indexes. Is there a script out there that can do this. I need the script if it exists. I am a newbie and not very good at coding SQL yet.
  2. Luis Martin Moderator

    My 2 cents.

    select 'drop ' + case when left(si.Name,3) in ('ixc') then 'index ' else 'statistics ' end + so.name
    + '.' + '[' + si.name + ']'
    /* si.rows as 'filas', SO.Name as Tabla, SI.name as 'Index', SFG.groupname as 'Filegroup' */
    from sysobjects as SO
    join sysindexes as SI
    on SO.Id = SI.id
    join sysfilegroups as SFG
    on SI.GroupId = SFG.GroupId
    where left(SI.Name,4) not in ('_WA_') and left(si.Name,3) in ('ixc') or left(si.Name,3) in ('hin')
    order by SO.Name , SI.name, SFG.GroupName

    This is to drop all indexes and statistics created by me. All begin with IXC. You can work with something like that.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. MohammedU New Member

    You can make use of the following script from MS...

    -- Ensure a USE <databasename> statement has been executed first.
    SET NOCOUNT ON;
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @partitioncount bigint;
    DECLARE @schemaname nvarchar(130);
    DECLARE @objectname nvarchar(130);
    DECLARE @indexname nvarchar(130);
    DECLARE @partitionnum bigint;
    DECLARE @partitions bigint;
    DECLARE @frag float;
    DECLARE @command nvarchar(4000);
    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
    -- and convert object and index IDs to names.
    SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
    INTO #work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

    -- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

    -- Open the cursor.
    OPEN partitions;

    -- Loop through the partitions.
    WHILE (1=1)
    BEGIN;
    FETCH NEXT
    FROM partitions
    INTO @objectid, @indexid, @partitionnum, @frag;
    IF @@FETCH_STATUS < 0 BREAK;
    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    JOIN sys.schemas as s ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;
    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid AND index_id = @indexid;
    SELECT @partitioncount = count (*)
    FROM sys.partitions
    WHERE object_id = @objectid AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
    IF @frag >= 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
    IF @partitioncount > 1
    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
    EXEC (@command);
    PRINT N'Executed: ' + @command;
    END;

    -- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;

    -- Drop the temporary table.
    DROP TABLE #work_to_do;
    GO

  4. Roji. P. Thomas New Member

  5. jezemine New Member

    this tool will create drop/create scripts for all objects, including indexes, with a separate file for each.

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17482

    it would be a simple matter to write a .bat file to execute each generated file using osql/sqlcmd.



    SqlSpec - a fast, cheap, and comprehensive data dictionary generator
    for SQL Server 2000 and 2005 and Analysis Server 2005 - www.elsasoft.org
  6. satya Moderator

    What is the reason behind stating you need to drop & recreate all the indexes?
    I don't think it is necessary to do so, you can reindex using DBCC REINDEX statement on indexes (tables) that are used frequently.

    quote:Originally posted by JerrySommerville

    I have a need to drop and then recreate all indexes in a database. I have tried DBCC DBREINDEX to clean up fragmentation, but now I have been asked to drop and recreate all indexes. Is there a script out there that can do this. I need the script if it exists. I am a newbie and not very good at coding SQL yet.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  7. MohammedU New Member

    Some people think drop and create will give better improvement than REINDEX<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />If the clustered index is not unique then only non-clustered indexes gets recreated other wise it create only clustered index.<br /><br />Mohammed.
  8. satya Moderator

    Then I would ask them to refer to Books online for a peace of mind, it is nothing but wasting the resource.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page