He estado analizando un "informe de error" recurrente (problema de rendimiento) en uno de nuestros sistemas relacionado con una operación de eliminación particularmente lenta. Larga historia corta: parece que elCASCADE DELETE Las claves fueron en gran parte responsables, y me gustaría saber (a) si esto tiene sentido, y (b) por qué es así.

Tenemos un esquema de, digamos, widgets, aquellos que están en la raíz de un gran gráfico de tablas relacionadas y tablas relacionadas, etc. Para ser perfectamente claro, eliminar de esta tabla se desaconseja activamente; es la "opción nuclear" y los usuarios no se hacen ilusiones de lo contrario. Sin embargo, a veces solo hay que hacerlo.

El esquema se ve así:

Widgets
   |
   +--- Anvils [1:1]
   |    |
   |    +--- AnvilTestData [1:N]
   |
   +--- WidgetHistory (1:N)
        |
        +--- WidgetHistoryDetails (1:N)

Las definiciones de columna tienen el siguiente aspecto:

Widgets (WidgetID int PK, WidgetName varchar(50))
Anvils (AnvilID int PK, WidgetID int FK/IX/UNIQUE, ...)
AnvilTestData (AnvilID int FK/IX, TestID int, ...Test Data...)
WidgetHistory (HistoryID int PK, WidgetID int FK/IX, HistoryDate datetime, ...)
WidgetHistoryDetails (HistoryID int FK/IX, DetailType smallint, ...)

Nada demasiado aterrador, de verdad. UNAWidget pueden ser de diferentes tipos, unAnvil es un tipo especial, por lo que la relación es 1: 1 (o más exactamente 1: 0..1). Luego hay una gran cantidad de datos, quizás miles de filas deAnvilTestData porAnvil recolectado con el tiempo, lidiando con dureza, corrosión, peso exacto, compatibilidad con martillos, problemas de usabilidad y pruebas de impacto con cabezales de dibujos animados.

Entonces cadaWidget tiene una larga y aburrida historia de varios tipos de transacciones: producción, movimientos de inventario, ventas, investigaciones de defectos, RMA, reparaciones, quejas de clientes, etc. Puede haber 10-20k detalles para un solo widget, o ninguno, dependiendo de su edad

Entonces, como era de esperar, hay unCASCADE DELETE relación en todos los niveles aquí. Si unWidget necesita ser eliminado, significa que algo salió terriblemente mal y necesitamos borrar cualquier registro de ese widget que haya existido, incluyendo su historial, datos de prueba, etc. Nuevamente, opción nuclear.

Todas las relaciones están indexadas, las estadísticas están actualizadas. Las consultas normales son rápidas. El sistema tiende a funcionar sin problemas para todo excepto las eliminaciones.

Llegando al punto aquí, finalmente, por varias razones, solo permitimos eliminar un widget a la vez, por lo que una declaración de eliminación se vería así:

DELETE FROM Widgets
WHERE WidgetID = @WidgetID

Bastante simple, eliminar de aspecto inocuo ...que demora más de 2 minutos en ejecutarse, para un widget con¡sin datos!

Después de analizar los planes de ejecución, finalmente pude elegirAnvilTestData yWidgetHistoryDetails elimina como las suboperaciones con el costo más alto. Así que experimenté apagando elCASCADE (pero manteniendo el FK real, solo configurándolo enNO ACTION) y reescribiendo el guión como algo muy parecido a lo siguiente:

DECLARE @AnvilID int
SELECT @AnvilID = AnvilID FROM Anvils WHERE WidgetID = @WidgetID

DELETE FROM AnvilTestData
WHERE AnvilID = @AnvilID

DELETE FROM WidgetHistory
WHERE HistoryID IN (
    SELECT HistoryID
    FROM WidgetHistory
    WHERE WidgetID = @WidgetID)

DELETE FROM Widgets WHERE WidgetID = @WidgetID

Ambas "optimizaciones" resultaron en aceleraciones significativas, cada una de las cuales redujo casi un minuto completo del tiempo de ejecución, por lo que la eliminación original de 2 minutos ahora toma alrededor de 5-10 segundos, al menos pornuevo widgets, sin mucha historia o datos de prueba.

Para ser absolutamente claro, todavía hay unCASCADE deWidgetHistory aWidgetHistoryDetails, donde el despliegue es más alto, solo eliminé el que se originó enWidgets.

Un mayor "aplanamiento" de las relaciones en cascada resultó en aceleraciones progresivamente menos dramáticas pero aún notables, hasta el punto de eliminar unnuevo el widget fue casi instantáneo una vez que todas las eliminaciones en cascada en tablas más grandes fueron eliminadas y reemplazadas por eliminaciones explícitas.

Estoy usandoDBCC DROPCLEANBUFFERS yDBCC FREEPROCCACHE antes de cada prueba. He desactivado todos los desencadenantes que podrían estar causando más ralentizaciones (aunque de todos modos aparecerían en el plan de ejecución). Y también estoy probando contra widgets más antiguos, y también noto una aceleración significativa allí; Las eliminaciones que solían tomar 5 minutos ahora toman 20-40 segundos.

Ahora soy un ferviente defensor de la filosofía "SELECCIONE no está roto", pero no parece haber ninguna explicación lógica para este comportamiento, aparte de la ineficacia aplastante y alucinante delCASCADE DELETE relaciones

Entonces, mis preguntas son:

¿Es este un problema conocido con DRI en SQL Server? (Parece que no pude encontrar ninguna referencia a este tipo de cosas en Google o aquí en SO; sospecho que la respuesta es no).

Si no, ¿hay otra explicación para el comportamiento que estoy viendo?

Si es un problema conocido, ¿por qué es un problema y hay mejores soluciones que podría estar usando?