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:-
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)
============================
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
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
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:-
- How to Enlarge AdventureWorks database
- Index maintenance options available in SQL 2017
- How to use index_resumable_operations
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
