Just How Long Is Too Long?
Two weeks ago we took the drastic measure of FTP-ing a backup file for a production database to the product’s vendor for assessment on why a post-upgrade execution of a SQL Server stored procedure fails. This is a process, according to the vendor, that updates records after any product upgrade. It is heavily cpu-intensive and may run for a while (their words, not mine.)
I typically don’t like to see situations where we need to send database backups to a vendor. It usually means that the application will be out of commission for the period of time of their assessment, or best-case scenairo, that it’s still usable, but that we will need to perform parallel processing after the fact on the production copy (so long as no issues are encountered). In this case, the vendor has asked that we leave the product offline until they can finish the execution of this stored procedure.
Fast-forward two weeks. The execution, they state, is 98% complete. I’ve been told in the past that this is an “important system” for use of <REDACTED>. Apparently this is not the case, but even then, my concern lies with the process more so than the assessment of importance.
Now I don’t know what the specifications are on the system they are running this stored procedure on in their environment, but we were doing so on a quad proc/dual core SQL Server Enterprise 64-bit instance with 32 GB of RAM. They have not been able to assess why we were not able to successfully execute the stored procedure (it accepts no parameters and in both their case and ours was executed as dbo from within SQL Server Management Studio.) If it ran successfully within our production environment would it have run quicker? Without their hardware specs it is difficult to say, but most-likely their hardware is not comparable to what we were throwing at it.
The question remains however: Is it acceptable for a software developer worth any measure to create a resource-intensive process that requires not just hours, but rather weeks to run?
The vendor has given me approval to troubleshoot and make recommendations for improving this stored procedure. (Why not, they get free consultation, don’t they?) Armed with my copy of Grant Fritchey’s Dissecting SQL Server Execution Plans I will be digging into the code as time permits. It will be interesting to see what I find. I’ll be sure to share them here if they are remotely interesting.