• Artículo

Recomendaciones para optimizar consultas

Algunas consultas consumen más recursos que otras. Por ejemplo, las consultas que devuelven grandes conjuntos de resultados y las que contienen cláusulas WHERE que no son únicas siempre consumen muchos recursos. Ningún grado de inteligencia del optimizador de consultas puede eliminar el costo de recursos de estas construcciones en comparación con una consulta menos compleja. SQL Server utiliza un plan de acceso óptimo, pero la optimización de consultas está limitada por lo que es posible.

Sin embargo, para mejorar el rendimiento de las consultas, puede:

  • Agregar más memoria. Esta solución es especialmente útil si el servidor ejecuta muchas consultas complejas y varias consultas se ejecutan lentamente.

  • Utilizar más de un procesador. Varios procesadores permiten que el Motor de base de datos use consultas en paralelo. Para obtener más información, vea Procesar una consulta en paralelo.

  • Vuelva a escribir la consulta. Considere lo siguiente:

    • Si la consulta utiliza cursores, determine si se puede escribir la consulta de cursor con un tipo de cursor más eficaz (como un cursor de sólo avance rápido) o con una única consulta. Las consultas únicas normalmente mejoran las operaciones de cursor. Debido a que un conjunto de instrucciones de cursor suele constituir una operación de bucle externo, en la que cada fila del bucle externo se procesa una vez con una instrucción interna, considere la posibilidad de utilizar en su lugar una instrucción GROUP BY o CASE, o una subconsulta. Para obtener más información, vea Tipos de cursores (motor de base de datos) y Aspectos básicos de las consultas.

    • Si una aplicación utiliza un bucle, considere la posibilidad de colocar el bucle en la consulta. A menudo, una aplicación contendrá un bucle que, a su vez, contendrá una consulta con parámetros que se ejecuta muchas veces y será necesario realizar un viaje de ida y vuelta en la red entre el equipo que ejecuta la aplicación y SQL Server. En su lugar, cree una sola consulta más compleja con una tabla temporal. Sólo necesita un viaje de ida y vuelta en la red, y el optimizador de consultas puede optimizar mejor la consulta única. Para obtener más información, vea Procedimientos de Transact-SQL y Variables de Transact-SQL.

    • No utilice varios alias para una sola tabla en la misma consulta para simular la intersección de índices. Ya no es necesario debido a que SQL Server tiene en cuenta automáticamente la intersección de índices y puede utilizar varios ííndices en la misma tabla de la misma consulta. Observe el ejemplo de consulta:

      SELECT * FROM lineitem 
WHERE partkey BETWEEN 17000 AND 17100 AND
    shipdate BETWEEN '1/1/1994' AND '1/31/1994'

      SQL Server puede utilizar ííndices sobre las columnas partkey y shipdate, y después realizar una coincidencia hash entre los dos subconjuntos para obtener la intersección de ííndices.

    • Utilice la parametrización de consultas para permitir la reutilización de los planes de ejecución de consulta almacenados en la memoria caché. Si un conjunto de consultas tiene el mismo hash de consulta y hash de plan de consulta, podría mejorar el rendimiento creando una consulta parametrizada. Llamar a una consulta con parámetros en lugar de a varias consultas con valores literales permite reutilizar el plan de ejecución de consulta almacenado en la memoria caché. Para obtener más información, vea Buscar y optimizar consultas similares utilizando hash del plan de consulta y de consulta y Almacenar en caché y volver a utilizar un plan de ejecución.

      Si no puede modificar la aplicación, puede utilizar las guías de plan de la plantilla con parametrización forzada para lograr un resultado similar. Para obtener más información, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan.

    • Utilice sugerencias de consultas sólo si es necesario. Las consultas que utilizan sugerencias ejecutadas en versiones anteriores de SQL Server deben probarse sin las sugerencias especificadas. Las sugerencias pueden impedir que el optimizador de consultas seleccione un plan de ejecución mejor. Para obtener más información, vea SELECT (Transact-SQL).

  • Utilice query_plan_hash para capturar, almacenar y comparar los planes de ejecución de consulta de las consultas a lo largo del tiempo. Por ejemplo, después de cambiar la configuración del sistema, puede comparar los valores hash del plan de consulta de las consultas esenciales con sus valores hash de plan de consulta originales. Las diferencias en los valores pueden indicarle si el cambio de la configuración del sistema produjo planes de ejecución de consulta actualizados para las consultas importantes. También podría decidir detener la ejecución de una consulta de larga duración si su hash de plan de consulta en sys.dm_exec_requests difiere de su hash de plan de consulta de línea base, que se sabe que tiene un buen rendimiento. Para obtener más información, vea Buscar y optimizar consultas similares utilizando hash del plan de consulta y de consulta.

  • Utilice la opción de configuración query governor (regulador de consultas). Puede utilizar la opción de configuración query governor para impedir que se consuman recursos del sistema al ejecutar consultas de larga duración. De forma predeterminada, la opción se establece para permitir que se ejecuten todas las consultas, sin importar su duración. Sin embargo, se puede establecer el regulador de consultas en el número máximo de segundos que está permitido ejecutar todas las consultas de todas las conexiones o sólo las consultas de una conexión específica. Debido a que el regulador de consultas se basa en el costo estimado de las consultas en lugar de en el tiempo real transcurrido, no tiene sobrecarga de tiempo de ejecución. También detiene las consultas de larga duración antes de que comiencen, en lugar de ejecutarlas hasta que se alcance el límite definido previamente. Para obtener más información, vea query governor cost limit, opción y SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).

  • Optimice la reutilización de los planes de consultas de la caché del plan. Motor de base de datos almacena en memoria caché los planes de consultas para una posible reutilización. Si un plan de consulta no se pone en la caché, nunca podrá reutilizarse. En su lugar, los planes de consulta que no están en la caché deben compilarse cada vez que se ejecutan, lo que produce un bajo rendimiento. Las siguientes opciones de la instrucción Transact-SQL SET impiden que los planes de consulta que están en la caché se reutilicen. Un lote de Transact-SQL que contenga estas opciones SET activadas no puede compartir sus planes de consulta con el mismo lote que se compiló cuando estas opciones SET estaban desactivadas:

    SET ANSI_NULL_DFLT_OFF

    SET ANSI_NULL_DFLT_ON

    SET ANSI_NULLS

    SET ANSI_PADDING

    SET ANSI_WARNINGS

    SET ARITHABORT

    SET CONCAT_NULL_YIELDS_NULL

    SET DATEFIRST

    SET DATEFORMAT

    SET FORCEPLAN

    SET LANGUAGE

    SET NO_BROWSETABLE

    SET NUMERIC_ROUNDABORT

    SET QUOTED_IDENTIFIER

    SET TEXTSIZE

     

    Además, la opción SET ANSI_DEFAULTS afecta a la reutilización de los planes de consulta en caché porque puede usarse para cambiar las opciones ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS y QUOTED_IDENTIFIER SET. Tenga en cuenta que la mayoría de las opciones SET que pueden cambiarse mediante SET ANSI_DEFAULTS se enumeran como opciones SET que pueden afectar a la reutilización de los planes de consulta.

    Puede cambiar algunas de estas opciones SET con los siguientes métodos:

Nota

Para evitar recompilaciones de los planes de consulta causadas por las opciones SET, establezca las opciones SET al conectarse y asegúrese de que no cambian mientras dura la conexión. Algunas opciones SET deben establecerse en valores específicos para usar vistas indizadas o ííndices en columnas calculadas. Para obtener más información, vea Opciones SET que afectan a los resultados.