Timeout due to delete expired

 

1. Problem

When a developer reflects the execution of a simple Delete statement in an application,"Timeout expired"Error. The delete statement format is as follows:

Delete * From table_1 where [email protected]

2. Analysis

1) Verify that the delete search field has an index

First of all, I think of whether there is an index in the field ID column, that is, whether this statement to be deleted can be quickly found.

After viewing the index list of the table, it is found that there is an index on the ID and it is a clustered index.

The execution of select * From table_1 where [email protected] takes the clustered index query, which is very fast.

This is not because the index is missing for the search field.

2) Check for blocking

Next, let's guess if there is a blocking, that is, the delete statement waits for other sessions to release the lock on the key to get the X lock to execute the deletion.

UseSYS. sysprocessesQueries the current Delete session Status and finds that the session is not blocked.

3) view the estimated execution plan of the delete statement

After the verification is completed in the first two steps, I feel a little confused. Let's take a look at the delete statement execution plan.

Because the statement execution times out, you cannot view the real execution plan.Estimated execution planTo analyze the problem.

Take the deletion test in adventureworks2012 as an example.

Execute Delete from person. Person Where businessentityid = 6. The execution plan is divided:

3. Conclusion

The cause of the problem is found in the execution plan:

When sqlserver deletes the referenced table data, it checks whether there is a reference value record in the referenced table to ensure the integrity of the data.

Currently, the referenced table does not have an index on the foreign key field. As a result, the index scan is used for searching, and the number of referenced table records is more than one million, resulting in deletion timeout.

4. Processing

Add a non-clustered index to the foreign key field of the referenced table

5. Thinking

1) Is it reasonable and necessary for an application to physically delete data? Can I perform logical deletion by adding a delete tag or document status?

2) must the foreign key of the field in the referenced table be created? I have read some application systems, such as yonyou and Kingdee systems. There are very few foreign key fields in the table. Too many foreign key fields may affect the insertion and deletion speed.

3) If a foreign key constraint is set up, it is best to index the foreign key field of the referenced table and the primary key field of the referenced table.

If anything is wrong, please make a brick. Thank you! O (partition _ partition) O