SQL Server Performance

SQL Server 2005 EE Performance Problem - No Parallel Execution Plan for Large Query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by cmwahlqu, Feb 1, 2008.

  1. cmwahlqu New Member

    Hi, I am working on a DW/BI project and I have a 16 way, 32 GB of ram system attached to a SAN with 32 arms. Our database is 50GB and growing quickly. I am have a large join query that is very slow, and does not execute using a parallel plan. I checked what queries where running in parallel and there was only one smaller query. I have parallelism enabled, and the threshold set to 2. I have the memory allocated to a max of 30gb and AWE on. I am looking for some help on figuring out why this query will not run in parallel, and or faster. Right now it takes about 16min to process, the main item table is 400,000 records with a few other tables of comparable size. I ran it through the tunning ad visor and created all of the suggested indexes and stats. Here is the query I am working on.
    SELECT
    I.ItemID,
    I.Item,
    I.ItemStockingUoM,
    I.ItemPurchaseUoM,
    I.ItemSellingUoM,
    I.ItemPurchaseToStockingUoMConversionFactor,
    I.ItemSellingUoMConversionFactor,
    --
    -- Item Type
    --
    I.ItemTypeID,
    IT.ItemType,
    ITGL.GLBPCSAccountID ItemTypeStockingGLAccountID,
    ITGL.GLBPCSAccount ItemTypeStockingGLAccount,
    IT.ItemTypeManufacturingFlag ItemManufacturingFlag,
    I.ItemTypeID HistItemTypeID,
    IT.ItemType HistItemType,
    ITGL.GLBPCSAccountID HistItemTypeStockingGLAccountID,
    ITGL.GLBPCSAccount HistItemTypeStockingGLAccount,
    IT.ItemTypeManufacturingFlag HistItemManufacturingFlag,

    I.ItemDaysLeadTime,
    I.ItemMinimumBalance,
    I.ItemListPrice,
    I.ItemListPrice HistItemListPrice,
    --
    -- Standard Cost
    --
    I.ItemStandardCostItemID,
    SC.StandardCost ItemStandardCostPerUnit,

    I.ItemLowLevelCodeForMRP,
    I.ItemDiscountCode,
    I.ItemCycleCountsPerYear,
    I.ItemProcessString,
    I.ItemRevisionLevel,
    I.ItemMasterScheduled,
    I.ItemMRPActivityFlag,
    --
    -- Order Policy
    --
    I.ItemOrderPolicyID,
    OP.ItemOrderPolicy,

    I.ItemLotSize,
    I.ItemMustSingleIssueFlag,
    I.ItemOrderSetupCost,
    I.ItemExtraDescription,
    I.ItemWeightPerUoM,
    I.ItemABCInventoryCode,
    I.ItemGroupCode,
    I.ItemPackagingSizeCode,
    --
    -- Primary Vendor
    --
    I.ItemPrimaryVendorID,
    PV.VendorName ItemPrimaryVendorName,

    I.ItemPrimaryVendorItemNumber,
    --
    -- Alternate Vendor
    --
    I.ItemAlternateVendorID,
    AV.VendorName ItemAlternateVendorName,

    I.ItemAlternateVendorItemNumber,
    I.ItemPlannerCode,
    I.ItemPrintCode,
    I.ItemTaxCode,
    I.ItemLotControlItemFlag,
    I.ItemIncrementalOrderQuantity,
    I.ItemStandardBatchSize,
    I.ItemCycleCountFlag,
    I.ItemPeriodSize,
    I.ItemStandardYieldPercent,
    I.ItemPeriodOrderDays,
    I.ItemHorizonDays,
    I.ItemForecastedItemFlag,
    --
    -- Cost Bucket
    --
    I.ItemMaterialCostBucketID,
    CB.CostBucketShortDescription ItemMaterialCostBucketShortDescription,
    CB.CostBucket ItemMaterialCostBucket,
    CB.CostBucketTypeID ItemMaterialCostBucketTypeID,
    CB.CostBucketType ItemMaterialCostBucketType,

    I.ItemUnitsPerPallet,
    --
    -- Default Warehouse
    --
    I.ItemDefaultWarehouseID,
    WH.Warehouse ItemDefaultWarehouse,

    I.ItemDefaultWarehouseLocationWarehouseID,
    I.ItemDefaultWarehouseLocation,
    I.ItemShopOrderFlag,
    --
    -- Last Cycle Count Date
    --
    CD.DateKey ItemLastCycleCountDateKey,

    I.ItemJITFlag,
    I.ItemJITMaximumRunRate,
    I.ItemJITMinimumRunRate,
    I.ItemUnitsPerContainer,
    I.ItemMinimumPlannedOrderPercent,
    I.ItemGroupTechnologyCode2,
    I.ItemMaximumProductionSize,
    I.ItemDemandTimeFence,
    --
    -- Demand Code 1
    --
    I.ItemDemandCode1ID,
    ID1.ItemDemand ItemDemandCode1,
    --
    -- Demand Code 2
    --
    I.ItemDemandCode2ID,
    ID2.ItemDemand ItemDemandCode2,

    I.ItemMinBalanceHorizonDaysOrPeriod,
    I.ItemMinBalanceDaysOrPeriod,
    I.ItemDailyLeadtimeRate,
    --
    -- Buyer
    --
    I.ItemBuyerID,
    B.BuyerName ItemBuyerName,

    I.ItemDiscrete,
    I.ItemTargetAnnualQuantity,
    I.ItemMaximumInventoryQuantity,
    I.ItemRequirementsCode,
    I.ItemValueBoxReference,
    --
    -- Item Status
    --
    I.ItemStatusID,
    IST.ItemStatus,
    --
    -- Record Create Date
    --
    RCD.TheDate ItemRecordCreateDate,

    I.ItemRecordStatus,
    I.ItemRecordStatus HistItemRecordStatus,
    I.ItemConditionStatus,
    I.ItemReference1,
    I.ItemReference2,
    I.ItemRoyaltyFlag,
    I.ItemReference4,
    I.ItemReference5,
    I.ItemCountryOfOrigin,
    I.ItemFormulatorCode,
    I.ItemBypassWIPFlag,
    I.ItemAverageLoTSize,
    I.ItemVerificationRequired,
    I.ItemSpecification,
    I.ItemTestingLevel,
    I.ItemDropShipAllowed,
    I.ItemRetailPackageQuantity,
    I.ItemManufacturingUnitsPerPack,
    I.ItemFlowRackStockingQuantity,
    I.ItemVolumn,
    I.ItemDistributionRestockQuantity,
    I.ItemCanadianListPrice,
    --
    -- Start Ship Date
    --
    SSD.DateKey ItemStartShipDateKey,
    --
    -- Obsolete Date
    --
    OD.DateKey ItemObsoleteDateKey,
    --
    -- Card Size
    --
    I.ItemCardSizeID,
    CS.CardSize ItemCardSize,
    CS.CardSizeDescription ItemCardSizeDescription,
    CS.CardWidth ItemCardWidth,
    CS.CardHeight ItemCardHeight,
    CS.CardPanels ItemCardPanels,

    I.ItemEnvelope,
    I.ItemDevelpmentCode ItemDevelopmentCode,
    I.ItemRetailUnitPrice,
    I.ItemPackageStructureCode,
    --
    -- Discontinued Date
    --
    DD.DateKey ItemDiscontinuedDateKey,
    --
    -- Closeout Date
    --
    COD.DateKey ItemCloseoutDateKey,

    I.ItemCanadaRetailUnitPrice,
    I.ItemPrimaryHarmonizationCode,
    I.ItemSecondaryHarmonizationCode,
    I.ItemCommercialDescription,
    I.ItemBackorderFlag,
    I.ItemMinimumAllocateQuantity,
    --
    -- UPC
    --
    UPC.ItemUPC,
    --
    -- ISBN
    --
    ISBN.ItemISBN,
    --
    -- Alternate ISBN
    --
    AISBN.ItemAlternateISBN,
    --
    -- Price Point UPC
    --
    PP.ItemPricePointUPC,
    --
    -- UPC Plus
    --
    PPP.ItemUPCPlus,
    --
    -- Prime
    --
    I.PrimeID,
    P.PrimeCurrentShippingItemID,
    P.PrimeRetailUnitsStandardCost,
    P.PrimeRetailMarkup,
    P.PrimeProgramCode,
    P.PrimeProgramDescription,
    P.PrimeEarliestShipDate,
    P.PrimeSystemRank,
    P.PrimeRankWeightID,
    P.PrimeAverageRetailUnitRank,
    P.PrimeAverageRetailDollarRank,
    P.PrimeAverageContributionRank,
    P.PrimeSampleReturnPercentageIndex,
    P.PrimeSampleNetUnitsPerAccountIndex,
    P.PrimeSampleNetDollarsPerAccountIndex,
    P.PrimeSampleContributionPerAccountIndex,
    P.PrimeUserDefinedRank,
    P.PrimeSKUSelectionRank,
    P.PrimeSystemRankDetailDollars,
    P.PrimeSystemRankRetailUnits,
    P.PrimeSystemRankGrossDollars,
    P.PrimeMinimumShipDate,
    P.PrimeMaximumShipdate,
    P.PrimeExposureToCustomer,
    P.PrimeRetailReturnQuantity,
    P.PrimeUniqueCountOfCustomers,
    P.PrimeBackCardNumberFromBOM,
    P.PrimeBackCardCaption,
    P.PrimeMonarchItemID,
    P.MonarchProductLineID,
    P.MonarchConsumerNeedID,
    P.MonarchProductLine,
    P.MonarchSendingOccasion,
    P.MonarchMajorOccasion,
    P.MonarchConsumerNeed,
    P.MonarchDesignID,
    P.MonarchDesignCode,
    P.MonarchDesign,
    P.MonarchDesignType,
    P.MonarchProductStyle,
    P.MonarchEditorialIntimacyLevel,
    P.MonarchSubjectMatter,
    P.MonarchSenderGender,
    P.MonarchSenderFaithSegment,
    P.MonarchSenderLifeStage,
    P.MonarchReceiverGender,
    P.MonarchReceiverFaithSegment,
    P.MonarchReceiverLifeStage,
    P.EditorialWordCount,
    P.EditorialHow1,
    P.EditorialWhat1,
    P.EditorialWhat2,
    P.EditorialOccasion,
    P.EditorialBibleBook,
    P.EditorialBibleReference,
    P.EditorialBibleVersion,
    P.EditorialMessageWriter,
    P.EditorialQuoteWriter,
    P.ProdalogArtID,
    P.ProdalogArtistName,
    P.ProdalogTraditionalArtistName,
    P.ProdalogDigitalArtistName,
    P.ProdalogHandLetterArtistName,
    P.ProdalogGraphicArtistName,
    P.ProdalogConceptDesignerName,
    P.ProdalogSeniorDesignerName,
    P.ProdalogTechnicalDesignerName,
    P.ProdalogCreativeManagerName,
    P.ProdalogArtCategory,
    P.ProdalogArtType,
    P.ProdalogArtStyle,
    P.ProdalogPrimeImageURL,
    --
    -- Product Line
    --
    I.ProductLineID,
    PL.ProductLine,
    PL.ProductLineGroup1,
    PL.ProductLineGroup2,
    PL.ProductLineManager,
    PL.ProductLineGroup4,
    PL.ProductLineGroup5,
    PL.ProductLineRecordID,
    PL.ProductLineReportRowNumber,
    PL.ProductLineAllowCPRFlag,
    PL.ProductLineCPREffFromDate,
    PL.ProductLineCPREffToDate,
    PL.ProductLineSummaryID,
    PL.ProductLineSummary,
    PL.ProductLineSummaryManager,
    PL.ProductLineSummaryDemandPattern,
    PL.ProductLineSummaryDemandPlanner,
    PL.ProductLineSummaryGroup4,
    PL.ProductLineSummaryGroup5,
    PL.ProductLineSummaryGrossMarginTemplate,
    PL.ProductGroupID,
    PL.ProductGroup,
    PL.ProductGroupDefaultReturnAddress,
    PL.ProductGroupDefaultReturnPercent,
    PL.ProductGroupDefaultAutoOrderCode,
    PL.ProductGroupDemandPattern,
    PL.ProductGroupGroup2,
    PL.ProductGroupGroup3,
    PL.ProductGroupGroup4,
    PL.ProductGroupGroup5,
    PL.ProductCategoryID,
    PL.ProductCategory,
    PL.ProductCategoryGroup1,
    PL.ProductCategoryGroup2,
    PL.ProductCategoryGroup3,
    PL.ProductCategoryGroup4,
    PL.ProductCategoryGroup5,
    PL.ProductSuperCategoryID,
    PL.ProductSuperCategory,
    PL.ProductSuperCategoryGroup1,
    PL.ProductSuperCategoryGroup2,
    PL.ProductSuperCategoryGroup3,
    PL.ProductSuperCategoryGroup4,
    PL.ProductSuperCategoryGroup5,
    PL.ProductTierID,
    PL.ProductTier,
    PL.ProductTierGroup1,
    PL.ProductTierGroup2,
    PL.ProductTierGroup3,
    PL.ProductTierGroup4,
    PL.ProductTierGroup5,
    I.ProductLineID HistProductLineID,
    PL.HistProductLine,
    PL.HistProductLineGroup1,
    PL.HistProductLineGroup2,
    PL.HistProductLineManager,
    PL.HistProductLineGroup4,
    PL.HistProductLineGroup5,
    PL.HistProductLineRecordID,
    PL.HistProductLineReportRowNumber,
    PL.HistProductLineAllowCPRFlag,
    PL.HistProductLineCPREffFromDate,
    PL.HistProductLineCPREffToDate,
    PL.HistProductLineSummaryID,
    PL.HistProductLineSummary,
    PL.HistProductLineSummaryManager,
    PL.HistProductLineSummaryDemandPattern,
    PL.HistProductLineSummaryDemandPlanner,
    PL.HistProductLineSummaryGroup4,
    PL.HistProductLineSummaryGroup5,
    PL.HistProductLineSummaryGrossMarginTemplate,
    PL.HistProductGroupID,
    PL.HistProductGroup,
    PL.HistProductGroupDefaultReturnAddress,
    PL.HistProductGroupDefaultReturnPercent,
    PL.HistProductGroupDefaultAutoOrderCode,
    PL.HistProductGroupDemandPattern,
    PL.HistProductGroupGroup2,
    PL.HistProductGroupGroup3,
    PL.HistProductGroupGroup4,
    PL.HistProductGroupGroup5,
    PL.HistProductCategoryID,
    PL.HistProductCategory,
    PL.HistProductCategoryGroup1,
    PL.HistProductCategoryGroup2,
    PL.HistProductCategoryGroup3,
    PL.HistProductCategoryGroup4,
    PL.HistProductCategoryGroup5,
    PL.HistProductSuperCategoryID,
    PL.HistProductSuperCategory,
    PL.HistProductSuperCategoryGroup1,
    PL.HistProductSuperCategoryGroup2,
    PL.HistProductSuperCategoryGroup3,
    PL.HistProductSuperCategoryGroup4,
    PL.HistProductSuperCategoryGroup5,
    PL.HistProductTierID,
    PL.HistProductTier,
    PL.HistProductTierGroup1,
    PL.HistProductTierGroup2,
    PL.HistProductTierGroup3,
    PL.HistProductTierGroup4,
    PL.HistProductTierGroup5,
    --
    -- Item Class
    --
    I.ItemClassID,
    IC.ItemClassDescription ItemClass,
    IC.ItemClassTypeID,
    IC.ItemClassType,
    IC.ItemClassKey,
    I.ItemClassID HistItemClassID,
    IC.ItemClassKey HistItemClassKey
    FROM
    Staging.Item I WITH (ROWLOCK)
    JOIN Staging.ItemType IT ON I.ItemTypeID = IT.ItemTypeID
    JOIN Golden.DimGeneralLedger ITGL ON IT.ItemTypeStockingGLAccountKey = ITGL.GLAccountKey
    JOIN Staging.ItemStandardCost SC ON I.ItemStandardCostItemID = SC.StandardCostItemID
    JOIN Staging.ItemOrderPolicy OP ON I.ItemOrderPolicyID = OP.ItemOrderPolicyID
    JOIN Staging.Vendor PV ON I.ItemPrimaryVendorID = PV.VendorID
    JOIN Staging.Vendor AV ON I.ItemAlternateVendorID = AV.VendorID
    JOIN Staging.CostBucket CB ON I.ItemMaterialCostBucketID = CB.CostBucketID
    JOIN Staging.Warehouse WH ON I.ItemDefaultWarehouseID = WH.WarehouseID
    JOIN Golden.DimDateDate CD ON I.ItemLastCycleCountNumericDate = CD.NumericDate
    JOIN Staging.ItemDemand ID1 ON I.ItemDemandCode1ID = ID1.ItemDemandID
    JOIN Staging.ItemDemand ID2 ON I.ItemDemandCode2ID = ID2.ItemDemandID
    JOIN Staging.Buyer B ON I.ItemBuyerID = B.BuyerID
    JOIN Staging.ItemStatus IST ON I.ItemStatusID = IST.ItemStatusID
    JOIN Golden.DimDateDate RCD ON I.ItemRecordCreateNumericDate = RCD.NumericDate
    JOIN Golden.DimDateDate SSD ON I.ItemStartShipDateID = SSD.DateID
    JOIN Golden.DimDateDate OD ON I.ItemObsoleteDateID = OD.DateID
    JOIN Staging.CardSize CS ON I.ItemCardSizeID = CS.CardSizeID
    JOIN Golden.DimDateDate DD ON I.ItemDiscontinuedDateID = DD.DateID
    JOIN Golden.DimDateDate COD ON I.ItemCloseoutDateID = COD.DateID
    JOIN Staging.ItemUPC UPC ON I.ItemUPCItemID = UPC.ItemID
    JOIN Staging.ItemISBN ISBN ON I.ItemISBNItemID = ISBN.ItemID
    JOIN Staging.ItemAlternateISBN AISBN ON I.ItemAlternateISBNItemID = AISBN.ItemID
    JOIN Staging.ItemPricePointUPC PP ON I.ItemPricePointUPCItemID = PP.ItemID
    JOIN Staging.ItemUPCPlus PPP ON I.ItemUPCPlusItemID = PPP.ItemID
    JOIN Golden.DimPrime P ON I.PrimeID = P.PrimeID
    JOIN Golden.DimProductLine PL ON I.ProductLineID = PL.ProductLineID AND 'Current' = PL.ProductLineCurrentFlag
    JOIN Golden.DimItemClass IC ON I.ItemClassID = IC.ItemClassID AND 'Current' = IC.ItemClassCurrentFlag
    --LEFT JOIN Golden.DimItem GI ON I.ItemID = GI.ItemID AND 'Current' = GI.ItemCurrentFlag
    WHERE
    I.ItemUpdatedate > '1/1/2000'
  2. MohammedU New Member

    If I am not mistaken, SQL server decides to use parallalism or not when it generate query plan based on the cost...
    Try using MAXDOP option in your query and see if it uses parallalism or not...
    Run the query in Query window and check the execution plan and stats... make sure it has right indexes and uses them...
    Don't use ROWLOCK hint... if possible use temp table to limit the data set for Staging.Item table...
  3. satya Moderator

  4. cmwahlqu New Member

    I tried using MAXDOP 16 and it did nothing different, I also made sure all the indexes and stats where created and rebuilt any existing ones. I took rowlock out and still no different.
    I did get the query to run in parallel by using "ORDER BY I.ItemID" but the query took much longer to run. I only see one query running in parallel across my entire system. How can this be correct, when I am running my heaviest load of multiple queries it only maxes to 20% cpu usage. When I run cubes across it I can get all CPU's to 100% sustained. Why is it the queries I have written not executing and utilizing my entire system? I am a DB2 buy and this is my first endeavor into the SQL server world, but in the DB2 world, you through a big query at it and it will use any and all recourses to execute that query as fast as possible, usually utilizing parallelism. Correct me if I am wrong, this is how SQL server should work too?
    Thanks for the help!!

Share This Page