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.
Wow, I find it hard to believe that there is anything in SQL Server that should take weeks to run. So if this database serves a mission critical application, your business is expected to just endure degraded performance for a couple of weeks after an upgrade? Don’t you expect better performance after an upgrade?
As you can tell, I certainly don’t think anything should take weeks to run. I’m glad the vendor is willing to let you look at it, this at least shows that they realize that it can be improved.
I’ve run into situations like this before, and generally it is because we have far more data in the DB than the vendors plan for. I know that we currenly have some application specific maintenance routines that run for 36+ hrs, and the vendor’s not seen any other customers have run times like this. Of course, they don’t have any other customrs with a db the same size as ours. It’s not acceptable, but unfortunately it’s not unheard of either.
[…] a stored procedure we could not successfully complete in our production environment. That post, Just How Long is Too Long, has some even more developments that make the initial issues seem utterly sensical. The crux of […]
[…] and that we ended up sending the original backup to them to re-do their work? I do believe I blogged about this transgression over the summer. Please provide back in comment form your completed SQL-LIB to this post as a way to share […]