Solucionar problemas de recompilación del procedimiento almacenado

Seleccione idioma Seleccione idioma
Id. de artículo: 243586 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

En esta página

Resumen

Este artículo se refiere a un problema específico de rendimiento que las aplicaciones pueden experimentar con de Microsoft SQL Server: la recompilación en tiempo de ejecución de procedimientos almacenados. Si está solucionando un problema de rendimiento, pero no ha determinado que se trata el origen exacto del problema, consulte el siguiente artículo en Microsoft Knowledge Base antes de continuar:

224587 Cómo: Solucionar problemas de Performance de aplicaciones con SQL Server
En este artículo se supone que ha utilizado para limitar el alcance del problema de dicho artículo, y que ha capturado una traza de SQL Server Profiler con las columnas específicas de los eventos y datos detalladas en él.

Más información

Cuando un usuario ejecuta un procedimiento almacenado, si no está ya disponible en la caché, SQL Server carga el procedimiento y compila un plan de consulta. El plan compilado se guarda en caché y se vuelve a utilizar los llamadores posteriores del procedimiento almacenado hasta que se produzca alguna acción invalidar el plan y forzar una recompilación. Las siguientes acciones pueden causar la recompilación de un plan de procedimiento almacenado:
  • Uso de una cláusula WITH RECOMPILE en el orEXECUTE de la instrucción CREATE PROCEDURE.
  • Cambios en el esquema a cualquiera de los objetos de referencia, includingadding colocar restricciones, valores predeterminados o reglas.
  • Ejecución de sp_recompile para una tabla que hace referencia el procedimiento.
  • Restaurar la base de datos que contiene el procedimiento o cualquiera de los objetos que hace referencia el procedimiento (si va a realizar entre databaseoperations).
  • Actividad del servidor suficiente causando el plan para clasificar por antigüedad de la caché.
Todas estas razones para volver a compilar un procedimiento almacenado existían en las versiones anteriores y provocó el plan para volver a compilar antes de comenzar la ejecución del procedimiento. En SQL Server 7.0, se introduce un nuevo comportamiento que puede provocar que un procedimiento almacenado que volver a compilar durante la ejecución. Este nuevo comportamiento garantiza que el optimizador tiene siempre el mejor plan para cada instrucción específica dentro de un procedimiento. Los siguientes sucesos pueden causar una recompilación de tiempo de ejecución de un procedimiento almacenado:
  • Un porcentaje suficiente de los datos cambia en una tabla que isreferenced por el procedimiento almacenado.
  • El procedimiento intercala las operaciones de lenguaje de definición de datos (DDL) y lenguaje de manipulación de datos (DML).
  • El procedimiento realiza ciertas operaciones en temporarytables.
Cada una de estas causas se explica con más detalle en este artículo.

En algunos casos, el costo de volver a compilar el procedimiento almacenado es mayor que el beneficio derivado de este modo, especialmente para los procedimientos de gran tamaño. Es muy importante tener en cuenta que cuando se desencadena una recompilación, el lote completo o el procedimiento se vuelve a compilar. Esto significa que una degradación del rendimiento es directamente proporcional al tamaño del lote o procedimiento. Para obtener más información acerca de este tema, consulte el tema "Sugerencias de Transact-SQL" en los libros en pantalla de SQL Server.


La siguiente información en este artículo se centra en la identificación de la causa de las recompilaciones de tiempo de ejecución de procedimiento almacenado y describe los métodos que puede utilizar para evitar que.

Práctica recomendada

Es mejor al propietario de calificar los nombres de procedimiento almacenado cuando se ejecuta un procedimiento. Esto permite mejor claridad y más fácil reutilización del plan de ejecución existente por el usuario actual. Por ejemplo, si un usuario que no es el propietario de la base de datos (dbo), ejecuta un procedimiento almacenado en la propiedad de dbo (llamado myProc en este ejemplo) en la base de datos pubs , utilice la siguiente instrucción:
exec dbo.myProc
				
En vez de esto:
exec myProc
				
Esta técnica elimina las dudas sobre otras versiones del procedimiento por distintos propietarios desde el punto de vista de código y el mantenimiento y también permite que SQL Server acceso más directo al plan de ejecución para el procedimiento específico.

Si no califica el nombre del propietario, SQL Server escribe el código de compilación y adquiere un bloqueo de compilación en el procedimiento. Sin embargo, finalmente determina que un nuevo plan no es necesario (suponiendo que no hay otras razones aplican), de modo que no vuelva a compilar el plan en este momento debido a la falta de calificación. Sin embargo, el paso adicional de obtener un bloqueo de compilación en el procedimiento puede causar contención de bloqueo en situaciones graves. Consulte Q263889 INF: SQL bloqueo debido a los bloqueos [[compilación]] para obtener más detalles sobre esta situación.

Si el propietario califica la llamada al procedimiento con owner.procedure, no es necesario adquirir el bloqueo de la compilación, por lo que se reduce la contención.

Identificación y resolución de problemas

Si aún no lo ha hecho, consulte el artículo siguiente en Microsoft Knowledge Base para obtener más información sobre cómo capturar datos del generador de perfiles para ayudar a analizar el rendimiento de su sistema:
224587 Cómo: Solucionar problemas de Performance de aplicaciones con SQL Server

Ver los datos del generador de perfiles

SQL Server Profiler incluye un suceso SP: Recompile que puede utilizar para supervisar el número de nuevas compilaciones que se producen. El suceso SP: Recompile se produce cada vez que se vuelve a compilar durante la ejecución de un procedimiento almacenado.
  • Agrupar la traza del analizador de la clase de evento

    1. En el menú archivo , haga clic en Propiedades.
    2. En la ficha Columnas de datos , utilice el botón para moverse primero la Clase de evento y el texto en el encabezado de grupos , con la Clase de evento . Utilice el botón de abajo para quitar todas las demás columnas bajo el encabezado grupos .
    3. Haga clic en Aceptar.
    Compruebe el número de los eventos SP: Recompile .

    Puede expandir el grupo de SP: Recompile para ver los detalles de las instancias individuales. La columna de texto del evento indica el nombre de la procedurethat almacenado a compilar. Si varios procedimientos causan recompilaciones, son sortedby el número de repeticiones. Si tiene un gran número de eventos de SP: Recompile y está experimentando mucha CPU, foco onresolving los procedimientos que tienen el mayor número de nuevas compilaciones. Tenga en cuenta elsistema proceso ID (SPID) y la hora de inicio del suceso SP: Recompile para una instancia de la seguida de procedimientos almacenados determinado los siguientes pasos.

    Si no ve los eventos SP: Recompile , pero sigue experimentando un problema de rendimiento, ver el siguiente artículo de Microsoft Knowledge Base:
    224587 Cómo: Solucionar problemas de Performance de aplicaciones con SQL Server
  • Determinar la instrucción que activó el evento Recompile

    1. En el menú archivo , haga clic en Propiedades.
    2. En la ficha Columnas de datos , utilice el botón de abajo para quitar todas las demás columnas bajo el encabezado grupos .
    3. En la ficha eventos , quite todos los eventos excepto SP: iniciando, SP: StmtStarting, SP: Recompile, y SP: completar. Si no captura el evento SP: StmtStarting , puede sustituir StmtCompleted, pero no incluya porque hacerlo así que duplica la cantidad de información que necesita para examinar.
    4. Si ha identificado una instancia determinada de una recompilación del procedimiento almacenado para examinar, puede limitar los datos que se ve al período de tiempo de la aparición y SPID específico mediante la ficha filtros .
    5. Haga clic en Aceptar.

    Se provoca el evento SP: Recompile directamente después del evento de SP:StmtStarted de la instrucción del procedimiento almacenado que causó therecompilation. Cuando finalice el evento recompile, verá una repetición del evento SP:StmtStarted , que indica que se ejecuta la instrucción con el plan de newlygenerated.

    Considere el siguiente ejemplo:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    Si ejecuta este código en el analizador de consultas y ver los eventos anteriores ina traza del analizador, verá la siguiente secuencia:

    Contraer esta tablaAmpliar esta tabla
    Clase de eventoTexto
    SP: inicioRecompProc
    SP: StmtStartingCrear tabla #t (un int)
    SP: StmtStartingSeleccionar * desde #t
    SP: RecompileRecompProc
    SP: StmtStartingSeleccionar * desde #t
    SP: completadoRecompProc


    Puede saber inmediatamente la thatcaused de instrucción que fue la recompilación:
    select * from #t
    						
    Ya que aparece antes y después del suceso SP: Recompile .

    Si capturó solamente el suceso SP: StmtCompleted , pero no el evento SP: StmtStarting , el SP: Recompile mostrará directamente antes de la instrucción que causó el asbelow:

    Contraer esta tablaAmpliar esta tabla
    Clase de eventoTexto
    SP: inicioRecompProc
    SP: RecompileRecompProc
    StmtCompletedSeleccionar * desde #t
    SP: completadoRecompProc


    Puede ver que el suceso SP: Recompile se produce antes del evento StmtCompleted para el "seleccionar * de #t" instrucción, que causó la therecompilation. Esto tiene sentido, como la instrucción no puede ser completada untilafter que se genera el nuevo plan de consulta para volver a compilar la. El resto de la theexamples de este artículo utilizan el evento SP: StmtStarting . Si se captura sólo el suceso SP: StmtCompleted , recuerde ver la instrucción después de la SP: Recompile, como se explicó anteriormente.

    Tenga en cuenta que si ejecuta almacenado de este procedimiento varias veces, SQL Server volverá a utilizar el existingplan para realizar este procedimiento. Sólo verá el evento vuelva a compilar en el firstexecution del procedimiento o si quitar y volver a crear la eachtime procedimiento ejecuta la secuencia de comandos. El motivo de la recopilación en este specificcase se explica en la sección "Recompilaciones debido al procesamiento simultáneo de datos DefinitionLanguage (DDL) y datos Language (DML) operaciones de manipulación" trate; Esto es simplemente un ejemplo para ilustrar cómo determinar fácilmente whichstatement está causando la recompilación.

Recompilaciones debido a modificaciones de fila

Si ha cambiado un porcentaje suficiente de los datos en una tabla hace referencia a un procedimiento almacenado desde el momento en que se generó el plan de consulta original, SQL Server volverá a compilar el procedimiento almacenado para asegurarse de que tiene un plan basado en los datos estadísticos más actualizados. Por ejemplo, considere el siguiente procedimiento almacenado:
drop procedure RowModifications 
go
create procedure RowModifications as
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications
				
En la segunda ejecución del procedimiento RowModifications , verá los siguientes sucesos en el generador de perfiles:

Contraer esta tablaAmpliar esta tabla
Clase de eventoTexto
SP: inicioRowModifications
SP: StmtStartingCrear tabla #t (a int, b char(10))
SP: StmtStartingSeleccionar * desde #t
SP: StmtStartingInsertar #t select * de Alguna_tabla
SP: StmtStartingSeleccione Count de #t donde un = 37
SP: RecompileRowModifications
Auto-UpdateStatsun
SP: StmtStartingSeleccione Count de #t donde un = 37
SP: completadoRowModifications

Nota: La primera ejecución también mostrará un suceso SP: Recompile para la "seleccionar * de #t" instrucción. El motivo de la recopilación en este caso se explica en la sección "Recompilaciones debido al procesamiento simultáneo de datos definición lenguaje (DDL) y datos Language (DML) operaciones de manipulación" de este artículo. En este ejemplo, se centran en el SP: Recompile mostrado anteriormente, ya que se produce cada vez que se ejecuta el procedimiento.

En este ejemplo, "select Count de #t donde un = 37" causa una recompilación del procedimiento debido al cambio en el número de filas desde que se creó la tabla. La presencia del evento UpdateStats de Auto confirma que la recompilación debido a las modificaciones de fila. La columna de texto indica la columna para la que se modificaron las estadísticas.

Cuando se creó la tabla #t, el número de filas es cero. El plan para el original "seleccionar * de #t" desarrollado con ese recuento de filas, así como el plan de la consulta "select count (*)". Sin embargo, antes de que se ejecute "select count", 1.000 nuevas filas se insertan en la tabla #t. Porque se ha cambiado una cantidad suficiente de datos, el optimizador vuelve a compilar el procedimiento para asegurarse de que elige el plan más eficaz para la instrucción. Esta recompilación se producirá en cada ejecución del procedimiento almacenado, ya que la inserción de 1.000 filas siempre se ven importantes como justificar la recompilación.

El algoritmo que utiliza SQL Server para determinar si se debe volver a compilar un plan es el mismo algoritmo que se utiliza para la actualización automática de estadísticas como se describe en el siguiente artículo en Microsoft Knowledge Base:
195565 INF: SQL Server 7.0 y Autostat de SQL Server 2000 en funcionamiento
En el ejemplo anterior, el procedimiento almacenado es lo suficientemente pequeño como para que la nueva compilación no tendría un efecto notable en el rendimiento. Sin embargo, si tiene un gran procedimiento almacenado que realiza actividades similares, resultando en varias de las recompilaciones, puede observar una disminución del rendimiento.

Existen los siguientes métodos para contrarrestar las recompilaciones debido a modificaciones de fila:
  • Ejecute la instrucción con sp_executesql.
    Éste es el método preferido. Las instrucciones ejecutadas mediante el procedimiento almacenadosp_executesql no se compilan como parte de la procedureplan almacenado. Por lo tanto, al ejecutar la instrucción, SQL Server se intoeither libre usarse un plan existente en la memoria caché para la instrucción o crear una nueva una atrun hora. En cualquiera caso, el plan de la llamada isunaffected del procedimiento almacenado y no tiene que volver a compilar.

    El statementwill de ejecución tienen el mismo efecto; Sin embargo, no se recomienda. Usando el EXECUTEstatement no es tan eficaz como utilizar sp_executesql porque no se permite para la parametrización de la unión.

    El procedimiento RowModifications anterior puede escribirse utilizar sp_executesql como sigue:

    drop procedure RowModifications2 
    go
    create procedure RowModifications2 as
    set nocount on
    -- assume SomeTable exists with the same definition as #t, 
    -- and has over 1000 rows
    create table #t (a int, b char(10))
    select * from #t
    insert #t select * from SomeTable
    exec sp_executesql N'select count(*) from #t where a = @a', 
                       N'@a int', @a =  37
    go
    exec RowModifications2
    exec RowModifications2
    						

    En la segunda ejecución del procedimiento RowModifications2 , verá la siguiente inProfiler de eventos:

    Contraer esta tablaAmpliar esta tabla
    Clase de eventoTexto
    SP: inicioRowModifications2
    SP: StmtStartingCrear tabla #t (un int, bchar(10))
    SP: StmtStartingSeleccionar * desde #t
    SP: StmtStartingInsertar selección #t * fromSomeTable
    SP: StmtStartingexec sp_executesql N'selectcount(*) de #t donde un = @ un ', N'@a int', @ un = 37
    SP: inicio
    SP: StmtStartingSeleccione Count en #t, donde un =@a
    Auto-UpdateStatsun
    SP: StmtStartingSeleccione Count en #t, donde un =@a
    SP: completado
    SP: completadoRowModifications2


    Observe que no hay ningún suceso SP: Recompile para el procedimiento RowModifications2 . Hay completa SP: a partir a SP: completado eventos para la sp_executesql llaman contexto y un evento de Auto-UpdateStats para la columna a. Sin embargo, dado que esta llamada es fuera del contexto del procedimiento almacenado, el procedimiento RowModifications2 no necesario volver a compilar en este caso.

    Para obtener más información acerca de cómo utilizar el procedimiento almacenado sp_executesql , vea los temas de "Usingsp_executesql" en los libros en pantalla de SQL Server y el "sp_executesql (T-SQL)".
  • Utilice procedimientos Sub para ejecutar las instrucciones que son las recompilaciones.
    En este caso, la instrucción todavía puede causar arecompilation, pero en lugar de volver a compilar el procedimiento almacenado de llamada grande, sólo recompilar el procedimiento Sub pequeño.
  • Utilice la opción KEEP PLAN.
    Reglas de especialesal de las tablas temporales con respecto a las recompilaciones que, en algunos casos, pueden ser morestringent que el algoritmo de recompilación predeterminado. Puede utilizar la PLANoption mantener relajar el umbral de tabla temporal el algoritmo predeterminado.Para obtener más información, consulte la sección "Evitar volver a compilar por mediante el mantener PLANOption" de este artículo.
Nota: El procedimiento RowModifications es un ejemplo muy simplificado de un procedimiento que se vuelve a compilar debido a las modificaciones de fila. Revise las advertencias siguientes relativas a este ejemplo:

  • Aunque el ejemplo utiliza una tabla temporal, esta situationapplies a los procedimientos almacenados que hacen referencia a las tablas permanentes. Si la suficiente cantidad de datos en una tabla de referencia se ha alterado desde que se generó el plan de la unión, se volverá a compilar el procedimiento almacenado. Thedifferences en las tablas temporales cómo se consideran para purposesare de recompilación que se describe en la sección "Evitar recompilación por mediante el PLAN opción mantener" de este artículo.
  • Las primeras ejecuciones de los dos procedimientos anteriores también ocurriría recompilación en la primera instrucción select de la tabla temporal #t. El razonespara esta recompilación se describen en la sección "Recompilaciones debido a InterleavingData Definition Language (DDL) y datos Language (DML) operaciones de manipulación" de este artículo.
  • Se utilizó una instrucción "select Count de #t" en thisexample, en lugar de un simple "seleccionar * de #t" instrucción. Para evitar excessiverecompilations, SQL Server no tiene en cuenta volver a compilar «planes triviales» (como una instrucción select * de una tabla) debido a las modificaciones de fila.

Recompilaciones debido al procesamiento simultáneo de lenguaje de definición de datos (DDL) y lenguaje (DML) operaciones de manipulación de datos

Si se realizan operaciones de DDL en un procedimiento o lote, se recompila el lote o al procedimiento cuando encuentra la primera operación DML subsiguiente que afectan a la tabla implicada en el DDL.

Tenga en cuenta el procedimiento almacenado del ejemplo siguiente:
drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table t1 (a int)
-- DML
select * from t1
-- DDL
create index idx_t1 on t1(a)
-- DML
select * from t1
-- DDL
create table t2 (a int)
-- DML
select * from t2
go
exec Interleave
				
Si ejecuta este código en el analizador de consultas y ver los eventos anteriores en una traza del analizador, verá la siguiente secuencia:

Contraer esta tablaAmpliar esta tabla
Clase de eventoTexto
SP: inicioIntercalar
SP: StmtStartingCrear tabla t1 (un int)
SP: StmtStartingSelect * from t1
SP: RecompileIntercalar
SP: StmtStartingSelect * from t1
SP: StmtStartingCrear índice idx_t1 en t1(a)
SP: StmtStartingSelect * from t1
SP: RecompileIntercalar
SP: StmtStartingSelect * from t1
SP: StmtStartingCrear tabla t2 (un int)
SP: StmtStartingSeleccionar * desde t2
SP: RecompileIntercalar
SP: StmtStartingSeleccionar * desde t2
SP: completadoIntercalar


En este caso, el procedimiento almacenado se vuelve a compilar tres veces durante la ejecución. Para entender por qué esto ocurre, tenga en cuenta cómo el optimizador desarrolla un plan para este procedimiento almacenado:
  1. Durante la compilación inicial del procedimiento, no existen tablas t1and t2. Por lo tanto, se puede crear ningún plan para las consultas que se hace referencia a thesetables. Debe generar en tiempo de ejecución.
  2. Cuando el procedimiento se ejecuta por primera vez, es el firststep crear la tabla t1. El siguiente paso es una instrucción select de la tabla t1: whichthere no es un plan para. Por lo tanto, se vuelve a compilar el procedimiento en este punto, desarrollar un plan para la instrucción SELECT. Se genera un plan para la currentselect de t1, así como la selección de t1 después de la creación del índice. No plancan se genera de la instrucción select en t2 porque t2 sigue no existyet.
  3. El siguiente paso es crear un índice en t1. Después, seleccione otro se realiza en t1, que ahora tiene un plan de la firstrecompile. Sin embargo, porque ha cambiado el esquema de t1 desde ese planwas generado, el procedimiento debe compilarse otra vez para generar un nuevo plan select from t1. Y t2 existe, ningún plan de alerta para la selección de t.
  4. A continuación, se crea la tabla t2 y Active de t2 isexecuted. Dado que no existe ningún plan para la instrucción, el procedimiento se vuelve a compilar tiempo final.
Estas recompilaciones se producen en cada ejecución del procedimiento almacenado. Para reducir las recompilaciones, modifique el procedimiento para realizar todas las operaciones de DDL en primer lugar, seguido por las operaciones DML, tal como se muestra en el siguiente ejemplo:
drop procedure NoInterleave 
go
create procedure NoInterleave as
-- All DDL first
create table t1 (a int)
create index idx_t1 on t1(a)
create table t2 (a int)
-- Then DML 
select * from t1
select * from t1
select * from t2
go
exec NoInterleave 
exec NoInterleave
				
La primera ejecución del procedimiento /NoInterleave mostrará los siguientes eventos en el generador de perfiles:

Contraer esta tablaAmpliar esta tabla
Clase de eventoTexto
SP: inicio/NoInterleave
SP: StmtStartingCrear tabla t1 (un int)
SP: StmtStartingCrear índice idx_t1 en t1(a)
SP: StmtStartingCrear tabla t2 (un int)
SP: StmtStartingSelect * from t1
SP: Recompile/NoInterleave
SP: StmtStartingSelect * from t1
SP: StmtStartingSelect * from t1
SP: StmtStartingSeleccionar * desde t2
SP: completado/NoInterleave


En este caso todas las instrucciones de DDL se realizan por adelantado. El optimizador compila este procedimiento como sigue:
  1. Durante la compilación inicial del procedimiento, no existen tablas t1and t2. Por lo tanto, se puede crear ningún plan para las consultas que se hace referencia a thesetables. Debe generar en tiempo de ejecución.
  2. Los primeros pasos que realiza el procedimiento son el DDLoperations, la creación de las tablas t1 y t2, así como el índice en t1.
  3. El siguiente paso es la primera instrucción select from t1. Porque no hay plan disponible para esta instrucción SELECT, se vuelve a compilar el procedimiento.Como todos los objetos se encuentran, se generan planes para todos los SELECTstatements en el procedimiento en este momento.
  4. El resto del procedimiento ejecuta en el plansgenerated. Porque no hay ningún cambio en los objetos que se hace referencia, no hay necesidad de volver a compilar el procedimiento adicional.
Nota: Las segunda y posteriores ejecuciones hacen uso del plan de consulta y de caché existentes y hacer no como resultado de las recompilaciones en absoluto. Procedimientos para crean, modificar o quitar tablas deben modificarse para asegurarse de que todas las instrucciones de DDL se encuentra al principio del procedimiento.

Recompilaciones debido a determinadas operaciones de tabla temporal

Uso de tablas temporales en un procedimiento almacenado puede causar el procedimiento a compilarse cada vez que se ejecuta el procedimiento almacenado.

Para evitar este problema, cambie el procedimiento almacenado para que cumpla los siguientes requisitos:
  • Todas las instrucciones que contienen el nombre de un tablerefer temporal a una tabla temporal crean en el mismo procedimiento almacenado y no en lo o llama al procedimiento almacenado o en una cadena ejecutada con EXECUTEstatement o sp_executesql de un procedimiento almacenan.
  • Todas las instrucciones que contienen el nombre de un tableappear temporal sintácticamente después de la tabla temporal en la ortrigger del procedimiento almacenado.
  • No hay ninguna instrucción DECLARE CURSOR cuyas SELECTstatements hacer referencia a una tabla temporal.
  • Todas las instrucciones que contienen el nombre de cualquier tableprecede temporal cualquier instrucción DROP TABLE que hace referencia a una tabla temporal.

    Las instrucciones DROP TABLE no son necesarios para las tablas temporales creadas en un procedimiento almacenado. Las tablas se quitan automáticamente cuando el procedimiento hascompleted.
  • Ninguna instrucción crea una tabla temporal (como CREATETABLE o SELECT... INTO) aparecen en una instrucción de control de flujo, por ejemplo, IF...ELSE o WHILE.

Evitar volver a compilar con la opción KEEP PLAN

Uso de tablas temporales dentro de los procedimientos almacenados presenta cierta complejidad para el optimizador de consultas. El recuento de filas y la información estadística de las tablas pueden variar considerablemente a lo largo de la duración de la ejecución del procedimiento almacenado. Para asegurarse de que el optimizador utiliza el mejor plan en todos los casos relativos a las tablas temporales, se desarrolló un algoritmo especial para ser más agresivo con recompilaciones. El algoritmo indica que si una tabla temporal creada con un procedimiento almacenado ha cambiado más de seis veces, se volverán a compilar el procedimiento cuando la siguiente instrucción hace referencia a la tabla temporal.

Considere el siguiente ejemplo:
drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Make greater than 6 changes to #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Now reference #t
select count(*) from #t 
--option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan
				
En este caso, verá los siguientes sucesos en el generador de perfiles para la segunda ejecución:

Contraer esta tablaAmpliar esta tabla
Clase de eventoTexto
SP: iniciouseKeepPlan
SP: StmtStartingCrear tabla #t (un int)
SP: StmtStarting -Las siete insertar instrucciones:
SP: StmtStartingSeleccione Count en t1.
SP: RecompileuseKeepPlan
SP: StmtStartingSeleccione Count en t1.
SP: completadouseKeepPlan

El procedimiento se vuelve a compilar en la selección que se produce después de los cambios de siete a la tabla temporal #t.

Esta recompilación agresiva es útil en casos donde los cambios en la distribución de los datos de la tabla temporal pueden afectar drásticamente al plan de consulta óptimo para la instrucción de hacer referencia a él. Sin embargo, en el caso de los procedimientos de gran tamaño que modifican tablas temporales con frecuencia, pero no de una manera significativa, las recompilaciones pueden resultar en un rendimiento general. La opción de mantener el PLAN de la instrucción SELECT se introdujo para esta situación.

KEEP PLAN elimina las recompilaciones de procedimiento almacenado causadas por más de seis cambios a las tablas temporales dentro del procedimiento y vuelve al algoritmo estándar de recompilación debido a las modificaciones de fila, descrito anteriormente en la sección "Recompilaciones debido a modificaciones de fila" de este artículo. KEEP PLAN no evitar recompilaciones en conjunto, simplemente evita que las causadas por más de seis de los cambios en las tablas temporales que se hace referencia en el procedimiento. En el ejemplo anterior, si quita el comentario de la línea "opción (KEEP PLAN)" en el procedimiento almacenado, no se generará el evento SP: Recompile .

Elimine el comentario de la línea "opción (KEEP PLAN)" en el código anterior y ejecutarlo, verá los siguientes sucesos en el generador de perfiles:

Contraer esta tablaAmpliar esta tabla
Clase de eventoTexto
SP: iniciouseKeepPlan
SP: StmtStartingCrear tabla #t (un int)
SP: StmtStarting -Las siete insertar instrucciones:
SP: StmtStartingSeleccione Count de opción #t1 (KEEP PLAN)
SP: completadouseKeepPlan


Tenga en cuenta que no hay ningún evento SP: Recompile .

Recompilaciones debido a ciertas instrucciones SET que se ejecutan en el procedimiento almacenado

Las siguientes cinco opciones de SET se establecen en ON de forma predeterminada:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
Si se ejecuta la instrucción SET para establecer cualquiera de estas opciones en OFF, el procedimiento almacenado se volverá a compilar cada vez que se ejecuta. La razón de esto es que cambiar estas opciones puede afectar el resultado de la consulta que desencadenó la recompilación.

Considere el siguiente código de ejemplo:
Use pubs
drop procedure test_recompile
go

create procedure test_recompile as
Set ANSI_DEFAULTS OFF
Select au_lname, au_fname, au_id from authors
where au_lname like 'L%'
--Option (Keep Plan)
Go
				
En este caso, verá lo siguiente en el analizador de SQL para cada ejecución del procedimiento almacenado:
+---------------------------------------------------+
| Event Class     | Text                            | 
+---------------------------------------------------+
| SP:Starting     | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | Set ANSI_DEFAULTS OFF           | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Recompile    | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Completed    | test_recompile                  | 
+---------------------------------------------------+
				
Reemplazar la opción SET con cualquiera de las cinco opciones anteriores le mostrará los resultados de la mismos. Además, con la opción de mantener plan aquí no ayuda a evitar la recompilación porque es la causa de la recompilación de la instrucción SET.

La forma recomendada para evitar la recopilación es no utilizar cualquiera de estos cinco instrucción SET en un procedimiento almacenado. Para obtener información adicional, consulte el artículo siguiente en Microsoft Knowledge Base:
294942 PRB: SET CONCAT_NULL_YIELDS_NULL puede hacer que los procedimientos almacenados se vuelva a compilar
Sin embargo, como no se recomienda, ejecute la instrucción SET para restablecer la opción de conexión en el mismo valor que el procedimiento almacenado, también puede evitar volver a compilar la, hacerlo como:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
La traza del analizador de SQL no mostrará ningún suceso SP: Recompile más.

La tabla siguiente muestra algunas instrucciones de conjunto común y si se cambia la instrucción SET en un procedimiento almacenado, una recompilación:
Contraer esta tablaAmpliar esta tabla
Set (instrucción)Vuelva a compilar
Set quoted_identifierNo
Set arithabort
Set ansi_null_dflt_on
Set ansi_defaults
Set ansi_warnings
Set ansi_padding
Set concat_null_yields_null
Set numeric_roundabortNo
Set nocountNo
Set rowcountNo
Set xact_abortNo
Set implicit_transactionsNo
Set arithignoreNo
Set lock_timeoutNo
Set fmtonlyNo

Referencias

308737 INF: Cómo identificar la causa de la recompilación en un suceso SP: Recompile

Para obtener información acerca de cómo utilizar SQL Server Profiler, consulte los libros en pantalla de SQL Server.

Propiedades

Id. de artículo: 243586 - Última revisión: sábado, 23 de noviembre de 2013 - Versión: 5.0
La información de este artículo se refiere a:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
Palabras clave: 
kbinfo kbmt KB243586 KbMtes
Traducción automática
IMPORTANTE: Este artículo ha sido traducido por un software de traducción automática de Microsoft (http://support.microsoft.com/gp/mtdetails) en lugar de un traductor humano. Microsoft le ofrece artículos traducidos por un traductor humano y artículos traducidos automáticamente para que tenga acceso en su propio idioma a todos los artículos de nuestra base de conocimientos (Knowledge Base). Sin embargo, los artículos traducidos automáticamente pueden contener errores en el vocabulario, la sintaxis o la gramática, como los que un extranjero podría cometer al hablar el idioma. Microsoft no se hace responsable de cualquier imprecisión, error o daño ocasionado por una mala traducción del contenido o como consecuencia de su utilización por nuestros clientes. Microsoft suele actualizar el software de traducción frecuentemente.
Haga clic aquí para ver el artículo original (en inglés): 243586

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com