Descripción de las sugerencias de consulta

Las sugerencias de consulta son opciones o estrategias que se pueden aplicar para que el procesador de consultas use un operador determinado en el plan de ejecución para las instrucciones SELECT, INSERT, UPDATE o DELETE. Las sugerencias de consulta invalidan cualquier plan de ejecución que el procesador de consultas pueda seleccionar para una consulta determinada con la OPTION cláusula .

En la mayoría de los casos, el optimizador de consultas selecciona un plan de ejecución eficaz basado en los índices, estadísticas y distribución de datos. Los administradores de bases de datos rara vez necesitan intervenir manualmente.

Puede cambiar el plan de ejecución de la consulta agregando sugerencias de consulta al final de la consulta. Por ejemplo, si agrega OPTION (MAXDOP <integer_value>) al final de una consulta que usa una sola CPU, la consulta puede usar varias CPU (paralelismo) en función del valor que elija. O bien, puede usar OPTION (RECOMPILE) para asegurarse de que la consulta genera un nuevo plan temporal cada vez que se ejecuta.

--With maxdop hint
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2)
GO

--With recompile hint
SELECT City
FROM Person.Address
WHERE StateProvinceID=15 OPTION (RECOMPILE)
GO

Aunque las sugerencias de consulta pueden proporcionar una solución localizada a varios problemas relacionados con el rendimiento, debe evitar su uso en el entorno de producción por los siguientes motivos.

• Tener una sugerencia de consulta permanente en la consulta puede dar lugar a cambios estructurales en la base de datos que serían beneficiosos para esa consulta que no son aplicables.
• No puede beneficiarse de características nuevas y mejoradas en versiones posteriores de SQL Server si enlaza una consulta a un plan de ejecución específico.

Sin embargo, hay varias sugerencias de consulta disponibles en SQL Server, que se usan con fines diferentes. Vamos a analizar algunos de ellos a continuación:

• FAST <integer_value>: recupera el primer <integer_value> número de filas mientras continúa la ejecución de la consulta. Funciona mejor con conjuntos de datos pequeños y un valor bajo para la sugerencia de consulta rápida. A medida que aumenta el recuento de filas, el costo de las consultas aumenta.

• OPTIMIZE FOR: proporciona instrucciones al optimizador de consultas que se debe usar un valor determinado para una variable local cuando se compila y optimiza una consulta.

• USE PLAN: el optimizador de consultas usa un plan de consulta especificado por el atributo xml_plan .

• RECOMPILE: crea un nuevo plan temporal para la consulta y lo descarta inmediatamente después de ejecutar la consulta.

• { LOOP | MERGE | HASH } JOIN: especifica que todas las operaciones de combinación se realizan mediante LOOP JOIN, MERGE JOINo HASH JOIN en toda la consulta. El optimizador elige la estrategia de combinación menos costosa entre las opciones si especifica más de una sugerencia de combinación.

• MAXDOP <integer_value>: invalida el valor máximo de grado de paralelismo de sp_configure. La consulta que especifica esta opción también invalida el Resource Governor.

También puede aplicar varias sugerencias de consulta en la misma consulta. En el ejemplo siguiente se usan las sugerencias de consulta HASH GROUP y FAST <integer_value> en la misma consulta.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (HASH GROUP, FAST 10);  
GO    

Para obtener más información sobre las sugerencias de consulta, consulte Sugerencias (Transact-SQL).

Sugerencias de Query Store

Las sugerencias del Almacén de consultas proporcionan un método sencillo para dar forma a los planes de consulta sin modificar el código de la aplicación.

Las sugerencias del Almacén de consultas son útiles cuando el optimizador de consultas no genera un plan de ejecución eficaz y cuando el desarrollador o DBA no pueden modificar el texto de la consulta original. En algunas aplicaciones, el texto de la consulta se puede codificar o generar automáticamente.

Para usar sugerencias del Almacén de Consultas, debe identificar el query_id de la instrucción de consulta que desea modificar a través de vistas de catálogo del Almacén de Consultas, informes integrados del Almacén de Consultas o Información de Rendimiento de Consultas para Azure SQL Database. Después, ejecute sp_query_store_set_hints con query_id y la cadena de sugerencia de consulta que quiere aplicar a la consulta.

En el ejemplo siguiente se muestra cómo obtener el query_id para una consulta específica y luego usarlo para aplicar las indicaciones RECOMPILE y MAXDOP a la consulta.

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
    INNER JOIN sys.query_store_query q 
        ON qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY CustomerName DESC%'  
  AND query_sql_text not like N'%query_store%'
GO

--Assuming the query_id returned by the previous query is 42
EXEC sys.sp_query_store_set_hints @query_id= 42, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1)'
GO

Hay algunos escenarios en los que las sugerencias de Almacén de consultas pueden ayudar con problemas de rendimiento de nivel de consulta.

• Volver a compilar una consulta en cada ejecución.
• Limite el grado máximo de paralelismo para una operación de actualización estadística.
• Use una combinación hash en lugar de una combinación de bucles anidados.
• Use el nivel de compatibilidad 110 para una consulta específica mientras mantiene la base de datos en la compatibilidad actual.

Para obtener más información sobre las sugerencias del Almacén de consultas, consulte Sugerencias del Almacén de consultas.