February 17, 2009 at 4:38 pm
Lock Stock and Block...
I have a situation where I have a couple of SQL servers that serve basically as the 'back end'* for a number of 3rd party applications.
*(forgive me if I like to call it the 'a55 end' but anyway enough hating)
I'm sure you can all think of a few of this type of apps, the ones I am dealing with seem to love to block and deadlock.
Blockiddy block block, Block stock and 2 smoking barrels/Block stock and however many exclusive locks you can grab.
In a lot of cases their own spids/processes, in a lot of cases these blocks turn into Deadlocks (groan).
one example is a statement on a table that has over 4.5 million rows (table has around 12 columns mix of varchar, numeric and date formats and NO INDEXES!).
the statement goes something like...
DELETE FROM [myTable]
WHERE time_stamp < current_time_stamp - 365
A couple of questions:
- Just wondering if anyone else is experiencing these types of things?
- has anyone edited the structure of the 'a55 end' tables e.g. in my example A BLADDY INDEX! any pros? cons?
- I have seen a lot of this on 2000, not so much on 2005, has anyone put any of these on 2008? any experiences and examples appreciated.
Thanks
Carlton..
February 17, 2009 at 8:32 pm
Yeah... I went through such hell with a 3rd party POS myself. In my case, it had no archiving and, worse, it used a Sequence Table instead of IDENTITY columns. The code that drove the Sequence Table was responsible for 640 deadlocks per day with spikes to 4000 in a day!
You have several of choices and I appologize in advance for obviousness of some of them, but perhaps you'll also have a good laugh with me at this very frustrating problem of 3rd party crap... 😛
1. Tell the vendor they have to fix it (BWAAAAAAAA-HAAAAAA-HAAAA! Right, like that's ever gonna happen. Been there, done that, it just doesn't work anytime in the same decade in most cases. Of course, the answer is always "No" unless you ask. ;)).
2. Get a better product and drop the bad one (BWAAAAAAAA-HAAAAAA-HAAAA! Yeah, right. Step on some high manager's pet project and see how far that get's you. Plus, you'd have to show the ROI to justify the new expense for the new product, the cost of migration, the retraining of personnel, and full regression testing. Won't happen this century and, even if it did, you'd STILL have a POS 3rd party product.)
3. Write a new product within IT (danger-danger Will Robinson... not only does all of #2 (approriately numbered, I may add) above apply, but you'll have the added problem of meeting deadlines, justifying development cost, justifying QA cost, and (likely) putting up with more bugs than ever.)
So, that pretty much narrows down the choices...
4. Negotiate with the vendor about any maintenance contracts you may have that changes may violate. Identify the problems you've found and give them the opportunity to fail... they might not. If they do fail, that will give you the room to negotiate any changes you may want to make. Do NOT tell them the precise changes you're making unless they are willing to pay for them. It's a two way street. But ask them things like... "We have a bit of a performance problem that we feel an index or two may resolve. Will it violate any of your good (tongue in cheek but politically correct) warranties if we were to add them?" Make sure you get everything in writing to protect the maintenance contract (if you actually want to keep it at this point).
I ended up writing our own archival process, rewriting the crud ball, deadlocking piece of junk stored procedure they wrote for updating the Sequence Table (couldn't change the structure of the tables because it would break the GUI's), and replaced a lot of their stand-alone processes like the dupe check on 93 tables (all in separate databases) of 4 to 8 mega rows each that took 24 hours to fail and never had enough time to actually make it through more than 62 databases. Through a bit of negotiation and documentation, none of that violated our maintenance agreement.
Make the changes without talking to the vendor, and you can pretty much flush a maintenance agreement away. Of course, is it really worth having a maintenance agreement considering item #1 above?
p.s. I got that dupe check thingy to run in 11 minutes across all 93 databases, and it hasn't failed since I implemented it almost 2 years ago. It was worth it!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2009 at 8:55 pm
I actually worked on a project on an application as you describe, actually ran on an Oracle database too, that we ended up working with the vendor to provide them updates to their procedures, triggers and indexing so that they could share these with their other customers. It didn't help all that much because the application was really a piece of junk but the agreement was pretty cool.
So, you never know what talking with them might do. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 17, 2009 at 8:58 pm
Hi Jeff and David,
Nice to know that I'm not "all by my-seellllllf"
your comments have been both thought and laughter (thanks Jeff 🙂 provoking.
Carlton..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply