SQLTeam.com | Weblogs | Forums

How to analyze Execution plan and create missing indexes

Hi All,

I am working on below query which is performing slow. Based on the execution plan it seems that these two JOINS are taking huge amount of data. Can some please give expert advise which indexes should i create on this table.

Execution Plan: --> https://www.brentozar.com/pastetheplan/?id=rk4LZQl7U

JOINS

INNER JOIN dbo.ProductDataTemplate PDT WITH (NOLOCK)
ON PDT.fkProductId = P.ProductId
AND PDT.fkProductTypeId = 2
AND PDT.fkUniverseId = CPU.fkUniverseId

    INNER JOIN dbo.ProductDataTemplate PDPT WITH (NOLOCK)
        ON PDPT.fkProductId = P.ProductId
            AND PDPT.isPartnerTemplate = 1
            AND PDPT.fkUniverseId = CPU.fkUniverseId

Complete Query

DECLARE @BoxId INT = 1217707
DECLARE @PackagingSectionId INT = 1
DECLARE @LanguageId INT = 149

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @PhysicalProductFormatId INT = 1;
DECLARE @BoxCountryId INT = ( SELECT DISTINCT fkCountryId FROM dbo.ProductCountry WHERE fkProductId = @BoxId );
DECLARE @BoxUniverse INT = ( SELECT fkUniverseId FROM dbo.Product INNER JOIN dbo.ProductUniverse ON Product.ProductId = ProductUniverse.fkProductId WHERE Product.ProductId = @BoxId );

SELECT P.ShortName,
        [ProductVersion].[VersionNumber] AS BoxVersion,
        BXL.LanguageName AS BoxLanguage,
        BXL.SBLanguageCode,
        BU.UniverseName AS BoxUniverse,
        P.ProductName AS BoxTitle,
        B.BrandName AS BoxBrand,
        C.CountryName AS BoxCountry,
        PR.PartnerId,
        PR.PartnerName,
        PR.PartnerCode,
        PDPT.fkUniverseId AS PartnerTemplateUniverseId,
        EU.UniverseId,
        EU.UniverseName,
        CP.ProductId AS ExperienceId,
        CP.ProductCode,
        ExperienceCount = DENSE_RANK() OVER (PARTITION BY P.ProductId, PR.PartnerId ORDER BY ExperienceRank DESC, CP.ProductId ),
        EPT.ProductTemplateId,
        PPT.PartnerTemplateId,

--ISNULL([MinNumberOfPeople], '') as [MinNumberOfPeople],
--ISNULL([MaxNumberOfPeople], '') AS [MaxNumberOfPeople]
MinNumberOfPeople = ( SELECT TOP 1 FV.Value FROM dbo.ProductFacetValue PFV INNER JOIN dbo.FacetValue FV ON FV.FacetValueId = PFV.fkFacetValueId WHERE FV.fkFacetId = 45 AND PFV.fkProductId = CP.ProductId ),
MaxNumberOfPeople = ( SELECT TOP 1 FV.Value FROM dbo.ProductFacetValue PFV INNER JOIN dbo.FacetValue FV ON FV.FacetValueId = PFV.fkFacetValueId WHERE FV.fkFacetId = 44 AND PFV.fkProductId = CP.ProductId )
INTO #BoxExtract
FROM dbo.Product P
INNER JOIN dbo.RelatedProduct RP WITH (NOLOCK)
ON RP.fkProductId = P.ProductId
AND RP.Active = 1
INNER JOIN dbo.PartnerProduct PP WITH (NOLOCK)
ON PP.fkProductId = RP.fkProduct1Id
AND PP.Active = 1
INNER JOIN dbo.Partner PR WITH (NOLOCK)
ON PR.PartnerId = PP.fkPartnerId
AND PR.PartnerStatus = 1
INNER JOIN dbo.PartnerLocation PL WITH (NOLOCK)
ON PL.fkPartnerId = PR.PartnerId
INNER JOIN dbo.ProductUniverse PU WITH (NOLOCK)
ON PU.fkProductId = P.ProductId
LEFT JOIN dbo.MarketingRegionUniverse MRU WITH (NOLOCK)
ON MRU.UniverseId = P.fkUniverseBoxId
AND MRU.SubregionISOCode = PL.SubRegionISOCode
LEFT JOIN dbo.ProductBrand PB WITH (NOLOCK)
ON PB.fkProductId = P.ProductId
LEFT JOIN dbo.Brand B WITH (NOLOCK)
ON PB.fkBrandId = B.BrandId
LEFT JOIN dbo.Universe BU WITH (NOLOCK)
ON BU.UniverseId = P.fkUniverseBoxId
INNER JOIN dbo.Product CP WITH (NOLOCK)
ON CP.ProductId = RP.fkProduct1Id
AND CP.fkTriggerStateId = 4
-- LEFT JOIN
--(
-- SELECT PFV.fkProductId
-- ,MAX(CASE WHEN FV.fkfacetid = 45 THEN FV.[Value] ELSE '' END) AS [MinNumberOfPeople]
-- ,MAX(CASE WHEN FV.fkfacetid = 44 THEN FV.[Value] ELSE '' END) AS [MaxNumberOfPeople]
-- FROM ProductFacetValue PFV
-- INNER JOIN facetvalue FV
-- ON FV.FacetValueId = PFV.fkFacetValueId
-- WHERE FV.fkFacetId IN (44,45)
-- GROUP BY PFV.fkProductId
--) prod
--ON prod.fkProductId = CP.ProductId

    INNER JOIN dbo.ProductUniverse CPU WITH (NOLOCK)
        ON CPU.fkProductId = CP.ProductId
    INNER JOIN dbo.Universe EU WITH (NOLOCK)
        ON EU.UniverseId = CPU.fkUniverseId
    INNER JOIN dbo.ProductLanguage BL WITH (NOLOCK)
        ON BL.fkProductId = P.ProductId
    LEFT JOIN dbo.Country PLC WITH (NOLOCK)
        ON PLC.CountryName = PL.Country
    LEFT JOIN dbo.CountryLocale CL WITH (NOLOCK)
        ON CL.fkCountryId = PLC.CountryId
            AND BL.fkLanguageId = CL.fkLanguageId
    INNER JOIN dbo.Language BXL WITH (NOLOCK)
        ON BXL.LanguageId = BL.fkLanguageId
    LEFT JOIN dbo.ProductCountry PC WITH (NOLOCK)
        ON PC.fkProductId = P.ProductId
    LEFT JOIN dbo.Country C WITH (NOLOCK)
        ON C.CountryId = PC.fkCountryId
    INNER JOIN dbo.ProductDataTemplate PDT WITH (NOLOCK)
        ON PDT.fkProductId = P.ProductId
            AND PDT.fkProductTypeId = 2
            AND PDT.fkUniverseId = CPU.fkUniverseId

    INNER JOIN dbo.ProductDataTemplate PDPT WITH (NOLOCK)
        ON PDPT.fkProductId = P.ProductId
            AND PDPT.isPartnerTemplate = 1
            AND PDPT.fkUniverseId = CPU.fkUniverseId
    INNER JOIN dbo.ProductTemplate EPT WITH (NOLOCK)
        ON EPT.fkProductId = CP.ProductId
            AND EPT.fkLanguageId = BL.fkLanguageId
            AND EPT.IsDeleted = 0
            AND EPT.fkProductTemplateId IS NULL
            AND EPT.fkTriggerStateId = 14
            AND EPT.fkDataTemplateId = PDT.fkDataTemplateId

    INNER JOIN dbo.PartnerTemplate PPT WITH (NOLOCK)
        ON PPT.fkPartnerId = PR.PartnerId
            AND PPT.fkLanguageId = BL.fkLanguageId
            AND PPT.IsDeleted = 0
            AND PPT.fkPartnerTemplateId IS NULL
            AND PPT.fkTriggerStateId = 14
            AND PPT.fkDataTemplateId = PDPT.fkDataTemplateId
    LEFT JOIN
    (
        SELECT SUM(ps.Rank) AS ExperienceRank,
                RPPS.fkRelatedProductId
        FROM dbo.RelatedProductPackagingSection RPPS WITH (NOLOCK)
            INNER JOIN dbo.PackagingSection ps WITH (NOLOCK)
                ON ps.PackagingSectionId = RPPS.fkPackagingSectionId
        GROUP BY RPPS.fkRelatedProductId
    ) AS ERank
        ON RP.RelatedProductId = ERank.fkRelatedProductId
    LEFT JOIN dbo.[ProductVersion] WITH (NOLOCK)
        ON [ProductVersion].[fkProductId] = [P].[ProductId]
            AND [ProductVersion].[ProductVersionId] =
            (
                SELECT MAX([ProductVersionId])
                FROM dbo.[ProductVersion] [prv]
                WHERE [prv].[fkProductId] = [P].[ProductId]
                        AND fkProductVersionFormatId = @PhysicalProductFormatId
            )
WHERE P.ProductId = @BoxId
        AND BL.fkLanguageId = @LanguageId --and PR.PartnerId = 8725 
ORDER BY P.ProductId,
            PR.PartnerId,
            CP.ProductId;

There are a lot of KeyLookups in your plan. These are caused by using the index to find the row, but then having to go back to the table to get extra columns. You can avoid this extra step by modifying the indexes that are being used and including the columns you need in the index. This will cause the index to be larger, but these lookups to go away.

Also, try updating your stats. I see the CountryLocale table, it is expecting to read 125 rows, but it actually read ~318,000 rows

As mentioned before you really need to split this query to give the optimizer a chance.
If you cannot be bothered doing this, and as the main problem with the query plan is all the nested loop joins, you could look at upgrading to SQL2019 where the new Adaptive joins may help.

I have just had a quick look at this again while waiting for an upgrade to complete.

If you want a quick and dirty solution you could try putting OPTION (HASH JOIN) at the end of the query:

ORDER BY P.ProductId,
            PR.PartnerId,
            CP.ProductId
OPTION (HASH JOIN);

I would NOT test this on the production database as it could also totally tank the query.