Formas de evitar operaciones de spool ansiosas en SQL Server

sql-server tsql

Question

Tengo un proceso de ETL que involucra un procedimiento almacenado que hace un uso intensivo de las SELECT INTO (mínimamente registradas y, por lo tanto, más rápidas, ya que generan menos tráfico de registro). Del lote de trabajo que tiene lugar en un particular almacenado el procedimiento almacenado, varias de las operaciones más costosas son carretes ansiosos que parecen simplemente almacenar en búfer los resultados de la consulta y luego copiarlos en la tabla que se acaba de realizar.

La documentación de MSDN en los spool impares es bastante escasa. ¿Alguien tiene una idea más profunda de si estos son realmente necesarios (y bajo qué circunstancias)? Tengo algunas teorías que pueden tener sentido o no, pero no tienen éxito en eliminarlas de las consultas.

Los archivos .sqlplan son bastante grandes (160kb), así que supongo que probablemente no sea razonable publicarlos directamente en un foro.

Entonces, aquí hay algunas teorías que pueden ser susceptibles de respuestas específicas:

• La consulta utiliza algunas UDF para la transformación de datos, como el análisis de las fechas formateadas. ¿Esta transformación de datos requiere el uso de carretes ansiosos para asignar tipos sensibles (por ejemplo, longitudes varchar) a la tabla antes de que la construya?
• Como una extensión de la pregunta anterior, ¿alguien tiene una visión más profunda de lo que conduce o no esta operación en una consulta?

Answer 1

Mi comprensión del spooling es que es un poco una pista para tu plan de ejecución. Sí, representa una gran parte de su costo de consulta, pero en realidad es una optimización que SQL Server realiza automáticamente para evitar el costoso re-escaneo. Si tuviera que evitar el spooling, el costo del árbol de ejecución en el que se encuentra aumentará y, casi con certeza, aumentará el costo de toda la consulta. No tengo ninguna idea particular de lo que en particular podría hacer que el optimizador de consultas de la base de datos analice la ejecución de esa manera, especialmente sin ver el código SQL, pero probablemente sea mejor que confíe en su comportamiento.

Sin embargo, eso no significa que su plan de ejecución no pueda ser optimizado, dependiendo exactamente de lo que esté haciendo y cuán volátiles sean sus datos de origen. Cuando está haciendo un SELECT INTO , a menudo verá spooling items en su plan de ejecución, y puede estar relacionado con el aislamiento de lectura. Si es apropiado para su situación particular, puede intentar bajar el nivel de aislamiento de la transacción a algo menos costoso y / o usar la sugerencia de NOLOCK . He encontrado en consultas complicadas de rendimiento crítico que NOLOCK , si es seguro y apropiado para sus datos, puede aumentar enormemente la velocidad de ejecución de consultas, incluso cuando no parece haber ninguna razón para hacerlo.

En esta situación, si intenta READ UNCOMMITTED o la sugerencia NOLOCK , puede eliminar algunos de los Carretes. (Obviamente, no desea hacer esto si es probable que lo coloque en un estado incoherente, pero los requisitos de aislamiento de datos de todos son diferentes). El operador TOP y el operador OR ocasionalmente pueden causar spooling, pero dudo que esté haciendo alguno de esos en un proceso ETL ...

Tiene razón al decir que sus UDF también podrían ser los culpables. Si solo está utilizando cada UDF una vez, sería un experimento interesante intentar ponerlos en línea para ver si obtiene un gran beneficio de rendimiento. (Y si no puede encontrar la manera de escribirlos en línea con la consulta, probablemente sea por eso que podrían estar causando el spooling).

Una última cosa que vería es que, si está haciendo alguna unión que puede ser reordenada, intente usar una pista para forzar que la orden de unión ocurra en lo que usted sabe que es el orden más selectivo. Es un poco de alcance, pero no está de más intentarlo si ya estás atascado optimizando.