deletesql serversql-server-2008sql-server-2008-r2
In SQL Server 2008, there is a Primary table which is linked to three other child tables by 1 to many relationship. So, we are thinking of using Cascading delete in the primary table, so that all the records on the child table will be removed when record from primary table is deleted.
To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index.
This can be a good optimization, though the details depend on your hardware, how likely the affected pages are to be in memory, and whether the sorts can complete within the memory allocated to them. When the optimizer decides the cost of sorting will be paid back by the increased efficiencies associated with sequential index access, it sets a property DMLRequestSort on the update operator:
The optimizer may also decide to split the update into separate operators to maintain the clustered index (or heap) and then the nonclustered indexes. often, it will decide to sort more than once - first for the clustered index keys, and then again for the nonclustered index(es). Again, where sorting is considered optimal, each index update operator will have the DMLRequestSort property set to true.
All that said, the things I would fix first would be to eliminate the index scans where the join operator they feed is a nested loops join, and to remove the eager index spools, which are inserting rows into an empty index every time the query is executed. An eager index spool is often the clearest possible sign that you are missing a useful permanent index. The seek predicate in the index spool operator identifies the keys the optimizer would like an index on.
Examples of tables that are missing a nonclustered index (requiring an eager index spool) are:
child6gc8Selections
gc9s
child7s
gc6s
Examples of tables that are currently being scanned below a nested loops join are:
child1
parentObjectMessages
child8s
child7s
child6s
child5s
child4s
child3s
child2s
Taking the example shown above, the Clustered Index Scan has an output list of Id, parentObjectId, the Nested Loops Join predicate is child7s.parentObjectId = parentObject.Id, and the join output column list is child7s.Id.
From that information, it seems a good access method (index) on child7s for this part of the query would be keyed on parentObjectId with Id as an included column. You should be able to work out how best to work this into your existing indexing strategy.
The following are examples of tables where the optimizer is currently choosing a hash join. I would check tables like this to ensure that is a reasonable access method:
child6gc8Selections
gc2s
gc5s
gc6Properties
The table child2bigChild also participates in a merge join where an explicit sort is necessary. Again, I would check to see if this sort could be avoided.
Once the basic indexing issues are resolved, we can look at other optimizations if necessary.
What you need to check are the referential integrity constraints.
If you want to delete the parent key there are around four options on how you want to deal with the children keys:
Best Answer
I am generally wary of cascaded deletes (and other automatic actions that could drop/damage data), either via triggers or
ON <something> CASCADE. Such facilities are very powerful, but also potentially dangerous.It would certainly do what you are looking for it to do: remove related records when a parent record is removed, without you needing to implement any other logic to ensure that children get removed first therefore making your code more concise. All the actions will be wrapped in an implicit transaction so if something blocks the child deletes the whole operation is blocked, maintaining referential integrity with little or no extra coding effort.
Make sure that your use of cascaded deletes and other "behind the scenes" actions are well documented so future maintainers of the system are fully aware of it.
It should not be used if you are paranoid like me! One key point to consider is the other developers who currently, or may in future, work on your code/database (hence the comment above about documenting any "hidden" behaviours).
It is quite common in my experience for inexperienced people to use
DELETEthen re-INSERTin order to update rows, especially when what they really want is aMERGE/UPSERToperation (update existing rows and create new ones where a row with a given key does not exist) and the DBMS doesn't support merge/upsert (or they are unaware of its support). Without cascaded actions this is perfectly safe (or will error when it threatens data integrity) but if someone does this for rows in a parent table where referring FKs haveON DELETE CASCADEset then related data will be deleted as a result of the initial delete and not replaced - so data is lost (not that even if the delete and subsequent insert are wrapped in explicit transactions, the cascade happens with the delete operation - it won't wait to see if the transaction replaces rows in the parent table in subsequent statements) and the cascade could continue through other relation ships (for instance: delete a senior supervisor, his team get deleted by cascade, his teams' teams get deleted by cascade, all the tracked records for all those people get deleted by cascade, ...). Without cascading enabled, you would just get an error here instead of the data being silently lost.