Migración de planes de consulta

En la mayoría de los casos, actualizar una base de datos a la versión más reciente de SQL Server dará como resultado un rendimiento mejorado de las consultas. Sin embargo, si tiene consultas críticas que se han optimizado cuidadosamente para el rendimiento, es posible que desee conservar los planes de consulta de estas consultas antes de actualizar mediante la creación de una guía de plan para cada consulta. Si, después de actualizar, el optimizador de consultas elige un plan menos eficaz para una o varias de las consultas, puede habilitar las guías de plan y forzar al optimizador de consultas a usar los planes de actualización previa.

Para crear guías de plan antes de actualizar, siga estos pasos:

1. Registre el plan actual para cada consulta crítica mediante el procedimiento almacenado sp_create_plan_guide y especifique el plan de consulta en la sugerencia de consulta USE PLAN.

2. Compruebe que la guía de plan se aplica a la consulta.

3. Actualice la base de datos a la versión más reciente de SQL Server.

   Los planes se conservan en la base de datos actualizada en las guías de los planes y sirven como copia de seguridad en caso de regresiones de plan posterior a la actualización.

   Se recomienda no habilitar las guías de plan después de la actualización, ya que es posible que pierda oportunidades para mejores planes en la nueva versión o recompiles beneficiosos debido a las estadísticas actualizadas.

4. Si se eligen planes menos eficaces después de la actualización, active todos o un subconjunto de las guías de plan para invalidar los nuevos planes.

Ejemplo

En el ejemplo siguiente se muestra cómo registrar un plan de actualización previa para una consulta mediante la creación de una guía de plan.

Paso 1: Recopilar el plan

El plan de consulta registrado en la guía de plan debe estar en formato XML. Los planes de consulta con formato XML se pueden generar de las siguientes maneras:

• SET SHOWPLAN_XML

• SET STATISTICS XML

• Consulta de la columna query_plan de la función de administración dinámica sys.dm_exec_query_plan .

• Las clases de eventos del SQL Server Profiler, Showplan XML, Showplan XML Statistics Profile y Showplan XML For Query Compile.

En el ejemplo siguiente se recopila el plan de consulta de la instrucción SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; consultando vistas dinámicas de gestión.

USE AdventureWorks;  
GO  
SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%';  
GO  

Paso 2: Crear la Guía del Plan para imponer el plan

Con el plan de consulta con formato XML (obtenido por cualquiera de los métodos descritos anteriormente) dentro de la guía de planes, copie y pegue el plan de consulta como una cadena literal dentro del indicador de consulta USE PLAN especificado en la cláusula OPTION de sp_create_plan_guide.

Dentro del propio plan XML, las comillas de escape (') que aparecen en el plan con una segunda comilla antes de crear la guía de plan. Por ejemplo, un plan que contiene WHERE A.varchar = 'This is a string' debe escaparse modificando el código a WHERE A.varchar = ''This is a string''.

En el ejemplo siguiente se crea una guía de plan para el plan de consulta recopilado en el paso 1 e inserta el plan de presentación XML para la consulta en el @hints parámetro . Para mayor brevedad, solo se incluye la salida parcial del Showplan en el ejemplo.

EXECUTE sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',  
@type = N'SQL',  
@module_or_batch = NULL,  
@params = NULL,  
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan''''   
    Version=''''0.5'''' Build=''''9.00.1116''''>  
    <BatchSequence><Batch><Statements><StmtSimple>  
    ...  
    </StmtSimple></Statements></Batch>  
    </BatchSequence></ShowPlanXML>'')';  
GO  

Paso 3: Comprobar que la guía de plan se aplica a la consulta

Vuelva a ejecutar la consulta y examine el plan de consulta que se genera. Debería ver que el plan coincide con el que especificó en la guía del plan.

Véase también

sp_create_plan_guide (Transact-SQL)
Sugerencias de consulta (Transact-SQL)
Guías de planes