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

Se aplica a: SQL Server

La fase posterior a la migración en SQL Server es fundamental para reconciliar cualquier problema de precisión e integridad de los datos, así como para revelar problemas de rendimiento con la carga de trabajo.

Escenarios de rendimiento comunes

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 (CE)

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 versiones posteriores, y al actualizar el nivel de compatibilidad de la base de datos a la más reciente disponible, una carga de trabajo podría quedar expuesta al riesgo de regresión del rendimiento.

Esto se debe 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.

Para obtener más información sobre la CE, consulte Estimación de cardinalidad (SQL Server).

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 artículo, 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.

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 parametrizan. 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 no usa 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 artículo, consulte Confidencialidad de los 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>.

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 incorrectos o ausentes provocan E/S adicionales que llevan a que la memoria adicional y la CPU se desperdicien. Esto puede ser porque ha cambiado el perfil de carga de trabajo, como al usar predicados diferentes, con lo que se invalida 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. El uso del plan de ejecución gráfico para cualquier referencia de índice ausente.
2. Sugerencias de indexación generadas por el Asistente para la optimización de motor de base de datos.
3. Utilice sys.dm_db_missing_index_details o a través del panel de rendimiento de SQL Server.
4. Utilice 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.

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.

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 (Transact-SQL)).

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;

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.

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.

Pasos para resolver

1. Si MSTVF es solo una instrucción, conviértala en una función con valores de tabla 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.

Contenido relacionado

• 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