• Artículo

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

SQL Server tiene un bloque de memoria que se utiliza para almacenar planes de ejecución y búferes de datos. El porcentaje del conjunto 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 bloque 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 tienen los siguientes componentes principales:

  • Plan de consulta

    La mayor parte del plan de ejecución es una estructura de datos reentrante de solo lectura que varios usuarios pueden utilizar. Esto se conoce como plan de consulta. No se almacena ningún contexto de usuario en el plan de consulta. Nunca hay más de una o dos copias del plan de consulta 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.

Contexto de ejecución, misma consulta, diferentes literales

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

SQL Server tiene un algoritmo eficiente que permite encontrar cualquier plan de ejecución existente para una determinada instrucción SQL. 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 Person;

SELECT * FROM Person.Person;

Quitar planes de ejecución desde la memoria caché de procedimientos

Los planes de ejecución permanecen en la memoria caché de procedimientos en tanto en cuanto haya suficiente memoria para almacenarlos. Cuando existe presión de memoria, Motor de base de datos usa un enfoque basado en costos para determinar qué planes de ejecución hay que quitar de la memoria caché de procedimientos. Para tomar una decisión basada en costos, Motor de base de datos incrementa y reduce una variable de costo actual por cada plan de ejecución de acuerdo con los factores siguientes.

Cuando un proceso de usuario inserta un plan de ejecución en la memoria caché, el proceso de usuario establece el costo actual igual al costo de compilación de la consulta original; para los planes de ejecución ad hoc, el proceso de usuario establece el costo actual en cero. Después, cada vez que un proceso de usuario hace referencia a un plan de ejecución, restablece el costo actual en el costo de compilación original; en el caso de los planes de ejecución ad hoc, el proceso de usuario aumenta el costo actual. Para todos los planes, el valor máximo del costo actual es el costo de compilación original.

Cuando existe presión de memoria, Motor de base de datos responde quitando los planes de ejecución de la memoria caché de procedimientos. Para determinar qué planes quitar, Motor de base de datos examina repetidamente el estado de cada plan de ejecución y quita los planes cuando su costo actual es cero. Un plan de ejecución con un costo actual de cero no se quita automáticamente cuando existe presión de memoria; solamente se quita cuando Motor de base de datos examina el plan y el costo actual es cero. Al examinar un plan de ejecución, Motor de base de datos impulsa el costo actual hacia cero reduciendo el costo actual si una consulta no está usando actualmente el plan.

Motor de base de datos examina repetidamente los planes de ejecución hasta que se han quitado los suficientes para satisfacer los requisitos de memoria. Mientras existe presión de memoria, un plan de ejecución puede ver su costo incrementado y reducido más de una vez. Cuando ya no existe presión de memoria, Motor de base de datos deja de reducir el costo actual de los planes de ejecución no usados y todos los planes de ejecución permanecen en la memoria caché de procedimientos, incluso aunque su costo sea cero.

Motor de base de datos usa el monitor de recursos y los subprocesos de usuario para liberar memoria desde la memoria caché de procedimientos como respuesta a la presión de memoria. El monitor de recursos y los subprocesos de usuario pueden examinar los planes que se ejecutan simultáneamente y reducir el costo actual del plan de ejecución no usado. El monitor de recursos quita los planes de ejecución de la memoria caché de procedimientos cuando hay presión de memoria global. Libera memoria para aplicar las directivas correspondientes a la memoria del sistema, memoria de procesos, memoria del grupo de recursos y tamaño máximo de todas las memorias de caché.

El tamaño máximo de todas las memorias de caché es una función del tamaño del conjunto de búferes y no puede exceder de la memoria máxima del servidor. Para obtener más información acerca de la configuración de la memoria máxima del servidor, vea la configuración max server memory en sp_configure (Transact-SQL).

Los subprocesos de usuario quitan los planes de ejecución de memoria caché de procedimientos cuando existe presión de memoria única. Aplican las directivas del tamaño máximo de la memoria caché única y de las entradas máximas de la memoria caché única.

Los ejemplos siguientes ilustran qué planes de ejecución se quitan de la memoria caché de procedimientos:

  • Se suele hacer referencia a un plan de ejecución como si su costo nunca llegara a ser cero. El plan permanece en la caché de procedimiento y no se quita a menos que haya presión de memoria y el costo actual sea cero.

  • Se insertó un plan de ejecución ad hoc y no se le vuelve a hacer referencia antes de que exista presión de memoria. Dado que los planes ad hoc se inician con un costo actual de cero, cuando el motor de la base de datos examina el plan de ejecución, verá el costo actual de cero y quitará el plan de la memoria caché de procedimientos. El plan de ejecución ad hoc permanece en la memoria caché de procedimientos con el costo actual de cero cuando ya no hay presión de memoria.

Para quitar manualmente un único plan o todos los planes de la memoria caché, utilice DBCC FREEPROCCACHE (Transact-SQL).

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 y versiones posteriores, solo se vuelve a compilar la instrucción del lote que provoca la compilación. Debido a esta diferencia, los recuentos de compilaciones de SQL Server 2000 y de versiones posteriores no son comparables. Además, existen otros tipos de compilaciones en SQL Server 2005 y en versiones posteriores, gracias al 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 seguimiento SP:Recompile del SQL Server Profiler envía informes de recompilaciones de nivel de instrucciones. Estos eventos de seguimiento solo comunican recompilaciones de lote en SQL Server 2000. Además, se rellena la columna TextData de este evento. 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.

El evento de seguimiento SQL:StmtRecompile envía informes de recompilaciones de nivel de instrucciones. Este evento de seguimiento se puede utilizar para hacer el seguimiento y depurar recompilaciones. Aunque SP:Recompile solo 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.

Valor de EventSubClass

Descripción

1

Esquema modificado.

2

Estadísticas modificadas.

3

Compilación retrasada.

4

Opción SET modificada.

5

Tabla temporal modificada.

6

Conjunto de filas remoto modificado.

7

Permiso FOR BROWSE modificado.

8

Entorno de notificación de consultas modificado.

9

Vista con particiones modificada.

10

Opciones de cursor modificadas.

11

OPTION (RECOMPILE) solicitada.

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 acerca de cómo deshabilitar AUTO_UPDATE_STATISTICS, consulte Utilizar las estadísticas para mejorar el rendimiento de las consultas.