Almacenar en caché y volver a utilizar un plan de ejecución

Actualizado: 17 de julio de 2006

SQL Server 2005 tiene un búfer de memoria que se utiliza para almacenar planes de ejecución y búferes de datos. El porcentaje del búfer de memoria que se asigna a los planes de ejecución o a los búferes de datos varía dinámicamente según el estado del sistema. La parte del búfer de memoria que se utiliza para almacenar los planes de ejecución se denomina caché de procedimientos.

Los planes de ejecución de SQL Server 2005 tienen los siguientes componentes principales:

• Plan de consultas
  La mayor parte del plan de ejecución es una estructura de datos reentrante de sólo lectura que cualquier número de usuarios puede utilizar. Esto se conoce como plan de consultas. No se almacena ningún contexto de usuario en el plan de consultas. Nunca hay más de una o dos copias del plan de consultas en la memoria: una copia para todas las ejecuciones en serie y otra para todas las ejecuciones en paralelo. La copia en paralelo cubre todas las ejecuciones en paralelo, sin tener en cuenta el grado de paralelismo.
• Contexto de ejecución
  Cada usuario que ejecuta la consulta tiene una estructura de datos que alberga los datos específicos de su ejecución, como los valores de los parámetros. Esta estructura de datos se conoce como contexto de ejecución. Las estructuras de datos del contexto de ejecución se vuelven a utilizar. Si un usuario ejecuta una consulta y una de las estructuras no está en uso, ésta se reinicializa con el contexto del nuevo usuario.

Cuando se ejecuta una instrucción en SQL Server 2005, el motor relacional mira primero en la caché de procedimientos para ver si existe un plan de ejecución para la misma instrucción SQL. SQL Server 2005 vuelve a utilizar cualquier plan existente que encuentre, lo que evita el trabajo que supone volver a compilar la instrucción SQL. Si no existe ningún plan de ejecución, SQL Server 2005 genera uno nuevo para la consulta.

SQL Server 2005 tiene un algoritmo eficiente para encontrar cualquier plan de ejecución existente de una instrucción SQL dada. En la mayor parte de los sistemas, los recursos mínimos que utiliza este recorrido son menos que los recursos que se ahorran al poder utilizar de nuevo los planes existentes en lugar de compilar cada instrucción SQL.

Los algoritmos que hacen coincidir las instrucciones SQL nuevas con los planes de ejecución existentes no utilizados de la caché requieren que todas las referencias a objetos estén completas. Por ejemplo, la primera de estas instrucciones SELECT no coincide con un plan existente, pero la segunda sí:

SELECT * FROM Contact

SELECT * FROM Person.Contact

Hay una mayor probabilidad de que los planes de ejecución individuales vuelvan a utilizarse en una instancia de SQL Server 2000 y SQL Server 2005 que en SQL Server versión 6.5 y anteriores.

Planes de ejecución antiguos

Una vez que se genera un plan de ejecución, permanece en la caché de procedimientos. SQL Server 2005 sólo elimina de la caché los planes antiguos no utilizados cuando se necesita espacio. Cada plan de consultas y contexto de ejecución tiene un factor de costo asociado que indica cuánto cuesta compilar la estructura. Estas estructuras de datos tienen también un campo de antigüedad. Cada vez que una conexión hace referencia a un objeto, el factor de costo de compilación incrementa el campo de antigüedad. Por ejemplo, si un plan de consulta tiene un factor de costo de 8 y se le hace referencia dos veces, su antigüedad es 16. El proceso de escritura diferida recorre periódicamente la lista de objetos de la caché de procedimientos. A continuación, la escritura diferida reduce en 1 el campo de antigüedad de cada objeto en cada recorrido. La antigüedad de este plan de consultas de ejemplo se reduce hasta 0 después de 16 recorridos de la caché de procedimientos, a menos que otro usuario haga referencia al plan. El proceso de escritura diferida desasigna un objeto si se cumplen las siguientes condiciones:

• El administrador de memoria necesita memoria y se está utilizando actualmente toda la memoria disponible.
• El campo de antigüedad del objeto es 0.
• Ninguna conexión hace referencia actualmente al objeto.

Debido a que el campo de antigüedad se incrementa cada vez que se hace referencia a un objeto, los campos de antigüedad de los objetos a los que se hace referencia con frecuencia nunca disminuyen a 0 y no quedan anticuados en la caché. Los objetos a los que no se hace referencia con frecuencia son pronto aptos para su desasignación, aunque no se cancelará tal asignación a menos que otros objetos necesiten memoria.

Volver a compilar planes de ejecución

Determinados cambios en la base de datos pueden causar que un plan de ejecución deje de ser eficaz o válido en función del nuevo estado de la base de datos. SQL Server detecta los cambios que hacen que un plan de ejecución no sea válido y lo marca como no válido. Después, debe volver a compilarse un nuevo plan para la próxima conexión que ejecute la consulta. Las condiciones que hacen que un plan no sea válido son:

• Cambios en una tabla o vista a la que hace referencia la consulta (ALTER TABLE y ALTER VIEW).
• Cambios en los índices que utilizan el plan de ejecución.
• Actualizaciones de estadísticas que utiliza el plan de ejecución y se generan explícitamente desde instrucciones, como UPDATE STATISTICS, o automáticamente.
• Quitar un índice que utiliza el plan de ejecución.
• Una llamada explícita a sp_recompile.
• Numerosos cambios en las claves (generados por las instrucciones INSERT o DELETE de otros usuarios que modifican una tabla a la que hace referencia la consulta).
• Para tablas con desencadenadores, si el número de filas de las tablas inserted o deleted crece significativamente.
• Ejecutar un procedimiento almacenado utilizando la opción WITH RECOMPILE.

La mayoría de las recompilaciones se necesitan para comprobar si las instrucciones son correctas o para obtener planes de ejecución de consultas potencialmente más rápidos.

En SQL Server 2000, siempre que una instrucción de un lote provoca una recompilación, se vuelve a compilar todo el lote, independientemente de si se ha enviado por medio de un procedimiento almacenado, un desencadenador, un lote ad hoc o una instrucción preparada. En SQL Server 2005, sólo se vuelve a compilar la instrucción del lote que causa la recompilación. Debido a esta diferencia, los recuentos de recompilación de SQL Server 2000 y SQL Server 2005 no son comparables. Además, existen otros tipos de recompilaciones en SQL Server 2005, gracias a su conjunto de características ampliado.

La recompilación de instrucciones beneficia al rendimiento ya que, en la mayoría de los casos, un pequeño número de instrucciones provocan recompilaciones con sus penalizaciones asociadas, en términos de tiempo y bloqueos de la CPU. Estas penalizaciones se evitan para otras instrucciones del lote que no es necesario volver a compilar.

El evento de traza SP:Recompile del Analizador de SQL Server comunica recompilaciones de instrucciones en SQL Server 2005. Estos eventos de traza sólo comunican recompilaciones de lote en SQL Server 2000. Además, en SQL Server 2005, la columna TextData de este evento está rellenada. Por lo tanto, el método de SQL Server 2000 de tener que realizar un seguimiento de SP:StmtStarting o SP:StmtCompleted para obtener el texto de Transact-SQL que provocó la recompilación ya no se necesita.

SQL Server 2005 también agrega un nuevo evento de traza denominado SQL:StmtRecompile que comunica recompilaciones de instrucciones. Este evento de traza se puede utilizar para hacer el seguimiento y depurar recompilaciones. Aunque SP:Recompile sólo se genera para procedimientos almacenados y desencadenadores, SQL:StmtRecompile se genera para procedimientos almacenados, desencadenadores, lotes ad hoc, lotes que se ejecutan mediante sp_executesql, consultas preparadas y SQL dinámico.

La columna EventSubClass de SP:Recompile y SQL:StmtRecompile contiene un código entero que indica la razón de la recompilación. La siguiente tabla contiene el significado de cada número de código.

[!NOTA] Si la opción de base de datos AUTO_UPDATE_STATISTICS se establece en ON por medio del comando SET, las consultas se vuelven a compilar cuando su destino son tablas o vistas indizadas cuyas estadísticas se han actualizado o cuyas cardinalidades han cambiado mucho desde la última ejecución. Este comportamiento se aplica a las tablas estándar definidas por el usuario, a las tablas temporales y a las tablas inserted y deleted creadas por desencadenadores DML. Si el rendimiento de la consulta se ve afectado por un número excesivo de recompilaciones, considere la posibilidad de cambiar esta opción a OFF. Si la opción de base de datos AUTO_UPDATE_STATISTICS se establece en OFF por medio del comando SET, no se produce ninguna recompilación basada en los cambios de las estadísticas o la cardinalidad, con la excepción de las tablas inserted y deleted que se crean mediante los desencadenadores INSTEAD OF de DML. Como estas tablas se crean en tempdb, la recompilación de las consultas a las que tienen acceso depende de la configuración de AUTO_UPDATE_STATISTICS en tempdb. Tenga en cuenta que en SQL Server 2000, las consultas se siguen recompilando en función de los cambios de cardinalidad de las tablas inserted y deleted del desencadenador DML, incluso cuando esta opción está establecida en OFF. Para obtener más información sobre la deshabilitación de AUTO_UPDATE_STATISTICS, vea Estadísticas de índice.

Vea también

Referencia

SQL Statistics (objeto de SQL Server)

Conceptos

Administración de búfer

Otros recursos

Problemas de compilación y recompilación de lotes, y de almacenamiento en caché de planes en SQL Server 2005

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios