2

One of our custom log tables is 3TB in size. We need to create a new index. Read speed on the disk itself is 10mbyte/sec.

Does this mean that the index creation process will be 3.000.000/10 = 86 hours? The index is written to another filegroup on a fast disk so write time is not a factor.

5
  • This is going to depend greatly on the type of index being created …
    – matigo
    May 30, 2021 at 9:30
  • Just 1 datetime column. But probably sql will load full page anyway ? even if there is onl y 1 column being indexed.
    – MichaelD
    May 30, 2021 at 9:45
  • Might be time to get new disks...? A good HDD will read well over 100Mbyte/s, and a good SSD 3000Mbyte/s May 30, 2021 at 11:44
  • Even a bad HDD should deliver more than 10MB/sec with sequential reads, especially with the larger extent reads SQL Server will use. How are you measuring speed?
    – Dan Guzman
    May 30, 2021 at 13:36
  • Measuring speed based on Resource monitor. 100% active time and read B/sec is 10mbyte/sec
    – MichaelD
    May 31, 2021 at 9:00

1 Answer 1

2

It's more complicated than that. When you create an index (B-Tree), not only does all the data the index is for, needs to be read, some of it is read multiple times as it is converted to a B-Tree. This is because SQL Server specifically uses a balanced B-Tree. I'm not sure if there's any documentation on Microsoft's exact algorithm under the hood but this article discusses one practical way of converting an unordered set of data to a balanced B-Tree, which is a two step process.

Additionally if any re-sorting occurs during creation, that'll also add more time to the creation of the index. One example where this can happen, is if you're doing an online index build operation in SQL Server, and new rows are added to the table during the operation.

As others have pointed out, 10MB/s is a suspiciously slow hard drive. For reference, currently the slowest EBS storage on AWS averages around 65 MB/s (Previous Gen - between 40–90 MiB/s) and I don't think regular hard drives have been as slow as 10 MB/s since the 90s (though I couldn't find any sources for this).

Depending on the purpose of your index, perhaps a filtered index would be useful to you, if you don't need to index all rows. It theoretically should create faster since it only indexes a subset of the data based on the filter applied in the definition. This is useful in cases where you mainly query only a subset of the data, e.g. everything since 2015 or whatever static criteria you want to define in the filter. It also saves on space since less data is being indexed. But also please be aware of the limitations as well, which Brent Ozar discusses in What You Can (and Can’t) Do With Filtered Indexes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.