Why is the SQL Server query plan not using indexes in a query almost similar to one that uses indexes?

Question

I have two UPDATE queries that are similar in structure, yet the SQL Server query plan for one shows indexes being used, and for the other it shows only a regular table scan.

The following are the queries (as per the query plan, #1 does not use indexes, #2 does)-

 UPDATE Payment_Metadata 
    SET 
    Payment_Metadata.CommodityCode = 'RAW MATERIALS', 
    Payment_Metadata.C1 = 'RAW MATERIALS', 
    Payment_Metadata.C2 = 'INGREDIENTS', 
    Payment_Metadata.C3 = 'OTHER ', 
    Payment_Metadata.RuleText = '---', 
    Payment_Metadata.LastUpdatedIndex = Payment_Metadata.LastUpdatedIndex + 1, 
    Payment_Metadata.IsExcluded = 0, 
    Payment_Metadata.LogText = 'Commodity>Raw Materials>Ingredients>Other' 
    FROM 
    Payment_Metadata 
    WHERE 
    Payment_Metadata.IsProcessed = 0 
    AND (Payment_Metadata.EnrichedVendor = 'NFL' 
         OR Payment_Metadata.Vendor_No = 'NFL')

The second query uses an index:

UPDATE Payment_Metadata 
SET 
Payment_Metadata.CommodityCode = 'RAW MATERIALS', 
Payment_Metadata.C1 = 'RAW MATERIALS', 
Payment_Metadata.C2 = 'INGREDIENTS', 
Payment_Metadata.C3 = 'OTHER ', 
Payment_Metadata.RuleText = '---', 
Payment_Metadata.LastUpdatedIndex = Payment_Metadata.LastUpdatedIndex + 1, 
Payment_Metadata.IsExcluded = 0, 
Payment_Metadata.LogText = 'Commodity>Raw Materials>Ingredients>Other' 
FROM 
Payment_Metadata 
WHERE Payment_Metadata.IsProcessed = 0 
  AND (Payment_Metadata.EnrichedVendor = '0202054' OR  
        Payment_Metadata.Vendor_No = '0202054')

The following is the table definition:

CREATE TABLE [dbo].[Payment_Metadata](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Company_Code] [varchar](1024) NULL,
    [Comp_Code_Desc] [varchar](1024) NULL,
    [Vendor_Acct_Group] [varchar](1024) NULL,
    [Vendor_No] [varchar](10) NULL,
    [Vendor_Name] [varchar](1024) NULL,
    [Vendor_ABN] [varchar](1024) NULL,
    [Vendor_PTerm] [varchar](1024) NULL,
    [Vendor_PTerm_Desc] [varchar](1024) NULL,
    [Purchasing_Group] [varchar](1024) NULL,
    [Purchasing_Group_Des] [varchar](1024) NULL,
    [PO_DocType] [varchar](1024) NULL,
    [PO_DocType_Desc] [varchar](1024) NULL,
    [Purchasing_Document] [varchar](1024) NULL,
    [PO_Date] [varchar](1024) NULL,
    [PO_CreatedBy] [varchar](1024) NULL,
    [Plant] [int] NULL,
    [Item_Number] [varchar](1024) NULL,
    [Material_Number] [varchar](1024) NULL,
    [Material_Group] [varchar](7) NULL,
    [Material_Group_Desc] [varchar](1024) NULL,
    [Account_Assignment] [varchar](32) NULL,
    [Acct_Assignment_Desc] [varchar](1024) NULL,
    [GL_Account] [varchar](7) NULL,
    [GL_Account_Desc] [varchar](1024) NULL,
    [PO_Desc] [varchar](64) NULL,
    [PO_Quantity] [decimal](10, 2) NULL,
    [Order_UOM] [varchar](1024) NULL,
    [Order_Price_Unit] [varchar](1024) NULL,
    [Invoice_Receipt] [varchar](1024) NULL,
    [Invoice_Reference] [varchar](1024) NULL,
    [Invoice_Date] [datetime] NOT NULL,
    [Invoice_Scan_Date] [datetime] NULL,
    [Invoice_Item] [int] NULL,
    [Invoice_Amount] [decimal](15, 2) NULL,
    [GST] [decimal](10, 2) NOT NULL,
    [Invoice_Gross_Amount] [decimal](15, 2) NULL,
    [Currency] [varchar](1024) NULL,
    [Document_Type] [varchar](1024) NULL,
    [Document_Number] [varchar](1024) NULL,
    [Document_Date] [datetime] NOT NULL,
    [Posting_Date] [datetime] NOT NULL,
    [Payment_Term] [varchar](1024) NULL,
    [Baseline_Date] [datetime] NOT NULL,
    [Due_Date] [datetime] NOT NULL,
    [Payment_Document] [varchar](1024) NULL,
    [Clearing_Date] [datetime] NOT NULL,
    [CommodityCode] [varchar](128) NULL,
    [RuleText] [nvarchar](max) NULL,
    [IsProcessed] [bit] NOT NULL,
    [DataSource] [varchar](5) NULL,
    [IsContracted] [bit] NOT NULL,
    [IsPreferred] [bit] NOT NULL,
    [VendorRiskScore] [varchar](5) NULL,
    [EnrichedVendor] [varchar](128) NULL,
    [LastUpdatedIndex] [int] NOT NULL,
    [LogText] [nvarchar](max) NULL,
    [IsExcluded] [bit] NOT NULL,
    [C1] [varchar](50) NULL,
    [C2] [varchar](50) NULL,
    [C3] [varchar](50) NULL,
    [OriginalVendor] [varchar](60) NULL,
    [AdjustedAmount] [decimal](15, 2) NULL
) ON [PRIMARY]

As you can see, the only change in the WHERE clause is the usage of numeric vs non-numeric characters (which I suspect should not impact the query plan?)

There is one non-clustered index on each of the columns EnrichedVendor and Vendor_No.

Any help would be appreciated.

UPDATE: Including the query plans below, and I noticed that query #1 suggests an index (in green). That may be the solution to the answer.

Answer 1

SQL Server uses statistics to determine an execution plan. If an index is available, so are statistics, and SQL server will determine the path of least work. This could be using the index or doing a table scan.

In your example SQL server has determined that a table scan is less work than doing an index seek and a bookmark lookup.

What you can see is that one of your querys is "less selective", ie it probably updates more records, meaning that more bookmark lookups are needed to satisfy the second query. SQL server just estimated that for that query the sum of index seeks + bookmark lookups are more work than simply doing a table scan.

Since SQL server reads entire pages, and not records, your query (as indicated by statistics) needs to be selective enough for the index to be used, if SQL server estimates that it's going to have to read every page anyway it will go for a table scan.

See here and here for a more indepth explanation with sample data and results

Answer 2

The EnrichedVendor is not the same in the queries. I suspect that when you get a table scan you are working with products where the EnrichedVendor has a good majority of all the records in the table and therefore it makes sense to read all the table and the other EnrichedVendor has a much smaller percentile of the total records in the table.

But you also just might need to update the statistics for the table.

Answer 3

Can you change the update? In general "or" clauses are bad for performance / index usage. If you would do 2 updates,

First update with one of the keys:

update ...
WHERE IsProcessed = 0 AND EnrichedVendor = '1024'

and the rest of the rows:

update ...
WHERE IsProcessed = 0 AND Vendor_No = 'NFL' and EnrichedVendor != '1024'

Usually this kind of approach works better, even if it looks more complicated