August 9, 2012 at 12:43 pm
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:
August 11, 2012 at 8:31 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply