Sql-server – Do I need to update an index include columns every time I add a new column

database-designindexindex-tuningnonclustered-indexsql server

I work on a team where we have millions of records of data, but not 100s of millions anything crazy.

We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is loading data from disk into memory. This is crashing out DBs, and this has happened a few times in the past year.

A little bit about that way we have our data:

We have a "Carton" table and "Items" table. Items are contained inside cartons, so the carton is the parent entity and items are child (one to many relationship). These tables both have many attributes associated with them.

Okay, now moving forward. We look at the query optimizer and it tells us to create missing indexes. Here is an example:

CREATE INDEX missing_index_69 ON dbo.items (carton_number) INCLUDE
(item_number, entered_upc, item_desc, department, item_qty, item_cost_amt, cost_multi_qty,
item_retail_amt, retail_multi_qty, item_sell_amt, sell_multi_qty, mdse_div_nbr, sams_msde_cat_nbr,
base_retail_uom_cd, item_sale_type_co, scan_cnt, donation_item_id, price_embedded, weighted, cvp, gtin,
wm_discretion, category, sub_category, created_date, last_change_date, created_user_id,
last_change_user_id, replenished_by_unit, uom, deleted, qty_prev, qty_delta, bucket_id, bucket_color,
hazmat, hazwaste, cvp_value, claim_value, wmtd_claim_eligible, smart_label_id)

This issue arose when we did a outer fetch to get the items associated with the carton, and we looked for the items via the carton_number (Carton_number is primary key for Carton table, and is a Foreign-Key for the items, also with an index on it!)

Yes, we do Select All so that is why it is requesting to add all those fields, but I digress.

My problem is, this assumes that every single time I add a new column to the Item table, that I will have to update the index to add it in the include column. We have many queries where we search by different criteria (by smart_label_id, created_date, carton_state, etc.) For each one of those queries with indexes on them, do I really have to include all the columns, and update EACH ONE of those queries anytime I add a new column?? That seems extremely excessive, especially since we have many queries and adding a new column would require a LOT of refactoring.

Is this normal? It is my understanding that SQL server can handle billions of records with a lot of attributes, and I am surprised that we are running into this issue with the amount of data we have.

Best Answer

The missing index code is extremely simple in what recommendations in suggests. One thing is that it doesn't care about selectivity.

The reason to include columns is to cover a query. I.e., so you don't have to do a bookmark lookup for each row. If you have a rather high selectivity, then these lookups don't matter much, and it is extreme overkill to include bunch and bunch of columns. I.e., skip the included columns and just focus on the WHERE cause and the index keys.

So, don't give those "missing index" recommendations much credit. At all. But they can serve as starting points for your own analysis.

To cluster the "child table" om the FK is another often forgotten trick. As already mentioned by Laughing Vergil.

Related Solutions

Covering Index – Include columns

(I'm assuming SQL Server in this answer, please clarify which RDBMS you're using if it's not SQL Server)

The index you want is:

CREATE INDEX [IX_Parent_Id]
ON [sch].[Parent] (Id) INCLUDE (Name)

You use INCLUDE when a column isn't being filtered on (isn't in the WHERE clause) but is being selected. INCLUDE means that the column will only be included at the leaf level of the index (it's a small efficiency saving, essentially). Also as noted, if you have a SELECT statement without a where clause, indexes will never be used (faster to just scan the table rather than muck about with an index). Index seek is only used when you're returning a comparatively small number of rows, for your sample query if most of your ParentsChild and Parents rows are marked as Active then the optimiser may well decide to index scan instead of seek. A query that would be more likely to get an index seek instead of scan would be something like:

SELECT
     b.Id AS 'ParentsChildId'
    , b.[Name] AS 'ParentsChildName'
    , a.Id AS 'ParentId'
    , a.[Name] AS 'ParentName' 
FROM 
    [sch].[ParentsChild] b 
INNER JOIN 
    [sch].[Parent] a 
ON  a.Id = b.Id 
WHERE 
    a.Active = 1 AND
    b.Active = 1 AND
    a.Id = 2

(getting the children of Parent ID 2)

Ny reason to use index for very small tables (up to 1000 rows)

Generally yes. In lack of an index the access pattern has to inspect every row, just to see if is the one you need or not. The issue is not the table size, but concurrency. Depending on your isolation level, your scans may block behind uncommitted transaction just to wait for rows that ultimately are 'uninteresting' to be unlocked. Because your scan is guaranteed to 'look' at every row, every scan will block behind any write (insert, delete or update). Oracle defaults to snapshot isolation which is OK in this situation (no blocking) but other vendors default to something else, eg. SQL Server will default to Read committed which does block.

With an index present your access pattern will only look at the relevant rows (within the desired key range) so the pure statistical chance of hitting a lock conflict is greatly reduced.

Best Answer

Related Solutions

Covering Index – Include columns

Ny reason to use index for very small tables (up to 1000 rows)

Related Question