DBCC DBREINDEX in next versions | SansSQL

Sunday, October 11, 2009

DBCC DBREINDEX in next versions

DBCC DBREINDEX rebuilds one or more indexes for a table in the specified database.
This was the command which we used to rebuild the indexes.
This feature will be removed in the next version (after 10.0 or SQL Server 2008) of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Alter INDEX command to reorganize or rebuild indexes instead.
Below query would reorganize the table if table fragementation is between 10.0 to 30.0 and Rebuild the index if table fragementation is above 30.0


use DB_name
go
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);
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #Temp
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 partitions CURSOR FOR SELECT * FROM #Temp

OPEN 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;
        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 partitions;
DEALLOCATE partitions;
DROP TABLE #Temp
GO


Note: Rebuilding a clustered index does not rebuild associated non-clustered indexes unless the keyword ALL is specified



When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.
USE DB_name;
GO
ALTER INDEX ALL ON schema.table_name
REBUILD WITH (SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO


This piece of information was shared to me by Kumaravyas. 
Thanks to Kumaravyas for sharing this useful info.

5 comments:

Ash said...

by the way dbcc dbreindex still works in sql 2008.

Sandesh Segu said...

DBCC DBREINDEX still works in SQL 2008 is only for backward compatibility and is not recommended to use.

Anonymous said...

You shouldn't need to use QUOTENAME() in your variables prior to building your SQL command string. Actually, it will only make the command fail because of the quotes.

Simply insert your variables in the string without quotes and it should be fine.

Brave Technologies said...

Nice Blog. Thank you for Sharing. We are the best erp software providers in chennai. For more details call +91 9677025199.
erp solutions provider in chennai
erp for automotive industry chennai

Huongkv said...

Mua vé tại đại lý vé máy bay Aivivu, tham khảo

vé máy bay đi Mỹ khứ hồi

lịch bay từ california về việt nam

chuyến bay thương mại từ nhật về việt nam

bao giờ có chuyến bay từ đức về việt nam

khi nào có chuyến bay từ canada về việt nam

Máy bay từ Hàn Quốc về Việt Nam

khách sạn cách ly

chuyến bay chuyên gia về việt nam

Post a Comment

Ads