September 26, 2025 at 9:33 pm
Why is sql doing a full scan VS seeking on the index? I've included the execution plan below.
The address table has a primary key column, address_id, that is used as a foreign key on several tables including customer_account. The FK column has a non clustered index on all tables. When the delete runs the execution plan on the customer_account table estimates that every row will need to be read (over a million) which causes a table scan. But only 1 row is read. Keep in mind the FK column is role name as "ship_to_address_id" and "bill_to_address_id".
To attempt a fix, I rebuilt all table statistics using 100% sample and flushed the cache, including option recompile. The bad estimate remained along with the full scan. You can see the bad estimate in the execution plan along with the full scan on the customer_account PK. The two scans have a cost of 49% each.
September 26, 2025 at 11:21 pm
can you post the full ddl for table ecom.customer_account - including all indexes and FK definitions.
initial though is that you don't have an index on "ship_to_address_id" and "bill_to_address_id".
September 27, 2025 at 3:00 am
I started scripting the indexes to reply to your message and realized where the problem was rooted. There were indeed nonclustered indexes on the bill_to_address_id and ship_to_address_id columns, but they both had a filter on them. In this case the filter was for values greater than zero (shown below). I believe when sql runs the delete on the PK table it doesn't know what values are going to be checked against the foreign key constraint so it can't use a filtered index. Instead it uses the clustered index.
CREATE NONCLUSTERED INDEX [IX_customer_account_bill_to_address_id] ON [customer_account]
(
bill_to_address_id ASC
)
September 27, 2025 at 7:36 am
you didn't put the where clause.
if it is indeed saying "where bill_to_address_id > 0" I would check to see if it is like that to exclude potential negative numbers or just to exclude "null" values - if just to exclude null values I would try out with a change to the filter to be "where bill_to_address_id is not null" instead. It may or not make a difference on this case (I've never tried it myself)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply