2

In reading the whitepaper: Online Indexing Operations in SQL Server 2005 it says:

The online index build process can be roughly divided into three phases:

  • Preparation
  • Build
  • Final

And Additionally:

The duration of the preparation and final phases is usually short and is independent of the size of the data.

Assuming I am building a new non-clustered index in SQL Server 2008 (online), is there a way to see how how much time was spent during an online index build for the Final Phase?

I've tried looking at the Profiler EventClass: "Progress Report: Online Index Operation Event Class", but it appears that the Final phase begins after the EventSubClass "7 - Done" has been sent to profiler

(Tested by holding an IX lock on the table during the Build Phase of the index build and seeing the thread waiting for the IS lock to be converted to the S lock on the table).

  • 1
    I don't know for sure, but I think you answered your own question. That is, it'll take the amount of time that it takes to acquire the proper locks and reconcile any changes that happen in that amount of time. – Ben Thul May 7 '15 at 4:34
  • I believe this is an "It Depends" answer. This link describes the phase pretty clearly (technet.microsoft.com/en-us/library/ms191261(SQL.90).aspx) and while it will clearly be a faster phase in general (compared to the other phases anyway) it will depend on your hardware, resources being used on the system when the final phase is run, etc... as to how long it will end up taking. Ben said it really well, it will take how long it takes to do the work that it has to do, but that number will depend on a lot of other variables. – mskinner Aug 12 '15 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.