I have a table with nearly 6m records. The business key that identifies a unique row is quite large. Our update processing is now taking a lot longer since I added this new table for a new cube. I currently do not have an index on the join columns for the update. SQL Server estimated execution plan says I should create this index on the business key:
/* Missing Index Details from Server.db The Query Processor estimates that implementing the following index could improve the query cost by 86.9178%. */ /* USE [db] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [schema].[Production] ( [ProdId],[PriceCalc],[CalcTypeId],[OprId],[CostGroupId],[Resource], [BOM],[ResourceDepartment],[OprNum],[DateWIP],[DataAreaId],[Partition] ) INCLUDE ( [ProdOrderStatus],[ManufacturedItemId],[CalcType],[CalculationLevel], [CostAnalysisOrderType],[CostGrouping],[UnitId],[WorkCenter],[Name], [RealisedConsumption],[RealisedCostAmount],[RealisedCostAdjustment], [EstimatedConsumption],[EstimatedCostAmount],[LotSizeVariance], [StandardQty],[StandardCost],[ItemStandardQty],[HasSubstitutionVariance], [R2],[R3],[StandardQtyByRAFQty],[StandardCostByRAFQty],[ProductionOrderType], [RealisedAllocation],[CostVariance],[QuantityVariance],[SubstitutionVariance], [TotalVariance],[ComponentItemId],[InventoryUOM],[InventConsumptionTransUOM], [BomConsumptionTranUOM],[TransactionUOM],[TransUOMToInvUOMConversionRate], [InventConsumptionInvUOM],[BomConsumptionInvUOM],[TotalNetWeightPerUnitInvUOM], [InventoryNetWeightUOM],[ReportingNetWeightUOM], [NetWeight_InvUOMToReportingUOMConversionRate], [InventConsumptionTotalNetWeightInvUOM],[BomConsumptionTotalNetWeightInvUOM], [InventConsumptionTotalNetWeightReportingUOM], [BomConsumptionTotalNetWeightReportingUOM], [FinancialProductId],[FinancialDepartmentId],[FinancialMarketId],[FinancialCodeId], [FinancialTypeId],[FinancialSiteId],[ProdPoolId],[Company_SK],[ComponentItem_SK], [EndedDate_SK],[DateWIP_SK],[FinancialCode_SK],[FinancialDepartment_SK], [FinancialMarket_SK],[FinancialProduct_SK],[FinancialSite_SK],[FinancialType_SK], [InventoryNetWeightUOM_SK],[InventoryUOM_SK],[ManufacturedItem_SK], [ProductionOrder_SK], [ReportingNetWeightUOM_SK],[TransactionUOM_SK],[BatchRunId],[ValidInd],[ScrapVar], [QuantityPO],[ExpectedConsumption],[FibreScrapFactor],[QtyAndSubVariance], [EndedDate],[RealisedAllocationCost],[FilmScrapFactor]) GO */
it wants to create an index on the key columns, however it wants to include many columns. Should i listen to it or just include the key columns? Thanks for your help. This is a production issue so I can't go testing different things etc.
We are considering putting in a hashing solution, along these lines:
In the short term, next week is an important financial end period and the system needs to perform well so adding an index seems like a good idea. So I would like to do whatever I can quickly. Would you recommend that hashing solution for long term/new implementations?