Planes de ejecución con SQL Server V, planes de ejecución en XML

Trabajar con los planes de ejecución XML

El almacenamiento de los planes en XML abre varias posibilidades. En primer lugar, es muy fácil hacer una copia de un plan para compartirlo. Esta es la verdadera fuerza de tener a nuestra disposición XML para los planes de ejecución, podemos usar el lenguaje XQuery para ejecutar consultas directamente contra el plan de ejecución y en los planes en la caché.

Obtención de los planes reales y estimados en XML 

Con el fin de convertir a XML el plan estimado se puede activar o desactivar con:

SET SHOWPLAN_XML ON

...

SET SHOWPLAN_XML OFF

El comando SHOWPLAN_XML no es una instrucción esencialmente una vez se activa estos parámetros al ejecutar una sentencia SQL en lugar de ejecutarla recoge información del plan de ejecución en forma de documento XML. Una vez más, es importante poner SHOWPLAN_XML a OFF tan pronto como se haya terminado de recoger la información del plan, para permitir que las instrucciones SQL se ejecuten según lo previsto.

Para ver la versión XML del plan real:

SET ON STATISTICS XML
...
SET OFF STATISTICS XML

Una vez más, vamos a ver el mismo plan de ejecución como se evaluó con el plan de texto.

SET SHOWPLAN_XML ON;
GO
SELECT *
FROM [dbo] [NombreTabla].;
SET SHOWPLAN_XML OFF;
GO

Esta instrucción mostrará algo parecido a esto

Al hacer clic en el enlace se abre el plan de ejecución como un plan gráfico. Para ver el XML directamente, hay que hacer clic derecho sobre el plan gráfico y seleccionar mostrar plan de ejecución XML desde el menú contextual. Esto abre el formato XML en una ventana del navegador. No es necesario leer el XML directamente se trata sólo de una herramienta adicional. Los resultados, incluso para una consulta sencilla, son demasiado grandes. Aparece una gran cantidad de información que se analizará más tarde.

Aparecen en primer lugar los elementos del proceso por lotes y sus declaraciones. En este ejemplo, sólo estamos analizando un solo lote y una sola declaración, por lo que no se muestra nada más. 

<StmtSimple StatementText="SELECT * FROM dbo.NombreTabla;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0033227" StatementEstRows="37" StatementOptmLevel="TRIVIAL">

<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />

<QueryPlan DegreeOfParallelism="1" CachedPlanSize="9" CompileTime="0" CompileCPU="0" CompileMemory="56">

Después de eso, tenemos el elemento RelOp, que proporciona la información con la que estamos familiarizados, en este caso el recorrido de tabla. 

<RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="37" EstimateIO="0.003125" EstimateCPU="0.0001977" AvgRowSize="133" EstimatedTotalSubtreeCost="0.0033227" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">

No sólo hay más información que en los planes de texto, también está más fácilmente disponible y más fácil de leer que en los planes de texto. Es posible llegar a más información rápidamente que en los planes de gráficos (aunque el flujo a través de los planes  gráficos es mucho más fácil de leer). Por ejemplo, una columna problemática, que es difícil de leer es el elemento OutputList con una lista de elementos ColumnReference, cada uno con un conjunto de atributos que describen que columna 

<OutputList>

<ColumnReference Database="[dbPRUEBAS]" Schema="[DBO]" Table="[NombreTabla]" Column="bteAplicacion" />

<ColumnReference Database="[dbPRUEBAS]" Schema="[DBO]" Table="

[NombreTabla]" Column="strParametro" />

<ColumnReference Database="[dbPRUEBAS]" Schema="[DBO]" Table="

[NombreTabla]" Column="strValor" />

</OutputList>

Esto hace que XML no sea más fácil de leer, pero si mucho más fácil de traducir directamente la consulta original. Sabemos que columnas se han seleccionado y de qué tabla, ya que se ha definido en la consulta original. En este caso, esa información está ahora exactamente en el mismo formato.

Después del elemento de referencia anterior RelOp tenemos la exploración de la tabla:

 <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">

Seguido por una lista de valores definidos que establece las columnas referenciadas por la operación.

<DefinedValues>

<DefinedValue>

<ColumnReference Database="[dbPRUEBAS]" Schema="[DBO]" Table="[NombreTabla]" Column="bteAplicacion" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="[dbPRUEBAS]" Schema="[DBO]" Table="[NombreTabla]" Column="strParametro" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="[dbPRUEBAS]" Schema="[DBO]" Table="[NombreTabla]" Column="strValor" />

</DefinedValue>

</DefinedValues>

<Object Database="[dbPRUEBAS]" Schema="[DBO]" Table="[NombreTabla]" Index="[PK_tbNombreTabla]" />

</IndexScan>

Simplemente leyendo el XML puede ser extremadamente difícil tratar  de comprender qué operación viene a continuación en el plan. Sin embargo, se puede ver que la información se almacena en todos los planes de ejecución para que esté disponible para leer o realizar consultas con XQuery. 

Guardar planes XML como planes gráficos

Con la instrucción  SHOWPLAN_XML es posible almacenar el plan sin abrirlo, para ellos basta con hacer clic en los resultados y seleccionar Guardar como y al escribir el nombre del archivo que desea guardar seleccionando la extensión .sqlplan como tipo de archivo. En realidad, lo que se obtiene cuando se salva de esta manera es un archivo del plan de ejecución gráfico. Esta puede ser una característica muy útil. Se pueden recopilar múltiples planes en formato XML, guardarlos en un archivo y luego abrirlos en formato gráfico fácil de ver (y comparar).

Uno de los beneficios de la extracción y almacenamiento de un plan de XML es que podemos compartirlo. 

Recuperando planes de la caché utilizando Objetos de Administración Dinámica

Los Dynamic Management Objects (DMO) u Objetos de Administración Dinámica, son vistas del sistema y funciones que exponen la información interna describiendo lo que SQL Server está haciendo y cómo está funcionando. Uno de los más interesantes DMO para nuestros propósitos es sys.dm_exec_query_plan, que recupera los planes de ejecución desde la caché.

Con DMO y dinámico las funciones de gestión, se pueden ver fácilmente juntos en una única consulta para obtener un conjunto muy completo de información sobre los planes de ejecución en nuestro sistema. La consulta debe ejecutarse sobre la base de datos master.

SELECT [cp].[refcounts] ,

[cp].[usecounts] ,

[cp].[objtype] ,

[st].[dbid] ,

[st].[objectid] ,

[st].[text] ,

[qp].[query_plan]

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp;

En esta consulta aparecen diferentes DMO disponibles. Se ha utilizado, sys.dm_exec_cached_plans, que muestra las propiedades del objeto en la caché del plan, pero no el plan. Luego utiliza la declaración CROSS APPLY para recuperar la consulta de sys.dm_exec_sql_text y el plan de sys.dm_exec_query_plan. CROSS APPLY aplica una función una vez por cada fila del conjunto de resultados. Esta consulta devuelve todas las consultas de la caché como XML.

Estos planes que se almacenan en la memoria caché no contienen información de tiempo de ejecución ni el número real de filas o el número real de  ejecuciones. Son planes estimados.

Esto hace que sean un poco menos útiles que los planes reales pero si se está evaluando el rendimiento y no se pueden los planes reales ni sus valores de tiempo de ejecución. Es posible ver inmediatamente el plan directamente desde memoria caché lo que resulta muy útil.

Automatización de plan de captura usando eventos de traza de SQL Server

Durante el desarrollo, podemos capturar los planes de ejecución de instrucciones SQL específicas, usando las técnicas descritas anteriormente. 

Sin embargo, si estamos solucionando problemas en un servidor en producción, la situación es diferente. Un sistema en producción puede estar sujeto a decenas o cientos de sesiones de ejecución decenas o cientos o consultas, cada una con diferentes conjuntos de parámetros y variando los planes. Para esta situación, necesitamos una forma de automatizar la captura del plan para que poder guardar los planes específicos automáticamente.

En SQL Server, podemos utilizar perfiles para definir una traza de servidor para capturar planes de ejecución en XML. Después podremos examinar la recogida de planes, en busca de las consultas con los costos más altos, o simplemente buscando en los planes para encontrar, por ejemplo, las operaciones de exploración de tabla, que nos gustaría eliminar.

El seguimiento de eventos de SQL Server constituye una poderosa herramienta, lo que nos permite capturar de datos sobre los eventos, tales como la ejecución de transacciones SQL o procedimientos almacenados, que se producen dentro de SQL Server. Podemos ver estos datos en la pantalla o enviarlos a un archivo o a una tabla en una base de datos.

¿Por qué los planes de ejecución reales y estimados pueden ser diferentes?

En general, los planes de ejecución estimados y reales serán los mismos, en función de las operaciones realizadas. Sin embargo, pueden surgir circunstancias que pueden causar diferencias entre los planes de ejecución estimados y reales.

Cuando las estadísticas no están actualizadas

La principal causa de la diferencia entre los planes estimados y reales son las diferencias entre las estadísticas y los datos reales. Esto ocurre generalmente con el tiempo, a medida que los datos son agregados o eliminados. Esto hace que cambien los valores o la distribución de las claves que definen el índice.

La actualización automática de estadísticas se produce, asumiendo que es en  un subconjunto de los datos con el fin de reducir el costo de la operación. Esto significa que, con el tiempo, las estadísticas pueden hacerse cada vez más inexactas. Esto puede causar diferencias entre los planes, y además hacer que los planes de ejecución sean malos ya que los datos estadísticos no están actualizados.

Cuando el plan estimado no es válido 

En algunos casos, el plan estimado no funcionará en absoluto.  El optimizador, que es lo que genera el plan de ejecución estimado, no ejecuta SQL. Sino que se ejecuta a través de las declaraciones del algebrizador, el proceso es responsable de verificar los nombres de objetos de la base de datos.

Cuando se requiere el paralelismo 

Cuando un plan sobrepasa el umbral de paralelismo, el motor de búsqueda puede alterar el plan suministrado por el optimizador, para incluir las operaciones en paralelo, en un intento de aumentar el rendimiento de la consulta por un mayor uso de los procesadores disponibles. Este plan paralelo sólo es visible como un plan de ejecución real. El plan del optimizador no incluye la ejecución en paralelo, por lo que ninguno quedará almacenado en la memoria caché