Delete operation is slow and rebuilding index doesn’t seems to solve the issue

I have a simple delete query :

Delete from sales
where ImportLogId = @ImportLogid

ImportLog table has around 3 million records with all log details.

I have non clustered index created on ImportLog table on ImportLogID.

The fragementation is less than 10% but still the query is taking more time to execute but when I rebuild the index manually it is executed within a second. After a day the same problem repeats. Rebuilding the index using code doesn’t seems to help but rebuilding index in SQL Server UI helps.

Any difference in rebuilding index using SQL query and manually using UI? why does the index with less fragmentation also takes more time to execute whereas when I rebuild the index it executes within a second?

• Page fullness is 99.71 %
• Total Fragementation is 4%

Looking for a better solution

Answer

Right. So your problem is not the deletion of records, which is instantaneous. (84 rows). The problem is the scan on the WholesalerSale table afterwards.

My guess would be, that ImportSale.Id is a foreign key in WholesalerSale, and that SqlServer simply validates that you haven’t deleted a referenced key.

Solution is to index your foreign key column in WholesalerSale to speed up this check.

CREATE INDEX IX_WholesalerSale_ImportSaleId ON WholesalerSales (ImportSaleId);