April 23, 2018 at 8:12 am
Hi folks,
a really interesting fact has been mentioned to me today in so far as now (as of SQL Server 2012) an online rebuild of clustered indexes (in this case a primary key) is possible. My understanding is that a clustered index IS the data and a rebuild involves moving the data around on disk so that it lies physically ordered to improve IO. This results in the table being locked for access during the operation. Unlike a non clustered index, a copy of the index cannot be created next to the existing one, an operation that occurs during an online rebuild of a non-clustered index.
Maybe i have misunderstood something here but I don't think so.
The second point is that it is generally believed here (in-house) that because the datafiles are held on SSDs, that a heavily fragmented index (pages > 1000, fragmentation > 30%) will have very little effect on data access. Here I am also somewhat sceptical.
Does anyone have any thoughts or ideas to the contrary?
Regards,
Kev
April 23, 2018 at 8:22 am
kevaburg - Monday, April 23, 2018 8:12 AMHi folks,
a really interesting fact has been mentioned to me today in so far as now (as of SQL Server 2012) an online rebuild of clustered indexes (in this case a primary key) is possible. My understanding is that a clustered index IS the data and a rebuild involves moving the data around on disk so that it lies physically ordered to improve IO. This results in the table being locked for access during the operation. Unlike a non clustered index, a copy of the index cannot be created next to the existing one, an operation that occurs during an online rebuild of a non-clustered index.
Maybe i have misunderstood something here but I don't think so.
The second point is that it is generally believed here (in-house) that because the datafiles are held on SSDs, that a heavily fragmented index (pages > 1000, fragmentation > 30%) will have very little effect on data access. Here I am also somewhat sceptical.
Does anyone have any thoughts or ideas to the contrary?
Regards,
Kev
Yes.
First, if you have the Enterprise Edition, ONLINE rebuilds of clustered indexes have always been possible unless the CI contains blobs and that has changed in 2016.
Second, both online and offline rebuilds will create a new copy of the index if the index contains more than 128 Extents (that's only 8MB) and they always have for both CIs and NCIs. Online actually builds a (sort of) third table in the background to capture any transactions that occur during the rebuild. That's a part of why ONLINE takes a fair bit longer and doesn't do quite as good a job at defragging.
Third, what people measure as fragmentation will usually only affect Read_Aheads for performance... well, kind of. What most people don't realize is that fragmentation can only be caused by the "nasty" or "bad" kind of page splits and they don't actually look at % of page fullness in sys.dm_db_index_physical_stats. If you have an index with an average % of page fullness of less than 70%, you're wasting a huge amount of SSD space and RAM when it's loaded into memory.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply