Ok hands up who is still running SQL Server 2000 systems in production. I know you are out there.

Last week I had to determine why a set of DTS packages were running extremely slow. Perfmon indicated that the Disk IO latency was ranging from 50 ms to 1 to 2 seconds, that’s right seconds.

Now the DTS packages were written many years ago and were written to be extremely flexible but now are almost impossible to maintain, so there was no way I was going touch the embedded VBScript tasks. The other interesting factor was that the Disk IO latency was still very high even though the number of Disk IO per second were low (< 50/sec). Note also that the the IO size was 8K to 16K – so typical SQL IO sizes.

I also performed a trace of one of the DTS processes, there were lots of repeating SQL statements (which added to the performance issue), some that could have been tuned down but in the end I advised the customer to move the VM files to SSD storage, which they implemented the next day.

A 2 hour DTS job went down to 2 minutes, this was a big win with very little effort. No SQL tuning, no fiddling with the old “touchy” DTS code and no testing of code changes.

In this customer’s case, the process was IO bound for two reasons, firstly the IO generated by the DTS process plus the IO contention of other VMs sharing the same physical drives. The move to SSDs eliminated both IO issues.

Moral of the story is, SSD is your friend if you have identified an IO bottleneck and you want to avoid tinkering with a legacy system.