Migración de planes de consulta

En la mayoría de los casos, al actualizar una base de datos a SQL Server 2008 se obtendrá una mejora del rendimiento. No obstante, si tiene consultas de misión crítica que han sido cuidadosamente afinadas con el fin de obtener el máximo rendimiento, es probable que desee preservar los planes de consulta de dichas consultas antes de llevar a cabo la actualización mediante la creación de una guía de plan para cada una de ellas. Si tras la actualización, el optimizador de consultas elige un plan menos eficiente para una o varias de las consultas, podrá activar las guías de plan y obligar al optimizador de consultas a utilizar los planes previos a la actualización.

Siga estos pasos para crear planes de guía antes de llevar a cabo la actualización:

Registre el plan actual correspondiente a cada consulta de misión crítica utilizando el procedimiento almacenado sp_create_plan_guide y especificando el plan de consulta en la sugerencia de consulta USE PLAN.
Compruebe que la guía de plan corresponde a la consulta.
Actualice la base de datos a SQL Server 2008.

Los planes se conservan en las guías de planes residentes en la base en datos actualizada y hacen las veces de reserva en caso de regresión de los planes tras la actualización.

Recomendamos no activar las guías de plan tras la actualización, ya que es posible que se pierdan oportunidades de conseguir mejores planes en la nueva versión o recompilaciones beneficiosas gracias a las estadísticas actualizadas.
Si tras la actualización se escojen planes menos eficientes, active todas las guías de plan o bien sólo un subconjunto de ellas con el fin de reemplazar a los nuevos planes.

Ejemplo

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

Paso 1: recopilar el plan

El plan de consultas registrado en la guía de plan debe estar en formato XML. Es posible generar planes de consulta en formato XML de las siguientes formas:

SET SHOWPLAN_XML
SET STATISTICS XML
Consultando la columna query_plan de la función de administración dinámica sys.dm_exec_query_plan.
Las clases de eventos Showplan XML, Showplan XML Statistics Profile y Showplan XML For Query Compilede SQL Server Profiler.

Para obtener más información acerca de la forma de generar y analizar planes de consulta, vea Analizar una consulta.

En el siguiente ejemplo se recopila el plan de consulta para la instrucción SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; mediante la consulta a vistas de administración dinámica.

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 de plan para imponer el plan

Utilizando el plan de consulta con formato XML (obtenido mediante cualquiera de los métodos explicados) en la guía de plan, copie y pegue el plan de consulta como un literal de cadena en la sugerencia de consulta USE PLAN especificada en la cláusula OPTION de sp_create_plan_guide.

Dentro del propio plan XML, escape las comillas (') que aparezcan en el plan antes de utilizar la guía de plan, insertando para ello unas segundas comillas. Por ejemplo, a un plan que contiene WHERE A.varchar = 'This is a string' habrá que agregarle unas segundas comillas para que el código quede 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, y se inserta el XML Showplan par la consulta en el parámetro @hints. Por razones de brevedad, el ejemplo sólo incluye resultados parciales de Showplan.

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