Artículo
03/30/2012

Diseñar e implementar guías de plan

Puede utilizar las guías de plan para optimizar el rendimiento de las consultas cuando no pueda o no desee cambiar directamente el texto de la consulta. Las guías de plan influyen en la optimización de las consultas adjuntando sugerencias de consulta o un plan de consulta fijo para ellas. Se pueden crear guías de plan para comparar las consultas que se ejecutan en los contextos siguientes:

Una guía de plan OBJECT compara las consultas que se ejecutan en el contexto de procedimientos almacenados de Transact-SQL, funciones escalares definidas por el usuario, funciones definidas por el usuario con valores de tabla de múltiples instrucciones y desencadenadores DML.
Una guía de plan de SQL compara las consultas que se ejecutan en el contexto de instrucciones independientes de Transact-SQL y lotes que no forman parte de un objeto de base de datos. Las guías de plan basadas en SQL también se pueden utilizar para comparar consultas que se parametrizan en un formulario especificado.
Una guía de plan TEMPLATE compara consultas independientes que se parametrizan en un formulario especificado. Estas guías de plan se utilizan para invalidar la opción PARAMETERIZATION actual de una base de datos para una clase de consultas por medio de SET.

Para obtener más información, vea Descripción de las guías de plan.

El número total de guías de plan que se pueden crear sólo está limitado por los recursos de los que disponga el sistema. No obstante, las guías de plan deberían limitarse a aquellas consultas de gran importancia cuyo rendimiento se desea mejorar o estabilizar. No se deben utilizar las guías de plan para influenciar la mayor parte de la carga de la consulta de una aplicación implementada.

Se recomienda volver a evaluar y probar las definiciones de guías de plan al actualizar la aplicación a una nueva versión de SQL Server. Los requisitos de optimización del rendimiento y el comportamiento de la coincidencia de las guías de plan pueden cambiar. Aunque una guía de plan no válida no hará que una consulta provoque un error, el plan se compilada sin utilizar la guía de plan. Después de actualizar una base de datos, es recomendable que realice las siguientes tareas para validar las guías de plan existentes utilizando la función sys.fn_validate_plan_guide. Otra opción es encontrar las guías de plan no válidas buscando el evento Guía de plan incorrecta en SQL Server Profiler.

Nota

Aunque las guías de plan solo se pueden utilizar en las versiones Standard, Developer, Evaluation y Enterprise de SQL Server sí se pueden ver en todas las versiones. También se pueden adjuntar bases de datos que incluyen guías de plan a cualquier versión. Las guías de plan permanecen intactas cuando se restaura o adjunta una base de datos a una versión actualizada de SQL Server.

Asociar sugerencias de consulta a una guía de plan

En una guía de plan se puede utilizar cualquier combinación de sugerencias de consulta válidas. Cuando una guía de plan coincide con una consulta, la cláusula OPTION especificada en la cláusula de sugerencia de una guía de plan se agrega a la consulta antes de la compilación y optimización. Si una consulta que coincide con una guía de plan ya tiene una cláusula OPTION, las sugerencias de consulta especificadas en la guía de plan sustituirán a las de la consulta. Sin embargo, para que una guía de plan coincida con una consulta que ya tiene una cláusula OPTION, debe incluir esta cláusula de la consulta al especificar el texto con el que debe coincidir la instrucción sp_create_plan_guide. Si desea que las sugerencias especificadas en la guía de plan se agreguen a las que ya existen en la consulta, en lugar de sustituirlas, debe especificar tanto las originales como las adicionales en la cláusula OPTION de la guía de plan.

Advertencia
Las guías de plan que usan incorrectamente las sugerencias de consulta pueden provocar problemas de compilación, ejecución o rendimiento. Sólo deben utilizarlas los programadores y administradores de bases de datos con experiencia.

Sugerencias de consulta comúnmente utilizadas en las guías de plan

Las consultas que pueden aprovechar las guías de plan suelen estar basadas en parámetros, y puede que su rendimiento sea bajo porque utilizan planes de consulta en caché cuyos valores de parámetros no representan un caso del tipo "el peor de los casos" o el más representativo. Este problema se puede solucionar con las sugerencias de consulta OPTIMIZE FOR y RECOMPILE. OPTIMIZE FOR indica a SQL Server que utilice un valor determinado para un parámetro cuando se optimiza la consulta. RECOMPILE indica al servidor que descarte un plan de consulta tras la ejecución, con lo que se forzará al optimizador de consultas a volver a compilar un nuevo plan de consulta la próxima vez que se ejecute la misma consulta. Para obtener un ejemplo, vea Descripción de las guías de plan.

Por otro lado, puede especificar las sugerencias de tabla INDEX y FORCESEEK como sugerencias de consulta. Cuando se especifican como sugerencias de consulta, estas sugerencias se comportan de la misma manera que una tabla insertada o una sugerencia de vista. La sugerencia INDEX obliga al optimizador de consultas a usar sólo los índices especificados para tener acceso a los datos a los que se hace referencia en la tabla o vista. La sugerencia FORCESEEK obliga al optimizador de consultas a usar sólo una operación Index Seek para tener acceso a los datos a los que se hace referencia en la tabla o en la vista. Estas sugerencias proporcionan la funcionalidad de la guía de plan adicional y permiten para tener más influencia sobre la optimización de consultas que utilizan la guía de plan. Para obtener un ejemplo, vea Utilizar las sugerencias de consulta FORCESEEK e INDEX en guías de plan.

Asociar un plan de consultas a una guía de plan

Las guías de plan que se aplican a un plan de consultas fijo resultan útiles cuando ya se tiene constancia de un plan de ejecución existente cuyo rendimiento es mejor que el del seleccionado por el optimizador para una consulta determinada. Observe que aplicando un plan fijo a una consulta significa que el optimizador de consultas ya no puede adaptar el plan para la consulta a los cambios en las estadísticas y en los índices. En el caso de las guías de plan que utilizan planes de consultas fijos, asegúrese de comparar las ventajas de aplicar un plan fijo con la incapacidad de adaptar el plan automáticamente a medida que cambian la distribución de datos y los índices disponibles.

Puede asociar un plan de consultas concreto a una guía de plan especificando el plan de presentación XML del plan en el parámetro xml_showplan de la instrucción sp_create_plan_guide o especificando el identificador de un plan almacenado en caché de la instrucción sp_create_plan_guide_from_handle. Ambos métodos aplican el plan de consultas fijo a la consulta seleccionada.

Requisitos de coincidencia de la guía de plan

Las guías de plan tienen como ámbito la base de datos en la que se crean. Por tanto, sólo se pueden buscar las coincidencias con la consulta de las guías de plan que existen en la base de datos actual cuando se ejecuta una consulta. Por ejemplo, si AdventureWorks2008R2 es la base de datos actual y se ejecuta la consulta siguiente:

SELECT FirstName, LastName FROM Person.Person;

Solo las guías de plan de la base de datos AdventureWorks2008R2 serán aptas para buscar las coincidencias con esta consulta.

No obstante, si la base de datos actual es AdventureWorks2008R2 y se ejecutan las instrucciones siguientes:

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

Solo las guías de plan de DB1 serán aptas para buscar las coincidencias con la consulta, puesto que la consulta se ejecuta en el contexto de DB1.

En el caso de las guías de plan basadas en SQL o TEMPLATE, SQL Server examina los valores para los argumentos @ module_or_batch y @params con una consulta, comparando ambos valores carácter a carácter. Esto significa que se debe proporcionar el texto exactamente como lo recibe SQL Server en el lote real.

Si se especifica @type = 'SQL' y @module_or_batch es NULL, se establecerá el valor de @module_or_batch en el valor de @stmt. Esto significa que se debe proporcionar el valor de statement_text debe suministrarse en el mismo formato, carácter a carácter, en que se envía a SQL Server. Para facilitar esta concordancia no se realiza ninguna conversión interna.

Por lo general, debe probar las guías de plan mediante el SQL Server Profiler para comprobar que se buscan las coincidencias de la consulta con la guía de plan. La realización de pruebas de guías de plan basadas en SQL o TEMPLATE mediante la ejecución de lotes de SQL Server Management Studio puede provocar resultados inesperados. Para obtener más información, vea Usar SQL Server Profiler para crear y probar guías de plan.

Nota

El lote que contiene la instrucción en la que desea crear una guía de plan no puede contener una instrucción USE database.

Efecto de la guía de plan en la caché del plan

Al crear una guía de plan en un módulo, se quita el plan de consultas para dicho módulo de la caché del plan. Al crear una guía de plan de tipo OBJECT o SQL en un lote, se quita el plan de consultas para un lote que tiene el mismo valor hash. Al crear una guía de plan de tipo TEMPLATE, se quitan todos los lotes de instrucción única de la caché del plan dentro de esa base de datos.