Análisis plan de Ejecución de query en Sql Server


El Plan de ejecución nos muestra como se ejecuta una consulta en la base de datos; de su análisis podemos extraer conclusiones de cara a optimizar su ejecución.
El optimizador de consultas se encarga de generar el plan de ejecución en función de estadísticas de indicies y columnas. Este plan se almacena en cache para su uso ejecuciones repetidas. Es recomendable usar procedimientos almacenados o consultas parametrizadas para evitar el tiempo dedicado por el optimizador de consultas ;-).

Obtención del Plan de Ejecución

Podemos obtener dos planes de ejecución:

  • Estimado: Es el generado por el optimizador de consultas.
  • Actual: Es el obtenido por la ejecución real de la consulta (variaría en cada ejecución).

En Microsoft SQL Server Management Studio (SSMS) podemos activar el botón para mostrar el plan de ejecución estimado/actual y ejecutar la consulta con OPTION (RECOMPILE) que evita el uso de un plan de ejecución de cache.

Botón para activar la visualización del plan de Ejecución

Botón para activar la visualización del plan de Ejecución


En el menú de contexto del plan, tenemos la opción guardado en un fichero XML de extensión .sqlplan; que puede ser posteriormente cargado.

DetailNodeComo Interpretarlo

  • Se muestra un árbol con Nodos.
  • Cada Nodo se indica el tipo de procesado y un porcentaje que representa el tiempo del procesado con respecto al total de la consulta.
  • El grosor del conector de los Nodos indica el volumen de datos procesados.
  • Para cada Nodo y conector del árbol (pulsando sobre el ítem) podemos ver el detalle de recursos utilizados/requeridos. Adicionalmente podemos ver el detalle avanzado seleccionando propiedades del menú contextual.

Tipo de Procesado

Se pueden agrupar en diferentes categóricas:

  • Acceso a Tablas

    Describiremos los dos tipos de indices:

    • El indice clustered se almacena con los datos de la tabla en ese mismo orden; solo puede haber uno por tabla. Debido a esto, no es muy recomendable usarlo si hay frecuentes actualizaciones de la clave del indice ;-).
    • El indice noclustered se almacena un puntero a los datos de la tabla (RID).

    Tenemos las siguientes operativas de acceso a tablas en función del tipo de indice usado:

    • Clustered Index Scan: Lectura de toda la tabla a partir de un indice clustered; en las propiedades del Nodo podemos ver el indice usado. Puede indicarnos que quizás estamos retornado más registros de los necesarios; quizás podríamos filtrar con WHERE.
    • Clustered Index Seek: Lectura de uno o varios registros de la tabla usando el indice clustered. Muy eficiente ;-).
    • NonClustered Index Seek: Lectura de uno o varias claves (la clave clustered o RID cuando no existe un indice clustered en la tabla) a registros de la tabla usando un indice no clustered. En este caso el indice no contiene el registro, solo la tupla: valor del indice y la clave. Es eficiente.
    • Key Lookup o RID Lookup: Se combina normalmente con el anterior para incorporar los registros (columnas adicionales de información) de la tabla. Para el retorno de datasets grandes, es una operación a evitar. En determinadas ocasiones podríamos incrementar la eficiencia incluyendo en el indice columnas extra (INCLUDE) de esta manera evitaríamos este tipo de proceso. También limitar el número de columnas en el SELECT (por ejemplo, no usar * solo retornar las columnas que necesitemos).
    • Table Scan: Lectura de toda la tabla. A excepción que la tabla contenga pocos registros, indica que no hay indices útiles en la tabla.
  • Joins de tablas
    • Hash Match join: Pone la información del dataset más pequeño en una tabla hash temporal y procesa el dataset mayor para obtener el registro hash asociado. Cuando uno de los datasets es pequeño es eficiente. En otro caso, otros tipos de procesado son más eficientes; debemos intentar que se puedan aplicar ;-). Indica la falta de un indice, WHERE o un WHERE no optimizable por llamadas a funciones, operaciones aritméticas o una condición nonsargable.
      • Condiciones Sargable donde un indice es utilizable: =, >, >=, <, <=, and BETWEEN, y algunas condiciones LIKE como
        LIKE '%Texto'
      • Condiciones Nonsargable donde un indice no es utilizable: <>, !=, !>, !<, NOT EXISTS, NOT IN, and NOT LIKE IN, OR, y algunas condiciones LIKE como LIKE 'Texto%'
    • Nested Loop join: Procesa un dataset uniendo el otro dataset mediante su recorrido. Es eficiente cuando uno de los datasets es pequeño (caso de uso del optimizador).
    • Merge join: Procesa datasets ordenados combinándolos ya que el orden es el mismo. Es muy eficiente cuando las columnas de la tabla que se unen tienen indices ordenados; es decir no hay que realizar una tipo de operación Sort. Es importante considerar el orden en la creación del indice ;-).
  • Agrupación y Ordenado de tablas
    • Sort: Realiza la ordenación de un dataset. Cuando el porcentaje es elevado por dataset grande; indica la ausencia de una filtro WHERE o quizás si es factible un indice clustered sobre la columna. El ordenado se realiza en memoria; se generará un evento Sort Warning si debe usar disco como respaldo; indica que el servidor/servicio requiere más RAM.
    • Hash Mash Agregate: Crea una tabla hash donde añade los resultados de una agregación (GROUP BY). En consultas frecuentes, quizás aplicar una vista indizada.
    • Filter: Filtra los resultados de un dataset, por ejemplo para satisface una clausula HAVING.
  • Genericas
    • Table Spool: Almacena el dataset en una tabla temporal oculta para accederlo posteriormente.
    • Compute Scalar: Obtiene un valor calculado.

Documentación adicional

En el siguiente se encuentra disponible el libro SQL Server Execution Plans.

Anuncios
Esta entrada fue publicada en Sql Azure, Sql Server. Guarda el enlace permanente.

Una respuesta a Análisis plan de Ejecución de query en Sql Server

  1. Pingback: Monitorización de SQL II | Pensando bajo la lluvia

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s