Notice removed Authoritative reference needed by Neeraj Prasad Sharma
    Bounty Ended with Dan Guzman's answer chosen by Neeraj Prasad Sharma
13 Improved formatting; edited tags
source | link

EDIT:

Question Summary

Question Summary: A A fragmented Clustered Index is not performing well, even after a REBUILDREBUILD of the Indexindex. If the index is REORGANIZEDREORGANIZED then performance increases for the given table/index.

I am seeing this unusual behavior only on SQL Server 2016 and above, I have tested this scenario on different hardware and different versions  (Allall personal machines and all have the conventional rotating hard disk  ). LetLet me know if need any more information.

 

I can provide the complete details and analysis with the script if anybody wants, but not providing right now because the script quite large and will take a lot of space in the question. Please

Please test the shorter version of sample script taken from the provided link below in your DEV environment if you have SQL Server 2016 and above.

SCRIPT

**SCRIPT:**
-- SECTION 1
/*
Create a Test Folder in the machine and spefiy the drive in which you created
*/
USE MASTER

CREATE DATABASE RebuildTest
ON 
( NAME = 'RebuildTest',
    FILENAME = 'F:\TEST\RebuildTest_db.mdf',
    SIZE = 200MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB )
LOG ON
( NAME = 'RebuildTest_log',
    FILENAME = 'F:\TEST\RebuildTest_db.ldf',
    SIZE = 100MB,
      MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB ) ;
GO

BEGIN TRAN

USE RebuildTest

select  top 1000000
row_number () over ( order by (Select null)) n into Numbers from
sys.all_columns  a cross  join  sys.all_columns

CREATE TABLE [DBO].FRAG3 (
Primarykey int NOT NULL ,
SomeData3 char(1000) NOT NULL )

ALTER TABLE DBO.FRAG3
ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)

INSERT INTO [DBO].FRAG3
SELECT n , 'Some text..'
FROM Numbers
Where N/2 = N/2.0

Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
Where  Primarykey>500001

COMMIT

 -- SECTION 2

SELECT @@VERSION

/*                                                       BEGIN PART FRAG1.1     */

----- BEGIN CLEANBUFFER AND DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF

----- END CLEANBUFFER AND DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

--BEGIN REORGANIZE the Index
ALTER INDEX ALL ON [DBO].[FRAG3] REORGANIZE ;
--END REORGANIZE the Index

----- BEGIN CLEANBUFFER  FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------END PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

Results

Results: enter image description here

Crystal Disk Mark Test Results:

Crystal Disk Mark Test Results

I will add more later.

Detail

Detail: I I am seeing an unusual behavior of storage engine  (maybe) on SQL Server 2016 and above, I have created a highly fragmented table for (read problems with fragmentation) demo purpose then rebuilt it,.

Even after rebuilding the index performance doesn't increase as expected. To make sure data access pattern should be in key order not in IAM Driven-driven (Allocation Order Scan), I used the range predicate.

Initially, I thought may bemaybe SQL Server 2016 and above is more aggressive for large Scan, toscans. To check that, I adjusted the page count and row count but the performance pattern doesn`tdoesn't change,. I tested all on a personal system so I can say there was no other user activity was going on, I.

I have tested this behavior on other hardware as well (all have traditional rotating hard disks). Performance patterns are almost same.

I have checked wait stats all seem normal only PAGELATCH_IOPAGELATCH_IO (using Paul Randal script) there. I checked data pages using DMV sys.dm_db_database_page_allocations it also seems ok. But 

If I Reorganizereorganize the table or move all the data to a new table with same index definition disk IO performance increases,. I have checked this with perfmon and it seems like reorganizing index /new table can leverage sequential IO and the rebuild index still using the random reads in spite of the fact that both have almost same data pages internal and external fragmentation.

WARNING: This test consists some undocumented commandcommands and DROPCLEANBUFFERSDROPCLEANBUFFERS so not at all run at the production server.

LINKS: (PASTEBIN)

Links (pastebin)

LINK:(GOOGLE DRIVE)

Links:(Google Drive)

EDIT:

Question Summary: A fragmented Clustered Index is not performing well, even after a REBUILD of the Index. If the index is REORGANIZED then performance increases for the given table/index.

I am seeing this unusual behavior only on SQL Server 2016 and above, I have tested this scenario on different hardware and different versions(All personal machines and all have the conventional rotating hard disk  ). Let me know if need any more information.

I can provide the complete details and analysis with the script if anybody wants, but not providing right now because the script quite large and will take a lot of space in the question. Please test the shorter version of sample script taken from the provided link below in your DEV environment if you have SQL Server 2016 and above.

**SCRIPT:**
-- SECTION 1
/*
Create a Test Folder in the machine and spefiy the drive in which you created
*/
USE MASTER

CREATE DATABASE RebuildTest
ON 
( NAME = 'RebuildTest',
    FILENAME = 'F:\TEST\RebuildTest_db.mdf',
    SIZE = 200MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB )
LOG ON
( NAME = 'RebuildTest_log',
    FILENAME = 'F:\TEST\RebuildTest_db.ldf',
    SIZE = 100MB,
      MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB ) ;
GO

BEGIN TRAN

USE RebuildTest

select  top 1000000
row_number () over ( order by (Select null)) n into Numbers from
sys.all_columns  a cross  join  sys.all_columns

CREATE TABLE [DBO].FRAG3 (
Primarykey int NOT NULL ,
SomeData3 char(1000) NOT NULL )

ALTER TABLE DBO.FRAG3
ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)

INSERT INTO [DBO].FRAG3
SELECT n , 'Some text..'
FROM Numbers
Where N/2 = N/2.0

Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
Where  Primarykey>500001

COMMIT

 -- SECTION 2

SELECT @@VERSION

/*                                                       BEGIN PART FRAG1.1     */

----- BEGIN CLEANBUFFER AND DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF

----- END CLEANBUFFER AND DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

--BEGIN REORGANIZE the Index
ALTER INDEX ALL ON [DBO].[FRAG3] REORGANIZE ;
--END REORGANIZE the Index

----- BEGIN CLEANBUFFER  FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------END PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

Results: enter image description here

Crystal Disk Mark Test Results:

I will add more later.

Detail: I am seeing an unusual behavior of storage engine(maybe) on SQL Server 2016 and above, I have created a highly fragmented table for (read problems with fragmentation) demo purpose then rebuilt it,

Even after rebuilding the index performance doesn't increase as expected. To make sure data access pattern should be in key order not in IAM Driven (Allocation Order Scan), I used the range predicate.

Initially, I thought may be SQL Server 2016 and above is more aggressive for large Scan, to check that I adjusted the page count and row count but the performance pattern doesn`t change, I tested all on a personal system so I can say there was no other user activity was going on, I have tested this behavior on other hardware as well (all have traditional rotating hard disks) Performance patterns are almost same.

I have checked wait stats all seem normal only PAGELATCH_IO(using Paul Randal script) there. I checked data pages using DMV sys.dm_db_database_page_allocations it also seems ok. But If I Reorganize the table or move all the data to a new table with same index definition disk IO performance increases, I have checked this with perfmon and it seems like reorganizing index /new table can leverage sequential IO and the rebuild index still using the random reads in spite of the fact that both have almost same data pages internal and external fragmentation.

WARNING: This test consists some undocumented command and DROPCLEANBUFFERS so not at all run at the production server.

LINKS: (PASTEBIN)

LINK:(GOOGLE DRIVE)

Question Summary

A fragmented Clustered Index is not performing well, even after a REBUILD of the index. If the index is REORGANIZED then performance increases for the given table/index.

I am seeing this unusual behavior only on SQL Server 2016 and above, I have tested this scenario on different hardware and different versions  (all personal machines and all have the conventional rotating hard disk). Let me know if need any more information.

 

I can provide the complete details and analysis with the script if anybody wants, but not providing right now because the script quite large and will take a lot of space in the question.

Please test the shorter version of sample script taken from the provided link below in your DEV environment if you have SQL Server 2016 and above.

SCRIPT

-- SECTION 1
/*
Create a Test Folder in the machine and spefiy the drive in which you created
*/
USE MASTER

CREATE DATABASE RebuildTest
ON 
( NAME = 'RebuildTest',
    FILENAME = 'F:\TEST\RebuildTest_db.mdf',
    SIZE = 200MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB )
LOG ON
( NAME = 'RebuildTest_log',
    FILENAME = 'F:\TEST\RebuildTest_db.ldf',
    SIZE = 100MB,
      MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB ) ;
GO

BEGIN TRAN

USE RebuildTest

select  top 1000000
row_number () over ( order by (Select null)) n into Numbers from
sys.all_columns  a cross  join  sys.all_columns

CREATE TABLE [DBO].FRAG3 (
Primarykey int NOT NULL ,
SomeData3 char(1000) NOT NULL )

ALTER TABLE DBO.FRAG3
ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)

INSERT INTO [DBO].FRAG3
SELECT n , 'Some text..'
FROM Numbers
Where N/2 = N/2.0

Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
Where  Primarykey>500001

COMMIT

 -- SECTION 2

SELECT @@VERSION

/*                                                       BEGIN PART FRAG1.1     */

----- BEGIN CLEANBUFFER AND DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF

----- END CLEANBUFFER AND DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

--BEGIN REORGANIZE the Index
ALTER INDEX ALL ON [DBO].[FRAG3] REORGANIZE ;
--END REORGANIZE the Index

----- BEGIN CLEANBUFFER  FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------END PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

Results

enter image description here

Crystal Disk Mark Test Results

Detail

I am seeing an unusual behavior of storage engine  (maybe) on SQL Server 2016 and above, I have created a highly fragmented table for (read problems with fragmentation) demo purpose then rebuilt it.

Even after rebuilding the index performance doesn't increase as expected. To make sure data access pattern should be in key order not in IAM-driven (Allocation Order Scan), I used the range predicate.

Initially, I thought maybe SQL Server 2016 and above is more aggressive for large scans. To check that, I adjusted the page count and row count but the performance pattern doesn't change. I tested all on a personal system so I can say there was no other user activity was going on.

I have tested this behavior on other hardware as well (all have traditional rotating hard disks). Performance patterns are almost same.

I have checked wait stats all seem normal only PAGELATCH_IO (using Paul Randal script) there. I checked data pages using DMV sys.dm_db_database_page_allocations it also seems ok. 

If I reorganize the table or move all the data to a new table with same index definition disk IO performance increases. I have checked this with perfmon and it seems like reorganizing index /new table can leverage sequential IO and the rebuild index still using the random reads in spite of the fact that both have almost same data pages internal and external fragmentation.

WARNING: This test consists some undocumented commands and DROPCLEANBUFFERS so not at all run at the production server.

Links (pastebin)

Links:(Google Drive)

    Mod Moved Comments To Chat
    Tweeted twitter.com/StackDBAs/status/889525828723388417
12 Image added
source | link
    Notice added Authoritative reference needed by Neeraj Prasad Sharma
    Bounty Started worth 50 reputation by Neeraj Prasad Sharma
11 Formatting
source | link

Question Summary: A fragmented Clustered Index is not performing well, even after a REBUILD of the Index. If the index is REORGANIZED then performance increases for the given table/index.

I am seeing this unusual behaviourbehavior only on SQL Server 2016 and above, I have tested this scenario on different hardware and different versions(All personal machines and all have the conventional rotating hard disk ). Let me know if need any more information.

I can provide the complete details and analysis with the script if anybody wantwants, but not providing right now because the script quite large and will take a lot of space in the question. Please test the shorter version of sample script taken from the provided link below in your DEV enviromentenvironment if you have SQL Server 2016 and above.

**SCRIPT:**
-- SECTION 1
/*
Create a Test Folder in the machine and spefiy the drive in which you created
*/
USE MASTER

CREATE DATABASE RebuildTest
ON 
( NAME = 'RebuildTest',
    FILENAME = 'F:\TEST\RebuildTest_db.mdf',
    SIZE = 200MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB )
LOG ON
( NAME = 'RebuildTest_log',
    FILENAME = 'F:\TEST\RebuildTest_db.ldf',
    SIZE = 100MB,
      MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB ) ;
GO

BEGIN TRAN

USE RebuildTest

select  top 1000000
row_number () over ( order by (Select null)) n into Numbers from
sys.all_columns  a cross  join  sys.all_columns

CREATE TABLE [DBO].FRAG3 (
Primarykey int NOT NULL ,
SomeData3 char(1000) NOT NULL )

ALTER TABLE DBO.FRAG3
ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)

INSERT INTO [DBO].FRAG3
SELECT n , 'Some text..'
FROM Numbers
Where N/2 = N/2.0

Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
Where  Primarykey>500001

COMMIT

 -- SECTION 2

SELECT @@VERSION

/*                                                       BEGIN PART FRAG1.1     */

----- BEGIN CLEANBUFFER AND DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF

----- END CLEANBUFFER AND DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

--BEGIN REORGANIZE the Index
ALTER INDEX ALL ON [DBO].[FRAG3] REORGANIZE ;
--END REORGANIZE the Index

----- BEGIN CLEANBUFFER  FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------END PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

Detail: I am seeing an unusual behaviourbehavior of storage engine(maybe) on SQL Server 2016 and above, I have created a highly fragmented table for (read problems with fragmentation) demo purpose then rebuilt it,

Initially, I thought may be SQL Server 2016 and above is more aggressive for large Scan, to check that I adjusted the page count and row count but the performance pattern doesn`t change, I tested all on a personal system so I can say there was no other user activity was going on, I have tested this behaviourbehavior on other hardware as well (  all have traditional rotating hard disks) Performance patterns are almost same.

I have checked wait stats all seem normal only PAGELATCH_IO(using Paul Randal script) there. I checked data pages using DMV sys.dm_db_database_page_allocations it also seems ok. But If I Reorganize the table or move all the data to a new table with same index definition disk IO performance increases, I have checked this with perfmon and it seems like reorganizedreorganizing index /new table can leverage sequential IO and the rebuild index still using the random reads in spite of the fact that both have almost same data pages internal and external fragmentation.

I am attaching the complete query with the results on my system I captured. if you guys have SQL Server 2016 and above DEV box, please check this and share your results.

WARNING: This test consists some undocumented command and DROPCLEANBUFFERS so not at all run at the production server.

LINKS:  (PASTBINPASTEBIN)

2 SuppotingSupporting SPs RUN AFTER TABLE CREATION

3 Test   

4 Any Problem With Rebuild

Question Summary: A fragmented Clustered Index is not performing well even after a REBUILD of the Index. If the index is REORGANIZED then performance increases for the given table/index.

I am seeing this unusual behaviour only on SQL Server 2016 and above, I have tested this scenario on different hardware and different versions(All personal machines and all have the conventional rotating hard disk ). Let me know if need any more information.

I can provide the complete details and analysis with the script if anybody want, but not providing right now because the script quite large and will take a lot of space in the question. Please test the shorter version of sample script taken from the provided link below in your DEV enviroment if you have SQL Server 2016 and above.

**SCRIPT:**
-- SECTION 1
/*
Create a Test Folder in machine and spefiy the drive in which you created
*/
USE MASTER

CREATE DATABASE RebuildTest
ON 
( NAME = 'RebuildTest',
    FILENAME = 'F:\TEST\RebuildTest_db.mdf',
    SIZE = 200MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB )
LOG ON
( NAME = 'RebuildTest_log',
    FILENAME = 'F:\TEST\RebuildTest_db.ldf',
    SIZE = 100MB,
      MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB ) ;
GO

BEGIN TRAN

USE RebuildTest

select  top 1000000
row_number () over ( order by (Select null)) n into Numbers from
sys.all_columns  a cross  join  sys.all_columns

CREATE TABLE [DBO].FRAG3 (
Primarykey int NOT NULL ,
SomeData3 char(1000) NOT NULL )

ALTER TABLE DBO.FRAG3
ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)

INSERT INTO [DBO].FRAG3
SELECT n , 'Some text..'
FROM Numbers
Where N/2 = N/2.0

Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
Where  Primarykey>500001

COMMIT

 -- SECTION 2

SELECT @@VERSION

/*                                                       BEGIN PART FRAG1.1     */

----- BEGIN CLEANBUFFER AND DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF

----- END CLEANBUFFER AND DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

--BEGIN REORGANIZE the Index
ALTER INDEX ALL ON [DBO].[FRAG3] REORGANIZE ;
--END REORGANIZE the Index

----- BEGIN CLEANBUFFER  FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------END PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

Detail: I am seeing an unusual behaviour of storage engine(maybe) on SQL Server 2016 and above, I have created a highly fragmented table for (read problems with fragmentation) demo purpose then rebuilt it,

Initially, I thought may be SQL Server 2016 and above is more aggressive for large Scan, to check that I adjusted the page count and row count but the performance pattern doesn`t change, I tested all on a personal system so I can say there was no other user activity was going on, I have tested this behaviour on other hardware as well (  all have traditional rotating hard disks) Performance patterns are almost same.

I have checked wait stats all seem normal only PAGELATCH_IO(using Paul Randal script) there. I checked data pages using DMV sys.dm_db_database_page_allocations it also seems ok. But If I Reorganize the table or move all the data to a new table with same index definition disk IO performance increases, I have checked this with perfmon and it seems like reorganized index /new table can leverage sequential IO and the rebuild index still using the random reads in spite of fact that both have almost same data pages internal and external fragmentation.

I am attaching the complete query with the results on my system I captured. if you guys have SQL Server 2016 and above DEV box please check this and share your results.

WARNING: This test consists some undocumented command and DROPCLEANBUFFERS so not at all run at production server.

LINKS:(PASTBIN)

2 Suppoting SPs RUN AFTER TABLE CREATION

3 Test  4 Any Problem With Rebuild

Question Summary: A fragmented Clustered Index is not performing well, even after a REBUILD of the Index. If the index is REORGANIZED then performance increases for the given table/index.

I am seeing this unusual behavior only on SQL Server 2016 and above, I have tested this scenario on different hardware and different versions(All personal machines and all have the conventional rotating hard disk ). Let me know if need any more information.

I can provide the complete details and analysis with the script if anybody wants, but not providing right now because the script quite large and will take a lot of space in the question. Please test the shorter version of sample script taken from the provided link below in your DEV environment if you have SQL Server 2016 and above.

**SCRIPT:**
-- SECTION 1
/*
Create a Test Folder in the machine and spefiy the drive in which you created
*/
USE MASTER

CREATE DATABASE RebuildTest
ON 
( NAME = 'RebuildTest',
    FILENAME = 'F:\TEST\RebuildTest_db.mdf',
    SIZE = 200MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB )
LOG ON
( NAME = 'RebuildTest_log',
    FILENAME = 'F:\TEST\RebuildTest_db.ldf',
    SIZE = 100MB,
      MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB ) ;
GO

BEGIN TRAN

USE RebuildTest

select  top 1000000
row_number () over ( order by (Select null)) n into Numbers from
sys.all_columns  a cross  join  sys.all_columns

CREATE TABLE [DBO].FRAG3 (
Primarykey int NOT NULL ,
SomeData3 char(1000) NOT NULL )

ALTER TABLE DBO.FRAG3
ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)

INSERT INTO [DBO].FRAG3
SELECT n , 'Some text..'
FROM Numbers
Where N/2 = N/2.0

Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
Where  Primarykey>500001

COMMIT

 -- SECTION 2

SELECT @@VERSION

/*                                                       BEGIN PART FRAG1.1     */

----- BEGIN CLEANBUFFER AND DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF

----- END CLEANBUFFER AND DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

--BEGIN REORGANIZE the Index
ALTER INDEX ALL ON [DBO].[FRAG3] REORGANIZE ;
--END REORGANIZE the Index

----- BEGIN CLEANBUFFER  FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------END PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

Detail: I am seeing an unusual behavior of storage engine(maybe) on SQL Server 2016 and above, I have created a highly fragmented table for (read problems with fragmentation) demo purpose then rebuilt it,

Initially, I thought may be SQL Server 2016 and above is more aggressive for large Scan, to check that I adjusted the page count and row count but the performance pattern doesn`t change, I tested all on a personal system so I can say there was no other user activity was going on, I have tested this behavior on other hardware as well (all have traditional rotating hard disks) Performance patterns are almost same.

I have checked wait stats all seem normal only PAGELATCH_IO(using Paul Randal script) there. I checked data pages using DMV sys.dm_db_database_page_allocations it also seems ok. But If I Reorganize the table or move all the data to a new table with same index definition disk IO performance increases, I have checked this with perfmon and it seems like reorganizing index /new table can leverage sequential IO and the rebuild index still using the random reads in spite of the fact that both have almost same data pages internal and external fragmentation.

I am attaching the complete query with the results on my system I captured. if you guys have SQL Server 2016 and above DEV box, please check this and share your results.

WARNING: This test consists some undocumented command and DROPCLEANBUFFERS so not at all run at the production server.

LINKS:  (PASTEBIN)

2 Supporting SPs RUN AFTER TABLE CREATION

3 Test  

4 Any Problem With Rebuild

10 added 159 characters in body
source | link
9 spelling mistake corrected
source | link
8 added more results to the question
source | link
    Post Reopened by ypercubeᵀᴹ, McNets, joanolo, SqlWorldWide, RDFozz
7 Changed SQL Server 2106 to 2016 and above
source | link
6 reformatted code and added question
source | link
5 added 84 characters in body
source | link
4 added 3755 characters in body; edited title
source | link
3 added 3755 characters in body; edited title
source | link
    Post Closed as "unclear what you're asking" by Erik Darling, Joe Obbish, joanolo, McNets, SqlWorldWide
2 added 2 characters in body
source | link
1
source | link