No se puede probar la consulta, pero se necesita la mejor estrategia de indexación para la cláusula WHERE OR.. OR.. OR

Una aplicación ejecuta una rutina una vez a la semana. La última ejecución fue extremadamente lenta y sabemos que se procesarán más datos durante la próxima ejecución. La instrucción más lenta de la rutina, con diferencia, une una tabla temporal a una tabla de productos. Profiler muestra un número muy alto de lecturas, lo que sugiere que no está bien indexado. Durante la última ejecución, la tabla Productos tenía 200 000 filas y la tabla temporal tenía 1200.

 update tmp      
 set tmp.col1 = pd.col1, 
     tmp.col2 = pd.col2, 
     tmp.col3 = pd.col3 
 from #temptable tmp
, Products pd with (nolock)      
 where tmp.col2 = pd.col2 
    or tmp.col2 = pd.col3 
    or tmp.col2 = pd.col4       
    or tmp.col2 = pd.col5 

Solo tengo una oportunidad de aplicar una estrategia de indexación. La tabla temporal se genera a partir de datos que solo existen durante un breve período de tiempo y no existe ninguna copia, por lo que no se puede volver a crear antes de la próxima ejecución. El caché del plan no tiene un plan de ejecución.

La consulta debe actualizarse a ANSI-92, pero la trato como se encontró.

La tabla de productos tiene índices en cada una de las columnas col2, col3, col4, col5, pero no cubre compuestos ni INCLUYE para los valores de actualización.

La tabla temporal no tiene indexación.

No he probado nada porque no hay forma de probar antes de la próxima ejecución en vivo.

¿Alguien puede aconsejarme si debo aplicar un índice compuesto que cubra las 4 columnas de Producto o usar cuatro índices, uno para cada columna e INCLUYE para col1, col2 y col3?

Su pregunta tiene limitaciones ya que no puede obtener el showplan, prefiero seguir estos enfoques. Nota: esto da una pista de índices y consultas costosas si su servidor no se reinicia desde la última vez que se ejecutó su proceso.

Si utiliza la combinación interna de la tabla temporal con la tabla de productos, mejorará la ejecución de consultas.

Siga estos enfoques

1) Verifique la consulta más costosa por query_stats, use el siguiente script para aislar si ese proceso almacenado está en la lista de consultas costosas

SELECT TOP 5 t.TEXT AS 'SQL Text'
    ,st.execution_count
    ,ISNULL(st.total_elapsed_time / st.execution_count, 0) AS 'AVG Excecution Time'
    ,st.total_worker_time / st.execution_count AS 'AVG Worker Time'
    ,st.total_worker_time
    ,st.max_logical_reads
    ,st.max_logical_writes
    ,st.creation_time
    ,ISNULL(st.execution_count / DATEDIFF(second, st.creation_time, getdate()), 0) AS 'Calls Per Second'
FROM sys.dm_exec_query_stats st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) t
ORDER BY st.total_elapsed_time DESC

2) También verifique los requisitos de índice para cualquier consulta reciente ejecutada en el servidor, este es un script excelente de Pinal que nos brinda la necesidad de índices, si no puede volver a ejecutar la consulta manualmente o no tiene un plan de presentación https://blog.sqlauthority .com/2011/01/03/sql-server-2008-missing-index-script-download/

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Ps: esta solución es solo una indicación de los índices requeridos, principalmente use su intuición