Diseñar e implementar guías de plan

Actualizado: 15 de septiembre de 2007

Puede utilizar las guías de plan para optimizar el rendimiento de las consultas si no puede o no desea cambiar directamente el texto de la consulta. Se pueden crear guías de plan para comparar las consultas que se ejecutan en los contextos siguientes:

• Las guías de plan OBJECT comparan las consultas que se ejecutan en el contexto de procedimientos almacenados de Transact-SQL, funciones escalares, funciones con valores de tabla de múltiples instrucciones y desencadenadores DML.
• Las guías de plan de SQL comparan 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.
• Las guías de plan TEMPLATE comparan consultas independientes que se parametrizan en un formulario especificado. Estas guías de plan se utilizan para reemplazar la opción PARAMETERIZATION actual de una base de datos para una clase de consultas por medio de SET.

[!NOTA] Aunque las guías de plan sólo se pueden usar en las versiones Standard, Developer, Evaluation y Enterprise de SQL Server 2005 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 2005. Después de realizar una actualización de servidor, debe comprobar la idoneidad de las guías de plan en cada base de datos.

En el caso de las guías de plan basadas en SQL o TEMPLATE que especifican @type = N'SQL' o @type = N'TEMPLATE' en la instrucción sp_create_plan_guide, SQL Server busca la coincidencia de los valores de los argumentos @module_or_batch y @params con una consulta comparando ambos valores carácter por carácter. Esto significa que se debe proporcionar el texto exactamente como lo recibe SQL Server en el lote real. Para capturar el texto del lote real, puede utilizar el Analizador de SQL Server. Por lo general, debe probar las guías de plan mediante el Analizador de SQL Server 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 el Analizador de SQL Server 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.

Si @type = 'SQL' y @module\_or\_batch se establece en NULL, el valor de @module\_or\_batch se establece en el valor de @stmt. Esto significa que debe suministrarse el valor de statement_text con el mismo formato exactamente, carácter a carácter, en que se envía a SQL Server. Para facilitar esta concordancia no se realiza ninguna conversión interna.

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 AdventureWorks es la base de datos actual y se ejecuta la consulta siguiente:

SELECT * FROM Person.Contact

Sólo las guías de plan de la base de datos AdventureWorks serán aptas para buscar las coincidencias con esta consulta.

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

USE DB1;
GO
SELECT * FROM Person.Contact;

Sólo 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 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 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 de la misma que debe coincidir con 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.

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 no se deben utilizar demasiado a menudo para tratar sólo consultas individuales 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. En concreto, las guías de plan que aplican la sugerencia de consulta USE PLAN aplican un plan fijo para la consulta en cuestión. Por tanto, el optimizador de consultas ya no puede adaptar el plan de la consulta a los cambios de estadísticas e índices.

En el caso de las guías de plan que utilizan la consulta USE PLAN, 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.

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.

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 ese 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.

Para crear una guía de plan

• sp_create_plan_guide (Transact-SQL)

Para deshabilitar, volver a habilitar o quitar guías de plan

• sp_control_plan_guide (Transact-SQL)

Para obtener información acerca de las guías de plan de la base de datos actual

• sys.plan_guides

Vea también

Conceptos

Optimizar consultas en aplicaciones implementadas mediante guías de plan

Otros recursos

Rendimiento de las consultas

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios