How to speed up ALTER COLUMN when changing column size on big tables, leading to SUSPENDED state / PAGEIOLATCH_EX wait type?



  • I tried changing a column on a table with a few million records, changing a varchar column length from 100 to 250. After more than one and a half hours of execution time, whereupon monitoring the connection, it was almost all the time with a SUSPENDED state and PAGEIOLATCH_EX wait type, with very short intervals where the state changed to RUNNING.
    I couldn't tell if it took that long due to an index being recomputed, disk being slow (didn't seem like it), or this was simply the expected behavior. Nor could I tell how long the task would take, so I just gave up and stopped the process.
    Is there a way to know beforehand if there's something to be done for the task to run on a shorter time interval, or at least to know how much is left to do when the task is running?

    Edit

    As suggested by @J.D, I've run a benchmark on the SQL server SSD and I got some below average results, at least less than I would expect from an SSD imho. Here are the results. So maybe it has something to to with the performance: enter image description here



  • Did you use the SSMS GUI to do the change?

    The SSMS GUI isn't smart. It will create a new table, etc.

    If you use ALTER TABLE... ALTER COLUMN... directly, then it is likely that this change can be a meta-data only operation. I.e., no data movement and only a super-quick very restrictive lock while the meta-data operation is performed.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2