May 31, 2013 at 8:35 am
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
May 31, 2013 at 8:41 am
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
May 31, 2013 at 8:47 am
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
May 31, 2013 at 9:07 am
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
June 3, 2013 at 3:15 am
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