sql server query execution digest shows wrong "actual quarrel count" an used index opening terrible slow
today i stumbled on an engaging opening problem stored way controlling sql server 2005 sp2 db controlling congruous turn 80 (sql2000).
the proc runs 8 mins execution digest shows use an index an tangible quarrel count 1.339.241.423 means 1000 aloft "real" tangible rowcount list itself 1.144.640 shown justly estimated quarrel count. tangible quarrel count given query digest optimizer definitly wrong!
interestingly enough, i duplicate procs parameter values inside proc internal variables internal variables tangible query, all works glorious - proc runs 18 seconds execution digest shows right tangible quarrel count.
edit: suggested trickynixon, seems pointer parameter sniffing problem. nonetheless actually, i both cases accurate same execution plan. same indices beeing used same order. wholly inadequacy i proceed high tangible quarrel count pk_ed_transitions index directly controlling parametervalues.
i have finished dbcc dbreindex transform statistics already but any success.
dbcc show_statistics shows good information index, too.
the proc combined recompile each runs new execution digest removing compiled.
to some-more specific - runs fast:
create proc [dbo].[myproc](
@param datetime
)
with recompile
as
set nocount on
declare @local datetime
set @local = @param
select
some columns
from
table1
where
column = @local
group by
some columns
and chronicle runs terribly slow, nonetheless produces accurately same execution digest (besides too high tangible quarrel count an used index):
create proc [dbo].[myproc](
@param datetime
)
with recompile
as
set nocount on
select
some columns
from
table1
where
column = @param
group by
some columns
any ideas?
anybody out there knows where sql server gets tangible quarrel count value calculating query plans?
update: i attempted query another server woth copat mode set 90 (sql2005). the same behavior. i cruise i open adult an ms support call, since looks me bug.
Comments
Post a Comment