sql-server - una - output procedimiento almacenado sql server

La consulta se ejecuta rápidamente, pero se ejecuta lentamente en el procedimiento almacenado

Question

Estoy haciendo algunas pruebas usando el generador de perfiles SQL 2005.

Tengo un procedimiento almacenado que simplemente ejecuta una consulta SQL.

Cuando ejecuto el procedimiento almacenado, lleva mucho tiempo y realiza 800,000 lecturas de disco.

Cuando ejecuto la misma consulta por separado para el procedimiento almacenado, hace 14,000 lecturas de disco.

Descubrí que si ejecuto la misma consulta con OPTION (recompilación), se requieren 800,000 lecturas de disco.

A partir de esto, hago la suposición (posiblemente errónea) de que el procedimiento almacenado se recompila cada vez, y eso está causando el problema.

¿Alguien puede arrojar algo de luz sobre esto?

He puesto ARITHABORT ENCENDIDO. (Esto resolvió un problema similar en stackoverflow, pero no resolvió el mío)

Aquí está todo el procedimiento almacenado:

CREATE PROCEDURE [dbo].[GET_IF_SETTLEMENT_ADJUSTMENT_REQUIRED]
 @Contract_ID int,
 @dt_From smalldatetime,
 @dt_To smalldatetime,
 @Last_Run_Date datetime
AS
BEGIN
 DECLARE @rv int


 SELECT @rv = (CASE WHEN EXISTS
 (
  select * from 
  view_contract_version_last_volume_update
  inner join contract_version
  on contract_version.contract_version_id = view_contract_version_last_volume_update.contract_version_id
  where contract_version.contract_id=@Contract_ID
  and volume_date >= @dt_From
  and volume_date < @dt_To
  and last_write_date > @Last_Run_Date
 )
 THEN 1 else 0 end)

 -- Note that we are RETURNING a value rather than SELECTING it.
 -- This means we can invoke this function from other stored procedures
 return @rv
END

Aquí hay un script que ejecuto que demuestra el problema:

DECLARE 
 @Contract_ID INT,
 @dt_From smalldatetime,
 @dt_To smalldatetime,
 @Last_Run_Date datetime,
    @rv int


SET @Contract_ID=38
SET @dt_From=''2010-09-01''
SET @dt_To=''2010-10-01''
SET @Last_Run_Date=''2010-10-08 10:59:59:070''


-- This takes over fifteen seconds
exec GET_IF_SETTLEMENT_ADJUSTMENT_REQUIRED @Contract_ID=@Contract_ID,@dt_From=@dt_From,@dt_To=@dt_To,@Last_Run_Date=@Last_Run_Date

-- This takes less than one second!
SELECT @rv = (CASE WHEN EXISTS
(
 select * from 
 view_contract_version_last_volume_update
 inner join contract_version
 on contract_version.contract_version_id = view_contract_version_last_volume_update.contract_version_id
 where contract_version.contract_id=@Contract_ID
 and volume_date >= @dt_From
 and volume_date < @dt_To
 and last_write_date > @Last_Run_Date
)
THEN 1 else 0 end)


-- With recompile option. Takes 15 seconds again!
SELECT @rv = (CASE WHEN EXISTS
(
 select * from 
 view_contract_version_last_volume_update
 inner join contract_version
 on contract_version.contract_version_id = view_contract_version_last_volume_update.contract_version_id
 where contract_version.contract_id=@Contract_ID
 and volume_date >= @dt_From
 and volume_date < @dt_To
 and last_write_date > @Last_Run_Date
)
THEN 1 else 0 end) OPTION(recompile)

Answer 1

Como otros han mencionado, este podría ser un problema de ''olfateo de parámetros''. Intenta incluir la línea:

OPTION (RECOMPILE)

al final de su consulta SQL.

Hay un artículo aquí que explica qué es el rastreo de parámetros: http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Answer 2

En mi problema que he corrido:

exec sp_updatestats 

y esto acelera mi sp de 120s a solo 3s. Puede encontrar más información sobre la actualización de estadísticas aquí https://msdn.microsoft.com/en-us/library/ms173804.aspx

Answer 3

La cuestión de por qué un lote tarda una eternidad en ejecutarse dentro de un procedimiento almacenado SQL y se ejecuta instantáneamente en SSMS tiene que ver con el rastreo de parámetros SQL, especialmente con los parámetros de fecha y hora.

Hay varios artículos excelentes sobre detección de parámetros por ahí.

Aquí está uno de ellos (no lo escribí, simplemente lo pasé).

http://www.sommarskog.se/query-plan-mysteries.html

Answer 4

OK, hemos tenido problemas similares a este antes.

La forma en que solucionamos esto fue haciendo parámetros locales dentro del SP, de forma que

DECLARE @LOCAL_Contract_ID int, 
        @LOCAL_dt_From smalldatetime, 
        @LOCAL_dt_To smalldatetime, 
        @LOCAL_Last_Run_Date datetime

SELECT  @LOCAL_Contract_ID = @Contract_ID, 
        @LOCAL_dt_From = @dt_From, 
        @LOCAL_dt_To = @dt_To, 
        @LOCAL_Last_Run_Date = @Last_Run_Date

Luego usamos los parámetros locales dentro del SP en lugar de los parámetros que se pasaron.

Esto generalmente solucionó el problema para nosotros.

Creemos que esto se debe a la detección de parámetros, pero no tenemos ninguna prueba, lo siento ... X-)

EDITAR:

Eche un vistazo a los diferentes enfoques para corregir el olfateo de los parámetros de SQL Server para obtener ejemplos, explicaciones y correcciones perspicaces.

Answer 5

Supongo que esto es causado por la sniffing. parameter sniffing.

Answer 6

Yo también tengo el mismo problema hoy. He descartado y recreado el SP y funcionó. Esto es algo con caché SP y cuando se descarta el SP, el plan en caché se ha eliminado. Puede intentar lo mismo o usar ''DBCC FREEPROCCACHE'' para borrar el caché.