0

I am executing the rebuild index script in SSMS to rebuild a specific primary key index of a major table in our database. It has been running for 13 hours now. sp_who2 shows me that it was suspended due to IO_COMPLETION wait type.

Now I don't have any idea to how to deal with the situation.

  • What is IO_COMPLETION?
  • How can I make this job runnable from suspended?
  • Can I kill the reindex job SPID and plan it for some other time? I heard that it's not a good idea as it will take a lot of time to rollback.

enter image description here

When I check to see the percentage of completion of rebuilt index with some query using exec_sql_text and exec_sql_request, it is showing ZERO. Now the physical reads = 310,777,191. A while later the physical reads = 319,741,497. How long should we wait and let it run? Is there anyway to get/see an estimate?

The table contains 8 billion records. It was fragmented 80 percent.

1

Community wiki answer:

What is IO_COMPLETION?

This wait is associated with a sort operator that runs out of memory and has to spill to tempdb. It is not immediately obvious why rebuilding an index would involve sorting, perhaps there are some details missing from the question.

Is there anyway to get/see an estimate?

If you happen to know how large the table is (page size specifically), that would be roughly the number of reads. If you only know the size in MB (or GB), you'd have to divide by 8192 bytes (the size of a data page) to get a rough estimate of total number of reads.

Rebuilding an index might do at least 2x this number depending on which rebuild options you choose.

Your Answer

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

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