Different actual execution plan for same query

  • I have a query that I execute with a recompile hint with sp_executesql. This query performs rather ok. Then I comment the recompile hint and add some text making the query text unique thus forcing recompilation of the plan. The actual execution plan looks completely different then and the query is much slower. The number of estimated rows is very low which might be the symptom of the root cause. Also the io statistics are completely different.

    The query is run in transaction isolation level snapshot. We have seen some high read/write stalls on the tempdb file but I cannot imagine any problems on the tempdb causing other execution plans. Also all the statistics on the database were updated 8 hours ago.

    We see this phenomenon on and off. Sometimes the query is fast and sometimes slow. Recompile sometimes gives a boost but not always. Parameter sniffing was a first candidate explanation but with the latest test seems unlikely. The query has a lot of parameters which is due to the way our software handles queries.

    What influences the execution plan other than statistics? I have attached exec plans of fast and slow for completeness but my first goal is to have some lead for follow up research as we are

  • Query text?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I was reluctant to post this since it is quite complex. Query below is the slow one with the recompile hint commented and some comment that would force recompilation of the plan

    set transaction isolation level snapshot

    GO

    exec sp_executesql N'with allowedDepartments as

    (

    select department.id_department as ''department''

    from cwbUserDepartment

    join department on

    department.id_department = cwbUserDepartment.id_department

    where id_user = @user

    )

    select

    transport.id_transport as id,

    transport.id_transport as id_transport,

    o.id_order as id_order,

    ud_order.udf_brand,

    s.shiftCode as TripCode,

    s.shiftTemplateCode as CAMISTrip,

    DS.departmentCode as TripOwner,

    o.ordernumber as order_number,

    c.contactName as contact_name,

    s.comment,

    DRV.resourceName as Driver,

    DRV.resourceCode as DriverCode,

    DA.phone as Phone,

    DA.email as EMAIL,

    UR.udf_DriverCos as DriverCos,

    UR.udf_DriverRank as DriverRank,

    BA.FinishInstant as PlannedTill,

    AAD.cityName as LastCity,

    AAD.stateName as LastState,

    APR.regionCode as LastZone,

    TRV.resourceCode as Tractor,

    TTRV.resourceCode as Trailer,

    AAD.cityName as Acity_name,

    AAD.stateName as Acity_state,

    APR.regionCode as Aregion_code,

    ud_order.udf_originGrid,

    ud_order.udf_pickupFrom,

    ud_order.udf_pickupTill,

    AA.StartInstant as Load_ETA,

    case when AA.exportStatus = ''export''

    and AA.actionState = ''mutable'' then ''assigned''

    when AA.actionState = ''finished'' then ''completed''

    when AA.exportStatus is NULL and AA.actionState = ''mutable'' then ''planned''

    end as LoadStatus,

    BAD.cityname as Bcity_name,

    BAD.statename as Bcity_state,

    BPR.regionCode as Bregion_code,

    ud_order.udf_destinationGrid,

    ud_order.udf_deliverFrom,

    ud_order.udf_deliverTill,

    BA.StartInstant as Deliver_ETA,

    case when BA.exportStatus = ''export''

    and BA.actionState = ''mutable'' then ''assigned''

    when BA.actionState = ''finished'' then ''completed''

    when BA.exportStatus is NULL and BA.actionState = ''mutable'' then ''planned''

    end as DeliverStatus,

    pounds.uvalue as LBS,

    cubes.uvalue as Cube,

    ud_order.udf_miles,

    ud_order.udf_packDate,

    ud_order.udf_packtillDate,

    o.agreed_amount,

    ud_order.udf_PLH,

    ud_order.udf_cars,

    ud_order.udf_otherBulkyItems,

    ud_order.udf_bulkyDescription,

    ok.orderKindName as order_kind_name,

    ud_order.udf_bookingAgent,

    ud_order.udf_R19,

    ud_order.udf_originAgent,

    ud_order.udf_sitOrigin,

    ud_order.udf_destinationAgent,

    ud_order.udf_sitDestination,

    ud_order.udf_selfHaul,

    ud_order.udf_status,

    ud_order.udf_longshort,

    ud_order.udf_priceType,

    ud_order.udf_keyAccount,

    ud_order.udf_crosshaul,

    ud_order.udf_VFC,

    ud_order.udf_valuationtype,

    ud_order.udf_valuationAmount,

    ud_order.udf_COD,

    ud_order.udf_reweigh as Reweigh,

    ud_order.udf_TFD as TFD,

    ud_order.udf_RCVD as RCVD,

    ud_order.udf_sales as SALES,

    AAD.streetName as OriginStreet,

    AAD.phone as OriginPhone,

    BAD.streetName as DestinationStreet,

    BAD.phone as DestinationPhone ,

    BPR.regionCode as DestinationZone,

    BD.departmentName as BA_Name,

    OD.departmentName as OA_Name,

    DD.departmentName as DA_Name,

    ud_shift.udf_camisnr_xhaul as CamisNrCrosshaul

    from

    transport

    left outer join plannedTransport pt on pt.id_transport = transport.id_transport

    join [order] o on o.id_order = transport.id_order

    join ud_order on ud_order.id_order = o.id_order

    join contact c on c.id_contact = o.id_contact

    join orderKind ok on ok.id_orderKind = o.id_orderKind

    LEFT outer join department BD on BD.departmentCode = ud_order.udf_bookingAgent

    LEFT outer join department OD on OD.departmentCode = ud_order.udf_originAgent

    LEFT outer join department DD on DD.departmentCode = ud_order.udf_destinationAgent

    LEFT outer join orderAmount cubes on cubes.id_order = o.id_order

    join unit cu on cu.id_unit = cubes.id_unit

    and cu.unitCode = ''Cube''

    LEFT outer join orderAmount pounds on pounds.id_order = o.id_order

    join unit lu on lu.id_unit = pounds.id_unit

    and lu.unitCode = ''Pounds''

    join task AT on AT.id_task = transport.start_task

    left outer join action AA on AA.id_task = AT.id_task

    join address AAD on AAD.id_address = AT.id_address

    join planRegion APR on APR.id_planRegion = AAD.id_planRegion

    left outer join shift S on AA.id_shift = S.id_shift

    left outer join ud_shift on ud_shift.id_shift = AA.id_shift

    left outer join plannedShift PS on PS.id_shift = S.id_shift

    left outer join department DS on DS.id_department = S.id_department

    left outer join [resourcecombinationitem] DRC

    join resource DRV on DRV.id_resource=DRC.id_resource and resourceTag = 1

    join ud_resource UR on UR.id_resource = DRV.id_resource

    join address DA on DA.id_address = DRV.home_address

    join plannedResource PR on PR.id_resource = DRV.id_resource

    on DRC.[id_resourceCombination]=AA.[id_unionResourceCombi]

    left outer join [resourcecombinationitem] TRC

    join resource TRV on TRV.id_resource=TRC.id_resource and resourceTag = 2

    on TRC.[id_resourceCombination]=AA.[id_unionResourceCombi]

    left outer join [resourcecombinationitem] TRRC

    join resource TTRV on TTRV.id_resource=TRRC.id_resource and resourceTag = 0

    on TRRC.[id_resourceCombination]=AA.[id_unionResourceCombi]

    join task BT on BT.id_task = transport.stop_task

    left outer join action BA on BA.id_task = BT.id_task

    join address BAD on BAD.id_address = BT.id_address

    join planRegion BPR on BPR.id_planRegion = BAD.id_planRegion

    where coalesce(DRV.resourceName, N'''') like @driver

    and coalesce(DRV.resourcecode, N'''') like @drivercode

    and coalesce(S.shiftCode, N'''') like @shiftcode

    and coalesce(S.shiftTemplateCode, N'''') like @camiscode

    and coalesce(o.orderNumber, N'''') like @registration

    and ud_order.udf_pickupFrom > coalesce(@ALPFROM,''1-jan-1900'')

    and ud_order.udf_pickupTill < coalesce(@ALPTILL,''1-jan-1900'')

    and (APR.regionCode in (@fromzones01) or apr.regionCode like ISNULL(@fromzones,''%''))

    and (BPR.regionCode in (@tozones01) or bpr.regionCode like ISNULL(@tozones,''%''))

    and ((isnull(cubes.uvalue, 0) >= @mincubes) and (isnull(cubes.uvalue,0) <= @maxcubes))

    and ((isnull(pounds.uvalue, 0) >= @minlbs) and (isnull(pounds.uvalue,0) <= @maxlbs))

    and (AAD.statename in (@fromstates01) or AAD.statename like ISNULL(@fromstates,''%''))

    and (BAD.statename in (@tostates01) or BAD.statename like ISNULL(@tostates,''%''))

    and isnull(ud_order.udf_selfhaul,'''') like @selfhaul

    and isnull(ud_order.udf_longshort,'''') like @longshort

    and isnull(ud_order.udf_brand,'''') like @brand-2

    and

    case when

    ps.id_shift is null then ''unplanned''

    when

    (BA.actionState = ''finished'') then ''completed''

    when

    (BA.actionState = ''mutable'' or BA.actionState = ''started'')

    and AA.actionState = ''finished'' then ''started''

    when

    AA.exportStatus is null

    and ud_shift.udf_offered = ''offered'' then ''offered''

    when

    AA.exportStatus = ''export'' then ''assigned''

    when

    AA.exportStatus = ''export''

    and ud_shift.udf_offered = ''rejected'' then ''rejected''

    when

    AA.exportStatus is null then ''planned''

    end like @shipmentstatus

    and (bd.departmentcode in (@bas01) or bd.departmentcode like ISNULL(@bas,''%''))

    and (od.departmentcode in (@oas01) or od.departmentcode like ISNULL(@oas,''%''))

    and (dd.departmentcode in (@das01) or dd.departmentcode like ISNULL(@das,''%''))

    and

    (((bd.id_department in (select department from alloweddepartments) or

    od.id_department in (select department from alloweddepartments) or

    dd.id_department in (select department from alloweddepartments)) and @owndepartment = 1)

    or @owndepartment01 = 0)

    --OPTION(RECOMPILE) bladibla

    '

    ,N'@user nvarchar(2000),@driver nvarchar(2000),@drivercode nvarchar(2000),@shiftcode nvarchar(2000),

    @camiscode nvarchar(2000),@registration nvarchar(2000),@ALPFROM date,@ALPTILL date,@fromzones01 nvarchar(4),@fromzones nvarchar(2000)

    ,@tozones01 nvarchar(max) ,@tozones nvarchar(2000),@mincubes int,@maxcubes int,@minlbs int,@maxlbs int,@bas01 nvarchar(max) ,

    @Bas nvarchar(2000),@oas01 nvarchar(max) ,@oas nvarchar(2000),@das01 nvarchar(max) ,@das nvarchar(2000),@fromstates01 nvarchar(2),

    @fromstates nvarchar(2000),@tostates01 nvarchar(max) ,@tostates nvarchar(2000),@selfhaul nvarchar(2000),@longshort nvarchar(2000),

    @brand-2 nvarchar(2000),@shipmentstatus nvarchar(2000),@owndepartment bit,@owndepartment01 bit',

    @user=N'6249',@driver=N'%',@drivercode=N'%%',@shiftcode=N'%',@camiscode=N'%',@registration=N'%',

    @ALPFROM='2010-02-05',@ALPTILL='2050-02-02',@fromzones01=NULL,@fromzones=NULL,@tozones01=NULL,@tozones=NULL,

    @mincubes=0,@maxcubes=1000000,@minlbs=0,@maxlbs=1000000,@bas01=NULL,@bas=NULL,@oas01=NULL,@oas=NULL,@das01=NULL,@das=NULL,

    @fromstates01=N'CA',@fromstates=N'CA',@tostates01=NULL,@tostates=NULL,@selfhaul=N'%',@longshort=N'%',@brand=N'%',

    @shipmentstatus=N'%',@owndepartment=0,@owndepartment01=0

  • Not being nasty, but could you edit that and take out the font tags? Use the [ code ] tags (shortcut on the left)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I've changed the text to normal font, still getting used to the font sizes.

    I did some more research and found some remarks that for the recompile hint the plan is not cached which sounds logical. But might it be that recompile approaches statistics differently or maybe refresh them without saving them (so cached plans are not affected)? It must be somewhere along these lines since the estimaterows are so different.

Viewing 5 posts - 1 through 4 (of 4 total)

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