环境:Microsoft SQL Server 2016 (SP2-CU3)企业版

问题SQL：

selectRowNumber=ROW_NUMBER()OVER(--ThisorderingisfromthevariousFulfillmentMapsortorderstomatchthefulfillmentapp'sroworder.ORDERBYhtly.LicenseYear,mht.Name,h.HuntFirstOpenDate,h.DisplayOrder,h.HuntCode,ci_orderby.LastName,ci_orderby.FirstName,fmu.FulfillmentMailingUnitID),ShippingName=ISNULL(fism_aot.ShippingName,dbo.udf_GetCustomerName(c.CustomerID)),FulfillmentMailingUnitID=fmu.FulfillmentMailingUnitID,GoID=goid.IdentityValue,MailingZip=ISNULL(fism_zc.ZipCode,zc.ZipCode),TransactionID=fism_th.TransactionID,TransactionHeaderID=fism_th.TransactionHeaderID,HuntDate=h.HuntFirstOpenDate,HuntCode=h.HuntCode,--HeaderinfoBatchNumber=fmulg.FulfillmentMailingUnitLockGroupID,PrintedByUserName=au.UserName,LockedDate=fmulg.LockedDatefromdbo.FulfillmentMailingUnitLockGroupfmulgcrossjoindbo.Enum_IdentityTypeeitcrossjoindbo.Enum_LicenseActionTypeelatinnerjoindbo.FulfillmentMailingUnitLockfmulonfmulg.FulfillmentMailingUnitLockGroupID=fmul.FulfillmentMailingUnitLockGroupIDinnerjoindbo.FulfillmentMailingUnitfmuonfmul.LockedFulfillmentMailingUnitID=fmu.FulfillmentMailingUnitIDinnerjoindbo.ApplicationUserauonfmulg.LockedByApplicationUserID=au.ApplicationUserID--GettingtotheTransactionHeaderbyFulfillmentInternetSalesMapORFulfillmentDrawIssuanceMapleftjoindbo.FulfillmentInternetSalesMapfismonfmu.FulfillmentMailingUnitID=fism.FulfillmentMailingUnitIDleftjoindbo.FulfillmentDrawIssuanceMapfdimonfmu.FulfillmentMailingUnitID=fdim.FulfillmentMailingUnitIDleftjoindbo.TransactionHeaderthonfism.TransactionHeaderID=th.TransactionHeaderIDorfdim.TransactionHeaderID=th.TransactionHeaderIDleftjoindbo.TransactionHeaderfdim_thonfdim.TransactionHeaderID=fdim_th.TransactionHeaderID--GettingtoLicensefromFulfillmentDrawNotificationMapleftjoindbo.FulfillmentDrawNotificationMapfdnmonfmu.FulfillmentMailingUnitID=fdnm.FulfillmentMailingUnitIDleftjoindbo.DrawTicketLicensefdnm_dtlonfdnm.DrawTicketLicenseID=fdnm_dtl.DrawTicketLicenseIDleftjoindbo.Licensefdnm_lonfdnm_dtl.LicenseID=fdnm_l.LicenseIDleftjoindbo.DrawTicketfdnm_dtonfdnm_dtl.DrawTicketID=fdnm_dt.DrawTicketIDleftjoindbo.DrawTicketHuntChoicefdnm_dthconfdnm_dt.DrawTicketID=fdnm_dthc.DrawTicketIDand(--Ifthedrawticketisawinner,linktothehuntchoicethatwon.(fdnm_dt.WasDrawn=1andfdnm_dthc.WasDrawn=1)--Elseifthedrawticketwasnotawinner,linktothefirsthuntchoicesince--LosingandAlternatenotificationsarenotvalidformulti-choicehuntsor(fdnm_dt.WasDrawn=0andfdnm_dthc.OrderIndex=1))leftjoindbo.TransactionDetailfdim_tdonfdim.TransactionHeaderID=fdim_td.TransactionHeaderIDleftjoindbo.LicenseActionfdim_laonfdim_td.TransactionDetailID=fdim_la.TransactionDetailID--ThismightbesillysinceitshouldonlybeIssuedforissuance...(currentlyit'ssoldinthestoredprocthatissuestags)and(fdim_la.LicenseActionTypeID=elat.Soldorfdim_la.LicenseActionTypeID=elat.Issuedorfdim_la.LicenseActionTypeID=elat.Duplicated)leftjoindbo.Licensefdim_lonfdim_la.LicenseID=fdim_l.LicenseIDleftjoindbo.Hunthonfdnm_dthc.HuntID=h.HuntIDorfdim_l.HuntID=h.HuntIDleftjoindbo.HuntTypeLicenseYearhtlyonh.HuntTypeLicenseYearID=htly.HuntTypeLicenseYearIDleftjoindbo.MasterHuntTypemhtonhtly.MasterHuntTypeID=mht.MasterHuntTypeIDleftjoindbo.Customerconfdnm_l.CustomerID=c.CustomerIDorth.CustomerID=c.CustomerIDleftjoindbo.CustomerIndividualcionc.CustomerID=ci.CustomerIDleftjoindbo.CustomerIdentitygoidonc.CustomerID=goid.CustomerIDandgoid.IdentityTypeID=eit.GOIDandgoid.[Status]=1leftjoindbo.AddressDetailadonc.MailingAddressID=ad.AddressIDandad.IsActive=1leftjoindbo.ZipCodezconad.ZipCodeID=zc.ZipCodeIDleftjoindbo.CustomerIndividualci_orderbyonfdnm_l.CustomerID=ci_orderby.CustomerIDorfdim_th.CustomerID=ci_orderby.CustomerIDleftjoindbo.TransactionHeaderfism_thonfism.TransactionHeaderID=fism_th.TransactionHeaderIDleftjoindbo.ActiveOutdoorsTransactionfism_aotonfism_aot.TransactionID=fism_th.TransactionIDleftjoindbo.AddressDetailfism_adonfism_aot.ShippingAddressID=fism_ad.AddressIDandfism_ad.IsActive=1leftjoindbo.ZipCodefism_zconfism_ad.ZipCodeID=fism_zc.ZipCodeIDwherefmulg.FulfillmentMailingUnitLockGroupID=@FulfillmentMailingUnitLockGroupID

该SQL执行192s后出记录，分析一下sql的执行计划：

分析一：

最终的排序消耗了大量的cost：

分析二：

该SQL存在大量多表连接，MSSQL引擎由于统计信息的算法单一，在处理大量级联连接时，实际数据可能严重偏离统计信息

连接中存在Actual Rows和Estimated Rows严重不一致的情况，随着连接表数目增加，该不一致更加严重：

经过分析，优化的目标是减少多表连接的统计信息不一致导致的执行计划错误并且对最终的排序操作进行外推。

优化的手法主要是利用临时表固化统计信息，外推排序：

最终优化SQL:

selectfmu.FulfillmentMailingUnitID,elat.Sold,elat.Issued,elat.Duplicated,fmulg.FulfillmentMailingUnitLockGroupID,au.UserName,fmulg.LockedDate,eit.GOIDinto#tempfromdbo.FulfillmentMailingUnitLockGroupfmulgcrossjoindbo.Enum_IdentityTypeeitcrossjoindbo.Enum_LicenseActionTypeelatinnerjoindbo.FulfillmentMailingUnitLockfmulonfmulg.FulfillmentMailingUnitLockGroupID=fmul.FulfillmentMailingUnitLockGroupIDinnerjoindbo.FulfillmentMailingUnitfmuonfmul.LockedFulfillmentMailingUnitID=fmu.FulfillmentMailingUnitIDinnerjoindbo.ApplicationUserauonfmulg.LockedByApplicationUserID=au.ApplicationUserIDwherefmulg.FulfillmentMailingUnitLockGroupID=@FulfillmentMailingUnitLockGroupIDselectfdnm_l.CustomerIDfdnm_l_CustomerID,th.CustomerIDth_CustomerID,fdim_th.CustomerIDfdim_th_CustomerID,t.FulfillmentMailingUnitID,h.HuntFirstOpenDate,h.HuntCode,t.FulfillmentMailingUnitLockGroupID,t.UserName,LockedDate,t.GOID,htly.LicenseYear,mht.Name,h.DisplayOrder,--ci_orderby.LastName,--ci_orderby.FirstName,fism.TransactionHeaderIDinto#temp1from#tempt--GettingtotheTransactionHeaderbyFulfillmentInternetSalesMapORFulfillmentDrawIssuanceMapleftjoindbo.FulfillmentInternetSalesMapfismont.FulfillmentMailingUnitID=fism.FulfillmentMailingUnitIDleftjoindbo.FulfillmentDrawIssuanceMapfdimont.FulfillmentMailingUnitID=fdim.FulfillmentMailingUnitIDleftjoindbo.TransactionHeaderthonfism.TransactionHeaderID=th.TransactionHeaderIDorfdim.TransactionHeaderID=th.TransactionHeaderIDleftjoindbo.TransactionHeaderfdim_thonfdim.TransactionHeaderID=fdim_th.TransactionHeaderID--GettingtoLicensefromFulfillmentDrawNotificationMapleftjoindbo.FulfillmentDrawNotificationMapfdnmont.FulfillmentMailingUnitID=fdnm.FulfillmentMailingUnitIDleftjoindbo.DrawTicketLicensefdnm_dtlonfdnm.DrawTicketLicenseID=fdnm_dtl.DrawTicketLicenseIDleftjoindbo.Licensefdnm_lonfdnm_dtl.LicenseID=fdnm_l.LicenseIDleftjoindbo.DrawTicketfdnm_dtonfdnm_dtl.DrawTicketID=fdnm_dt.DrawTicketIDleftjoindbo.DrawTicketHuntChoicefdnm_dthconfdnm_dt.DrawTicketID=fdnm_dthc.DrawTicketIDand(--Ifthedrawticketisawinner,linktothehuntchoicethatwon.(fdnm_dt.WasDrawn=1andfdnm_dthc.WasDrawn=1)--Elseifthedrawticketwasnotawinner,linktothefirsthuntchoicesince--LosingandAlternatenotificationsarenotvalidformulti-choicehuntsor(fdnm_dt.WasDrawn=0andfdnm_dthc.OrderIndex=1))leftjoindbo.TransactionDetailfdim_tdonfdim.TransactionHeaderID=fdim_td.TransactionHeaderIDleftjoindbo.LicenseActionfdim_laonfdim_td.TransactionDetailID=fdim_la.TransactionDetailID--ThismightbesillysinceitshouldonlybeIssuedforissuance...(currentlyit'ssoldinthestoredprocthatissuestags)and(fdim_la.LicenseActionTypeID=t.Soldorfdim_la.LicenseActionTypeID=t.Issuedorfdim_la.LicenseActionTypeID=t.Duplicated)leftjoindbo.Licensefdim_lonfdim_la.LicenseID=fdim_l.LicenseIDleftjoindbo.Hunthonfdnm_dthc.HuntID=h.HuntIDorfdim_l.HuntID=h.HuntIDleftjoindbo.HuntTypeLicenseYearhtlyonh.HuntTypeLicenseYearID=htly.HuntTypeLicenseYearIDleftjoindbo.MasterHuntTypemhtonhtly.MasterHuntTypeID=mht.MasterHuntTypeID--setstatisticsioon--setstatisticstimeonselectt1.LicenseYear,t1.Name,t1.DisplayOrder,c.CustomerID,t1.FulfillmentMailingUnitID,t1.GOID,zc.ZipCode,t1.HuntFirstOpenDate,t1.HuntCode,t1.FulfillmentMailingUnitLockGroupID,t1.UserName,t1.LockedDate,t1.fdnm_l_CustomerID,t1.fdim_th_CustomerID,t1.TransactionHeaderIDinto#temp2from#temp1t1--GettingtoCusotmerfromthejoinedtransactionheaderorthelicensefromtheDrawTicketLicenseleftjoindbo.Customercont1.fdnm_l_CustomerID=c.CustomerIDort1.th_CustomerID=c.CustomerIDleftjoindbo.CustomerIndividualcionc.CustomerID=ci.CustomerIDleftjoindbo.AddressDetailadonc.MailingAddressID=ad.AddressIDandad.IsActive=1leftjoindbo.ZipCodezconad.ZipCodeID=zc.ZipCodeIDselectt2.LicenseYear,t2.Name,t2.DisplayOrder,ci_orderby.LastName,ci_orderby.FirstName,ShippingName=ISNULL(fism_aot.ShippingName,dbo.udf_GetCustomerName(t2.CustomerID)),FulfillmentMailingUnitID=t2.FulfillmentMailingUnitID,GoID=goid.IdentityValue,MailingZip=ISNULL(fism_zc.ZipCode,t2.ZipCode),TransactionID=fism_th.TransactionID,TransactionHeaderID=fism_th.TransactionHeaderID,HuntDate=t2.HuntFirstOpenDate,HuntCode=t2.HuntCode,--HeaderinfoBatchNumber=t2.FulfillmentMailingUnitLockGroupID,PrintedByUserName=t2.UserName,LockedDate=t2.LockedDateinto#temp3from#temp2t2leftjoindbo.CustomerIdentitygoidont2.CustomerID=goid.CustomerIDandgoid.IdentityTypeID=t2.GOIDandgoid.[Status]=1leftjoindbo.CustomerIndividualci_orderbyont2.fdnm_l_CustomerID=ci_orderby.CustomerIDort2.fdim_th_CustomerID=ci_orderby.CustomerIDleftjoindbo.TransactionHeaderfism_thont2.TransactionHeaderID=fism_th.TransactionHeaderIDleftjoindbo.ActiveOutdoorsTransactionfism_aotonfism_aot.TransactionID=fism_th.TransactionIDleftjoindbo.AddressDetailfism_adonfism_aot.ShippingAddressID=fism_ad.AddressIDandfism_ad.IsActive=1leftjoindbo.ZipCodefism_zconfism_ad.ZipCodeID=fism_zc.ZipCodeIDselectRowNumber=ROW_NUMBER()OVER(--ThisorderingisfromthevariousFulfillmentMapsortorderstomatchthefulfillmentapp'sroworder.ORDERBYt3.LicenseYear,t3.Name,t3.HuntDate,t3.DisplayOrder,t3.HuntCode,t3.LastName,t3.FirstName,t3.FulfillmentMailingUnitID),ShippingName,FulfillmentMailingUnitID,GoID,MailingZip,TransactionID,TransactionHeaderID,HuntDate,HuntCode,--HeaderinfoBatchNumber,PrintedByUserName,LockedDatefrom#temp3t3droptable#tempdroptable#temp1droptable#temp2droptable#temp3

经过测试，执行时间由192秒降低到2秒。