SQL Server一次SQL调优案例

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

问题SQL：

select         RowNumber = ROW_NUMBER() OVER         (                 -- This ordering is from the various Fulfillment Map sort orders to match the fulfillment app's row order.                ORDER BY                         htly.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,        -- Header info        BatchNumber = fmulg.FulfillmentMailingUnitLockGroupID,        PrintedByUserName = au.UserName,        LockedDate = fmulg.LockedDatefrom        dbo.FulfillmentMailingUnitLockGroup fmulg        cross join dbo.Enum_IdentityType eit        cross join dbo.Enum_LicenseActionType elat        inner join dbo.FulfillmentMailingUnitLock fmul                on fmulg.FulfillmentMailingUnitLockGroupID = fmul.FulfillmentMailingUnitLockGroupID        inner join dbo.FulfillmentMailingUnit fmu                on fmul.LockedFulfillmentMailingUnitID = fmu.FulfillmentMailingUnitID        inner join dbo.ApplicationUser au                on fmulg.LockedByApplicationUserID = au.ApplicationUserID        -- Getting to the Transaction Header by FulfillmentInternetSalesMap OR FulfillmentDrawIssuanceMap        left join dbo.FulfillmentInternetSalesMap fism                on fmu.FulfillmentMailingUnitID = fism.FulfillmentMailingUnitID        left join dbo.FulfillmentDrawIssuanceMap fdim                on fmu.FulfillmentMailingUnitID = fdim.FulfillmentMailingUnitID                left join dbo.TransactionHeader th                               on fism.TransactionHeaderID = th.TransactionHeaderID                                or fdim.TransactionHeaderID = th.TransactionHeaderID        left join dbo.TransactionHeader fdim_th                on fdim.TransactionHeaderID = fdim_th.TransactionHeaderID        -- Getting to License from FulfillmentDrawNotificationMap        left join dbo.FulfillmentDrawNotificationMap fdnm                on fmu.FulfillmentMailingUnitID = fdnm.FulfillmentMailingUnitID                left join dbo.DrawTicketLicense fdnm_dtl                        on fdnm.DrawTicketLicenseID = fdnm_dtl.DrawTicketLicenseID                 left join dbo.License fdnm_l                        on fdnm_dtl.LicenseID = fdnm_l.LicenseID                left join dbo.DrawTicket fdnm_dt                        on fdnm_dtl.DrawTicketID = fdnm_dt.DrawTicketID                left join dbo.DrawTicketHuntChoice fdnm_dthc                        on                                 fdnm_dt.DrawTicketID = fdnm_dthc.DrawTicketID                                and                                (                                        -- If the draw ticket is a winner, link to the hunt choice that won.                                        (fdnm_dt.WasDrawn = 1 and fdnm_dthc.WasDrawn = 1)                                        -- Else if the draw ticket was not a winner, link to the first hunt choice since                                        -- Losing and Alternate notifications are not valid for multi-choice hunts                                        or (fdnm_dt.WasDrawn = 0 and fdnm_dthc.OrderIndex = 1)                                )                left join dbo.TransactionDetail fdim_td                        on fdim.TransactionHeaderID = fdim_td.TransactionHeaderID                left join dbo.LicenseAction fdim_la                        on fdim_td.TransactionDetailID = fdim_la.TransactionDetailID                                -- This might be silly since it should only be Issued for issuance... (currently it's sold in the stored proc that issues tags)                                and (fdim_la.LicenseActionTypeID = elat.Sold or fdim_la.LicenseActionTypeID = elat.Issued or fdim_la.LicenseActionTypeID = elat.Duplicated)                left join dbo.License fdim_l                        on fdim_la.LicenseID = fdim_l.LicenseID                                left join dbo.Hunt h                        on fdnm_dthc.HuntID = h.HuntID                                or fdim_l.HuntID = h.HuntID                left join dbo.HuntTypeLicenseYear htly                        on h.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearID                left join dbo.MasterHuntType mht                        on htly.MasterHuntTypeID = mht.MasterHuntTypeID        left join dbo.Customer c                on fdnm_l.CustomerID = c.CustomerID                        or th.CustomerID = c.CustomerID        left join dbo.CustomerIndividual ci                on c.CustomerID = ci.CustomerID        left join dbo.CustomerIdentity goid                on c.CustomerID = goid.CustomerID                        and goid.IdentityTypeID = eit.GOID                        and goid.[Status] = 1        left join dbo.AddressDetail ad                on c.MailingAddressID = ad.AddressID                        and ad.IsActive = 1        left join dbo.ZipCode zc                on ad.ZipCodeID = zc.ZipCodeID        left join dbo.CustomerIndividual ci_orderby                on fdnm_l.CustomerID = ci_orderby.CustomerID                        or fdim_th.CustomerID = ci_orderby.CustomerID                left join dbo.TransactionHeader fism_th                  on fism.TransactionHeaderID = fism_th.TransactionHeaderID        left join dbo.ActiveOutdoorsTransaction fism_aot                on fism_aot.TransactionID = fism_th.TransactionID        left join dbo.AddressDetail fism_ad                 on fism_aot.ShippingAddressID = fism_ad.AddressID                        and fism_ad.IsActive = 1        left join dbo.ZipCode fism_zc                on fism_ad.ZipCodeID = fism_zc.ZipCodeID        where                fmulg.FulfillmentMailingUnitLockGroupID = @FulfillmentMailingUnitLockGroupID

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

分析一：

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

分析二：

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

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

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

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

最终优化SQL:

select         fmu.FulfillmentMailingUnitID        ,elat.Sold        ,elat.Issued        ,elat.Duplicated        ,fmulg.FulfillmentMailingUnitLockGroupID        ,au.UserName        ,fmulg.LockedDate        ,eit.GOID        into #temp        from         dbo.FulfillmentMailingUnitLockGroup fmulg        cross join dbo.Enum_IdentityType eit        cross join dbo.Enum_LicenseActionType elat        inner join dbo.FulfillmentMailingUnitLock fmul                on fmulg.FulfillmentMailingUnitLockGroupID = fmul.FulfillmentMailingUnitLockGroupID        inner join dbo.FulfillmentMailingUnit fmu                on fmul.LockedFulfillmentMailingUnitID = fmu.FulfillmentMailingUnitID        inner join dbo.ApplicationUser au                on fmulg.LockedByApplicationUserID = au.ApplicationUserID        where                fmulg.FulfillmentMailingUnitLockGroupID = @FulfillmentMailingUnitLockGroupID        select                 fdnm_l.CustomerID fdnm_l_CustomerID,                th.CustomerID th_CustomerID,                fdim_th.CustomerID fdim_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.TransactionHeaderID                into #temp1                from #temp t                        -- Getting to the Transaction Header by FulfillmentInternetSalesMap OR FulfillmentDrawIssuanceMap                left join dbo.FulfillmentInternetSalesMap fism                        on t.FulfillmentMailingUnitID = fism.FulfillmentMailingUnitID                left join dbo.FulfillmentDrawIssuanceMap fdim                        on t.FulfillmentMailingUnitID = fdim.FulfillmentMailingUnitID                left join dbo.TransactionHeader th                               on fism.TransactionHeaderID = th.TransactionHeaderID                                or fdim.TransactionHeaderID = th.TransactionHeaderID                left join dbo.TransactionHeader fdim_th                        on fdim.TransactionHeaderID = fdim_th.TransactionHeaderID                -- Getting to License from FulfillmentDrawNotificationMap                left join dbo.FulfillmentDrawNotificationMap fdnm                        on t.FulfillmentMailingUnitID = fdnm.FulfillmentMailingUnitID                left join dbo.DrawTicketLicense fdnm_dtl                        on fdnm.DrawTicketLicenseID = fdnm_dtl.DrawTicketLicenseID                 left join dbo.License fdnm_l                        on fdnm_dtl.LicenseID = fdnm_l.LicenseID                left join dbo.DrawTicket fdnm_dt                        on fdnm_dtl.DrawTicketID = fdnm_dt.DrawTicketID                left join dbo.DrawTicketHuntChoice fdnm_dthc                        on                                 fdnm_dt.DrawTicketID = fdnm_dthc.DrawTicketID                                and                                (                                        -- If the draw ticket is a winner, link to the hunt choice that won.                                        (fdnm_dt.WasDrawn = 1 and fdnm_dthc.WasDrawn = 1)                                        -- Else if the draw ticket was not a winner, link to the first hunt choice since                                        -- Losing and Alternate notifications are not valid for multi-choice hunts                                        or (fdnm_dt.WasDrawn = 0 and fdnm_dthc.OrderIndex = 1)                                )                left join dbo.TransactionDetail fdim_td                        on fdim.TransactionHeaderID = fdim_td.TransactionHeaderID                left join dbo.LicenseAction fdim_la                        on fdim_td.TransactionDetailID = fdim_la.TransactionDetailID                                -- This might be silly since it should only be Issued for issuance... (currently it's sold in the stored proc that issues tags)                                and (fdim_la.LicenseActionTypeID = t.Sold or fdim_la.LicenseActionTypeID = t.Issued or fdim_la.LicenseActionTypeID = t.Duplicated)                left join dbo.License fdim_l                        on fdim_la.LicenseID = fdim_l.LicenseID                        left join dbo.Hunt h                        on fdnm_dthc.HuntID = h.HuntID                                or fdim_l.HuntID = h.HuntID                left join dbo.HuntTypeLicenseYear htly                        on h.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearID                left join dbo.MasterHuntType mht                        on htly.MasterHuntTypeID = mht.MasterHuntTypeID        --set statistics io on        --set statistics time on        select                 t1.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.TransactionHeaderID         into #temp2         from #temp1 t1                        -- Getting to Cusotmer from the joined transaction header or the license from the DrawTicketLicense                left join dbo.Customer c                        on t1.fdnm_l_CustomerID = c.CustomerID                                or t1.th_CustomerID = c.CustomerID                left join dbo.CustomerIndividual ci                        on c.CustomerID = ci.CustomerID                                left join dbo.AddressDetail ad                        on c.MailingAddressID = ad.AddressID                                and ad.IsActive = 1                left join dbo.ZipCode zc                        on ad.ZipCodeID = zc.ZipCodeID                        select                 t2.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,                -- Header info                BatchNumber = t2.FulfillmentMailingUnitLockGroupID,                PrintedByUserName = t2.UserName,                LockedDate = t2.LockedDate         into #temp3        from #temp2 t2                        left join dbo.CustomerIdentity goid                                on t2.CustomerID = goid.CustomerID                                and goid.IdentityTypeID = t2.GOID                                 and goid.[Status] = 1                        left join dbo.CustomerIndividual ci_orderby                                on t2.fdnm_l_CustomerID = ci_orderby.CustomerID                                        or t2.fdim_th_CustomerID = ci_orderby.CustomerID                                left join dbo.TransactionHeader fism_th                                  on t2.TransactionHeaderID = fism_th.TransactionHeaderID                        left join dbo.ActiveOutdoorsTransaction fism_aot                                on fism_aot.TransactionID = fism_th.TransactionID                        left join dbo.AddressDetail fism_ad                                 on fism_aot.ShippingAddressID = fism_ad.AddressID                                        and fism_ad.IsActive = 1                        left join dbo.ZipCode fism_zc                                on fism_ad.ZipCodeID = fism_zc.ZipCodeID        select  RowNumber = ROW_NUMBER() OVER                 (                         -- This ordering is from the various Fulfillment Map sort orders to match the fulfillment app's row order.                        ORDER BY                                 t3.LicenseYear,                                 t3.Name,                                 t3.HuntDate,                                 t3.DisplayOrder,                                t3.HuntCode,                                t3.LastName,                                t3.FirstName,                                t3.FulfillmentMailingUnitID                ),                ShippingName,                FulfillmentMailingUnitID,                GoID,                MailingZip,                TransactionID,                TransactionHeaderID,                HuntDate,                HuntCode,                -- Header info                BatchNumber,                PrintedByUserName,                LockedDate        from #temp3 t3        drop table #temp        drop table #temp1        drop table #temp2        drop table #temp3

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