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

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'

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...

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!!