/ notes / sql

Sql Server : Indexes rebuild tests

Here some tests I have done to better understand the behavior of indexes rebuild in SQL Server.

I used the database ContosoDataGeneratorV2 size “10M”. I added the extra table [Customer2] : it is a clone (structure+data) of [Customer]. It is not referenced by other tables and has 4 indexes on GeoAreaKey, GivenName, StartDT and EndDT.

Here’s the indexes involved in the tests (script at the end of the page for creating the report):

SchemaName  TableName  IndexName                IndexType     IndexSizeMB
-------------------------------------------------------------------------
Data        Customer   PK_Customer              CLUSTERED          610.90
Data        Sales      IX_Sales_CustomerKey     NONCLUSTERED       450.85
Data        Sales      IX_Sales_ProductKey      NONCLUSTERED       450.85
Data        Sales      IX_Sales_StoreKey        NONCLUSTERED       450.85
Data        Sales      PK_Sales                 CLUSTERED         1764.11
Data        Customer2  IX_Customer2_EndDT       NONCLUSTERED        27.67
Data        Customer2  IX_Customer2_GeoAreaKey  NONCLUSTERED        22.78
Data        Customer2  IX_Customer2_GivenName   NONCLUSTERED        48.52
Data        Customer2  IX_Customer2_StartDT     NONCLUSTERED        27.67
Data        Customer2  PK_Customer2             CLUSTERED          612.78

I executed the testing queries Q1…Q12 measuring the space used on the MDF and LDF during the executions. To do that, I wrote a piece of C# to measure the MDF and LDF file usage. The database has been set to SIMPLE mode. The results are below.

ID	Index (*)	Action	IndexSizeMB	UsedMDF_MB	UsedLDF_MB	FinalDeltaMDF
Q1	IX_Sales_CustomerKey	REBUILD	450	446	476	0
Q2	IX_Sales_StoreKey	REBUILD	450	423	455	0
Q3	PK_Sales	REBUILD	1764	1764	1886	0
Q4	PK_Sales	DROP	1764	1120	1200	-249
Q5	PK_Sales	ADD	1764	3100	3400	+249
Q6	(FK_Sales_Customers)	DROP	-	0	0	0
Q7	PK_Customer	DROP	610	0	0	0
Q8	PK_Customer	ADD	610	610	660	0
Q9	(FK_Sales_Customers)	ADD	-	0	0	0
Q10	PK_Customer	REBUILD	610	593	637	0
Q11	PK_Customer2	DROP	610	132	120	0
Q12	PK_Customer2	ADD	610	738	789	0

Key findings:

MDF space is used and then released at the end of the operation.
LDF space is released if database is in SIMPLE recovery mode. ⚠
Rebuilding an index requires the double of the space of the index itself.
Dropping a clustered index requires the double of the space of the other indexes.
Adding a clustered index requires the size of the heap table plus all the other indexes.
To stay safe, add at least 25%-50% to the value you calculate.

Some charts

Below some charts that highlight the variation of MDF and LDF files during the execution of the commands.

Q2

Q5

Q12

Scripts

Testing commands

CHECKPOINT
GO

-- Q1
--ALTER INDEX [IX_Sales_CustomerKey] ON [Data].[Sales] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

-- Q2
--ALTER INDEX [IX_Sales_StoreKey] ON [Data].[Sales] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

-- Q3
--ALTER INDEX [PK_Sales] ON [Data].Sales REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

-- Q4
--ALTER TABLE [Data].[Sales] DROP CONSTRAINT [PK_Sales] WITH ( ONLINE = OFF )
 
-- Q5
-- ALTER TABLE [Data].[Sales] ADD CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED ([OrderKey] ASC,[LineNumber] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

-- Q6
-- ALTER TABLE [Data].[Sales] DROP CONSTRAINT [FK_Sales_Customers]

-- Q7
-- ALTER TABLE [Data].[Customer] DROP CONSTRAINT [PK_Customer] WITH ( ONLINE = OFF )

-- Q8
-- ALTER TABLE [Data].[Customer] ADD  CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED  ([CustomerKey] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

-- Q9
-- ALTER TABLE [Data].[Sales] WITH NOCHECK ADD  CONSTRAINT [FK_Sales_Customers] FOREIGN KEY([CustomerKey]) REFERENCES [Data].[Customer] ([CustomerKey])
-- ALTER TABLE [Data].[Sales] CHECK CONSTRAINT [FK_Sales_Customers]

-- Q10
-- ALTER INDEX [PK_Customer] ON [Data].[Customer] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

-- Q11
-- ALTER TABLE [Data].[Customer2] DROP CONSTRAINT [PK_Customer2] WITH ( ONLINE = OFF )

-- Q12  
-- ALTER TABLE [Data].[Customer2] ADD CONSTRAINT [PK_Customer2] PRIMARY KEY CLUSTERED (CustomerKey,GeoAreaKey)  WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CHECKPOINT
GO

Show index size

SELECT   
    sc.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    SUM(s.used_page_count) * 8 / 1024.0 AS IndexSizeMB
FROM sys.dm_db_partition_stats  AS s
    JOIN sys.indexes  AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    JOIN sys.tables AS t ON s.object_id=t.object_id
    JOIN sys.schemas AS sc ON sc.schema_id = t.schema_id
GROUP BY 
    sc.name,t.name,i.name,i.type_desc
ORDER BY 
    sc.name,t.name,i.name,i.type_desc