Sql-server – Are there any (functional or nuanced) differences between rebuilding an index and a full drop/create? If so, what are they

fragmentationindexsql server

I have been working on index fragmentation for indexes that are heavily used and causing performance decreases. Our standard job checks fragmentation levels and reorgs if >5% and <30%, and rebuilds if >30%. As far as I have found these don't seem to be functionally different but I'm being asked if a drop/create is more effective as a client tried this on their own and believes they saw greater improvement. Is this truly possible?

This is comparing a full DROP and CREATE vs ALTER INDEX REBUILD on a non-clustered index.

Best Answer

From Microsoft SQL Server 2012 Internals, page 366

You can completely rebuild an index in several ways. You can use a simple combinination of DROP INDEX followed by CREATE INDEX, but this method is probably the least preferable.

But appeals to authority are boring, so let's test it ourselves!

Performance:

Set up a table with a non-clustered index, load it with junk, and build a table for logging time.

CREATE TABLE IndexTest
(ID int identity(1,1) primary key clustered,
Junk uniqueidentifier)

CREATE TABLE IndexTestLogging
(Operation char(10),
TimeInms int)
GO

INSERT IndexTest
SELECT TOP 1000000 NEWID() AS Junk
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c

CREATE INDEX IX_IndexTest ON IndexTest(Junk)
GO

Now let's build some procs that will DROP/CREATE or REBUILD, plus log (approximately) the time taken:

CREATE PROC usp_DropCreate
AS
DECLARE @t1 datetime,
@t2 datetime

--scramble the table for fragmentation
UPDATE IndexTest
SET Junk = NEWID()

SET @t1 = GETDATE()
DROP INDEX IX_IndexTest ON IndexTest
CREATE INDEX IX_IndexTest ON IndexTest(Junk)
SET @t2 = GETDATE()

INSERT IndexTestLogging
SELECT 'DropCreate' AS Operation, DATEDIFF(millisecond,@t1,@t2) AS TimeInms
GO


CREATE PROC usp_Rebuild
AS
DECLARE @t1 datetime,
@t2 datetime

--scramble the table for fragmentation
UPDATE IndexTest
SET Junk = NEWID()

SET @t1 = GETDATE()
ALTER INDEX IX_IndexTest ON IndexTest REBUILD
SET @t2 = GETDATE()

INSERT IndexTestLogging
SELECT 'Rebuild' AS Operation, DATEDIFF(millisecond,@t1,@t2) AS TimeInms

OK, now we can run each procedure multiple times and gather data

EXEC usp_DropCreate
GO 10

EXEC usp_Rebuild
GO 10

SELECT AVG(
         CASE 
         WHEN Operation = 'DropCreate' THEN TimeInms
         END) AS AvgDropCreate,
       AVG(
         CASE 
         WHEN Operation = 'Rebuild' THEN TimeInms
         END) AS AvgRebuild
FROM IndexTestLogging

Here are my own results: average time in ms for DROP/CREATE: 2547, for REBULD: 1314

It looks like in my contrived example, the winner is REBUILD.

Constraints

What if the index was created to support a constraint? In this case, a simple DROP and CREATE will fail because you have to drop the constraint first. Let's look at the clustered index from the previous example.

--Find out what the clustered index is named - it will be different for you
SELECT name
FROM sys.indexes
WHERE object_id = OBJECT_ID('IndexTest')

--enjoy the error message
DROP INDEX PK__IndexTes__3215078707C12930 ON IndexTest

Note that the other two methods succeed:

CREATE UNIQUE CLUSTERED INDEX PK__IndexTes__3215078707C12930 ON IndexTest(ID)
WITH DROP_EXISTING

ALTER INDEX PK__IndexTes__3215078707C12930 ON IndexTest REBUILD

Of course, like Erik alluded to, your actual problems probably have nothing to do with fragmentation, but hey, this was fun.

Related Question