Long-Running Query Completed with Change in MAXDOP Setting

After running a T-SQL script on a SQL Server test database and not seeing it complete after 5 days, my colleague asked me for help. The software vendor ("vendor") had told my colleague that it wasn't unusual for the script to take 5 days or more to run. I was absolutely appalled that the vendor would send out SQL update scripts that would even take that long to run to completion and to advise customers that it can potentially take that long to complete. Just think about how you would explain to your organization that the upgrade outage would take more than one business week just for the database scripts alone.

Under closer observation, the script would stall after running for about an hour. When it failed, there were 72 blocked processes. My initial gut reaction was that the database needed indexes in order for the queries to complete. When asked, the vendor denied the need for additional indexes for the script to run successfully and they asked us to add tempdb files to tempdb database. The script still stalled in the same exact place running from a new database server with a restored copy of the database and the tempdb configured with 4 data files (there were 4 cores on the server).

The detail of the blocked process referred to an ExchangeEvent id#. I Googled ExchangeEvent and found this post.

Since I didn't have any better suggestions from the vendor, I opted not to tune the vendor-provided script and decided to change the Maximum Degree of Parallelism (MAXDOP) setting on the SQL Server from 0 (which is the default) to 1. With what little help we were receiving from this software vendor, I did not want to jeopardize our support agreement by adding indexes to the database without their consent. Changing the MAXDOP setting forced single-threaded execution and the script completed without blocking in 2 hours and 12 seconds. It was another matter that the script took this long to complete but we were relieved that the script ran to completion.

So we were eventually able to move forward with upgrading the software. I was quite disappointed with the lack of assistance from the vendor to get us to this point. I wouldn't say the upgrade was smooth sailing but that's a tale for another time. Let's just say there was enough foreshadowing for how the actual upgrade would transpire.