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!



alt text



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

Popular posts from this blog

list macos calm editors formula editors

how i practical urls indicate .aspx pages asp.net deployed an iis? (preferably but iis)

jaxb - xjc - reworking generated typesafe enum category members