Share via

Recomendaciones para optimizar consultas

  • Artículo

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 SQL Server Database Engine (Motor de base de datos) utilice 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 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 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 (límite de costo del regulador de consultas) y SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).

  • Optimice la reutilización de los planes de consulta desde la caché del plan. El SQL Server Database Engine (Motor de base de datos) pone en la caché planes de consulta para su 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.

Vea también

Conceptos

Aspectos básicos de las subconsultas
Componentes de GROUP BY

Otros recursos

CASE (Transact-SQL)

Ayuda e información

Obtener ayuda sobre SQL Server 2005