Does creating indexes require downtime

Question

We have a table with few million rows on production. We ran this query and it froze our database,

USE ExampleDatabase

GO
CREATE NONCLUSTERED INDEX IX_Table1_URL
ON [dbo].[Table1] ([URL])
GO

GO
CREATE NONCLUSTERED INDEX IX_Table1_LastModified
ON [dbo].[Table1] ([LastModified])
GO

GO
CREATE NONCLUSTERED INDEX IX_Table2_URL
ON [dbo].[Table2] ([URL])
GO

GO
CREATE NONCLUSTERED INDEX IX_Table2_LastModified2
ON [dbo].[Table2] ([LastModified2])
GO

I am a developer with very limited knowledge of indexes, I am not sure where to start, just to add both Table1 and Table2 have million of rows.

score 12 · Accepted Answer · answered Feb 05 '18 at 10:54

If you're on SQL Server Enterprise Edition, you can add the ONLINE switch:

CREATE NONCLUSTERED INDEX IX_Table1_URL
ON [dbo].[Table1] ([URL])
WITH (ONLINE=ON);
GO

Most of the index creation will happen without blocking, although not all - read this DBA.se question & answer for details about when even online index creation blocks other queries.

Your server may still be much slower during the index creation, though - creating indexes can be hard work, taxing your SQL Server's storage.

Does creating indexes require downtime

1 Answers1