Guía de optimización y validación posterior a la migración

Se aplica a:SQL Server

El paso posterior a la migración de SQL Server es fundamental para reconciliar cualquier precisión e integridad de los datos, así como para solucionar problemas de rendimiento con la carga de trabajo.

Escenarios comunes de rendimiento

A continuación se muestran algunos de los escenarios comunes de rendimiento detectados después de migrar a la plataforma SQL Server y cómo resolverlos. Puede tratarse de escenarios que son específicos de la migración de SQL Server a SQL Server (de versiones anteriores a versiones más recientes), así como de una plataforma externa (por ejemplo, Oracle, DB2, MySQL y Sybase) a SQL Server.

Consulta de regresiones debidas a un cambio en la versión de estimación de cardinalidad

Se aplica a: migración de SQL Server a SQL Server.

Al migrar desde una versión anterior de SQL Server a SQL Server 2014 (12.x) o posterior, y al actualizar el nivel de compatibilidad de la base de datos al más reciente disponible, una carga de trabajo podría quedar expuesta al riesgo de regresión del rendimiento.

Esto es debido a que, a partir de SQL Server 2014 (12.x), todos los cambios del optimizador de consultas están vinculados al nivel de compatibilidad de la base de datos más reciente, por lo que los planes no se cambian en el momento de la actualización, sino cuando un usuario cambia la opción COMPATIBILITY_LEVEL de la base de datos a la versión más reciente. Esta función, junto con el Almacén de consultas, confiere al usuario un enorme control sobre el rendimiento de las consultas en el proceso de actualización.

Para obtener más información sobre los cambios del optimizador de consultas introducidos en SQL Server 2014 (12.x), consulte Optimización de los planes de consulta con el estimador de cardinalidad de SQL Server 2014.

Pasos para resolver

Cambie el nivel de compatibilidad de la base de datos a la versión de origen y siga el flujo de trabajo de actualización recomendado, tal como se muestra en la siguiente imagen:

Para obtener más información sobre este tema, consulte Mantener la estabilidad del rendimiento al actualizar a SQL Server 2016.

Sensibilidad al examen de parámetros

Se aplica a: migración de una plataforma externa (por ejemplo, Oracle, DB2, MySQL y Sybase) a SQL Server.

Nota:

En el caso de las migraciones de SQL Server a SQL Server, si este problema existía en la instancia de SQL Server de origen, la migración a una versión más reciente de SQL Server tal cual no solucionará el problema.

SQL Server compila planes de consulta con procedimientos almacenados mediante el examen de parámetros de entrada en la primera compilación y la generación de un plan con parámetros reutilizable, optimizado para esa distribución de datos de entrada. Incluso si no hay procedimientos almacenados, la mayoría de instrucciones que generan planes triviales se parametrizarán. Después de que un plan se almacene en caché por primera vez, cualquier ejecución futura se asigna a un plan previamente almacenado en caché. Surge un posible problema si esa primera compilación puede que no haya usado los conjuntos de parámetros más comunes para la carga de trabajo habitual. Para parámetros diferentes, el mismo plan de ejecución es ineficaz. Para obtener más información sobre este tema, consulte Examen de parámetros.

Pasos para resolver

1. Use la sugerencia RECOMPILE. Un plan se calcula cada vez adaptado a cada valor de parámetro.
2. Vuelva a escribir el procedimiento almacenado para usar la opción (OPTIMIZE FOR(<input parameter> = <value>)). Decida qué valor quiere usar que se adapte a la mayor parte de la carga de trabajo relevante, creando y manteniendo un plan que sea eficaz para el valor con parámetros.
3. Vuelva a escribir el procedimiento almacenado mediante la variable local dentro del procedimiento. Ahora, el optimizador usa el vector de densidad para las estimaciones, lo que produce el mismo plan independientemente del valor del parámetro.
4. Vuelva a escribir el procedimiento almacenado para usar la opción (OPTIMIZE FOR UNKNOWN). Se consigue el mismo efecto que al usar la técnica de variable local.
5. Vuelva a escribir la consulta para usar la sugerencia DISABLE_PARAMETER_SNIFFING. Se consigue el mismo efecto que al usar la técnica de variable local al deshabilitar completamente el examen de parámetros, a menos que se usen OPTION(RECOMPILE), WITH RECOMPILE o OPTIMIZE FOR <value>.

Sugerencia

Aproveche la característica de análisis de plan de Management Studio para identificar rápidamente si se trata de un problema. Encontrará más información aquí.

Faltan índices

Se aplica a: migración de una plataforma externa (por ejemplo, Oracle, DB2, MySQL y Sybase) y SQL Server a SQL Server.

Los índices que faltan o son incorrectos causan una E/S adicional que producen una memoria adicional y se malgasta CPU. Esto puede ser porque ha cambiado el perfil de carga de trabajo, como al usar predicados diferentes, invalidando el diseño de índices existente. Evidencia de una estrategia de indexación deficiente o cambios en el perfil de carga de trabajo incluyen:

• Busque índices duplicados, redundantes, usados con poca frecuencia y que no se han usado nunca.
• Tenga especial cuidado con los índices no usados con actualizaciones.

Pasos para resolver

1. Aproveche el plan de ejecución gráfico de cualquiera referencia de índice que falte.
2. Sugerencias de indexación generadas por el Asistente para la optimización de motor de base de datos.
3. Aproveche la DMV de los índices que faltan o a través del panel de rendimiento de SQL Server.
4. Aproveche los scripts que existían previamente que puedan usar DMV existentes para proporcionar una visión general de los índices, duplicados, redundantes, poco usados, que no se han usado nunca o que faltan, pero también si alguna referencia de índice se sugiere o codifica de forma rígida en procedimientos y funciones existentes de la base de datos.

Sugerencia

Entre los ejemplos de estos scripts que existían previamente se encuentran la creación de índices y la información del índice.

Incapacidad de usar predicados para filtrar datos

Se aplica a: migración de una plataforma externa (por ejemplo, Oracle, DB2, MySQL y Sybase) y SQL Server a SQL Server.

Nota:

En el caso de las migraciones de SQL Server a SQL Server, si este problema existía en la instancia de SQL Server de origen, la migración a una versión más reciente de SQL Server tal cual no solucionará el problema.

El optimizador de consultas de SQL Server solo puede tener en cuenta información que se conoce en tiempo de compilación. Si una carga de trabajo se basa en predicados que solo se pueden conocer en tiempo de ejecución, se aumenta la posibilidad de una opción de plan deficiente. Para un plan de mejor calidad, los predicados deben ser SARGable o Search Argumentable.

Algunos ejemplos de predicados que no son SARGable:

• Conversiones implícitas de datos, como VARCHAR a NVARCHAR o INT a VARCHAR. Busque las advertencias de CONVERT_IMPLICIT en tiempo de ejecución en los planes de ejecución reales. La conversión de un tipo a otro también puede provocar una pérdida de precisión.
• Las expresiones complejas indeterminadas como WHERE UnitPrice + 1 < 3.975, pero no WHERE UnitPrice < 320 * 200 * 32.
• Expresiones que usan funciones, como WHERE ABS(ProductID) = 771 o WHERE UPPER(LastName) = 'Smith'.
• Cadenas con un carácter comodín inicial, tales como WHERE LastName LIKE '%Smith', pero no WHERE LastName LIKE 'Smith%'.

Pasos para resolver

1. Declare siempre las variables o los parámetros como el tipo de datos de destino deseado.

• Esto puede implicar la comparación de cualquier construcción de código definido por el usuario que se almacena en la base de datos (por ejemplo, procedimientos almacenados, vistas o funciones definidas por el usuario) con tablas del sistema que contienen información de tipos de datos usados en las tablas subyacentes (como sys.columns).

2. Si no se puede recorrer todo el código hasta el punto anterior, con la misma finalidad, cambie el tipo de datos en la tabla para que coincida con las declaraciones de variable o parámetro.
3. Motivo de la utilidad de las siguientes construcciones:

• Funciones que se usan como predicados;
• Búsquedas con caracteres comodín;
• Expresiones complejas en función de los datos en columnas: evalúe la necesidad de crear columnas calculadas persistentes, que se pueden indexar;

Nota:

Todo lo anterior puede realizarse mediante programación.

Uso de funciones con valores de tabla (múltiples instrucciones frente a insertadas)

Se aplica a: migración de una plataforma externa (por ejemplo, Oracle, DB2, MySQL y Sybase) y SQL Server a SQL Server.

Nota:

En el caso de las migraciones de SQL Server a SQL Server, si este problema existía en la instancia de SQL Server de origen, la migración a una versión más reciente de SQL Server tal cual no solucionará el problema.

Las funciones con valores de tabla devuelven un tipo de datos de tabla que puede ser una alternativa a las vistas. Mientras que las vistas se limitan a una única instrucción SELECT, las funciones definidas por el usuario pueden contener instrucciones adicionales que permiten una lógica más eficaz que en las vistas.

Importante

Puesto que no se ha creado la tabla de salida de una función con valores de tabla de varias instrucciones (MSTVF) en el tiempo de compilación, el optimizador de consultas de SQL Server se basa en la heurística (y no en las estadísticas reales) para determinar las estimaciones de fila. Aunque los índices se agreguen a las tablas base, esto no servirá de ayuda. Para MSTVF, SQL Server usa una estimación fija de 1 para el número de filas que se espera que va a devolver una MSTVF (a partir de SQL Server 2014 [12.x], esa estimación corregida es de 100 filas).

Pasos para resolver

1. Si la TVF de múltiples instrucciones es la única instrucción, conviértala en TVF insertada.

   CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int)
   RETURNS @tblAddress TABLE
   ([Address] VARCHAR(60) NOT NULL)
   AS
   BEGIN
     INSERT INTO @tblAddress ([Address])
     SELECT TOP 1 [AddressLine1]
     FROM [Person].[Address]
     WHERE  AddressID = @ID
     ORDER BY [ModifiedDate] DESC
   RETURN
   END
   

   El ejemplo de formato en línea se muestra a continuación.

   CREATE FUNCTION dbo.tfnGetRecentAddress_inline(@ID int)
   RETURNS TABLE
   AS
   RETURN (
     SELECT TOP 1 [AddressLine1] AS [Address]
     FROM [Person].[Address]
     WHERE  AddressID = @ID
     ORDER BY [ModifiedDate] DESC
   )
   

2. Si es más complejo, considere la opción de usar los resultados intermedios que se almacenan en tablas optimizadas para memoria o tablas temporales.

Lectura adicional

Procedimiento recomendado con el Almacén de consultas
Tablas optimizadas para la memoria
Funciones definidas por el usuario
Table Variables and Row Estimations - Part 1 (Variables de tabla y estimaciones de fila: parte 1)
Table Variables and Row Estimations - Part 2 (Variables de tabla y estimaciones de fila: parte 2)
Almacenar en caché y volver a utilizar un plan de ejecución