About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!
Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

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






How to detect said database is primary Replica or Are You the Primary Replica? in SQL Server 2014 and SQL 2016

Are You the Primary Replica? in SQL Server 2014 and SQL 2016


-----------------------------------------------------------------------------------------------------------------------

How to identify whether or not a database on the current server is the primary or secondary replica.

The good news is on  SQL Server 2014 / SQL Server 2016  has included a DMO for same and the name is sys.fn_hadr_is_primary_replica 

Declare @Status int
SELECT  @Status=sys.fn_hadr_is_primary_replica ('AdventureWorks2014');
print @Status
if @Status = 1 
  begin
  print 'database on the current instance is the primary replica'
  end
  else
    begin
  if @Status =0
         begin
     print 'database on the current instance is not in primary replica'
  end
           else
             begin
                if @Status is NULL
                   begin
                     print 'Kindly provide correct database Name'
                   end

          end
 end


In SQL Server 2012 you can create this function and use for finding database is part of Primary Replica.
--------------------------------------------------------------------------------------------------------------------------------------------------------

USE [master]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_is_database_primary_replica](@database_name sysname) returns bit as

begin


declare

@is_primary bit

declare

@is_role_primary tinyint 

select @is_role_primary =role

from sys.dm_hadr_availability_replica_states where replica_id=

(select replica_id from sys.dm_hadr_database_replica_states where

database_id=DB_ID(@database_name)  and is_local = 1 ); 

If @is_role_primary = 1               


set @is_primary = 1


else


set @is_primary = 0


return @is_primary


End 

------------------------------------------------------------------------------------------------------------------------------------------------------------------


/* Another script does not require to put a database Name */



if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U') 

  and o.id = object_id(N'tempdb..#AGdetails')
)
begin
DROP TABLE #AGdetails;
end;


declare @rd varchar(10)
IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT

   AGC.name -- Availability Group
, RCS.replica_server_name -- SQL cluster node name
, ARS.role_desc  -- Replica Role
, AGL.dns_name  -- Listener Name

into #AGdetails
FROM
sys.availability_groups_cluster AS AGC
  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
   ON
    RCS.group_id = AGC.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
   ON
    ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL
   ON
    AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'PRIMARY'
END

select @rd=role_desc from #AGdetails

if @rd = 'PRIMARY'

print  @@servername + ' is Primary Replica'
else
print @@servername + ' is Secondary Replica'
---------------------------------------------------------------------------------
output:-
(1 row(s) affected)

SQLNODE1\SQl1  is Primary Replica

-------------------------------------------------------------------------------------------------------------------
/* Check Which Availability group is primary on this replica - SQLNODE1\SQl1 .. */

if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')
  and o.id = object_id(N'tempdb..#Replicadetails')
              )
begin
DROP TABLE #Replicadetails;
end;
SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
into #Replicadetails
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

select 'The primary Availability group for this replica  ' + @@servername +   ' is '  +  Name from #Replicadetails where LocalReplicaRole = 1

Output

-----------------------------------------------

The primary Availability group for this SQLNODE1\SQl1   is AG1