1

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

Improve this question
5
  • 2
    1.5 hours seems suspiciously long to me for only a few million rows, unless this field is part of the clustered index / many nonclustered indexes maybe on mechanical disks. I'd try looking for some other root problem. Maybe check you have sufficient Memory installed on the server, and verify your disk's I/O is what you'd expect (CrystalDiskMark is a helpful tool for this).
    – J.D.
    Apr 28 at 12:20
  • 1
    @J.D. well well, the benchmark did show some subpar results, perhaps this is indeed the culprit, I'll have to investigate. Thanks for the tool recommendation.
    – Mateus Viccari
    Apr 28 at 14:49
  • No problem, just curious what I/O results you saw?...you can add a screenshot of the results to your post if you want, it's probably relevant / helpful.
    – J.D.
    Apr 29 at 2:47
  • I would expect increasing the column length to be a only a metadata change. A gotcha I've seen is inadvertently changing the column in the process. That will happen if the existing column collation is different than the database default and no collation is specified on the ALTER statement.
    – Dan Guzman
    Apr 29 at 13:21
  • @J.D. Sure, I've included a screenshot with the results, see the edited question.
    – Mateus Viccari
    Apr 29 at 19:44

3 Answers 3

Reset to default
1

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.

Improve this answer
2
  • I never use the clunky SSMS GUI. It was a direct simple alter column command, and the locks were not quick. I would say about 95% of the time it was suspended, 5% running.
    – Mateus Viccari
    Apr 28 at 12:34
  • Got it! I would as first step play around with a repro for this and see if it can be done as a meta-data only operation. I.e., is there some index that prohibits this, for instance. If you are on Enterprise Edition then an option might be to drop and re-crate that index ONLINE (or perhaps even OFFLINE would be better than current situation). Just thinking out loud...
    – Tibor Karaszi
    Apr 29 at 6:49
1

:) Experience is the thing that gives you insight into whether the task will be quick or not. Expected to be "quick" - varchar(100) to varchar(250). During production time? Never quick.

So, did you check there are no constraints you could disable? Are there foreign keys on the table, or targetting the table? Did you see if a trigger was on the table?

I made the same mistake recently - blocked the processes using the table because table lock. Process had been running 5 minutes, but rollback took 2 hours, and eventually we restarted the SQL Instance to recover from the rollback. Rollback is single-threaded... so that hurts.

Tread lightly in Production woods - restore a copy of prod and apply the change there if you can.

Improve this answer
1

Yea those I/O results are fairly bad. Some SSDs are not great, but to be honest I have a mechanical HDD running behind my development SQL Server and it's getting 33% faster read speeds than your SSD. By the way, only the first (SEQ1M) and third (RND4K) row results really matter when it comes to benchmarking I/O for SQL Server type of workloads.

It may not be the SSD itself that's the issue but rather how it's connected to your server. Hopefully you have an infrastructure team you can work with to diagnose the issue. I just went through the same thing with my development server and the setup was incorrect in a few ways including not properly connected with iSCSI. Once we fixed the setup, the mechanical HDD went from ~120 MB/s read speeds (particularly for SEQ1M) to almost ~200 MB/s. So it definitely makes a difference. Unfortunately I'm not well enough versed on hardware to advise more specifically, but you may find some luck asking for configuration / setup help on Server Fault.

I also agree with Dan that the change you're doing should only be a meta-data change and be very simple / quick to accomplish, so long as you wrote the script and didn't use the SSMS GUI like Tibor mentioned, so that's strange too.

Improve this answer

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.