inconsistent of a select then update (part 2)

Question

Post reply

inconsistent of a select then update (part 2)

light_wt

Ten Centuries

Points: 1023
More actions
August 9, 2012 at 12:43 pm

#264951

ok,
I've gotten closer to the issue. In part, it is related to cache execute plan and statistics. What happened?
I have a store proc with 2 major parts within a loop in an explict TRANS block.
set transaction isolation level SERIALIZABLE ;
while (@loop>0)
begin
begin trans
with cte as(
select *
from tableA a
join tableB b
on a.id=b.id
WHERE col1=@loop <- :exclamationmark:
)
update tableA
set a.col2= 'yes'
from tableA
join cte
on a.id= cte.id
commit
end -- while loop
the query gives different count of rows being updated at different run time.
i then started doing many ... transaction isolation levels, table hints, and today, I've started...
declare @id tinyint = 0
select @id= db_id(DB_NAME())
DBCC FLUSHPROCINDB(@id);
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE ('ALL');
DBCC FREESESSIONCACHE ;
DBCC DROPCLEANBUFFERS ;
DROP STATISTICS StinkyDB.YuckyTable.Statsssss[1...n]
REBOOT, REBOOT, REBOOT.
Nothing improve very much, until...
I select INTO a newtablename with no index, just one cluster, no stat
In the proc, i change the tablename to newtablename.
it ran, and got the much better result (not 100% correct count, but, a 98% correct).
my question is this, what are the commands I need to deep clean all stat. i've listed those above which doesn't help much. I believe it is a stale execution plan, stat, or index issue here. But, running out of ideas.
Please help.
:crying:

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Instead of fracturing responses between threads, please post all responses here.

There really is no reason for starting a second thread for the same problem.

light_wt Ten Centuries Points: 1023 More actions · Answer 2

Hi Lynn,

I'm sorry I started a separate thread in a different category. It was intended to broaden the scope of audience, since there is no response from the other post at the "T-SQL" thread.

Thanks for pointing out and redirect. Hopefully, it will be the good reason for getting correct result for the problem.