Pausing Online Index Rebuilds

Andrew Pruski demos resumable online index rebuilds in SQL Server 2017:

Pretty cool, huh? Full information on this can be found here: –https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

I think this is very useful but we do need to be careful. The documentation says that pausing an online index rebuild for a long time may affect query performance and disk utilisation. This is due to the newly rebuild index being created side-by-side to the original one so we’ll need to watch out for that.

It’s a good first look at what might be a very interesting solution for companies tight on maintenance window time.

Related Posts

Smarter Indexes Based On Column Cardinality

Eric Blinn has a function which organizes columns in the missing index DMV by cardinality: Bryan Rebok and Brent Ozar recently opened my eyes to something I didn’t know.  When SQL Server recommends missing indexes to you it puts the columns in order in which they are found in the table.  That’s it.  I always thought […]

Read More

Don’t Forget Those Paused Indexes

Arun Sirpal tries to create a new index on his Azure SQL Database: I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code: CREATE NONCLUSTERED INDEX [dbo.NCI_Time] ON [dbo].[Audit] ([UserId]) INCLUDE ([DefID],[ShopID]) Msg 10637, Level 16, State 3, Line 7 Cannot […]

Read More

Categories

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930