May 13, 2021 at 6:51 pm
The other part about the documentation on index maintenance is that it's spread all over hell's little half acre. This IS a spot that actually describes what REORGANIZE does in a very correct manner but most people never read it because it's under a heading that talks about disk space rather than as a definition of REORGANIZE.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2021 at 9:35 am
Having a weird case today (still under investigation). Parallel clustered index scan slower dan serial index scan (forced by MAXDOP 1)
*edit* repartition streams spilled to tempdb 🙁 Repartition stream 1=0,2=5000000,3=0,4=5000000. Oh well, query can't be modified
June 2, 2021 at 1:52 pm
What happened to Gail Shaw ?
She used to be a regular poster on SSC.
June 2, 2021 at 2:12 pm
Gail is still around, but less on technical sites. Life has taken a turn for her, and we chat at times, but mostly she's less engaged in this space. She still blogs at: https://sqlinthewild.co.za/
June 2, 2021 at 4:11 pm
Having a weird case today (still under investigation). Parallel clustered index scan slower dan serial index scan (forced by MAXDOP 1)
*edit* repartition streams spilled to tempdb 🙁 Repartition stream 1=0,2=5000000,3=0,4=5000000. Oh well, query can't be modified
I believe you've correctly identified the problem and the reason why I frequently laugh at people that suggest resolving performance problems with the recommendation of "Kill it with Iron". It's also one of the reasons why understanding and correctly setting the "Cost Threshold for Parallelism" and database level MAXDOP is so very important and why using the OPTION(MAXDOP X) can be very important in code.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2021 at 1:21 pm
Thanks Jeff. Now investigating sql plan guides for additional nudges.
Summer has arrived + 25 degrees Celsius
June 14, 2021 at 1:01 pm
Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
June 14, 2021 at 1:12 pm
Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
If the trigger is badly written, it will only fire for 1 record in each batch
https://www.brentozar.com/archive/2019/05/the-silent-bug-i-find-in-most-triggers/
June 14, 2021 at 1:18 pm
Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
I'll say what we're all thinking... it's because the vendor probably heard of people missing rows in triggers, didn't realize that it was because people wrote truly single row handling, and thought the only way to ensure that didn't happen was to write a Cursor. Unless they're calling a stored proc for each and every row (which would also be a huge mistake), they're making a huge mistake. Set-Based triggers don't skip rows... people's code does when written incorrectly.
Based on the, ummmm... shall we say, displayed bit of ignorance on the vendor, you might want to start looking for a better vendor because you're probably not going to be able to convince them that they're wrong. Too many nose-coners at the helm there. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2021 at 2:00 pm
Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
Like the others, I have never been in a position where I've witnessed inserted
not containing all the rows that were inserted, and likewise, that the only time I have witness a trigger not processing all the rows in inserted
is because someone (foolishly) assumed that the trigger runs once per row, rather than for the entire DML statement.
Either someone needs to educate the vendor on their flawed understanding so that they can roll out a priority fix (and in my view RBAR triggers being replaced with set-based ones is a huge priority) or yes you need to look elsewhere.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 14, 2021 at 2:02 pm
I just took a glance at the trigger. The size of the thing is truly horrendous.
Not sure if it was truly built for single row processing because it'll probably take me days to break it down and verify. But I have a non-prod environment I might try rewriting the update code for set based just to test. Use a snapshot so I can roll back if it doesn't fix it.
And yes, we're already looking for a new vendor due to other reasons. Still, I'm glad to know I wasn't wrong to be shocked by that note.
June 14, 2021 at 2:04 pm
Brandie Tarvin wrote:Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
I'll say what we're all thinking... it's because the vendor probably heard of people missing rows in triggers, didn't realize that it was because people wrote truly single row handling, and thought the only way to ensure that didn't happen was to write a Cursor. Unless they're calling a stored proc for each and every row (which would also be a huge mistake), they're making a huge mistake. Set-Based triggers don't skip rows... people's code does when written incorrectly.
Based on the, ummmm... shall we say, displayed bit of ignorance on the vendor, you might want to start looking for a better vendor because you're probably not going to be able to convince them that they're wrong. Too many nose-coners at the helm there. 😀
I used to write those kind of triggers and procedures at my first job. Everyone did it that way. Of course, that was before I learn how to do it correctly.
June 14, 2021 at 2:39 pm
I just took a glance at the trigger. The size of the thing is truly horrendous.
If it's for a table with a large number of columns and it's an "audit" trigger that does the auditing by column rather than by row, that may be one of the reasons for it's size. I have code that regenerates audit triggers after you add columns to a given table and they're freakin' huge. They run nasty fast but they're huge.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2021 at 2:52 pm
I just took a glance at the trigger. The size of the thing is truly horrendous.
Not sure if it was truly built for single row processing because it'll probably take me days to break it down and verify. But I have a non-prod environment I might try rewriting the update code for set based just to test. Use a snapshot so I can roll back if it doesn't fix it.
And yes, we're already looking for a new vendor due to other reasons. Still, I'm glad to know I wasn't wrong to be shocked by that note.
At a former employer, we had what was simply called "THE trigger". It was so-named by previous developers. It was in Oracle and was over 1400 lines long. It balanced employee hours (scans, jobs, missing scans, etc.) in an ERP system and was the ultimate example of spaghetti code. Nobody even wanted to touch it for fear of breaking it. I tackled it, but it was no small task. It took days to just figure out the execution paths. It was eventually fixed, but it took a long time. The important part is that, while it was fixable, the code should never have existed in the first place. The ERP system lacked key functionality for the manufacturing environment, even though it was designed for manufacturing.
In the end, the company changed to a different ERP for reasons other than this missing functionality. If you're having that much trouble with a system, then you're probably right to look for a better solution. Good luck with it, Brandie. Dealing with this type of problem is a rough road to travel.
June 14, 2021 at 3:19 pm
So fun thing I just realized, glancing at it again. The trigger is doing error handling. Yes, it's doing an audit thing for updating a history table, but … it's doing error handling as well.
To my mind, and maybe I'm off base, shouldn't error handling be handled in the actual code, not the triggers?
Viewing 15 posts - 65,491 through 65,505 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply