5

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:

Using Hashbytes to track and store historical changes for SQL Server data

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?

  • 1
    I'm curious why you don't have a surrogate to represent your wide business key. There are dozens of things that make wide keys problematic, many of them not showing up at all until your tables get large. – Aaron Bertrand Jan 31 '16 at 14:12
  • Hi Aaron, thanks for your response. We are considering putting in a hashing solution, along these lines. mssqltips.com/sqlservertip/2543/… 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? – jhowe Jan 31 '16 at 14:30
  • Is that query really the performance problem though, or is it just taking a long time to process the data in the cube after it has the data from the database engine? Maybe run the query into a temp table in SSMS and see how long that takes first (or extract this info from SQL's cache). BTW I think that hashing solution is designed for dimensions, not facts. Often you wouldn't update fact table rows, just keep adding new ones for later versions instead. Is your fact table partitioned? I notice a PARTITION column, but your table doesn't have many rows. – Simon Birch Feb 1 '16 at 1:29
  • hi partition is a column in dynamics AX it's actually 'company'. yes i believe this is the problem. updates on a multi million record table with no index is an expensive operation as SQL has to scan the whole table to find where it needs to update. – jhowe Feb 1 '16 at 11:37
8

I'm working on the assumption that the table in question is a fact table, not a dimension table with a huge composite key:

Just to fix the performance issue in the short term, I would add all of these key columns as the table's clustered index, which means you won't have to INCLUDE a lot of measures and stuff, like the suggested index does. Also, make the index unique if the data allows for this.

As for the column order of a clustered index on a fact table, it depends on how you're accessing them. If you're only using a cube to read large chunks of data, I would probably prioritize INSERT priority by making the index chronological, i.e. putting the date column first - that way, new rows get added to the end of the index (in the best of worlds).

If you're running user T-SQL queries on the fact table, I would try to arrange the index columns in an order that gives you Index Seeks or Range Scans as much as possible: first, columns that are filtered on single dimension keys (think "year", "type", "unit" or "department"-type dimensions), then those columns that are filtered on multiple dimension members, ranges, or used for sorting.

There are, of course, other schools on how to build indexes - this is not a "single correct answer".

Edit: More on clustered vs non-clustered indexes:

I'm guessing that you already have an existing clustered index, and that's why SQL Server suggests a non-clustered index. However, non-clustered indexes have to be explicitly defined with INCLUDE columns. Clustered indexes define the actual storage/sort order of the table, and as such, they will implicitly include all columns in the table (I won't go into LOB columns like varchar(max) and xml).

The clustered index is normally the "catch-all index" that takes care of queries that are not suitable for an existing non-clustered index, which makes it even the more important (in my opinion) that it's well-designed and not, for instance, just on an IDENTITY() column.

Plus, a non-clustered index will take up more drive space, so a non-clustered index that covers all of the table's columns will in effect take up as much space as the table itself. A clustered index is the table.

| improve this answer | |
  • 3
    Well, I don't know, I think in a lot of cases the clustered index on the identity column makes sense, for a number of reasons - keeping new rows added at the "end" of the table, for example. There are edge cases where you actually don't want that, for example prefer to take page splits on a GUID column in order to spread out I/O, or cluster on a date column instead because all queries are on date range. My point is simply that it's not fair to say that clustering an identity column is bad, though I'll admit that - like a lot of things - It's certainly not perfect for all scenarios. – Aaron Bertrand Jan 31 '16 at 20:31
  • Fair enough, @AaronBertrand. My answer applies to this type of fact table, where a cube runs a massive star schema-style join on 12 dimensions. In this type of situation, an identity column won't be very helpful, and a non-clustered index that covers all the measure columns will just be twice the I/O. However, if the cube doesn't join any dimensions or even loads incrementally, a clustered identity column may well turn out to be ideal. :) – Daniel Hutmacher Jan 31 '16 at 22:58
  • Thank you for your comments. This fact table is joined to dimensions in SSAS. Our date dimension is joined to two dates in this table but this is not a 'created date' timestamp. One is linked to ended dates of prod orders the other shows 'orders in progress' . The information in the fact table is updated sometimes when they change source data in MS AX. – jhowe Feb 1 '16 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.