Migrar los planes de consulta

Artículo
02/18/2014

En la mayoría de los casos, al actualizar una base de datos a la versión más reciente de SQL Server se obtendrá una mejora del rendimiento de las consultas. No obstante, si tiene consultas de gran importancia que se han optimizado cuidadosamente con el fin de obtener el máximo rendimiento, es probable que desee conservar 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á habilitar 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 se aplica a la consulta.
Actualice la base de datos a la versión más reciente de SQL Server.

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

Recomendamos no habilitar 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 escogen planes menos eficientes, active todas las guías de plan o bien solo un subconjunto de ellas con el fin de invalidar 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 consulta 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 Compile de SQL Server Profiler.

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 descritos anteriormente) de 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 crear 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 solo 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

Paso 3: comprobar que la guía de plan corresponde a la consulta

Vuelva a ejecutar la consulta y examine el plan de consulta generado. Observará que el plan coincide con el plan especificado en la guía de plan.

Vea también

Referencia

sp_create_plan_guide (Transact-SQL)

Sugerencias de consulta (Transact-SQL)

Conceptos

Guías de plan

Share via