Yet another SQL Server enthusiast

2017-08-31

SQL Server 2017 New Features: Resumable Index Operations

Filed under: Uncategorized — sa @ 8:06 AM

SQL Server 2017 comes with a new functionality that allows to pause and resume online index operations (INDEX REBUILD) including resuming after a failure.

Example:

-- Start index rebuild
ALTER INDEX PK_FactSalesOnline on dbo.FactSalesOnline REBUILD 
WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) 
-- Pause
ALTER INDEX PK_FactSalesOnline on dbo.FactSalesOnline PAUSE
-- Resume index rebuild
ALTER INDEX PK_FactSalesOnline on dbo.FactSalesOnline RESUME
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: