February 3, 2012 at 6:36 am
DavidL (2/1/2012)
I'm assuming this isn't just a matter of rows being in a different order than expected. There are less/more rows different times, right?
Correct. The example I sent with two rows showed one that shows up 'all' the time, and an example of one that showed up 'most' of the time.
We don't have a dba, so I am stuck trying to figure this out.
I set up a trace to try and see what happens when the stored procedure is run -- I thought that if I could see how it unpacked the query, functions, view, etc. I might gain some insights. However I couldn't find any appropriate events that captured that. If you can recommend some I would appreciate it. TSQL statement compile etc. didn't seem to show anything other than 'EXEC usp_xyz @a='b', @b-2='c' etc. etc.
THanks for you help. D Lewis
Execution plans have that kind of data in them. Take a look at event ID 122, for example.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 3, 2012 at 8:45 am
I would like to make two suggestions here:-
1. Use cross apply to join with the function rether then inner join.
2. Make sure no duplicate value is there. Normally incosistency occurs when duplicacy exists and SQL selects any one value to show the output. It's then only everytime you run the same query you get different result.
I faced similar issue once and I used aggregate functions to supress duplicacy.....Hope it might be of some help to you:-)
February 3, 2012 at 9:05 am
One suggestion I would make is to try to zero in on which piece of code causes the inconsistency: the view or the splitter function.
Can you select the "missing" rows consistently from the view directly using a WHERE clause rather than the INNER JOINs to the splitter?
Can you select the "missing" rows consistently using the SELECTs behind the view?
February 3, 2012 at 9:05 am
ashutosh.and GSquared:
Execution plans have that kind of data in them. Take a look at event ID 122, for example.
and
I would like to make two suggestions here:-
1. Use cross apply to join with the function rether then inner join.
2. Make sure no duplicate value is there. Normally incosistency occurs when duplicacy exists and SQL selects any one value to show the output. It's then only everytime you run the same query you get different result.
I faced similar issue once and I used aggregate functions to supress duplicacy.....Hope it might be of some help to you:-)
Thank you. I will try them both and let you know if I find anything. d lewis
February 17, 2012 at 5:02 pm
All: I've continued to dig into this, and as it turns out some of the assumptions I had made were incorrect. Most importantly, the tables were not static. An outside vendor had created the etl processes for the reporting tables, and unbeknownst to any of us there were triggers firing every minute inserting and updating records. The common factor in all the records that were occasionally missing was the value 'gp_import_failed' in one column, and their trigger would continually delete and re-insert rows with that status into the reporting table. After we mentioned the difficulties we were having, and describing all we knew of the problem, they quickly emailed back with a description of the cause (faulty logic in their trigger). It has not recurred.
Thanks to everyone who contributed. d. lewis
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply