hola,
run your query and noticed diffs at sp_spaceused because tables were
modified by nightly process. Read newsgroups threads on "update deferred",
also found at Sybase "Performance and Tuning Series: Query Processing and
Abstract Plans - Designing for direct updates".
Ran dbcc checktable, sp_statistics, reorg rebuild. Checked tables structure
for nullables. Started playing with the qry, modified to Update statement
and still the same... then removed t3 and qry plan changed ! Overmissed from
my analysis because of being small, 11 records. But I can see now it will
densily match against the whole t2 table. So ran : update index statistics
t2 t2_idx, update statistics t2 (fld3), update statistics t3 (fld3) and got
same qry plan at both servers.
On my first attemp used delete statistics => update statistics =>
sp_recompile.
Will tell tomorrow if success, hope so. Thanks for pointing me in the
direction !
regards,
Post by Mark A. ParsonsNot enough details to know what the issue is.
"very similar" ... differences in table design, table space usage
settings, dataserver version, optimization goal, statistics, etc ... all
can lead to different query plans.
Could you run the following script on both dataservers and post back here
=======================
go
select low as maxpagesize
from master..spt_values
where number = 1
and type = 'E'
go
exec sp_configure 'optimization'
exec sp_configure 'histogram'
go
exec sp_help t1
exec sp_spaceused t1,1
go
exec sp_help t2
exec sp_spaceused t2,1
go
exec sp_help t3
exec sp_spaceused t3,1
go
set showplan on
set statistics io on
go
delete t1
from t1, t2, t3
where t1.fld1+t1.fld2=td.fld2
and t2.fld3 = t3.fld3
go
=======================
I'd recommend you cut-n-paste the *entire* results of both runs into 2
separate *.txt files and attach said files to your newsgroup reply. (The
*.txt files will maintain formatting thus making it easier to read.)
You've stated that statistics are the same. Can you provide the actual
commands you used to update the stats for all 3 tables in both
dataservers?
You reference the DELETE as a standalone query. Are you getting the
conflicting query plans when you run the DELETE as a standalone query, or
is the DELETE part of a larger batch of SQL (or perhaps embedded in a
stored proc)?
Post by msthandihi !
got a funny behavior between 2 servs, which are very similar afaik. Same
version, same statistics (both anyway rebuilt), same table/indexes, same
db/server options.
A delete query is consistently chosing "update direct" mode at one and
"deferred update" at the other, also skipping index.
What can query optimizer be using as criteria ? I can mimic "update
direct" at latter by setting merge_join off.... standalone qry is like
delete t1
from t1, t2, t3
where t1.fld1+t1.fld2=td.fld2
and t2.fld3 = t3.fld3
thanks & regards,