August 26, 2011 at 7:21 pm
UMG Developer (8/26/2011)
Ninja's_RGR'us (8/26/2011)
The only weight it needs is to blow up when they run it. That breaks their sla so they have to fix it.That may be true, but when it gets fixed and for what release it gets fixed may depend some on who reported it. (A bug I reported was fixed in Denali and closed in Connect. That doesn't really do me any good at this point.)
Of course mine wasn't any where near as serious as this, so I imagine this one will get fixed in all supported versions fairly quickly. (At least I hope it will.)
We'll see with "mine". It literally breaks the RDBMS sla so I can't see them NOT fixing it. Let's see how long now ;-).
August 27, 2011 at 1:39 am
Jack Corbett (8/26/2011)
ALZDBA (8/26/2011)
According to your script, IMO it is quit normal DRI checks may take a long time.One of my standard advises for DRI is to support FK with matching indexes, meaning put an index on the child table that matches the PK of the parent table 100% ( N columns + column order + asc/desc). (or the used AK for DRI)
I didn't find those on the published script.
( You can opt to disable them during regular ops time, and only enable them when your delete cycle has to be run )
Can you test your situation having these FK-indexes in place on a larger volume ?
Maybe this can help you enough, but it doesn't debug the situation.
I didn't put the indexes on because in reality I don't think they make a difference when it comes to the bug.
I originally found the issue running against a table with millions of rows with 48 fk's defined against it with many of those tables having millions of rows. I didn't have any orphaned rows in the original delete but I did see the plan re-use which caused me to develop the simple test script.
Thanks. That explains a lot.
Back to the bug. 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 30, 2011 at 8:17 pm
Well the bug response from MSFT was unexpected:
"Since this is not a common scenario or use case i.e., doing DDL changes and DML in the same transaction we don't see this as a big issue."
I can't say how common this is in the real world, but I can't imagine Jack is the only one to have thought of trying this approach. I'm going to have a chat with UC about it, but if it's really that hard to fix, I doubt I'll make much progress. Ideally, 10,000 people will now vote for the bug and also click on the 'I can reproduce this' option as well. If you feel stringly that this should be fixed, please encourage friends, relatives, co-workers, and people you meet in the street to vote!
August 31, 2011 at 5:18 am
Wow. I went to the Connect issue to post a comment, but I really can't think of a response that sounds polite.
August 31, 2011 at 6:25 am
It does seem like it would be a rarely hit bug, but, based on my recent experience with deletes, I think it is probably more common than MS thinks. I mean if I'm purging data, even if I've archived it off to a DW, from multiple tables disabling FK's is definitely a way to do it quickly, but you don't want the deletes to persist if you can't re-enable the FK's because that means you have to restore, so you are going to wrap that entire thing in a transaction. I'll definitely make a comment on the item. It might not make a difference, but at least I'll get my 2 cents in.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 31, 2011 at 6:27 am
I have a similar ticket opened both with connect and PSS.
PSS has reproduced the bug and said it'll be fixed in future versions (2k8+, not 2k5).
You might want to go that route. Since it's a real bug, the call should be free... 1-800-MICROSOFT
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply