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".
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply