May 21, 2015 at 6:42 am
Hi All,
So, I was trying to help one of the support devs out here with an update of a couple of thousand rows in a table that was taking nearly an hour to run. On the face of it, his query looked fine, he had a temp table with the keys he wanted to update in the target table, everything was indexed, but this was taking ages. I'm fairly new here, and don't know all the systems and databases particularly well yet.
So, I asked him to run with statistics IO on and statistics XML on, which resulted in the following error:-
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
Uh-oh. I'm quite startled by this, so actually pay a little more attention to what's going on. The output from the statistics IO was mental, and referring to all sorts of tables in this database and some others too, so I know there's something crazy going on.
I expand the triggers node on the table and find three triggers.
I script up one of the triggers..... and get presented with some ~700 lines of code, one of the first lines being DECLARE #cur CURSOR. (After the cursor closes we do some stuff in a while loop as well, but hey ho).
Gradually, as I start reading through the code and unpicking what's going on, I work out that the following chain of events happens:-
1. Update table a
2. Update trigger fires, opens cursor
3. Inside the cursor, a procedure is called
4. Procedure inserts a row into another table b
5. Insert trigger on table b fires, opens a cursor
6. Inside cursor, updates table a
7. Update trigger on table a fires.....
"Fortunately", and I use that loosely, there's actually a check in the trigger on table a to see if the insert to table b has been carried out already, so we don't get caught in a trigger loop of doom.....
So, what sort of horror gems have you found recently?
Matthew
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
May 21, 2015 at 8:44 am
Matthew Darwin (5/21/2015)
... so we don't get caught in a trigger loop of doom.....So, what sort of horror gems have you found recently?
Matthew
Matthew that's awesome, thanks for the chuckle, and i feel your pain.
In my neck of the woods, i inherited a painful one:
there was a process, related to closing the books on a monthly basis for the company.
It was more or less reasonably documented, but lots of missing pieces were not there because it was so complex.
seventy one seperate and distinct manual processes. that guaranteed that it was a week of prep time for the persons involved to ramp up and get ready. it typically took two full business days and nights to complete by a two person team, and included multiple ssis jobs, TSQL scripts,SSRS reports, backups, and so much more.
I took it over, and once piece at a time, i painfully automated everything, rewrote it, etc so it's a single multistep SQL job that just needs to be kicked off, and baby sat in case it fails. restart any step that fails to continue.
oh yeah, it runs in 5 hours, and can still be tuned a bit more, but it's good enough that i can sit back and enjoy it.
fear of automatation and lack of experience was the show stopper on that before i came on board.
the black box/don't touch or a voodoo curse will happen was the mindset.
Lowell
May 21, 2015 at 9:32 am
Yeah, fear of automation does bug me, it's almost as bad as "we've always done it like that" as a cause for irritating me!
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply