How to Enlarge AdventureWorks DB & Pause-Resuming-Abort online index rebuilds in SQL server 2017

How to  Enlarge AdventureWorks DB & SQL Server 2017 Resumable Online Index Rebuilds


Problem:-

Rebuilding indexes on large tables can sometimes take quite a while to complete, and if an index rebuild fails for any reason, such as a failover occurring or a disk runs out of available space, then the entire process would normally need to start all over again at the beginning.

SQL Server 2017 fixes this problem by enabling an option to pause, and then later resume, online index rebuilds without losing any progress.


Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article.

Here we will Discuss:-

1. How to Enlarge AdventureWorks database
2. Index maintenance options available in SQL 2017
3. How to use index_resumable_operations

lets Start

First download AdventureWorks2014 database from below link:-


https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks



Restore adventureworks2014  database
Change compatibility level to 140.

and then execute script provided below, this will Enlarge AdventureWorks database..

USE AdventureWorks2014;
GO



IF OBJECT_ID('Sales.SalesOrderHeaderEnlarged') IS NOT NULL

 DROP TABLE Sales.SalesOrderHeaderEnlarged;

GO



CREATE TABLE Sales.SalesOrderHeaderEnlarged
 (
 SalesOrderID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,
 RevisionNumber tinyint NOT NULL,
 OrderDate datetime NOT NULL,
 DueDate datetime NOT NULL,
 ShipDate datetime NULL,
 Status tinyint NOT NULL,
 OnlineOrderFlag dbo.Flag NOT NULL,
 SalesOrderNumber  AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***')),
 PurchaseOrderNumber dbo.OrderNumber NULL,
 AccountNumber dbo.AccountNumber NULL,
 CustomerID int NOT NULL,
 SalesPersonID int NULL,
 TerritoryID int NULL,
 BillToAddressID int NOT NULL,
 ShipToAddressID int NOT NULL,
 ShipMethodID int NOT NULL,
 CreditCardID int NULL,
 CreditCardApprovalCode varchar(15) NULL,
 CurrencyRateID int NULL,
 SubTotal money NOT NULL,
 TaxAmt money NOT NULL,
 Freight money NOT NULL,
 TotalDue  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),
 Comment nvarchar(128) NULL,
 rowguid uniqueidentifier NOT NULL ROWGUIDCOL,
 ModifiedDate datetime NOT NULL
 )  ON [PRIMARY]
GO

SET IDENTITY_INSERT Sales.SalesOrderHeaderEnlarged ON
GO

INSERT INTO Sales.SalesOrderHeaderEnlarged (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)

SELECT SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
FROM Sales.SalesOrderHeader WITH (HOLDLOCK TABLOCKX)
GO
SET IDENTITY_INSERT Sales.SalesOrderHeaderEnlarged OFF
GO
ALTER TABLE Sales.SalesOrderHeaderEnlarged ADD CONSTRAINT
 PK_SalesOrderHeaderEnlarged_SalesOrderID PRIMARY KEY CLUSTERED
 (
 SalesOrderID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderEnlarged_rowguid ON Sales.SalesOrderHeaderEnlarged
 (
 rowguid
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderEnlarged_SalesOrderNumber ON Sales.SalesOrderHeaderEnlarged
 (
 SalesOrderNumber
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_CustomerID ON Sales.SalesOrderHeaderEnlarged
 (
 CustomerID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_SalesPersonID ON Sales.SalesOrderHeaderEnlarged
 (
 SalesPersonID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
IF OBJECT_ID('Sales.SalesOrderDetailEnlarged') IS NOT NULL
 DROP TABLE Sales.SalesOrderDetailEnlarged;
GO
CREATE TABLE Sales.SalesOrderDetailEnlarged
 (
 SalesOrderID int NOT NULL,
 SalesOrderDetailID int NOT NULL IDENTITY (1, 1),
 CarrierTrackingNumber nvarchar(25) NULL,
 OrderQty smallint NOT NULL,
 ProductID int NOT NULL,
 SpecialOfferID int NOT NULL,
 UnitPrice money NOT NULL,
 UnitPriceDiscount money NOT NULL,
 LineTotal  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
 rowguid uniqueidentifier NOT NULL ROWGUIDCOL,
 ModifiedDate datetime NOT NULL
 )  ON [PRIMARY]
GO
SET IDENTITY_INSERT Sales.SalesOrderDetailEnlarged ON
GO
INSERT INTO Sales.SalesOrderDetailEnlarged (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
FROM Sales.SalesOrderDetail WITH (HOLDLOCK TABLOCKX)
GO
SET IDENTITY_INSERT Sales.SalesOrderDetailEnlarged OFF
GO
ALTER TABLE Sales.SalesOrderDetailEnlarged ADD CONSTRAINT
 PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID PRIMARY KEY CLUSTERED
 (
 SalesOrderID,
 SalesOrderDetailID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderDetailEnlarged_rowguid ON Sales.SalesOrderDetailEnlarged
 (
 rowguid
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderDetailEnlarged_ProductID ON Sales.SalesOrderDetailEnlarged
 (
 ProductID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
BEGIN TRANSACTION
DECLARE @TableVar TABLE
(OrigSalesOrderID int, NewSalesOrderID int)
INSERT INTO Sales.SalesOrderHeaderEnlarged
 (RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag,
  PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID,
  BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID,
  CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment,
  rowguid, ModifiedDate)
OUTPUT inserted.Comment, inserted.SalesOrderID
 INTO @TableVar
SELECT RevisionNumber, DATEADD(dd, number, OrderDate) AS OrderDate,
  DATEADD(dd, number, DueDate),  DATEADD(dd, number, ShipDate),
 Status, OnlineOrderFlag,
  PurchaseOrderNumber,
  AccountNumber,
  CustomerID, SalesPersonID, TerritoryID, BillToAddressID,
  ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode,
  CurrencyRateID, SubTotal, TaxAmt, Freight, SalesOrderID,
  NEWID(), DATEADD(dd, number, ModifiedDate)
FROM Sales.SalesOrderHeader AS soh WITH (HOLDLOCK TABLOCKX)
CROSS JOIN (
  SELECT number
  FROM ( SELECT TOP 10 number
    FROM master.dbo.spt_values
    WHERE type = N'P'
      AND number < 1000
    ORDER BY NEWID() DESC
   UNION
    SELECT TOP 10 number
    FROM master.dbo.spt_values
    WHERE type = N'P'
      AND number < 1000
    ORDER BY NEWID() DESC
   UNION
    SELECT TOP 10 number
    FROM master.dbo.spt_values
    WHERE type = N'P'
      AND number < 1000
    ORDER BY NEWID() DESC
   UNION
    SELECT TOP 10 number
    FROM master.dbo.spt_values
    WHERE type = N'P'
      AND number < 1000
    ORDER BY NEWID() DESC
    ) AS tab
) AS Randomizer
ORDER BY OrderDate, number
INSERT INTO Sales.SalesOrderDetailEnlarged
 (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID,
  SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
SELECT
 tv.NewSalesOrderID, CarrierTrackingNumber, OrderQty, ProductID,
 SpecialOfferID, UnitPrice, UnitPriceDiscount, NEWID(), ModifiedDate
FROM Sales.SalesOrderDetail AS sod
JOIN @TableVar AS tv
 ON sod.SalesOrderID = tv.OrigSalesOrderID
ORDER BY sod.SalesOrderDetailID
COMMIT

/* it will take 8~10 minutes

output would be like:-


(31465 row(s) affected)

(121317 row(s) affected)

(1195670 row(s) affected)

(4610046 row(s) affected)

Resuming online index rebuilds

============================

USE [AdventureWorks2014]
GO
ALTER INDEX [PK_SalesOrderHeaderEnlarged_SalesOrderID] ON [Sales].[SalesOrderHeaderEnlarged] REBUILD PARTITION = ALL
WITH
 (PAD_INDEX = OFF,
  STATISTICS_NORECOMPUTE = OFF,
   SORT_IN_TEMPDB = OFF,
  ONLINE = ON,RESUMABLE=ON,
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

if you want to pause


on another tab we can execute below query..



USE [AdventureWorks2014]

GO

ALTER INDEX [PK_SalesOrderHeaderEnlarged_SalesOrderID] ON [Sales].[SalesOrderHeaderEnlarged] pause



select * from sys.index_resumable_operations



concentrate on 3 column

1. State_description
2. %complete
3. sql_text



If you want to resume it again



go to another tab and execute the command



USE [AdventureWorks2014]

GO

ALTER INDEX [PK_SalesOrderHeaderEnlarged_SalesOrderID] ON [Sales].[SalesOrderHeaderEnlarged] resume



 If you want to abort


USE [AdventureWorks2014]
GO
ALTER INDEX [PK_SalesOrderHeaderEnlarged_SalesOrderID] ON [Sales].[SalesOrderHeaderEnlarged] abort

We have another option here max_duration while rebuilding index

Coming to MAX_DURATION

USE [AdventureWorks2014]
GO

ALTER INDEX [PK_SalesOrderHeaderEnlarged_SalesOrderID] ON [Sales].[SalesOrderHeaderEnlarged] REBUILD PARTITION = ALL
WITH
 (PAD_INDEX = OFF,
  STATISTICS_NORECOMPUTE = OFF,
   SORT_IN_TEMPDB = OFF,
 ONLINE = ON,RESUMABLE=ON,max_duration = 10 minutes,
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Thanks for Reading