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

Unused Indexes Might Not Be

Tara Kizer has a warning for people eager to drop “unused” indexes: About 10 years ago, I decided to drop an unused index on a table that had 2 billion rows. The database was around 7TB in size. We were having storage and performance issues. I thought I could help the system out if I […]

Read More

Hybrid Columnstore And B+ Tree Designs

Adrian Colyer reviews a Microsoft paper on the combination of columnstore and B+ tree indexes on a single table: The authors conducted a series of microbenchmarks as follows: scans with single predicates with varying selectivity to study the trade-off between the range scan of a B+ tree vs a columnstore scan sort and group-by queries […]

Read More

Categories

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